COF-C03 Question 77
Single answerSizing (up, down)A data engineering team runs a nightly ELT pipeline on a Snowflake virtual warehouse named ETL_WH. The warehouse is currently sized MEDIUM and is configured with AUTO_SUSPEND and AUTO_RESUME. Most nights, the workload completes in 40 minutes, but at month-end the same pipeline takes more than 2 hours because several large transformation queries spill to local and remote storage. The team wants to reduce the month-end runtime without changing the SQL logic and without adding more clusters. Which action is the MOST appropriate?
- A
Resize ETL_WH to a larger warehouse before the month-end run, then resize it back after the pipeline completes
- B
Disable AUTO_SUSPEND so the warehouse cache stays warm throughout the month
- C
Create a second warehouse of the same size and point the month-end jobs to it sequentially
- D
Reduce the warehouse size to SMALL so fewer resources are wasted on shorter nightly runs
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, sizing up a virtual warehouse is the standard first action when queries are slow because they need more compute or memory. Larger warehouses can process more data in parallel and often reduce query spilling to local or remote storage, which is a common indicator that the warehouse is under-sized for the workload. This is especially appropriate for predictable peak periods such as month-end processing. Snowflake best practices recommend balancing cost and performance by right-sizing warehouses for the workload and using resizing strategically when demand varies over time. AUTO_SUSPEND and AUTO_RESUME help control cost, but they do not solve insufficient compute capacity. Additional warehouses or multi-cluster configurations are mainly for concurrency and workload isolation, not for accelerating a single sequential batch pipeline.
- A. Correct.
Correct. Increasing the virtual warehouse size adds more compute resources, including CPU, memory, and cache capacity. For queries that are slowed by spilling to local or remote storage, scaling up can reduce or eliminate spilling and improve execution time. Resizing up for the month-end batch and then resizing down afterward is a practical Snowflake cost-performance pattern when peak workloads are predictable.
- B. Incorrect.
Incorrect. Keeping the warehouse running may preserve some cache, but cache warmth does not address the main problem described: large transformation queries are spilling due to resource pressure. Disabling AUTO_SUSPEND would also increase cost by billing for idle time. Warehouse cache can help repeated data access patterns, but it is not the primary solution for memory- or compute-constrained month-end batch jobs.
- C. Incorrect.
Incorrect. A second warehouse of the same size does not make a single sequential pipeline faster if the workload is not being parallelized across warehouses. Separate warehouses are useful for workload isolation, but they do not improve the performance of the same queries unless the work is split and run concurrently. The scenario specifically asks for improvement without adding more clusters.
- D. Incorrect.
Incorrect. Downsizing would reduce available compute and memory, which would likely worsen spilling and increase runtime. Although a smaller warehouse may save money on light workloads, it is not an appropriate response to a known peak-period performance problem caused by insufficient resources.