SnowPro Associate: Platform Question 238
Single answer● Standard warehouses compared to multi-clustered warehousesA retail company uses Snowflake to support both hourly ETL jobs and a business intelligence dashboard queried by hundreds of users during weekday mornings. The BI team reports that dashboard queries slow down significantly between 8:00 AM and 10:00 AM, while ETL workloads at other times perform acceptably. The data volume is stable, and query performance is generally good when only a few users are active. The company wants to reduce query queuing during peak concurrency without permanently running a larger warehouse all day. Which solution best meets this requirement?
- A
Replace the current standard warehouse with a larger standard warehouse so each query gets more CPU resources throughout the day
- B
Configure a multi-cluster warehouse with auto-scale so additional clusters start during peak dashboard usage and shut down when demand decreases
- C
Enable Snowflake Time Travel on the database so concurrent dashboard queries can read historical table versions without contention
- D
Convert the warehouse to a single-cluster warehouse with a higher auto-suspend setting so it remains warm during business hours
Show answer and explanation
Correct answer: B
Explanation
This scenario tests the distinction between a standard warehouse and a multi-cluster warehouse in Snowflake. A standard warehouse uses a single cluster of compute resources. Resizing that warehouse can make individual queries run faster, but it does not scale out concurrency the same way a multi-cluster warehouse does. A multi-cluster warehouse is intended for situations with many simultaneous users or queries, such as dashboard bursts, where query queuing becomes the bottleneck. With auto-scale, Snowflake can automatically start additional clusters when demand rises and shut them down when demand falls, helping control cost. This aligns with Snowflake documentation and best practices: scale up to improve query performance, and scale out with multi-cluster warehouses to address concurrency.
- A. Incorrect.
Incorrect. Increasing the size of a standard warehouse adds more compute resources to a single cluster and can improve the performance of individual queries, but it does not address concurrency as effectively as adding clusters. In this scenario, the issue is peak user concurrency causing query slowdowns and likely queuing during specific hours. A larger standard warehouse would also run at higher cost all day unless manually resized frequently.
- B. Correct.
Correct. A multi-cluster warehouse is designed for handling higher concurrency by automatically adding additional clusters when query demand increases. With auto-scale enabled, Snowflake can start extra clusters during the 8:00 AM to 10:00 AM surge and reduce them afterward, which matches the requirement to avoid paying for a larger compute footprint all day. This is the recommended approach when the main problem is concurrent workload pressure rather than slow single-query execution.
- C. Incorrect.
Incorrect. Time Travel is a data protection and recovery feature that allows access to historical data versions. It does not solve warehouse concurrency, queuing, or compute scaling issues. Someone might choose this option because it involves multiple users reading data, but Time Travel is unrelated to warehouse performance scaling for active workloads.
- D. Incorrect.
Incorrect. Keeping a single-cluster warehouse warm by changing auto-suspend may reduce startup latency after resume, but it does not add concurrency capacity. The core problem here is many simultaneous dashboard users during a peak window. A single-cluster warehouse, even if continuously running, can still experience query queuing under high concurrency.