ARA-C01 Question 172
Select 2Data workloadsA retail company runs three major Snowflake workloads on the same account: (1) a near-real-time ELT pipeline that loads clickstream data every 5 minutes, (2) hourly transformation jobs that use large joins and aggregations, and (3) business analyst dashboards with strict response-time expectations during business hours. The current design uses a single large virtual warehouse for all workloads. During peak periods, dashboard queries become slow whenever transformation jobs start, and the ELT pipeline occasionally falls behind. The company wants to improve workload isolation while controlling compute cost and minimizing administrative overhead. Which TWO actions should the architect recommend?
- A
Create separate virtual warehouses for ELT, transformations, and BI, and right-size each warehouse independently based on workload characteristics.
- B
Replace the single warehouse with one multi-cluster warehouse in MAXIMIZED mode so all workloads share the same compute but can scale out automatically.
- C
Configure the BI warehouse with multi-cluster auto-scaling and set an appropriate auto-suspend/auto-resume policy to handle concurrency spikes efficiently.
- D
Move the hourly transformation jobs to Snowflake Tasks running on the same shared warehouse, because task scheduling prevents query contention.
- E
Increase the size of the existing warehouse to 4X-Large and keep all workloads together, since a larger warehouse eliminates cross-workload interference.
Show answer and explanation
Correct answers: A, C
Explanation
The best architectural response is to isolate distinct data workloads onto separate virtual warehouses and then tune each warehouse according to its workload profile. Snowflake's architecture separates storage and compute, allowing multiple warehouses to access the same data without resource contention. This makes separate warehouses the preferred design for mixed workloads such as frequent ELT ingestion, batch transformations, and latency-sensitive BI queries. For user-facing analytics, a multi-cluster warehouse is often appropriate because it addresses concurrency by adding clusters when multiple dashboard queries run simultaneously. By contrast, simply making one warehouse larger or relying on Tasks does not provide true workload isolation. Snowflake documentation and best practices consistently emphasize using separate warehouses for different workloads and using multi-cluster warehouses selectively for concurrency-heavy use cases, especially BI and dashboarding.
- A. Correct.
Correct. Separating compute by workload is a core Snowflake design best practice for workload isolation. Virtual warehouses do not share compute resources, so assigning ELT, transformation, and BI queries to different warehouses prevents one workload from consuming slots needed by another. This also allows each warehouse to be sized for its own query patterns: small or medium for frequent ELT micro-batches, larger for heavy transformations, and a BI-focused warehouse optimized for interactive usage.
- B. Incorrect.
Incorrect. A multi-cluster warehouse is primarily useful for handling concurrency on a single workload pattern, especially many similar user queries. It does not provide the same level of isolation as separate warehouses when fundamentally different workloads compete, such as ETL/ELT, heavy batch transformations, and interactive BI. In MAXIMIZED mode, Snowflake can keep additional clusters running more aggressively, which may increase cost without fully addressing mixed-workload contention.
- C. Correct.
Correct. BI workloads typically benefit from multi-cluster warehouses because they experience unpredictable concurrency spikes from many simultaneous dashboard users. Auto-scaling helps maintain response times by adding clusters when queues form, while auto-suspend/auto-resume limits idle cost. This is especially effective once BI is isolated onto its own warehouse rather than competing with batch transformations.
- D. Incorrect.
Incorrect. Snowflake Tasks orchestrate and schedule SQL execution, but they do not remove compute contention if the task runs on the same warehouse as other workloads. Scheduling alone does not guarantee isolation. If transformations remain on the shared warehouse, they can still consume resources and impact ELT and BI performance.
- E. Incorrect.
Incorrect. Increasing warehouse size can improve performance for some individual queries, but it does not reliably solve mixed-workload contention. Large batch jobs can still monopolize resources, and interactive dashboards may still queue behind other work. This approach also tends to be less cost-efficient than separating workloads and scaling each warehouse according to need.