COF-C03 Question 264
Single answerQueuingA data engineering team uses a single Snowflake virtual warehouse named ETL_WH to run nightly ELT jobs. Recently, the team added several parallel transformation tasks, and users report that some statements spend a long time in the queue before execution begins. The team wants to reduce queuing without manually resizing the warehouse each night. Which configuration change would best address this requirement?
- A
Convert ETL_WH to a multi-cluster warehouse with auto-scale enabled so additional clusters can start when concurrency increases
- B
Enable Query Acceleration Service on ETL_WH so queued statements can bypass the warehouse and execute sooner
- C
Increase the STATEMENT_TIMEOUT_IN_SECONDS parameter so queued statements are allowed more time to start
- D
Create a materialized view on the largest source table so Snowflake has fewer statements to queue
Show answer and explanation
Correct answer: A
Explanation
When statements are queued on a virtual warehouse, a common cause is concurrency pressure: more statements are trying to run than the warehouse can effectively process at once. In Snowflake, multi-cluster warehouses are specifically intended to support higher concurrency by automatically adding clusters when demand increases, especially when configured in auto-scale mode. This is the most appropriate solution when the goal is to reduce queuing without requiring manual intervention.
The other options address different concerns. Query Acceleration Service is for speeding up eligible queries, not solving queue buildup from concurrency. STATEMENT_TIMEOUT_IN_SECONDS affects timeout behavior, not queue depth. Materialized views may reduce runtime for some queries but do not directly address warehouse queuing under concurrent ELT workloads.
This aligns with Snowflake best practices around using multi-cluster warehouses for concurrency scaling and queue reduction in busy workloads.
- A. Correct.
Correct. A multi-cluster warehouse is designed to help manage concurrent workload demand. With auto-scale enabled, Snowflake can automatically add clusters when query concurrency increases, which reduces or eliminates queuing caused by too many statements competing for the same warehouse resources. This directly matches the requirement to reduce queuing without manual resizing each night.
- B. Incorrect.
Incorrect. Query Acceleration Service can improve the performance of eligible queries by offloading portions of scan and aggregation work, but it is not a solution for warehouse queuing caused by concurrency limits. Queries still require warehouse resources and QAS does not allow statements to bypass a busy warehouse queue.
- C. Incorrect.
Incorrect. Increasing STATEMENT_TIMEOUT_IN_SECONDS changes how long a statement can run or wait before timing out, but it does not reduce the underlying queue. This may let queued statements wait longer, but it does not improve concurrency or throughput.
- D. Incorrect.
Incorrect. Materialized views can improve performance for some repeated query patterns, but they do not directly solve queuing caused by many concurrent ELT statements on the same warehouse. The problem described is resource contention and concurrency, not repeated query optimization.