ARA-C01 Question 383
Single answerWarehouse queuingA data engineering team runs hourly ELT pipelines on a Snowflake virtual warehouse named ETL_WH. During peak business hours, analysts also submit ad hoc queries to the same warehouse. Recently, pipeline tasks have started missing their SLA because many statements spend significant time in the queue before execution. The architect must reduce warehouse queuing without unnecessarily increasing compute cost during off-hours. Which solution best addresses this requirement?
- A
Convert ETL_WH to a multi-cluster warehouse with auto-scale enabled and configure an appropriate minimum and maximum cluster count.
- B
Increase the STATEMENT_QUEUED_TIMEOUT_IN_SECONDS parameter so queued statements wait longer instead of failing.
- C
Enable Query Acceleration Service on ETL_WH so queued statements can bypass the warehouse queue.
- D
Create a materialized view for the largest ETL target table so the warehouse can process more concurrent statements without queuing.
Show answer and explanation
Correct answer: A
Explanation
Warehouse queuing in Snowflake commonly occurs when more statements are submitted than the virtual warehouse can execute concurrently with its available resources. When the primary problem is concurrency, the recommended architectural solution is often a multi-cluster warehouse, especially for mixed or unpredictable workloads. With auto-scale enabled, Snowflake can automatically add clusters as demand rises and remove them when demand drops, which directly addresses the requirement to reduce queuing while controlling cost. By contrast, increasing queue timeout only changes failure behavior, not throughput. Query Acceleration Service helps certain long-running queries but is not intended to eliminate warehouse queuing. Materialized views can improve specific query performance patterns but do not address general concurrency contention. Snowflake documentation on virtual warehouses and multi-cluster warehouses describes using multi-cluster auto-scaling to manage concurrent user and query workloads and reduce queuing.
- A. Correct.
Correct. A multi-cluster warehouse is specifically designed to handle concurrency by adding clusters when query load increases, which helps reduce or eliminate queuing. Using auto-scale with suitable min/max cluster settings allows Snowflake to add compute during peak periods and scale back down during quieter times, aligning with the requirement to avoid unnecessary cost off-hours. This is the most direct architecture choice for concurrency-driven queuing.
- B. Incorrect.
Incorrect. STATEMENT_QUEUED_TIMEOUT_IN_SECONDS controls how long a statement can remain queued before Snowflake aborts it, but it does not reduce queue time or increase available compute capacity. Raising it may hide the symptom by allowing statements to wait longer, but SLA performance would likely remain poor or worsen.
- C. Incorrect.
Incorrect. Query Acceleration Service can improve the performance of eligible queries by offloading parts of scan and aggregation processing, but it is not a mechanism for resolving warehouse concurrency queues. Queued statements still require available warehouse resources to begin execution. This option reflects a common misconception between improving query runtime and addressing queuing caused by limited concurrent execution capacity.
- D. Incorrect.
Incorrect. Materialized views can reduce the compute required for some repeated query patterns, but they do not directly solve warehouse queuing caused by mixed workload concurrency. In this scenario, the issue is many statements waiting to start, not a single repeatedly expensive query pattern that would be best addressed with precomputed results.