ADA-C01 Question 319
Select 3Identify impact and solutions for data spillingA BI team reports that a dashboard query became much slower after data volume increased. Query Profile shows large bytes spilled to local storage and remote storage during a hash join and subsequent aggregation. The warehouse is a MEDIUM warehouse with high concurrency during business hours. As the Snowflake administrator, which TWO actions are the most appropriate to reduce the impact of data spilling for this workload?
- A
Increase the warehouse size so each query has access to more memory during execution
- B
Rewrite the query to filter rows earlier and reduce the amount of data participating in the join and aggregation
- C
Enable Search Optimization Service on both tables because it eliminates spilling for joins and aggregations
- D
Add more clusters to the multi-cluster warehouse so each individual query gets more memory on its current cluster
- E
Review join cardinality and data skew, and optimize the join logic to reduce oversized intermediate results
Show answer and explanation
Correct answers: A, B, E
Explanation
Snowflake Query Profile exposes spill metrics such as bytes spilled to local storage and remote storage. Spilling usually indicates that operators like joins, sorts, or aggregations exceeded available memory. Local spill is slower than fully in-memory processing, and remote spill is typically even more expensive because it involves writing to and reading from remote storage, often resulting in substantial latency increases.
The most appropriate responses are to either increase memory available to the query or reduce the memory required by the query. Increasing warehouse size is the standard infrastructure-side fix because larger warehouses provide more compute and memory resources. On the query-design side, pushing filters earlier, reducing unnecessary columns/rows, and improving join cardinality are best practices to shrink intermediate results. Reviewing data skew and join explosion is also important because even a correctly sized warehouse can spill when query logic produces very large intermediates.
By contrast, adding clusters to a multi-cluster warehouse addresses concurrency, not the memory footprint of a single query. Search Optimization Service can accelerate certain selective queries, but it is not a general solution for memory spills in large joins and aggregations. These recommendations align with Snowflake performance tuning guidance and Query Profile-based troubleshooting best practices.
- A. Correct.
Correct. Data spilling occurs when an operation needs more memory than is available, causing Snowflake to spill intermediate data to local disk and, if needed, remote cloud storage. Increasing warehouse size increases compute resources and memory available per node, which can reduce or eliminate spilling for memory-intensive joins, sorts, and aggregations. This is a common and practical mitigation when Query Profile shows significant spill.
- B. Correct.
Correct. Reducing the volume of rows as early as possible is a key tuning technique. Applying selective filters before joins or aggregations lowers the size of intermediate result sets, which directly reduces memory pressure and the likelihood of spilling. This is especially effective when the query currently joins large tables before filtering.
- C. Incorrect.
Incorrect. Search Optimization Service can improve performance for selective point-lookups and certain supported access patterns, but it does not generally eliminate spilling for large hash joins and aggregations. Spilling is primarily a memory and intermediate-result-size issue, not something Search Optimization is designed to solve broadly.
- D. Incorrect.
Incorrect. Adding clusters to a multi-cluster warehouse improves concurrency by allowing more queries to run in parallel on separate clusters. It does not increase the memory available to a single query already running on one cluster. This option is a common misconception: multi-cluster helps queueing and concurrent workload throughput, not per-query memory pressure.
- E. Correct.
Correct. Poor join logic, many-to-many joins, and skewed join keys can create very large intermediate datasets that exceed available memory and spill. Reviewing join conditions, eliminating unnecessary row multiplication, and addressing skew are effective ways to reduce spill. This is a realistic administrative and tuning action when Query Profile indicates heavy spill during join processing.