ADA-C01 Question 337
Select 2Implement performance improvementsA data engineering team runs a nightly ETL workload that joins a 12 TB FACT_SALES table with several smaller dimension tables. Recently, runtime has increased significantly. Query Profile for the slowest statements shows a large amount of data scanned from FACT_SALES, very low micro-partition pruning, and frequent spilling to remote storage during large joins and aggregations. The ETL warehouse is currently a single Medium warehouse. Which TWO actions would most directly improve performance for this workload?
- A
Define a clustering key on frequently filtered FACT_SALES columns used by the ETL workload, then monitor clustering depth and pruning effectiveness
- B
Resize the warehouse to a larger size for the ETL run so the joins and aggregations have more memory and compute available
- C
Convert the ETL warehouse to a multi-cluster warehouse with auto-scale enabled to improve the performance of each long-running query
- D
Increase STATEMENT_TIMEOUT_IN_SECONDS so long-running queries can finish instead of being terminated
- E
Suspend and resume the warehouse before the ETL starts to clear the local disk cache and force a fresh execution plan
Show answer and explanation
Correct answers: A, B
Explanation
The strongest performance improvements should target the symptoms shown in Query Profile. Low micro-partition pruning on a 12 TB fact table indicates that Snowflake is scanning far more data than necessary, so evaluating and implementing an appropriate clustering strategy on frequently filtered columns is a direct optimization. Frequent spill to remote storage during joins and aggregations indicates insufficient memory for the workload, so increasing warehouse size is another direct fix. By contrast, multi-cluster warehouses are intended to address concurrent query throughput rather than accelerate a single query. Increasing statement timeout only allows inefficient queries to run longer. Clearing cache by suspending and resuming the warehouse is counterproductive for performance. These recommendations align with Snowflake best practices around using Query Profile to identify scan inefficiency, partition pruning issues, and spill behavior, then tuning storage layout and warehouse size accordingly.
- A. Correct.
Correct. The scenario explicitly mentions poor micro-partition pruning on a very large fact table. In Snowflake, clustering can improve pruning when queries repeatedly filter on columns whose values are not well-correlated with the table's existing micro-partition layout. Defining an appropriate clustering key on commonly filtered columns can reduce the number of micro-partitions scanned and therefore lower I/O and execution time. Monitoring clustering depth and pruning effectiveness is important because clustering is not universally beneficial and should be validated against workload patterns and maintenance cost.
- B. Correct.
Correct. Frequent spilling to remote storage during joins and aggregations is a strong sign that the warehouse does not have enough memory for the workload. Increasing warehouse size provides more compute resources and memory per query, which can reduce or eliminate spilling and significantly improve performance for large ETL transformations. This is a standard tuning approach for memory-intensive operations in Snowflake.
- C. Incorrect.
Incorrect. Multi-cluster warehouses primarily help with concurrency by adding clusters when many queries compete for resources. They do not make a single long-running query execute faster, because one query uses one cluster. In this scenario, the issue is the performance of large individual ETL statements, not many concurrent users contending for slots.
- D. Incorrect.
Incorrect. Increasing statement timeout may prevent cancellation, but it does not address the root causes identified in Query Profile: poor pruning and remote spilling. The query would still scan excessive data and continue to use inefficient memory patterns. This option changes query governance, not performance characteristics.
- E. Incorrect.
Incorrect. Suspending and resuming a warehouse clears the local SSD cache, which usually hurts rather than helps repeated ETL performance. It also does not improve pruning or reduce memory pressure. Execution planning is handled by Snowflake automatically; clearing cache is not a recognized tuning method for this problem.