ARA-C01 Question 384
Select 2Warehouse spillingA data engineering team runs a nightly transformation that joins two very large fact tables, performs a window function with ORDER BY, and then writes the result to a reporting table. The job runs on a MEDIUM warehouse and frequently exceeds its SLA. Query history shows significant bytes spilled to local storage and remote storage for the longest-running statements. The team wants to reduce elapsed time for this workload without changing the business logic. Which TWO actions are most appropriate?
- A
Resize the warehouse to a larger size so each query has access to more memory and compute resources
- B
Convert the warehouse to a multi-cluster warehouse with additional clusters to reduce spilling within each query
- C
Rewrite the transformation to reduce intermediate result size early, such as filtering sooner or pre-aggregating before the largest join/window steps
- D
Enable Query Acceleration Service because it is designed to eliminate all local and remote spilling for memory-intensive operators
- E
Increase AUTO_SUSPEND to keep the warehouse warm, which directly reduces spilling during large sorts and joins
Show answer and explanation
Correct answers: A, C
Explanation
Warehouse spilling in Snowflake occurs when intermediate data for query operators exceeds available memory, causing data to spill first to local storage and, if necessary, to remote storage. Remote spilling is generally more expensive and is a strong indicator that the workload is memory-constrained. For a single long-running query, the most effective remedies are usually to increase the warehouse size, giving the query more memory and compute, and/or optimize the SQL plan to reduce intermediate data earlier in execution. Common examples include filtering earlier, reducing unnecessary columns, pre-aggregating, and avoiding unnecessarily large sorts. By contrast, multi-cluster warehouses are designed mainly for concurrency scaling rather than accelerating one memory-bound query. Similarly, AUTO_SUSPEND settings affect cost and startup behavior, not operator memory usage. Snowflake documentation and best practices around performance optimization emphasize using Query Profile and Query History spill metrics to identify memory-intensive operators and then addressing them through warehouse sizing or query design improvements.
- A. Correct.
Correct. Spilling typically indicates that a query's working set exceeded available memory for certain operators such as joins, aggregations, and sorts. Moving from a MEDIUM warehouse to a larger warehouse increases the memory and compute available to a single query, which can reduce or eliminate spill to local and remote storage and often improve elapsed time for memory-intensive statements.
- B. Incorrect.
Incorrect. Multi-cluster warehouses primarily improve concurrency by adding clusters for additional simultaneously running queries. They do not make a single query run with more memory per cluster, so they are not the primary remedy for spilling within one long-running query.
- C. Correct.
Correct. Reducing the volume of data processed by expensive operators is a best-practice way to address spilling. Applying selective filters earlier, pre-aggregating where appropriate, or otherwise shrinking intermediate results before large joins, sorts, or window functions can reduce memory pressure and spill volume without changing the required business outcome.
- D. Incorrect.
Incorrect. Query Acceleration Service can help certain parts of eligible queries complete faster by offloading scan/filter-type work, but it is not a general-purpose feature that guarantees elimination of spilling for memory-heavy operators like large joins and sorts. Treating it as a direct fix for all spilling is a misconception.
- E. Incorrect.
Incorrect. Increasing AUTO_SUSPEND may reduce resume frequency, but it does not increase per-query memory or change how operators manage working data. Warehouse warmth is not the direct cause of bytes spilled to local or remote storage during large joins and sorts.