SnowPro Associate: Platform Question 250
Single answer○ Scaling in or outA data engineering team runs hourly ELT jobs on a Snowflake virtual warehouse named ETL_WH. Most of the time, query performance is acceptable, but during the top of each hour many transformation statements start at once and some spend several minutes in the queue before running. The team wants to reduce queueing during these short concurrency spikes without permanently increasing compute size for the entire hour. Which change should they make?
- A
Resize ETL_WH from MEDIUM to LARGE so each individual query gets more compute resources at all times
- B
Convert ETL_WH to a multi-cluster warehouse with auto-scale enabled so additional clusters can start during concurrent bursts
- C
Enable auto-suspend with a shorter timeout so the warehouse resumes faster when many queries arrive together
- D
Create a materialized view on the largest source table so more queries can run in parallel without queueing
Show answer and explanation
Correct answer: B
Explanation
This scenario tests the difference between scaling up and scaling out in Snowflake. Scaling up means increasing the warehouse size, which provides more compute resources per cluster and is typically useful when individual queries need more CPU, memory, or I/O throughput. Scaling out means using a multi-cluster warehouse so Snowflake can add clusters to handle higher concurrency. For periodic spikes where many queries begin at once and queue, Snowflake best practice is to use a multi-cluster warehouse with auto-scale enabled. This allows the system to add clusters during bursts and reduce them later, helping balance performance and cost. Snowflake documentation distinguishes these two approaches clearly: use larger warehouse sizes to improve performance of single workloads, and use multi-cluster warehouses to address concurrent query demand and reduce queuing.
- A. Incorrect.
Incorrect. Scaling up from MEDIUM to LARGE gives each running query access to more compute resources, which can improve execution time for compute-heavy queries. However, the scenario specifically describes short periods of many statements starting at once and waiting in the queue due to concurrency pressure. Increasing warehouse size does not directly address concurrency the way scaling out does, and it would increase cost for the full period the larger warehouse is running.
- B. Correct.
Correct. A multi-cluster warehouse is designed to address concurrency by scaling out horizontally. With auto-scale enabled, Snowflake can add clusters when query load increases and remove them when demand drops. This is the best fit for periodic bursts where the goal is to reduce queuing without keeping a larger single cluster running all the time.
- C. Incorrect.
Incorrect. Auto-suspend controls how quickly a warehouse stops after inactivity, and auto-resume starts it again when new work arrives. These settings help manage cost and convenience, but they do not solve query queuing caused by many concurrent statements on an already running warehouse.
- D. Incorrect.
Incorrect. Materialized views can improve performance for some repeated query patterns by precomputing results, but they do not address the core issue described here: many jobs arriving simultaneously and waiting for compute capacity. The bottleneck is concurrency management, not necessarily repeated scan cost on one table.