COF-C03 Question 5
Single answerCompute layerA data engineering team runs hourly ELT jobs on a Snowflake virtual warehouse named ETL_WH. During each run, 15 transformation queries start at the same time and often wait in the warehouse queue before executing. The team wants to reduce query queuing during these bursts without making each individual query run on a larger cluster than necessary. Which configuration change best addresses this requirement?
- A
Resize ETL_WH from MEDIUM to XLARGE so each query gets more compute resources
- B
Convert ETL_WH to a multi-cluster warehouse with auto-scale enabled and an appropriate maximum cluster count
- C
Enable auto-suspend with a shorter timeout so queued queries can start sooner
- D
Increase the warehouse statement timeout so queued queries are not canceled during peak periods
Show answer and explanation
Correct answer: B
Explanation
Snowflake virtual warehouses provide compute resources independently from storage. When the main problem is concurrency, the recommended approach is typically to scale out using a multi-cluster warehouse rather than only scale up the warehouse size. Scaling up increases resources for a single cluster and often helps large or complex queries run faster. Scaling out with multi-cluster warehouses is specifically intended to reduce queuing when many queries arrive at the same time. Auto-scale allows Snowflake to add clusters as demand increases and remove them when demand decreases, which aligns with the team's bursty hourly workload. This matches Snowflake best practices for handling concurrent workloads in the compute layer. Relevant Snowflake documentation covers virtual warehouses, warehouse sizing, and multi-cluster warehouses for concurrency scaling.
- A. Incorrect.
Incorrect. Resizing the warehouse to XLARGE increases the compute resources available to a single cluster, which can improve the performance of individual queries. However, the scenario specifically focuses on many queries starting simultaneously and queuing. A larger single-cluster warehouse does not address concurrency as effectively as adding clusters. This is a common misconception: scaling up helps query speed, while scaling out helps concurrent workload handling.
- B. Correct.
Correct. A multi-cluster warehouse is designed to handle concurrency by automatically adding clusters when query demand increases. In this scenario, the team wants to reduce queuing during burst periods without unnecessarily increasing compute assigned to each individual query. Auto-scale with multiple clusters addresses exactly that by scaling out for concurrent users and queries.
- C. Incorrect.
Incorrect. Auto-suspend controls how long a warehouse remains running while idle before suspending. It affects cost management and startup behavior, but it does not reduce queueing for concurrent queries that are already waiting because of insufficient available compute capacity.
- D. Incorrect.
Incorrect. Statement timeout determines how long statements are allowed to run before being canceled. Increasing it may prevent cancellation of long-running or waiting queries, but it does not solve the root problem of concurrency-related queuing. The warehouse would still have the same capacity bottleneck.