Question 70
An analytics engineering team reports this behavior: A complex aggregation produces unusually high local disk spill and runs slower on a small warehouse than in a representative benchmark. Which underlying issue should be investigated first?
Explanation
Correct Answer
The described symptoms indicate that the operator's working set exceeds the memory available to the query. The appropriate resolution is: Review query memory demand and benchmark suitable warehouse sizing or aggregation rewrites. Confirm with: The tuned run reduces spill and meets runtime targets without an excessive credit increase.
Incorrect Answers
Single-run timing cannot distinguish workload trends, parameter changes and warehouse effects. “Compare Snowflake query history metrics for comparable query signatures and time windows” would address that alternative cause, not the condition in this question: the operator's working set exceeds the memory available to the query.
“Inspect query history and transaction blockers before changing compute size” responds to this separate finding: the statement is blocked by concurrent transaction contention rather than compute speed. This question instead needs “Review query memory demand and benchmark suitable warehouse sizing or aggregation rewrites”, so the alternative does not meet the stated condition.
A highly skewed join key creates uneven parallel work. “Identify skewed key distributions and rewrite or preaggregate the affected data where justified” applies to that separate problem. The observed case is a complex aggregation produces unusually high local disk spill and runs slower on a small warehouse than in a representative benchmark; choosing this alternative would address the wrong failure mode.