ADA-C01 Question 291
Single answerDomain 4.0: Performance Monitoring and Tuning (20%)A retail company runs hourly ELT pipelines on a MEDIUM virtual warehouse and has recently seen the end-to-end load window exceed its SLA. An administrator reviews Query Profile for several slow COPY INTO and transformation statements and notices that many queries spend significant time in the remote disk I/O and spilling phases during peak hours. Warehouse load history also shows sustained high concurrency during those periods. The company wants the fastest improvement with minimal SQL code changes. Which action should the administrator take first?
- A
Increase the virtual warehouse size so each query has more memory and compute available
- B
Enable Search Optimization Service on all tables used by the ELT pipeline
- C
Cluster the largest target tables on the load timestamp column
- D
Convert all frequently used tables in the pipeline to temporary tables
Show answer and explanation
Correct answer: A
Explanation
This question tests applied performance tuning using Snowflake monitoring artifacts. In Query Profile, spilling to local or remote storage is a strong indicator that operators did not have enough memory for in-memory execution. When this coincides with high warehouse concurrency, the administrator should first consider warehouse sizing because a larger warehouse provides more compute and memory per running query. This is often the quickest way to reduce spill and improve elapsed time for ETL/ELT workloads without changing application SQL. Snowflake best practices for performance troubleshooting emphasize using Query Profile, warehouse load indicators, and tuning compute size appropriately before making unnecessary table design changes. Search Optimization Service is for selective access patterns, and clustering is useful for pruning-based scan reduction, but neither is the most direct fix for spill-driven bottlenecks. Relevant Snowflake documentation areas include Query Profile, optimizing warehouses for performance, and understanding data spill to local and remote storage during query execution.
- A. Correct.
Correct. Query Profile indicators such as remote disk I/O and spilling usually point to memory pressure during execution. When this happens alongside sustained high concurrency, the most direct first action is to scale up the warehouse so individual queries have access to more compute resources and memory. This often reduces spilling to local or remote storage and improves performance without requiring SQL rewrites. For a time-sensitive SLA issue, resizing the warehouse is typically the fastest operational fix.
- B. Incorrect.
Incorrect. Search Optimization Service is designed to improve selective point-lookups and certain highly selective predicate queries, not to address broad ELT workloads experiencing spill due to memory pressure and concurrency. Enabling it broadly would add cost and is unlikely to be the best first response to COPY and transformation statements dominated by spilling.
- C. Incorrect.
Incorrect. Clustering can help prune micro-partitions for large tables when queries filter on clustering keys, but it does not directly solve warehouse memory pressure or concurrent execution spill for ELT workloads. In this scenario, the issue is visible in Query Profile as remote disk I/O and spilling, which suggests compute sizing is the more immediate tuning lever.
- D. Incorrect.
Incorrect. Temporary tables can reduce some metadata or persistence considerations in specific workflows, but converting all frequently used tables to temporary tables does not address the root cause shown in Query Profile. It also introduces manageability and session-scope limitations, making it an unsuitable first action for this performance problem.