SnowPro Associate: Platform Question 253
Single answer○ Scaling up or downA data engineering team runs a nightly ELT workload on a Snowflake virtual warehouse named ETL_WH. The workload usually finishes in 25 minutes on a MEDIUM warehouse, but during month-end processing it takes over 2 hours because several large transformation queries run much longer than usual. The team wants to reduce the month-end runtime without changing the SQL logic, and they also want to avoid paying for extra capacity on normal days. Which action should they take?
- A
Resize ETL_WH to a larger warehouse before the month-end run, then resize it back to MEDIUM after processing completes
- B
Enable auto-suspend with a shorter timeout so long-running month-end queries complete faster
- C
Convert ETL_WH to a multi-cluster warehouse so individual month-end queries get more compute power
- D
Create additional schemas for the month-end tables so Snowflake can parallelize the transformations better
Show answer and explanation
Correct answer: A
Explanation
This scenario tests the difference between scaling up and other Snowflake features. When a workload has long-running individual queries, the best remedy is often to scale up the virtual warehouse to a larger size, which provides more compute resources per cluster. Snowflake warehouses can be resized up or down with minimal operational overhead, making this a practical approach for predictable peak periods such as month-end processing. By contrast, multi-cluster warehouses are intended for concurrency scaling, where many simultaneous queries need separate clusters; they are not the primary solution for speeding up one slow query or a small set of large queries. Auto-suspend reduces cost during idle periods but does not improve active query performance. These behaviors align with Snowflake documentation and best practices for warehouse sizing, resizing, and concurrency management.
- A. Correct.
Correct. Scaling up a virtual warehouse means increasing its size, which adds more compute resources to execute queries faster. For a workload where individual queries are taking much longer at month-end, resizing from MEDIUM to a larger size such as LARGE or XLARGE is the appropriate action. Resizing back down after the peak period is a common cost-control practice because Snowflake charges based on warehouse size and runtime.
- B. Incorrect.
Incorrect. Auto-suspend helps reduce cost by stopping a warehouse when it is idle, but it does not improve the performance of queries that are already running. A shorter auto-suspend setting is useful for intermittent workloads, not for accelerating a heavy batch process.
- C. Incorrect.
Incorrect. Multi-cluster warehouses are primarily designed to improve concurrency by adding clusters when many queries compete for resources at the same time. They do not make a single query run faster by giving it more power across clusters. For a small number of long-running transformation queries, scaling up the warehouse size is the better choice.
- D. Incorrect.
Incorrect. Schema design does not control warehouse compute scaling in this way. Creating additional schemas does not provide more CPU or memory to queries and does not automatically improve transformation performance. This option reflects a misconception between logical object organization and compute resource scaling.