COF-C03 Question 80
Single answerScaling (in, out)A BI team uses a Snowflake virtual warehouse named ANALYTICS_WH to support hundreds of dashboard users each morning. During the 8:00-9:00 AM peak, users report long waits before queries begin running, but once a query starts, execution time is generally acceptable. Warehouse monitoring shows high query queueing during that hour, while CPU-intensive batch jobs are not running. The team wants to reduce user wait time during the peak hour without significantly changing performance for individual queries. Which action is the BEST choice?
- A
Scale up ANALYTICS_WH to a larger warehouse size during the peak hour
- B
Configure ANALYTICS_WH as a multi-cluster warehouse and allow additional clusters during the peak hour
- C
Enable Search Optimization Service on the largest dashboard tables
- D
Convert dashboard tables to transient tables to reduce warehouse contention
Show answer and explanation
Correct answer: B
Explanation
This scenario tests the difference between scaling up and scaling out in Snowflake. Scaling up means increasing warehouse size, which adds more compute resources to a single cluster and is most useful when individual queries need more power to run faster. Scaling out means using a multi-cluster warehouse, which adds clusters to handle more concurrent queries and reduces queueing. Because the users' main complaint is waiting for queries to start, not slow execution after they begin, the best solution is to scale out with a multi-cluster warehouse. This aligns with Snowflake best practices: use larger warehouse sizes to improve performance for compute-heavy queries, and use multi-cluster warehouses to address concurrency and queuing for many simultaneous users. Relevant Snowflake documentation covers virtual warehouse sizing, multi-cluster warehouses, and concurrency scaling behavior for user workloads.
- A. Incorrect.
Incorrect. Scaling up increases the compute resources available to a single cluster, which can improve the performance of individual queries. However, the scenario states that execution time is generally acceptable once queries start, and the main problem is queueing caused by high concurrency. A larger single cluster may help somewhat in some workloads, but it is not the best fit when the primary issue is many concurrent users waiting for slots to begin execution.
- B. Correct.
Correct. Scaling out with a multi-cluster warehouse is designed to address concurrency by adding additional clusters when query demand increases. In this scenario, users are experiencing queueing during a predictable peak period, while individual query runtime is acceptable once running. Allowing additional clusters is the most appropriate way to reduce wait time without primarily targeting single-query speed.
- C. Incorrect.
Incorrect. Search Optimization Service can improve the performance of selective point-lookups and certain query patterns, but it does not address warehouse query queueing caused by many concurrent users. Since the main issue is that queries wait before starting, this option does not directly solve the problem described.
- D. Incorrect.
Incorrect. Transient tables reduce data protection costs by having limited fail-safe behavior, but they do not reduce virtual warehouse contention or improve concurrency. This option confuses storage/table type settings with compute scaling behavior.