COF-C03 Question 87
Single answerHigh concurrencyA retail company uses a BI dashboard that sends hundreds of short-running queries to Snowflake every few minutes during business hours. Users report intermittent slowdowns only during peak dashboard usage. The warehouse is a single-cluster MEDIUM warehouse with AUTO_SUSPEND enabled. Query profiles show queueing during these spikes, but CPU and memory are not consistently saturated outside peak periods. The company wants to improve performance during high concurrency while minimizing cost when demand is low. Which action should the Snowflake administrator take?
- A
Convert the warehouse to a multi-cluster warehouse with auto-scale enabled so additional clusters can start during concurrency spikes
- B
Increase the warehouse size from MEDIUM to XLARGE and disable auto-suspend to prevent any startup delay
- C
Enable Search Optimization Service on the queried tables to eliminate concurrency-related queueing
- D
Create a materialized view for every dashboard query so queries no longer compete for warehouse resources
Show answer and explanation
Correct answer: A
Explanation
For high concurrency workloads, especially BI and dashboard use cases with many short-running simultaneous queries, Snowflake best practice is to use a multi-cluster virtual warehouse with auto-scale enabled. This allows Snowflake to add compute clusters during spikes to reduce or eliminate query queueing, then scale back down when concurrency decreases, helping balance performance and cost. Simply resizing a warehouse can improve throughput somewhat, but multi-cluster warehouses are the feature specifically intended for concurrency scaling. Features such as Search Optimization Service and materialized views can improve query execution in certain cases, but they do not directly solve queueing caused by a large number of concurrent queries. This aligns with Snowflake documentation on warehouses, multi-cluster warehouses, and managing concurrency for BI workloads.
- A. Correct.
Correct. Multi-cluster warehouses are designed to handle higher concurrency by adding clusters when query demand increases and scaling back down when demand drops, which aligns with the requirement to improve peak performance while controlling cost during low-demand periods. Auto-scaling helps reduce query queueing caused by many simultaneous requests.
- B. Incorrect.
Incorrect. Increasing warehouse size can improve per-query performance and may help some concurrency situations, but it does not address concurrency as directly as multi-cluster auto-scaling. Disabling auto-suspend would also increase cost during idle periods, which conflicts with the requirement to minimize cost when demand is low.
- C. Incorrect.
Incorrect. Search Optimization Service can improve the performance of selective queries, but it does not solve warehouse queueing caused by many concurrent queries competing for execution slots. This option confuses query access optimization with compute concurrency management.
- D. Incorrect.
Incorrect. Materialized views can improve performance for certain repeated query patterns, but creating one for every dashboard query is not practical and does not directly address concurrency spikes. Queries against materialized views still consume warehouse resources, so this is not the best solution for queueing caused by high concurrent workload.