ARA-C01 Question 390
Single answerScale up/down (resizing)A data engineering team runs a nightly ELT pipeline on a Snowflake warehouse named ETL_WH. Most tasks are executed serially, but one stage performs a single large transformation query that spills heavily to remote storage and regularly exceeds the batch SLA. Concurrency during the batch is low, and the team wants the fastest improvement with minimal redesign. Which action should the architect recommend first?
- A
Resize ETL_WH to a larger warehouse during the heavy transformation stage, then scale it back down afterward
- B
Convert ETL_WH to a multi-cluster warehouse so the large transformation query can use more clusters in parallel
- C
Enable Query Acceleration Service on ETL_WH and keep the warehouse size unchanged because resizing does not affect spill behavior
- D
Keep the warehouse size the same and increase AUTO_SUSPEND so the warehouse remains warm between pipeline steps
Show answer and explanation
Correct answer: A
Explanation
When a workload is dominated by a single slow query and concurrency is low, the architect should first consider scaling up the virtual warehouse rather than scaling out. In Snowflake, increasing warehouse size provides more compute resources to queries running on that warehouse, which is often the most direct way to improve performance for a large transformation query. By contrast, multi-cluster warehouses are intended to handle concurrent query load and reduce queueing; they are not the main tool for accelerating one query. This scenario also highlights an important operational best practice: resize only for the period that needs additional performance, then scale back down to control cost. Snowflake documentation on virtual warehouses, warehouse considerations, and multi-cluster warehouses emphasizes the distinction between scaling up for query performance and scaling out for concurrency.
- A. Correct.
Correct. Scaling up a warehouse increases the compute resources available to each query running on that warehouse. For a single large query with low concurrency, resizing to a larger warehouse is typically the first action to test because it can improve execution speed and reduce memory pressure that contributes to spilling. Since the workload is time-bound to one stage of the pipeline, scaling back down afterward is a practical cost-control approach.
- B. Incorrect.
Incorrect. Multi-cluster warehouses are designed primarily to address concurrency by adding clusters so more queries can run simultaneously. They do not make one individual query run faster by splitting that query across clusters. In this scenario, the workload bottleneck is a single large transformation query and concurrency is low, so scaling out with more clusters is not the best first recommendation.
- C. Incorrect.
Incorrect. Query Acceleration Service can help certain eligible scan- and filter-heavy workloads, but it is not the primary first step for a single large query that is spilling and missing SLAs when simple warehouse resizing has not yet been tried. The statement that resizing does not affect spill behavior is also misleading; a larger warehouse can provide more resources and often reduces or eliminates spilling for memory-intensive queries.
- D. Incorrect.
Incorrect. Increasing AUTO_SUSPEND may reduce resume frequency, but it does not materially improve the runtime of a resource-constrained query. Keeping a warehouse warm can help avoid startup latency, but that latency is usually small compared with the runtime of a long, spill-heavy transformation. This option does not address the core performance bottleneck.