ARA-C01 Question 385
Single answerWarehouse spillingA data engineering team runs a nightly ETL workflow on a Snowflake warehouse named ETL_WH. One transformation query that joins a very large fact table to two dimension tables has recently slowed down significantly. Query history shows substantial bytes spilled to local storage and remote storage for this query, while CPU utilization is not saturated. The team wants to reduce runtime and improve performance for this workload with the least operational complexity. Which action is the BEST recommendation?
- A
Increase ETL_WH to a larger warehouse size so the query has more memory available and is less likely to spill
- B
Convert ETL_WH to a multi-cluster warehouse with additional clusters so the spilling query can use memory from multiple clusters
- C
Enable Query Acceleration Service for ETL_WH because spilled queries are primarily caused by insufficient compute parallelism
- D
Suspend and resume ETL_WH before the ETL job so temporary storage used by prior queries is cleared and future queries avoid spilling
Show answer and explanation
Correct answer: A
Explanation
This scenario focuses on diagnosing warehouse spilling for a single long-running ETL query. In Snowflake, bytes spilled to local storage and especially remote storage are important indicators that the query exceeded available memory for in-memory processing. Common causes include large joins, sorts, aggregations, or poor join/selectivity patterns. The most direct and lowest-complexity recommendation is usually to scale up the warehouse size, which increases memory available to the query and often reduces spill-related latency. By contrast, multi-cluster warehouses improve concurrency, not the memory available to one query. Query Acceleration Service is not the default fix for memory spilling, and warehouse restart behavior does not address runtime memory requirements. Snowflake best practices also suggest reviewing the query profile to identify spilling operators and, if needed, optimizing the SQL or data layout in addition to right-sizing the warehouse.
- A. Correct.
Correct. Warehouse spilling typically indicates the query needs more memory than is available in the virtual warehouse for operations such as large joins, sorts, or aggregations. When Snowflake spills to local disk and especially remote storage, performance degrades. Increasing the warehouse size is a standard and practical first step because larger warehouses provide more compute resources, including more memory per cluster, which can reduce or eliminate spilling for a single query.
- B. Incorrect.
Incorrect. Multi-cluster warehouses are primarily for handling concurrency by adding independent clusters for additional simultaneous queries. A single query runs on one cluster and does not combine memory across multiple clusters. Therefore, adding clusters does not usually help a single spilling query.
- C. Incorrect.
Incorrect. Query Acceleration Service can help certain eligible queries by offloading portions of scan and filter processing, but it is not the primary remedy for memory pressure causing join/sort spill. Spilling to local or remote storage usually points first to warehouse sizing or query design issues rather than a need for more clusters of parallel scan resources.
- D. Incorrect.
Incorrect. Suspending and resuming a warehouse does not prevent query spilling. Spilling occurs when an active query exceeds available memory during execution. It is not caused by leftover temporary spill files from prior queries consuming current query memory in a way that suspend/resume would fix.