The Late-Arriving Lookup
A daily workflow joins a 1.2M-row transaction stream (left input) to a 40,000-row customer dimension (right input) on CustomerID, keeping only matched rows. The stream arrives at 06:00; the dimension file is refreshed by an overnight job that usually finishes at 05:30 but occasionally at 07:00. On late days the Join outputs zero rows and downstream outputs are empty, but the run reports success. The team wants the workflow to fail loudly rather than publish empty files, and to avoid re-reading the 1.2M-row stream when only the dimension changes.
1. Diagnose why the run succeeds with empty output and propose a control-flow change that fails the run instead.
Read the worked answer
The Join is an inner-style match: if the right input is empty (or stale/mis-keyed), every left record is unmatched and routed to the Left output, so the J output is empty but the tool itself reports no error. A successful run status therefore only means 'tools executed', not 'data was correct'.
Add an explicit data-quality gate after the Join: count J records (or compare J count to a threshold such as 95% of left rows) and use a Test tool (or a Message tool with an expression) configured to error when the count falls below the threshold. That converts a silent empty publish into a hard failure and stops downstream Output Data tools from writing.
2. Propose a caching strategy so the 1.2M-row stream is not re-read when only the dimension changes.
Read the worked answer
Split the workflow at the join boundary. Stage the large stream once to a persistent Alteryx database (.yxdb) with an Output Data tool, and have the join read that staged file rather than the source. When only the dimension changes, re-run only the downstream branch (or use a Cache and Run Workflow option on the join's upstream tools during development) so the 1.2M rows are not re-extracted.
Caveats to state: cached/staged data must be invalidated when the stream itself changes (date-partition the staging file by run date and clean up old partitions), and the gate from the first task must still run so a stale dimension cannot silently pass.