SnowPro Associate: Platform Question 247
Single answer● Warehouse scalingA retail company runs hourly ELT jobs on a Snowflake virtual warehouse named ETL_WH. During peak business hours, several long-running transformation queries overlap, and users report growing query queue times. The SQL logic is already optimized, and the company wants to reduce queueing during these peak periods without significantly increasing compute cost during quieter hours. Which warehouse configuration change is the most appropriate?
- A
Resize ETL_WH from Medium to 2X-Large so every query runs faster at all times
- B
Convert ETL_WH to a multi-cluster warehouse with auto-scale enabled and a suitable maximum cluster count
- C
Enable auto-suspend with a shorter timeout so queued queries are distributed across suspended compute resources
- D
Create a materialized view on all source tables so Snowflake can automatically eliminate warehouse queues
Show answer and explanation
Correct answer: B
Explanation
When the main problem is query queueing caused by many concurrent workloads, Snowflake best practice is to consider a multi-cluster warehouse rather than only resizing a warehouse larger. Resizing a warehouse primarily gives more resources to individual queries, while multi-cluster warehouses are intended to improve concurrency by adding clusters as demand increases. Using auto-scale with an appropriate maximum cluster count helps balance performance and cost, since extra clusters run only when needed. Auto-suspend is a cost-management feature, not a concurrency solution. Materialized views may improve specific query performance but do not replace warehouse scaling for queue reduction. This aligns with Snowflake documentation on virtual warehouses, resizing, and multi-cluster warehouses.
- A. Incorrect.
Incorrect. Increasing warehouse size can improve the performance of individual queries by providing more compute resources per query, but it does not directly address concurrency-related queueing as effectively as multi-cluster scaling. It also increases cost continuously whenever the warehouse is running, including during quieter periods. This option may help some workloads, but it is not the most appropriate choice when the main issue is overlapping queries and queue time during peak periods.
- B. Correct.
Correct. A multi-cluster warehouse is designed to handle concurrency by adding additional clusters when demand increases and removing them when demand decreases. With auto-scale enabled, Snowflake can increase the number of clusters during peak periods to reduce or eliminate query queuing, while avoiding unnecessary cost during low-usage periods. This is the best fit when the workload is already optimized and the primary issue is concurrent query demand.
- C. Incorrect.
Incorrect. Auto-suspend helps control cost by shutting down an idle warehouse after a period of inactivity, but it does not distribute queued queries across extra compute resources. Suspended warehouses do not process queries until resumed, and auto-suspend is unrelated to concurrency scaling. This distractor reflects a common misunderstanding between cost controls and performance scaling features.
- D. Incorrect.
Incorrect. Materialized views can improve performance for some repeated query patterns, but they do not serve as a warehouse scaling mechanism for general concurrency issues. In addition, creating materialized views on all source tables is neither practical nor necessary. This option confuses query optimization techniques with warehouse scaling strategies.