ARA-C01 Question 171
Single answerData workloadsA retail company runs three major Snowflake workloads in the same account: (1) continuous ELT pipelines that load clickstream and order data every 5 minutes, (2) a business intelligence dashboard used by executives throughout the day, and (3) ad hoc data science queries that sometimes scan large historical tables for several hours. Recently, executives have complained that dashboard queries are becoming unpredictable during business hours, especially when data scientists start exploratory analysis. The company wants to improve performance isolation while minimizing cost and operational complexity. Which approach should the architect recommend?
- A
Move all workloads to a single larger warehouse so there is more total compute available to every query.
- B
Create separate virtual warehouses for ELT, BI, and data science workloads, and enable auto-suspend/auto-resume with appropriately sized warehouses for each workload.
- C
Keep all workloads on one warehouse, but increase the statement timeout so long-running data science queries do not fail during peak BI usage.
- D
Use a single multi-cluster warehouse for all workloads and set the minimum cluster count to 3 at all times to guarantee dashboard performance.
Show answer and explanation
Correct answer: B
Explanation
The key architectural principle is workload isolation. Snowflake virtual warehouses are independent compute clusters, so assigning separate warehouses to ELT, BI, and data science workloads prevents one class of activity from consuming resources needed by another. This is especially important when mixing latency-sensitive dashboard workloads with long-running exploratory queries. Auto-suspend and auto-resume are standard cost-optimization features that reduce spend when warehouses are idle. Snowflake documentation and best practices consistently recommend separating workloads by usage pattern and SLA, then sizing each warehouse appropriately. Multi-cluster warehouses are useful when a single workload experiences concurrency pressure, but they are not a substitute for isolating unlike workloads. In this scenario, the most practical and cost-conscious recommendation is separate warehouses per workload, with additional tuning such as multi-cluster considered only for the BI warehouse if concurrency demands it.
- A. Incorrect.
Incorrect. Increasing the size of a single shared warehouse may improve raw throughput, but it does not provide strong workload isolation. Long-running or bursty workloads can still contend for the same compute resources and create unpredictable performance for latency-sensitive BI queries. This is a common misconception: bigger shared compute is not the same as isolating unlike workloads.
- B. Correct.
Correct. Snowflake best practice is to separate heterogeneous workloads with different performance and concurrency characteristics onto different virtual warehouses. In this scenario, ELT, BI, and ad hoc data science workloads have distinct patterns and service-level expectations. Dedicated warehouses provide compute isolation, so heavy exploratory queries do not degrade dashboard responsiveness. Auto-suspend and auto-resume help control cost, and right-sizing each warehouse reduces unnecessary spend while keeping operations straightforward.
- C. Incorrect.
Incorrect. Statement timeout settings affect how long queries are allowed to run, not how compute resources are isolated. Increasing the timeout would not address the root cause of dashboard latency, which is resource contention from mixed workloads on the same warehouse.
- D. Incorrect.
Incorrect. A multi-cluster warehouse is most effective for handling concurrency spikes of similar queries, especially many simultaneous short-running requests. It is not the best primary solution for isolating fundamentally different workloads such as BI, ELT, and long-running data science processing. Setting a minimum of 3 clusters at all times would also increase cost significantly, even during quiet periods. While multi-cluster could be appropriate for the BI warehouse itself if dashboard concurrency is high, using one shared multi-cluster warehouse for all workloads does not provide the clean isolation required here.