SnowPro Associate: Platform Question 246
Single answer● Warehouse scalingA retail company runs hourly ELT jobs on a Snowflake virtual warehouse named ETL_WH. Most of the time, ETL_WH handles the workload well, but during the top of each hour, 15 independent transformation statements start at nearly the same time and users notice long queue times. The SQL statements are similar in complexity and already run efficiently when they start executing. The team wants to reduce queuing during these short bursts while avoiding unnecessary cost increases during quieter periods. Which configuration change is the most appropriate?
- A
Increase ETL_WH from MEDIUM to XLARGE so each individual query finishes faster, regardless of concurrency needs
- B
Configure ETL_WH as a multi-cluster warehouse with auto-scale so additional clusters start only during concurrency spikes
- C
Enable Snowpipe Streaming on ETL_WH so ingestion scaling removes query queueing for transformation jobs
- D
Convert ETL_WH to a warehouse in maximized mode and set auto-suspend to 24 hours to avoid startup delays
Show answer and explanation
Correct answer: B
Explanation
This scenario tests the difference between vertical scaling and horizontal scaling in Snowflake warehouses. Increasing warehouse size improves the resources available to each query and is best when queries themselves need more compute. In contrast, multi-cluster warehouses are intended to handle concurrency by adding compute clusters when many queries run at the same time, which reduces queueing. Because the bursts are short and intermittent, auto-scale is the most cost-efficient choice: Snowflake can start additional clusters during the spike and scale back down afterward. This aligns with Snowflake documentation and best practices around warehouse sizing and multi-cluster warehouse behavior: resize for query performance, use multi-cluster for concurrency, and use auto-scale when demand varies over time.
- A. Incorrect.
Incorrect. Resizing a warehouse vertically can improve the performance of individual queries by giving each query more compute resources, but the scenario specifically identifies queueing caused by many similar statements starting at the same time. When the main issue is concurrency rather than single-query performance, simply making one cluster larger may not be the most efficient or cost-effective solution. It may reduce runtime somewhat, but it does not address burst concurrency as directly as multi-cluster scaling.
- B. Correct.
Correct. A multi-cluster warehouse is designed to address concurrency by adding clusters when query demand exceeds the capacity of the current cluster. Auto-scale is appropriate here because the heavy concurrency occurs in short bursts at predictable times, and the company wants to avoid paying for extra compute during quieter periods. This directly aligns with Snowflake best practices: use larger warehouse sizes to improve query performance, and use multi-cluster warehouses to handle concurrent workloads and reduce queueing.
- C. Incorrect.
Incorrect. Snowpipe Streaming is related to data ingestion, not warehouse concurrency management for SQL transformation workloads. It does not scale a virtual warehouse to reduce queued transformation statements. This option reflects a common misconception that all Snowflake scaling features are interchangeable across ingestion and query processing.
- D. Incorrect.
Incorrect. Multi-cluster warehouses support auto-scale and maximized modes, but maximized mode keeps adding clusters up to the defined maximum more aggressively and is typically chosen when consistently minimizing queuing is more important than cost. The scenario emphasizes controlling cost during quiet periods, so auto-scale is the better fit. Also, setting auto-suspend to 24 hours would keep compute running unnecessarily and does not solve the concurrency problem as effectively as adding clusters on demand.