COF-C03 Question 86
Single answerHigh concurrencyA retail company uses Snowflake to serve thousands of dashboard queries each morning between 8:00 AM and 9:00 AM. All BI tools point to a single virtual warehouse named BI_WH. During this period, users report slow response times, and the Query History shows many statements in a queued state due to warehouse overload. The data volume is moderate, and most queries are short-running and similar in complexity. The company wants to improve concurrency while minimizing cost increases and avoiding query rewrites. Which action should the Snowflake administrator take?
- A
Convert BI_WH to a multi-cluster warehouse with auto-scale enabled
- B
Increase the retention period for Time Travel on the database used by the dashboards
- C
Create a materialized view for every table referenced by the dashboards
- D
Resize BI_WH from MEDIUM to XLARGE and disable auto-suspend
Show answer and explanation
Correct answer: A
Explanation
For high concurrency workloads in Snowflake, the best practice is to use a multi-cluster warehouse when many users or applications submit queries simultaneously and queueing occurs due to warehouse overload. Snowflake documentation distinguishes between scaling up, which increases the resources available to a single cluster, and scaling out with multi-cluster warehouses, which is specifically intended to support more concurrent queries. This is especially effective for BI and dashboard workloads with many short, similar queries. Auto-scale further aligns cost with demand by starting additional clusters only during busy periods. By contrast, Time Travel settings are unrelated to concurrency, and materialized views are query optimization features rather than a direct concurrency-control mechanism.
- A. Correct.
Correct. A multi-cluster warehouse is designed to handle periods of high concurrency by automatically adding clusters when query demand exceeds the capacity of the current cluster. This is the most appropriate solution when many short-running queries are queued because of concurrent demand, especially when the goal is to improve performance without changing application SQL. Auto-scale helps control cost by adding clusters only when needed.
- B. Incorrect.
Incorrect. Time Travel retention affects how long historical data can be accessed, not how many queries can run concurrently. Increasing retention would add storage overhead but would not reduce queueing or improve dashboard response times during peak periods.
- C. Incorrect.
Incorrect. Materialized views can improve performance for some repeated query patterns, but creating them for every referenced table is not a practical or correct response to a concurrency bottleneck. The issue described is warehouse overload from many simultaneous queries, not necessarily expensive query processing on specific tables. In addition, materialized views introduce maintenance cost and are not a blanket solution.
- D. Incorrect.
Incorrect. Increasing warehouse size can improve the performance of individual queries by adding more compute resources to a single cluster, but it does not address concurrency as effectively as adding clusters for many simultaneous short queries. Disabling auto-suspend would likely increase cost unnecessarily outside the peak hour and does not directly solve the queueing issue.