SnowPro Associate: Platform Question 237
Single answer● Standard warehouses compared to multi-clustered warehousesA retail analytics team uses a Snowflake virtual warehouse to support a dashboard application. Every morning from 8:00 AM to 9:30 AM, hundreds of users submit many short, concurrent queries. Outside that period, query volume is low and predictable. The team notices that during the morning peak, queries spend significant time queued even though individual queries do not require more compute power. They want to reduce queuing during peak periods without resizing the warehouse to a larger t-shirt size for the entire day. Which solution best addresses this requirement?
- A
Replace the standard warehouse with a multi-cluster warehouse configured to auto-scale out during concurrency spikes
- B
Keep the standard warehouse and increase its auto-suspend time so it stays warm during peak periods
- C
Convert the warehouse to a multi-cluster warehouse and enable Query Acceleration Service to add clusters for concurrency
- D
Resize the standard warehouse from MEDIUM to XLARGE permanently, because only larger warehouses can reduce query queues
Show answer and explanation
Correct answer: A
Explanation
The key distinction is that standard warehouses use a single cluster, while multi-cluster warehouses can add additional clusters to handle increased concurrent query demand. In Snowflake, resizing a warehouse vertically increases compute capacity for the cluster, which may help long-running or resource-heavy queries, but it does not address concurrency as effectively as scaling out with multiple clusters. For bursty workloads such as dashboard traffic with many simultaneous short queries, a multi-cluster warehouse with auto-scaling is the recommended approach to reduce queueing while controlling cost outside peak times. This aligns with Snowflake guidance that multi-cluster warehouses are intended for managing concurrent users and query loads, whereas standard warehouse resizing is more appropriate when individual queries need more compute.
- A. Correct.
Correct. A multi-cluster warehouse is designed to address concurrency by automatically starting additional clusters when query load increases. This is appropriate when many users run short queries at the same time and queuing is caused by concurrent demand rather than by a single query needing more compute. It also lets the team avoid running a larger warehouse size all day, since clusters can scale out during busy periods and scale back in afterward.
- B. Incorrect.
Incorrect. Increasing auto-suspend time may reduce resume latency, but it does not solve query queuing caused by high concurrency. A standard warehouse still has a single cluster, so if many queries arrive at once, they can queue even when the warehouse is already running.
- C. Incorrect.
Incorrect. A multi-cluster warehouse can help with concurrency, but Query Acceleration Service is not the feature that adds clusters to handle queued concurrent queries. Query Acceleration Service is intended to improve the performance of eligible individual queries by offloading portions of query processing, not to provide multi-cluster concurrency scaling.
- D. Incorrect.
Incorrect. Resizing a standard warehouse gives each query more compute resources within a single cluster, which can help some compute-intensive workloads. However, this scenario specifically says the issue is high concurrency for many short queries, not that each query needs more power. Permanently moving to XLARGE would also increase cost throughout the day, including low-usage periods.