ADA-C01 Question 219
Single answerGiven a scenario, manage a multi-cluster warehouseA BI team uses a Snowflake virtual warehouse named BI_WH to support hundreds of dashboard queries during business hours. Usage is unpredictable: at 9 AM and 1 PM, many short-running queries arrive simultaneously, causing queueing delays. Outside these peaks, the workload is light. The warehouse is currently configured as a single-cluster MEDIUM warehouse with AUTO_SUSPEND = 60 and AUTO_RESUME = TRUE. The administrator must reduce query queueing during spikes while avoiding unnecessary compute cost during quieter periods. Which configuration change is the BEST choice?
- A
Convert BI_WH to a multi-cluster warehouse in AUTO-SCALE mode, set MIN_CLUSTER_COUNT = 1 and MAX_CLUSTER_COUNT greater than 1
- B
Convert BI_WH to a multi-cluster warehouse in MAXIMIZED mode, set MIN_CLUSTER_COUNT = MAX_CLUSTER_COUNT greater than 1
- C
Increase the warehouse size from MEDIUM to 2X-LARGE and keep it as a single-cluster warehouse
- D
Disable AUTO_SUSPEND so the current single-cluster warehouse stays warm and avoids queueing
Show answer and explanation
Correct answer: A
Explanation
For workloads characterized by many concurrent, short-running queries, especially BI and dashboard use cases, Snowflake best practice is to use a multi-cluster warehouse rather than only increasing warehouse size. Multi-cluster warehouses are intended to improve concurrency by adding clusters when query load exceeds the capacity of the current cluster. AUTO-SCALE mode is the most cost-efficient choice when demand is intermittent because Snowflake can start with the minimum number of clusters and scale out only as needed, then scale back in when concurrency drops. MAXIMIZED mode is generally reserved for cases where all configured clusters should remain available while the warehouse is running, which is less appropriate for variable usage patterns. Snowflake documentation on virtual warehouses and multi-cluster warehouses distinguishes between scaling up (larger warehouse size for more resources per query) and scaling out (additional clusters for higher concurrency). In this scenario, the key issue is queueing from concurrency spikes, so scaling out with AUTO-SCALE is the best fit.
- A. Correct.
Correct. A multi-cluster warehouse in AUTO-SCALE mode is designed for concurrency-driven workloads with variable spikes. Setting MIN_CLUSTER_COUNT = 1 keeps one cluster available for baseline usage, while allowing Snowflake to add clusters up to MAX_CLUSTER_COUNT when queries begin to queue. This directly addresses bursty dashboard traffic while limiting cost during non-peak periods because extra clusters are only added when needed.
- B. Incorrect.
Incorrect. MAXIMIZED mode provisions and runs all configured clusters for the duration the warehouse is active, which is appropriate only when consistently high concurrency is expected and cost is less of a concern. In this scenario, demand is bursty and light outside peak times, so keeping all clusters running would likely create unnecessary compute cost.
- C. Incorrect.
Incorrect. Increasing warehouse size can improve per-query performance and provide more compute resources, but it does not address concurrency as effectively as multi-cluster scaling for many simultaneous short-running queries. A larger single cluster may still experience queueing when many users submit queries at once. This option may also increase cost continuously, even when concurrency is low.
- D. Incorrect.
Incorrect. Disabling AUTO_SUSPEND keeps the warehouse running, which may reduce resume latency, but it does not solve the root problem of queueing caused by concurrent query spikes on a single cluster. It would also increase cost by keeping compute active during quiet periods.