ARA-C01 Question 386
Single answerVirtual warehouse configurationsA retail company runs two distinct workloads in Snowflake. During business hours, analysts submit many short, concurrent dashboard queries with strict response-time expectations. Overnight, a separate ETL process runs a small number of long-running transformation queries. The current setup uses one MEDIUM warehouse for both workloads, and users report dashboard slowdowns whenever ETL starts. The architect must improve dashboard performance while keeping credit usage predictable and avoiding unnecessary overprovisioning for ETL. Which configuration change best meets these requirements?
- A
Replace the single MEDIUM warehouse with one XL warehouse and let both dashboards and ETL share it.
- B
Create two warehouses: a multi-cluster warehouse for dashboard queries with auto-suspend/resume enabled, and a separate appropriately sized single-cluster warehouse for ETL.
- C
Keep one warehouse but increase STATEMENT_TIMEOUT_IN_SECONDS so dashboard queries wait longer instead of failing during ETL contention.
- D
Create two warehouses of the same large size, one for dashboards and one for ETL, and disable auto-suspend to avoid startup latency.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to isolate the two workloads and configure each warehouse according to its usage pattern. In Snowflake, virtual warehouses provide compute isolation, so running dashboards and ETL on separate warehouses prevents one workload from degrading the other. For interactive BI or dashboard traffic with many simultaneous short queries, a multi-cluster warehouse is often the best fit because it can add clusters to handle concurrency and reduce queueing. For overnight ETL with a limited number of long-running queries, a single-cluster warehouse sized appropriately for the workload is usually more efficient and predictable than a shared or oversized warehouse.
This aligns with Snowflake guidance on warehouse sizing and multi-cluster warehouses: scale up for larger query execution needs, scale out with multi-cluster for concurrency, and separate workloads when isolation is required. Auto-suspend and auto-resume are also standard cost-optimization practices for warehouses that are not continuously active.
- A. Incorrect.
Incorrect. Increasing the warehouse size to XL may improve overall compute capacity, but it does not isolate the dashboard and ETL workloads. The long-running ETL queries can still contend with dashboard queries for resources within the same warehouse. In addition, using a much larger shared warehouse may increase credits unnecessarily and does not specifically address high concurrency for short dashboard queries as effectively as a multi-cluster design.
- B. Correct.
Correct. Separating workloads across warehouses is a Snowflake best practice when workloads have different performance and concurrency characteristics. For dashboard queries, a multi-cluster warehouse helps absorb bursts of concurrent short-running queries without queueing, especially when configured with auto-scaling. For ETL, a separate single-cluster warehouse sized for throughput is usually more cost-predictable because the workload is a small number of long-running queries rather than a high-concurrency interactive workload. Auto-suspend/resume further helps control cost for the dashboard warehouse when not in use.
- C. Incorrect.
Incorrect. STATEMENT_TIMEOUT_IN_SECONDS controls how long a statement can run before timing out; it does not resolve resource contention or improve concurrency. This choice treats the symptom rather than the cause. Dashboard users would still experience degraded performance because the underlying issue is mixed workload contention on a shared warehouse.
- D. Incorrect.
Incorrect. Splitting workloads into separate warehouses is directionally correct, but making both warehouses large and disabling auto-suspend is not aligned with the stated requirement to keep credit usage predictable and avoid unnecessary overprovisioning. Dashboards may benefit more from multi-cluster scaling than simply using a larger fixed-size warehouse, and disabling auto-suspend can waste credits during idle periods.