Troubleshooting and Optimization
What to know
Troubleshooting starts with row counts and samples at each stage. Common issues: type mismatches, null propagation, join multiplication, and schema drift. Optimization focuses on reducing data volume early (filter and select columns before joins), avoiding unnecessary sorts, and simplifying formulas. Understand how to read tool-level errors and where a workflow stops.
Traps
- Debugging the end of the workflow instead of the first stage where counts diverge.
- Ignoring warnings that precede errors.
- Selecting all columns through every tool, slowing execution.
- Re-sorting repeatedly when order does not matter downstream.
- Assuming a failure is logic when it is actually a connection or permission issue.
Rule of thumb
Bisect: add a sample/count after each major tool and compare to expectations. The first divergence is the culprit.
Worked example: A final total is 3x expected. Counting rows after the join shows tripling; the lookup table has three rows per key. Fix: deduplicate the lookup before the join, then confirm the count returns to baseline.