COF-C03 Question 67
Single answerWarehouse type and configurations based on use casesA retail company runs two different workloads in Snowflake. First, it has a customer-facing dashboard that must return results quickly during unpredictable business hours with occasional spikes in concurrent users. Second, it runs a nightly ETL process that loads and transforms large volumes of data in a fixed 2-hour batch window. The company wants to balance performance and cost while minimizing manual intervention. Which configuration is the MOST appropriate?
- A
Use a multi-cluster warehouse in Auto-scale mode for the dashboard workload, and a separate standard warehouse sized appropriately for the nightly ETL job with auto-suspend enabled.
- B
Use one shared X-Large warehouse for both the dashboard and nightly ETL workloads so Snowflake can automatically prioritize queries by workload type.
- C
Use a Snowpark-optimized warehouse for the dashboard workload because it is designed for high concurrency, and use the same warehouse for ETL after business hours.
- D
Use a single-cluster warehouse for the dashboard with auto-resume disabled to avoid startup overhead, and run the ETL in the same warehouse to maximize cache reuse.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to separate the two workloads and match warehouse configuration to the access pattern. For user-facing BI/dashboard queries with variable concurrency, multi-cluster warehouses help reduce queuing by adding compute clusters as demand rises. For scheduled batch ETL, a dedicated warehouse sized for the transformation volume is usually more cost-effective than using multi-cluster concurrency scaling, because ETL performance is typically driven by compute capacity rather than many simultaneous users. Auto-suspend and auto-resume are standard cost-control features for both workloads. Snowflake documentation and best practices emphasize workload isolation with separate virtual warehouses and using multi-cluster warehouses primarily for concurrency-driven workloads such as BI and dashboards.
- A. Correct.
Correct. A multi-cluster warehouse is well suited for workloads with unpredictable concurrency, such as a customer-facing dashboard with usage spikes. In Auto-scale mode, Snowflake can add clusters when concurrent demand increases and remove them when demand drops, helping balance performance and cost. A separate warehouse for the nightly ETL job is also a best practice because it isolates workloads and avoids resource contention. For a fixed batch window, a standard warehouse sized for throughput is typically appropriate, and auto-suspend helps control cost when the warehouse is idle.
- B. Incorrect.
Incorrect. Although a larger shared warehouse can provide more compute, using one warehouse for both interactive dashboards and ETL mixes workloads and can create contention. Snowflake does not automatically prioritize queries by business workload type in a way that removes the need for workload isolation. Separating warehouses for distinct use cases is a common best practice for predictable performance and cost management.
- C. Incorrect.
Incorrect. Snowpark-optimized warehouses are intended for memory-intensive workloads, such as certain Snowpark operations, rather than general high-concurrency dashboard use cases. A common misconception is that specialized warehouse types are interchangeable with multi-cluster warehouses. For concurrency spikes, multi-cluster configuration is the more appropriate choice.
- D. Incorrect.
Incorrect. Keeping auto-resume disabled would require manual startup or scheduled management and does not minimize operational effort. In addition, using a single-cluster warehouse for unpredictable dashboard concurrency can lead to queuing during spikes. Running ETL in the same warehouse may also interfere with dashboard performance, even if cache reuse seems attractive.