ARA-C01 Question 382
Single answerWarehouse queuingA retail company runs hourly ELT pipelines and dashboard refreshes on the same Snowflake virtual warehouse, WH_ETL. During peak periods, users report intermittent delays before queries begin executing, even though individual query runtimes are acceptable once they start. Query history shows many statements in a queued state due to warehouse overload. The architect wants to reduce queueing during busy periods without permanently increasing compute costs during the rest of the day. Which configuration change is the most appropriate?
- A
Enable multi-cluster on WH_ETL with auto-scale and set the minimum cluster count to 1
- B
Increase the STATEMENT_TIMEOUT_IN_SECONDS parameter for sessions using WH_ETL
- C
Convert WH_ETL to a Snowpark-optimized warehouse to improve query admission
- D
Disable auto-suspend so the warehouse remains warm and avoids overload queueing
Show answer and explanation
Correct answer: A
Explanation
When query history shows statements queued due to warehouse overload, the issue is usually concurrency pressure on the warehouse rather than slow execution or resume latency. Snowflake best practice is to use a multi-cluster warehouse for workloads with unpredictable or bursty concurrency, especially when the goal is to improve throughput without paying for continuously larger capacity all day. With auto-scale enabled, Snowflake can start additional clusters as query demand increases and reduce them when demand falls. This makes option 1 the best architectural choice for minimizing queueing while controlling cost. By contrast, timeout parameters affect statement cancellation behavior, Snowpark-optimized warehouses target different workload characteristics, and disabling auto-suspend addresses cold starts rather than overload queues. This aligns with Snowflake documentation on virtual warehouse sizing, multi-cluster warehouses, and query queueing behavior.
- A. Correct.
Correct. Queueing caused by warehouse overload indicates concurrent demand is exceeding the capacity of the current warehouse. A multi-cluster warehouse with auto-scale can automatically add clusters during bursts of concurrency and scale back down when demand drops, which directly addresses queued queries while avoiding a full-time cost increase. Setting the minimum cluster count to 1 keeps baseline cost controlled outside peak periods.
- B. Incorrect.
Incorrect. STATEMENT_TIMEOUT_IN_SECONDS controls how long a statement can run before timing out; it does not increase warehouse concurrency or reduce the time queries spend waiting in an overload queue. Someone might choose this option if they confuse execution timeouts with query admission delays.
- C. Incorrect.
Incorrect. Snowpark-optimized warehouses are designed for memory-intensive workloads, especially certain Snowpark and ML-related processing patterns. They are not the standard solution for reducing queueing caused by concurrent SQL workload bursts. This option reflects a common misconception that any larger or specialized warehouse type improves concurrency management.
- D. Incorrect.
Incorrect. Disabling auto-suspend may reduce resume latency when a warehouse is starting up, but that is different from overload queueing. In this scenario, queries are queued because the active warehouse is saturated during peak concurrency, not because it is suspended. Keeping the warehouse running longer would increase cost without directly solving the overload issue.