COF-C03 Question 6
Single answerCompute layerA retail company runs ELT pipelines every 15 minutes using a Snowflake virtual warehouse named ETL_WH. During peak business hours, analysts also use ETL_WH for ad hoc queries, causing pipeline runtimes to spike because some statements wait in the warehouse queue before starting. The data engineering team wants to reduce queued queries during these periods without manually resizing the warehouse throughout the day, while also avoiding unnecessary cost when demand is low. Which Snowflake configuration change best addresses this requirement?
- A
Enable multi-cluster on ETL_WH with auto-scale so additional clusters start when concurrency increases and scale back down when demand drops
- B
Convert ETL_WH to a Snowpark-optimized warehouse because those warehouses are designed to eliminate query queueing for mixed workloads
- C
Increase the auto-suspend time on ETL_WH so the warehouse remains warm longer and queued queries can begin immediately
- D
Enable Query Acceleration Service on ETL_WH because it automatically adds compute clusters when many users submit queries at the same time
Show answer and explanation
Correct answer: A
Explanation
The best answer is to enable a multi-cluster virtual warehouse with auto-scale. In Snowflake, virtual warehouses provide compute resources independently from storage. For situations where many users or jobs submit queries concurrently, a single-cluster warehouse can experience query queuing. Multi-cluster warehouses are designed to improve concurrency by automatically adding clusters when needed and removing them when demand subsides. This makes them well suited for mixed workloads such as scheduled ELT plus interactive analytics. By contrast, resizing a warehouse vertically increases resources in one cluster and may help some performance issues, but it does not address concurrency as directly as adding clusters. Auto-suspend affects idle behavior, not active queueing. Query Acceleration Service is intended to accelerate certain eligible queries, not to manage concurrency spikes across many users. These behaviors are consistent with Snowflake documentation on virtual warehouses, multi-cluster warehouses, auto-suspend/auto-resume, and Query Acceleration Service.
- A. Correct.
Correct. A multi-cluster warehouse with auto-scale is specifically designed to handle higher concurrency. When query demand increases and statements begin to queue, Snowflake can automatically start additional clusters up to the configured maximum. When demand falls, clusters are removed, helping control cost. This directly addresses the requirement to reduce queueing without manual resizing and to avoid overpaying during low-demand periods.
- B. Incorrect.
Incorrect. Snowpark-optimized warehouses are intended for workloads that require more memory and compute resources, such as certain Snowpark and ML-style processing patterns. They are not the standard solution for concurrency-driven query queueing caused by multiple users sharing a warehouse. Choosing this option reflects a misconception that all specialized warehouse types solve concurrency issues.
- C. Incorrect.
Incorrect. Auto-suspend controls how long a warehouse stays running while idle before shutting down. Keeping a warehouse warm can reduce startup latency after idle periods, but it does not add compute capacity or reduce queueing caused by too many concurrent statements on an active warehouse. This is a common confusion between resume latency and concurrency scaling.
- D. Incorrect.
Incorrect. Query Acceleration Service can help speed up eligible individual queries by offloading portions of scan and filter processing, but it is not the feature used to scale a warehouse horizontally for concurrency. It does not function as automatic multi-cluster concurrency management for general queued workloads.