ARA-C01 Question 207
Single answerVirtual warehousesA retail company runs both hourly ELT transformations and highly variable BI dashboards in the same Snowflake account. Today, both workloads use a single X-LARGE virtual warehouse. During peak business hours, dashboard users report slow performance whenever ELT jobs start. The company wants to minimize user-facing latency for dashboards while also controlling compute costs and avoiding unnecessary overprovisioning. Which approach should the architect recommend?
- A
Keep the single X-LARGE warehouse and increase STATEMENT_TIMEOUT_IN_SECONDS for BI users so dashboard queries can wait for ELT queries to finish.
- B
Replace the single X-LARGE warehouse with one multi-cluster warehouse shared by both ELT and BI workloads, configured in Auto-scale mode with a higher maximum cluster count.
- C
Separate the workloads into two warehouses: a dedicated warehouse for ELT and a dedicated warehouse for BI, sizing them independently and enabling auto-suspend/auto-resume as appropriate.
- D
Use a single SMALL warehouse for both workloads and rely on Snowflake result cache to eliminate most dashboard latency during ELT processing.
Show answer and explanation
Correct answer: C
Explanation
The best answer is to isolate ELT and BI onto separate virtual warehouses. Snowflake virtual warehouses are designed to provide independent compute resources, allowing architects to separate workloads with different performance and concurrency characteristics. This prevents batch-oriented ELT jobs from consuming compute needed by latency-sensitive dashboard queries. Once separated, each warehouse can be right-sized independently, which is usually more cost-effective than keeping one oversized shared warehouse. Auto-suspend and auto-resume further help reduce spend for intermittent workloads. Multi-cluster warehouses are valuable when a single workload has high concurrency and queueing, but they do not replace the architectural benefit of workload isolation. This aligns with Snowflake best practices around using separate warehouses for different workloads and using warehouse sizing and suspend/resume settings to balance performance and cost.
- A. Incorrect.
Incorrect. Increasing STATEMENT_TIMEOUT_IN_SECONDS does not improve concurrency or reduce contention between ELT and BI queries. It only changes how long statements are allowed to run before timing out. Dashboard users would still experience delays because both workloads compete for the same warehouse resources.
- B. Incorrect.
Incorrect. A multi-cluster warehouse primarily helps with handling concurrent queries for the same warehouse workload pattern, especially many simultaneous independent queries. While it can reduce queueing for concurrent BI-style requests, sharing ELT and BI on the same warehouse still mixes resource-intensive batch processing with interactive workloads. This does not provide the same workload isolation as separate warehouses and can be more expensive than necessary. Snowflake best practice is often to isolate different workload types onto separate virtual warehouses.
- C. Correct.
Correct. Separate virtual warehouses provide compute isolation, which is one of the key architectural advantages of Snowflake. By placing ELT and BI on different warehouses, heavy transformation jobs no longer contend with interactive dashboard queries. Each warehouse can then be sized for its own workload characteristics, and auto-suspend/auto-resume can help control cost for intermittent usage. This is the most direct way to protect dashboard performance while avoiding overprovisioning a single shared warehouse.
- D. Incorrect.
Incorrect. A single SMALL warehouse is unlikely to handle both hourly ELT and peak BI demand effectively. Result cache can help only when identical queries can reuse previously computed results and underlying data has not changed in a way that invalidates the cached result. It does not solve resource contention for new, ad hoc, or frequently changing dashboard queries, nor does it provide workload isolation.