ARA-C01 Question 208
Single answerVirtual warehousesA retail company runs two distinct workloads in Snowflake. During business hours, hundreds of short BI dashboard queries run concurrently and must return in seconds. Overnight, a smaller number of data engineering jobs run long, scan large tables, and are less sensitive to occasional queueing. The company currently uses one LARGE virtual warehouse for both workloads and is seeing unpredictable BI performance and higher-than-expected compute costs. As the architect, which approach is the BEST way to improve BI responsiveness while controlling cost?
- A
Keep a single LARGE warehouse, but increase AUTO_SUSPEND to several hours so the warehouse stays warm and avoids startup delays for BI users.
- B
Create separate warehouses: use a multi-cluster warehouse for BI with auto-scaling enabled, and a different warehouse sized appropriately for overnight ETL workloads.
- C
Replace the LARGE warehouse with one XSMALL warehouse and enable query acceleration so both BI and ETL workloads can share it efficiently.
- D
Use a single multi-cluster warehouse for both BI and ETL so all workloads can consume the same pooled compute without contention.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to isolate the BI and ETL workloads onto separate virtual warehouses and use a multi-cluster warehouse for the BI workload. In Snowflake, virtual warehouses are independent compute resources, so assigning different workloads to different warehouses is a common architectural pattern for workload isolation, predictable performance, and cost control. Multi-cluster warehouses are designed to address concurrency by automatically adding clusters when query demand increases, which is well suited for dashboard and interactive BI patterns. By contrast, overnight ETL jobs often benefit more from an appropriately sized dedicated warehouse, where occasional queueing may be acceptable and where warehouse size can be tuned for batch throughput rather than concurrency. Snowflake documentation and best practices consistently emphasize separating workloads with different usage patterns and using multi-cluster warehouses to manage concurrent user activity rather than as a universal solution for all workload types.
- A. Incorrect.
Incorrect. Increasing AUTO_SUSPEND to several hours may reduce resume events, but it does not address the core issue: mixed workloads with different concurrency and performance profiles sharing the same compute. It can also increase cost by keeping the warehouse running while idle. Resume latency in Snowflake is typically short, so using a long auto-suspend setting just to avoid startup delays is usually not the best optimization.
- B. Correct.
Correct. Separating workloads by warehouse is a core Snowflake best practice when workloads have different characteristics and SLAs. BI workloads with many concurrent short queries benefit from a multi-cluster warehouse because additional clusters can be added to handle concurrency and reduce queueing. ETL workloads that are fewer in number but resource-intensive are often better served by a separate warehouse sized for throughput, isolating them from BI traffic and giving better cost and performance governance.
- C. Incorrect.
Incorrect. An XSMALL warehouse is unlikely to provide sufficient compute for large ETL scans and high-concurrency BI workloads. Query Acceleration Service can help certain eligible scan-heavy queries, but it is not a substitute for proper warehouse sizing and workload isolation, and it does not make one tiny warehouse suitable for all mixed workloads.
- D. Incorrect.
Incorrect. A multi-cluster warehouse primarily helps with concurrency scaling, especially for many simultaneous queries. It does not eliminate the drawbacks of mixing unlike workloads on one warehouse. Long-running ETL queries can still consume resources and create noisy-neighbor effects. Using one shared warehouse also makes chargeback, tuning, and workload-specific sizing more difficult.