A Nightly MERGE That Outgrew Its Warehouse
A nightly job MERGEs a 300 million-row stage table into a 1.2 billion-row target on ORDER_KEY. It used to finish in 20 minutes on a 4XL warehouse. After the upstream team began emitting change events per line item instead of per order, runtime is 95 minutes and the warehouse pegs at 100% utilization. The query profile's most expensive node is the join, which emits roughly 11 billion rows from a 300 million-row input. Bytes spilled to remote storage is around 900 GB; local spill is comparatively small. A second cluster was added to the warehouse and changed nothing. The business wants the 20-minute window back before the 06:00 SLA.
1. Is resizing to a 6XL the right next move? Interpret the profile.
Read the worked answer
Not first. The profile says the problem is cardinality, not memory.
- A join node producing ~11 billion rows from a 300 million-row input is a fan-out:
ORDER_KEYis now duplicated in the source because events arrive per line item. Roughly 35× amplification happens before any spill does. More warehouse memory just lets the wrong result set be computed faster. - Multi-cluster did nothing because multi-cluster scales concurrency, not a single query - one query runs on one cluster regardless.
- Remote spill ≫ local spill does indicate memory pressure, but it is downstream of the fan-out. Fix the row count, re-profile, and only then ask whether the residual spill justifies a larger size.
So: deduplicate the source to one row per ORDER_KEY (e.g. QUALIFY ROW_NUMBER() OVER (PARTITION BY order_key ORDER BY event_ts DESC) = 1), confirm the join output returns to ~300 million rows, and re-evaluate sizing against the new profile.
2. Beyond deduplication, what else would you change about the MERGE pattern, and what would you monitor going forward?
Read the worked answer
Pattern changes:
- Prune the target. If changes are concentrated in recent dates, a delete-then-insert (or
INSERT OVERWRITE) of the affected partition range is usually cheaper and far less spill-prone than a MERGE that must scan and join the full 1.2 billion rows every night. - Guard against silent regression. Add a pre-flight check that fails the job if the stage table's duplicate rate on
ORDER_KEYexceeds a threshold, so an upstream schema/semantics change surfaces as an alert rather than a 95-minute job. - Watch the upstream contract. The change from order-level to line-item events is a grain change; the pipeline should have a target grain defined in one place.
Monitoring: query history on the specific statement (elapsed time, bytes spilled to local vs remote, partitions scanned vs total) and the join node's output row count versus input. Output-to-input amplification is the single best early-warning metric for this class of regression, and it will catch a repeat before the SLA does.