SnowPro Associate: Platform Question 240
Single answer● Standard warehouses compared to multi-clustered warehousesA BI team runs many short dashboard queries during business hours. Performance is acceptable when only a few users are active, but at peak times users report long waits before queries start running. Query profiles show little time spent scanning data once execution begins, but warehouse load graphs show many queries queued for provisioning and overload. The team wants to reduce queueing during peak concurrency without manually resizing throughout the day. Which Snowflake warehouse configuration is the best fit for this requirement?
- A
Use a larger standard warehouse so each individual query runs faster, which eliminates concurrency queueing
- B
Use a multi-cluster warehouse with auto-scale enabled so additional clusters can start during periods of high concurrent demand
- C
Keep a standard warehouse and disable auto-suspend so queries are not delayed by warehouse resume time
- D
Use a single-cluster warehouse with more aggressive result caching, because queued queries are caused mainly by cache misses
Show answer and explanation
Correct answer: B
Explanation
This scenario distinguishes standard warehouses from multi-cluster warehouses based on the type of performance problem. A standard warehouse uses a single cluster, even if resized larger, and is often suitable when the goal is to improve the speed of individual queries. A multi-cluster warehouse is intended for higher concurrency workloads, especially when many users submit queries at the same time and queueing becomes the bottleneck. In Snowflake best practices, if queries are waiting because of concurrency or overload, multi-cluster warehouses with auto-scaling are the recommended approach. If queries are simply running slowly, resizing a warehouse may be more appropriate. Snowflake documentation on warehouses explains that multi-cluster warehouses can automatically start additional clusters to support concurrent workloads, making them well suited for BI and dashboarding use cases with variable peak demand.
- A. Incorrect.
Incorrect. Increasing the size of a standard warehouse can improve the performance of individual queries by adding more compute resources to a single cluster, but it does not address concurrency in the same way as adding clusters. In this scenario, the main issue is queueing during peak concurrent demand, not slow execution after queries begin. A larger standard warehouse may help somewhat, but the best fit for sustained concurrency spikes is a multi-cluster warehouse.
- B. Correct.
Correct. Multi-cluster warehouses are designed to handle higher concurrency by automatically adding additional clusters when demand increases, then removing them when demand drops, depending on the configuration. This is the most appropriate choice when many users submit short queries at the same time and the main symptom is queueing rather than slow per-query execution.
- C. Incorrect.
Incorrect. Disabling auto-suspend can reduce delays related to warehouse resume, but the scenario specifically mentions queueing caused by concurrency and overload during peak usage. Leaving a warehouse running continuously may increase cost and does not solve the core problem of too many concurrent queries for a single cluster to handle efficiently.
- D. Incorrect.
Incorrect. Result caching can reduce repeated query execution when the same query can reuse cached results, but it does not directly solve warehouse concurrency problems. The scenario indicates that users are experiencing queueing before execution starts, which points to insufficient concurrent compute capacity rather than primarily a caching issue.