System Design · medium
A model that ran in 2 minutes now takes 40. How do you diagnose and fix it?
Asked in Analytics Engineer interviews, in the System Design round.
Short answer
Check the query plan and the volume first: usually a full refresh where an incremental would do, a fan-out join from a non-unique key, or a missing partition/cluster filter. Fix by making it incremental, deduping the join key, or partitioning on the filter column. Measure before and after, it feels faster is not a diagnosis.
How to answer it
Diagnose before optimizing
Twenty times slower is almost never gradual growth. Something changed, so find what rather than start tuning.
Data volume did the source suddenly grow, or is a filter no longer
filtering? Compare row counts week over week
Data skew one key now holds most of the rows, so one worker does
all the work while the rest idle
The query changed a join added, a filter removed, a CTE now referenced
twice so it materialises twice
Loss of
incrementality a full refresh where an incremental run was intended,
which is the classic cause of exactly this symptom
Statistics stale table statistics, so the planner picks a bad join
order
Infrastructure a smaller warehouse, more concurrency, contentionRead the query plan
The plan is the evidence. Look for a join order that changed, a broadcast join that became a shuffle because a table outgrew the broadcast threshold, or a spill to disk saying the working set no longer fits in memory. Compare against the plan from when it was fast, if you kept one.
The fixes, in order of leverage
- Restore incrementality. A model that quietly went to full refresh is the most common single cause, and the fix takes minutes.
- Filter earlier. Push predicates before joins, not after.
- Select fewer columns. Columnar storage means unused columns are free only if you do not select them.
- Fix skew: salt the key, or handle the dominant key separately.
- Partition or cluster on the filter column.
- Only then consider more compute, since that hides the problem and bills you monthly for it.
Key points
- A twentyfold change means something changed. Find the change first.
- Compare row counts and query plans against a known-good run.
- Loss of incrementality is the usual culprit for exactly this pattern.
- Skew shows as one long-running task while the rest finish quickly.
- Scaling the warehouse is the last resort, not the first, because it is a recurring cost.
FAQ
- How do I spot skew? Look at per-task duration. A uniform workload finishes together; a skewed one has one straggler.
- What is a spill? The engine ran out of memory and wrote intermediate data to disk. It is a large, visible slowdown in the plan.
- Should I add an index? On a columnar warehouse, clustering or partitioning is the equivalent lever.
The 30-second answer
Twenty times slower means something changed rather than gradual growth, so I would diagnose before tuning. First compare row counts week over week to see whether the input grew or a filter stopped filtering, then read the query plan and compare it against a run from when it was fast.
The most common single cause of exactly this pattern is a model that was incremental and is now doing a full refresh, so I would check that early because the fix takes minutes.
After that I would look for skew, which shows up as one task running long while the rest finish, and for a spill to disk in the plan. The fixes in order are restoring incrementality, filtering earlier, selecting fewer columns, then partitioning or clustering. Scaling the warehouse is last, because it hides the problem and bills you for it every month.
In the interview
- What they are checking: that you look for the change, and that you can name the three usual causes.
- Common mistake: adding a bigger warehouse. A fan-out join is 400 times the work, and no size fixes that.