ADA-C01 Question 318
Single answerIdentify impact and solutions for data spillingA Snowflake administrator is investigating a dashboard query that became much slower after data volume increased. In Query Profile, the administrator sees significant local disk spilling and remote disk spilling on operators used for a large ORDER BY and aggregation. The business wants the fastest practical remediation with minimal SQL redesign. Which action should the administrator take first to reduce the impact of the spilling?
- A
Resize the warehouse to a larger size so the query has more memory available during execution
- B
Enable Search Optimization Service on the fact table to eliminate disk spilling for sort and aggregation operators
- C
Convert the permanent table to a transient table so less data is written to storage during query execution
- D
Increase the STATEMENT_TIMEOUT_IN_SECONDS parameter so the query can finish without spilling
- E
Suspend and resume the warehouse before the query runs so cache is cleared and memory is reclaimed
Show answer and explanation
Correct answer: A
Explanation
Snowflake Query Profile distinguishes between local disk spill and remote disk spill. Both indicate that intermediate data exceeded memory during execution, and remote spilling is generally more costly than local spilling. Common operators that spill include large joins, sorts, window functions, and aggregations. When a query regresses because data volume has grown and the goal is to remediate quickly with minimal SQL redesign, the best first action is usually to scale up the warehouse to provide more memory and compute capacity. After immediate stabilization, administrators should consider longer-term optimizations such as reducing rows processed earlier, improving join/selectivity patterns, avoiding unnecessarily large ORDER BY operations, and reviewing clustering or query design where appropriate. This aligns with Snowflake performance tuning guidance and use of Query Profile to identify resource bottlenecks, especially spill-related bottlenecks.
- A. Correct.
Correct. Data spilling occurs when intermediate query results exceed available memory. Spilling to local storage slows performance, and spilling further to remote storage is typically even more expensive. When the requirement is the fastest practical remediation with minimal SQL changes, increasing warehouse size is the primary first step because larger warehouses provide more compute resources and memory per cluster, which can reduce or eliminate spilling for memory-intensive operations such as large sorts, joins, and aggregations.
- B. Incorrect.
Incorrect. Search Optimization Service can improve performance for selective point-lookups and certain predicate patterns, but it does not directly solve memory pressure in sort and aggregation operators. An administrator might choose this because it is a performance feature, but it is not the appropriate first response to observed local and remote spill in Query Profile.
- C. Incorrect.
Incorrect. Table type does not address query execution memory for intermediate results. Transient tables reduce fail-safe costs and change data protection behavior, but they do not prevent spilling caused by large in-memory operations. This distractor targets the misconception that storage class changes query runtime memory characteristics.
- D. Incorrect.
Incorrect. Increasing statement timeout may allow a long-running query to continue longer, but it does nothing to reduce memory pressure or the amount of spill. The root issue is inefficient execution due to insufficient memory for the workload, not a timeout setting.
- E. Incorrect.
Incorrect. Suspending and resuming a warehouse clears local disk cache, which can actually hurt some query patterns rather than help them. Cache state is not the cause of spilling in the profile. Spilling is driven by the size of intermediate result sets relative to available memory.