ARA-C01 Question 391
Single answerScale up/down (resizing)A retail company runs a nightly ELT pipeline on a Snowflake virtual warehouse named ETL_WH. The pipeline is mostly a sequence of large transformation queries that currently takes 2 hours to complete on a MEDIUM warehouse. On several recent nights, a single long-running transformation step caused the pipeline to miss its SLA. Monitoring shows that this step is CPU-intensive, runs as one query at a time, and does not experience queueing because no other workloads share the warehouse. The architect wants to reduce the runtime of this step with the least architectural change and without adding more concurrent clusters. Which action should the architect take?
- A
Resize ETL_WH from MEDIUM to LARGE during the batch window, then optionally scale it back down afterward
- B
Enable multi-cluster auto-scaling on ETL_WH so Snowflake can add clusters for the long-running query
- C
Increase the auto-suspend time on ETL_WH so the warehouse remains warm between transformation steps
- D
Create a materialized view on the source tables to guarantee the transformation query completes faster
Show answer and explanation
Correct answer: A
Explanation
When a Snowflake workload consists of a single long-running query or a serial batch step, the best first action is usually to scale up the virtual warehouse so the query has more compute resources. Resizing a warehouse changes the compute available per cluster and can improve the performance of resource-intensive queries. By contrast, multi-cluster auto-scaling is intended for concurrency management and queue reduction, not for making an individual query run faster. Auto-suspend settings affect cost efficiency and startup behavior, not query execution power. From an architectural best-practice perspective, choose scale up/down when the bottleneck is per-query compute, and choose multi-cluster scaling when the bottleneck is concurrent workload pressure. This aligns with Snowflake guidance on virtual warehouse sizing, resizing, and multi-cluster usage.
- A. Correct.
Correct. Scaling up a warehouse increases the compute resources available to each query running on that warehouse. For a CPU-intensive, single-query workload with no queueing, resizing from MEDIUM to LARGE is the most direct way to reduce elapsed runtime. Because the workload is confined to the nightly batch window, the warehouse can be scaled back down afterward to control cost. This is the appropriate use of scale up/down (resizing).
- B. Incorrect.
Incorrect. Multi-cluster warehouses are designed primarily to improve concurrency by adding clusters when many queries run at the same time and would otherwise queue. In this scenario, there is only one long-running query at a time and no queueing, so adding clusters will not materially accelerate that single query. A common misconception is that more clusters make one query faster; they generally do not.
- C. Incorrect.
Incorrect. Increasing auto-suspend may reduce warehouse start-up frequency, which can help avoid small startup delays between intermittent tasks, but it does not provide more compute to the long-running transformation itself. Since the problem is the runtime of a CPU-bound query, not resume latency, this does not address the core issue.
- D. Incorrect.
Incorrect. A materialized view can improve performance for some query patterns, especially repeated aggregations or selective access paths that align with the view definition, but it is not a guaranteed or universal fix for a complex transformation step. It also introduces design and maintenance considerations and represents a larger architectural change than simply resizing the warehouse. The scenario asks for the least architectural change focused on runtime reduction.