SnowPro Associate: Platform Question 239
Single answer● Standard warehouses compared to multi-clustered warehousesA retail company runs large nightly ETL jobs and also serves many short, interactive BI queries from analysts during business hours. The BI team reports slowdowns only when many users submit queries at the same time. Query profiles show little evidence of data skew or inefficient SQL, but users are waiting in queue during peak periods. The data engineering team wants to reduce query queuing for the BI workload without manually resizing the warehouse throughout the day. Which Snowflake warehouse configuration is the best fit for this requirement?
- A
Use a standard warehouse and increase its size to 5XL so queued queries are eliminated permanently
- B
Use a multi-cluster warehouse in Auto-scale mode so additional clusters can start when concurrency increases
- C
Use a standard warehouse with auto-suspend disabled so compute remains available and queues are avoided
- D
Use a multi-cluster warehouse only for the ETL workload because multi-cluster warehouses are designed primarily to speed up single-query performance
Show answer and explanation
Correct answer: B
Explanation
The key distinction is that a standard warehouse uses a single cluster of compute resources, while a multi-cluster warehouse can use multiple clusters to handle concurrent query loads. For many simultaneous dashboard and ad hoc BI queries, the main symptom is often query queuing rather than slow execution of individual queries. Snowflake best practices and documentation describe multi-cluster warehouses as a solution for managing concurrency, especially for user-facing analytics workloads with variable demand. By contrast, resizing a standard warehouse mainly increases compute capacity within one cluster and is more directly associated with improving performance of individual workloads, not dynamically scaling out for many concurrent users. Auto-suspend settings affect cost and startup behavior, but not concurrency scaling. Therefore, a multi-cluster warehouse in Auto-scale mode is the most appropriate configuration for this scenario.
- A. Incorrect.
Incorrect. Increasing the size of a standard warehouse adds more compute resources to a single cluster and can improve query performance, but it is not the best targeted solution when the main problem is concurrent users waiting in queue. A much larger standard warehouse may reduce some queuing, but it can be costly and still does not provide the concurrency scaling behavior that multi-cluster warehouses are designed for.
- B. Correct.
Correct. Multi-cluster warehouses are intended to address concurrency by allowing Snowflake to start additional clusters when query demand increases. In Auto-scale mode, Snowflake can add clusters up to the defined maximum and remove them when demand falls. This is the best fit when the workload consists of many simultaneous BI queries causing queueing, especially when the team wants to avoid manual resizing throughout the day.
- C. Incorrect.
Incorrect. Disabling auto-suspend keeps the warehouse running, which can reduce resume latency, but it does not solve the core issue of too many concurrent queries competing for the resources of a single cluster. Queuing due to concurrency can still occur on a continuously running standard warehouse.
- D. Incorrect.
Incorrect. This reverses the main use case. Multi-cluster warehouses are primarily useful for handling higher concurrency and reducing queuing, not for making a single query run faster. ETL workloads often benefit from appropriately sized standard warehouses unless there is also significant concurrent demand.