COF-C03 Question 265
Single answerQueuingA data engineering team uses a single X-SMALL virtual warehouse to run both scheduled ELT transformations and ad hoc analyst queries. During business hours, analysts report that simple SELECT statements sometimes wait several minutes before starting, even though query execution itself is fast once it begins. The Snowflake administrator wants to reduce these wait times without manually resizing the warehouse throughout the day. Which configuration change would best address this problem?
- A
Enable multi-cluster on the warehouse with auto-scale so additional clusters can start when concurrent demand increases
- B
Increase the STATEMENT_TIMEOUT_IN_SECONDS parameter so queued queries are allowed to wait longer before failing
- C
Convert the warehouse to a Snowpark-optimized warehouse to improve concurrency for SQL queries
- D
Disable AUTO_SUSPEND so the warehouse stays running and avoids query queuing during peak periods
Show answer and explanation
Correct answer: A
Explanation
Snowflake query queuing commonly occurs when a warehouse lacks enough resources to handle concurrent statements. In mixed workloads, a small single-cluster warehouse can become saturated, causing later queries to wait in queue even if those queries are short. The best-practice solution for concurrency-related queueing is to use a multi-cluster warehouse with auto-scale, which adds clusters as needed for additional simultaneous workload demand. By contrast, increasing statement timeout only changes how long users can wait, not the cause of the wait. Snowpark-optimized warehouses target different workload characteristics, primarily memory-intensive processing rather than standard SQL concurrency. AUTO_SUSPEND settings mainly affect resume behavior and cost efficiency, not sustained peak concurrency. This aligns with Snowflake guidance on warehouse scaling and multi-cluster warehouses for managing concurrent user and query demand.
- A. Correct.
Correct. Query queuing in this scenario is caused by concurrent workload pressure on a single small warehouse. Configuring the warehouse as a multi-cluster warehouse with auto-scale allows Snowflake to add clusters automatically when concurrency increases, which helps reduce or eliminate provisioning and overload-related queueing for independent queries. This is the standard Snowflake approach when the goal is to improve concurrency without constant manual resizing.
- B. Incorrect.
Incorrect. STATEMENT_TIMEOUT_IN_SECONDS controls how long a statement can run or wait before timing out, depending on context, but it does not reduce queueing. Increasing it would only allow users to wait longer, not solve the underlying concurrency bottleneck.
- C. Incorrect.
Incorrect. Snowpark-optimized warehouses are designed for workloads needing more memory and compute resources for certain Snowpark and ML-style processing patterns. They are not the recommended feature for improving general SQL query concurrency or reducing queueing for mixed BI/ELT workloads.
- D. Incorrect.
Incorrect. Disabling AUTO_SUSPEND may reduce cold-start delays from resuming a suspended warehouse, but that is different from concurrency-based query queueing. In this case, queries already start slowly only during busy periods because the single warehouse is saturated. Keeping it running would not meaningfully address overload from competing workloads.