ADA-C01 Question 218
Single answerGiven a scenario, manage a multi-cluster warehouseA Snowflake administrator manages a virtual warehouse used by a BI tool during weekday business hours. The warehouse experiences sharp spikes in concurrent dashboard queries between 8:00 AM and 10:00 AM, causing queries to queue. Outside that window, the workload is light. Management wants to reduce query queuing during peak periods while minimizing unnecessary compute cost during quieter times. Which configuration change should the administrator implement?
- A
Convert the warehouse to a multi-cluster warehouse in Auto-scale mode, set MIN_CLUSTER_COUNT = 1, and increase MAX_CLUSTER_COUNT to allow additional clusters during peak concurrency.
- B
Increase the warehouse size from MEDIUM to XLARGE and keep it as a single-cluster warehouse so each query has more CPU and memory available.
- C
Convert the warehouse to a multi-cluster warehouse in Maximized mode with MIN_CLUSTER_COUNT = MAX_CLUSTER_COUNT = 4 so all clusters are always running during business hours.
- D
Enable Query Acceleration Service on the warehouse because it automatically adds clusters when statements are queued due to concurrency.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a multi-cluster warehouse in Auto-scale mode with a low minimum cluster count and a higher maximum cluster count. In Snowflake, multi-cluster warehouses are specifically intended to handle periods of increased concurrency by adding clusters when query queues build up, then scaling back down as demand decreases. This makes them well suited for BI and dashboard workloads that create many simultaneous short-running queries.
A larger single-cluster warehouse primarily improves the resources available within one cluster and is more appropriate when queries need more CPU, memory, or I/O individually. It does not provide the same concurrency scaling behavior as multi-cluster. Maximized mode can be useful for predictable sustained concurrency, but it is less cost-efficient because all clusters run continuously. Query Acceleration Service is a separate performance feature and does not replace multi-cluster scaling for concurrency management.
This aligns with Snowflake best practices and documentation on virtual warehouses and multi-cluster warehouses: use multi-cluster for concurrency-driven queuing, especially for dashboard/reporting workloads, and prefer Auto-scale when balancing performance and cost.
- A. Correct.
Correct. Multi-cluster warehouses are designed to address high concurrency and query queuing by adding clusters when demand increases. Auto-scale mode starts additional clusters only as needed and can shut them down when demand drops, which aligns with the requirement to reduce queuing during the 8:00 AM to 10:00 AM spike while minimizing cost outside that period. Setting MIN_CLUSTER_COUNT = 1 keeps a single cluster available, and a higher MAX_CLUSTER_COUNT gives Snowflake room to scale out during peak concurrency.
- B. Incorrect.
Incorrect. Increasing warehouse size adds more compute resources to a single cluster, which can help individual query performance but does not directly solve concurrency-related queuing as effectively as adding clusters. For bursts caused by many simultaneous BI queries, scaling out with multi-cluster is usually the recommended approach rather than only scaling up.
- C. Incorrect.
Incorrect. Maximized mode keeps all configured clusters running, and setting both minimum and maximum to 4 would provision four clusters continuously during the defined operating period. While this may reduce queuing, it does not meet the requirement to minimize unnecessary cost during lighter workload periods. This option overprovisions compute for the stated pattern.
- D. Incorrect.
Incorrect. Query Acceleration Service can improve performance for eligible queries by offloading portions of query processing, but it is not the feature used to add warehouse clusters in response to concurrency. Managing query queues caused by many simultaneous statements is the primary use case for multi-cluster warehouses, not Query Acceleration Service.