A 40-Minute Workflow That Should Take Four
A nightly workflow reads 60 CSV files (~200 MB each, ~11 GB total) with one Input Data tool using a wildcard, joins the result to a 6M-row reference table, applies eight Formula tools, and then - on the last tool before the output - filters to a single region, which keeps 4% of the rows. It takes 40 minutes, on a good day throws a memory error, and writes a .yxdb for a Tableau extract. The analyst is mid-development on one of the formulas and has been re-running the whole workflow after every edit.
1. Where is the time going, and what three changes would you make first? Justify each against the fact that 96% of the data is eventually thrown away.
Read the worked answer
The shape of the problem. Every expensive operation - the 11 GB read, the 6M-row join, and eight formula passes - runs over the full dataset to produce a 4% answer. That is three separate placements of the filter that are all in the wrong spot.
1. Move the region filter as early as it can legally go. Immediately after the join, not at the end. Then the eight Formula tools, and anything else downstream, touch ~4% of the rows. Better still: if the reference table itself carries region, filter the reference side to that region first and the join becomes 11 GB against a few thousand rows. The only reason to keep the filter late is if a formula changes the region value - check that before moving it.
2. Reduce columns with a Select tool at ingestion. The join and the formulas need a handful of fields, not all of them. Dropping the unused columns before the join cuts memory and shuffle volume for everything downstream, and it costs nothing.
3. Right-size the types with Auto Field after ingestion, so long strings and needlessly wide numerics are not carried through the whole run.
Measure, don't guess. Use the per-tool timing and row counts in the Results pane to confirm the join and the read dominate. If the reference table lives in a database, the strongest version of this is Connect In-DB: filter and aggregate inside the database and pull back only the 4%. That removes the 6M-row join from the Designer process entirely.
2. How do you shorten the edit-test cycle for the formula the analyst is developing, without changing the production design?
Read the worked answer
Cache the expensive prefix. Right-click the tool that feeds the formula branch and use Cache and Run Workflow. The 11 GB read and the join execute once; every subsequent edit re-runs only the tools downstream of the cache. For a formula tweak that is the difference between 40 minutes and seconds.
Two caveats worth stating out loud.
- The cache belongs to the open session. It disappears when the file is closed, and it is not part of what gets scheduled. It is a development convenience, and anyone who assumes production benefits from it will be disappointed.
- A cache freezes the data at that point. If a change to an upstream tool matters for what you are testing, the cache is now lying to you - clear it.
Other levers.
- Disable containers not under test (Tool Configuration → disable) rather than deleting tools.
- Temporarily insert a Sample tool at 1% upstream of the branch to iterate on logic, then remove it before promoting; leave a comment so it is not forgotten.
- Build the expression against a small Text Input of hand-made edge cases - one null, one empty string, one boundary value - before running it against real data. Logic bugs surface faster on ten crafted rows than on 11 GB.
Then prove the change is safe. Re-run uncached, compare row counts and a control total against the previous run, and only then promote. A cached run never validates a workflow.
3. The wildcard read is part of why memory blows up. Would you switch to a batch macro, and what does that change about runtime and reliability?
Read the worked answer
Yes, but not as a standalone fix. A batch macro driven by a Directory tool processes one file per iteration, so peak memory is one file's worth of data plus whatever accumulates, instead of 11 GB plus the join's working set. On 60 files the per-iteration overhead is negligible; the win is memory, and memory is what is causing the hard failures.
It also buys reliability. A wildcard read fails the whole run when one file is malformed - a truncated download, a schema drift, an encoding oddity - and you find out at 2 a.m. from a failed job. A batch macro can route a bad iteration to an error output and keep going, so the night's output is 59 files plus one alert rather than nothing plus one alert.
What it does not fix. If the macro Unions all 60 outputs and then joins to the 6M-row reference, you are back to holding the full dataset in memory. The macro must be paired with the early filter and the Select: either filter inside the macro so only ~4% of each file is emitted, or emit the reduced field set and filter immediately after the Union, before the join. Memory stays proportional to the answer, not the input.
Costs to weigh. More moving parts to maintain, and searching behaviour inside a batch macro is harder to debug than in a flat workflow. Do it in that order: filter early, trim columns, measure, and reach for the batch macro when the read itself is the constraint - which, at 11 GB against a 40-minute runtime, it almost certainly is.