SnowPro Associate: Platform Question 251
Single answer○ Scaling in or outA BI team uses a Snowflake virtual warehouse named ANALYTICS_WH to support dashboard queries during business hours. Users report that around 9:00 AM, many dashboard queries start at the same time and some wait in queue before running. The queries themselves are short and do not require more memory or CPU once they begin executing. The company wants to reduce query queuing during this spike while keeping costs as controlled as possible. Which action is the BEST choice?
- A
Resize ANALYTICS_WH from MEDIUM to XLARGE so each query gets more compute resources
- B
Convert ANALYTICS_WH to a multi-cluster warehouse with additional clusters available during concurrency spikes
- C
Disable auto-suspend so the warehouse stays running all day and avoids startup delays
- D
Create a materialized view for every dashboard query to eliminate warehouse contention
Show answer and explanation
Correct answer: B
Explanation
This scenario distinguishes between scaling up and scaling out in Snowflake. Scaling up means increasing warehouse size, which gives a single cluster more compute power and is most useful when individual queries are resource-intensive. Scaling out means adding clusters through a multi-cluster warehouse, which is intended to improve concurrency by allowing more queries to run simultaneously. Because the dashboard workload consists of many short queries that queue during a burst of simultaneous activity, the best solution is to scale out with a multi-cluster warehouse. This is consistent with Snowflake guidance that multi-cluster warehouses are best suited for handling large numbers of concurrent users and minimizing queuing, while larger warehouse sizes are better for improving the performance of individual queries. Auto-suspend affects cost and startup behavior, not concurrency capacity, and materialized views are a query optimization feature rather than the primary answer to warehouse queuing caused by concurrency.
- A. Incorrect.
Incorrect. Scaling up from MEDIUM to XLARGE adds more compute resources to a single cluster, which is most helpful when individual queries need more CPU, memory, or faster processing. In this scenario, the issue is that many short queries begin at the same time and are waiting in queue due to concurrency pressure, not that each query is underpowered once it starts. A larger single cluster may help somewhat, but it is not the best fit for reducing queuing caused by concurrent workloads.
- B. Correct.
Correct. Scaling out by using a multi-cluster warehouse is designed to handle higher concurrency. When many users submit short dashboard queries at the same time, additional clusters can be started to process more queries in parallel and reduce queuing. This aligns with the requirement to address a predictable concurrency spike while controlling cost, since extra clusters can be used only when demand increases.
- C. Incorrect.
Incorrect. Disabling auto-suspend may reduce warehouse resume latency, but it does not address query queuing caused by many concurrent requests. The warehouse would still have the same concurrency limits within a single cluster. It would also likely increase cost by keeping compute running when not needed.
- D. Incorrect.
Incorrect. Materialized views can improve performance for some repeated query patterns, but creating one for every dashboard query is not a practical or appropriate solution to a concurrency bottleneck. This option addresses query optimization rather than the warehouse scaling strategy described in the scenario.