SnowPro Associate: Platform Question 254
Single answer○ Scaling up or downA data engineering team runs a nightly ELT job on a Snowflake virtual warehouse named ETL_WH. The job usually finishes in 40 minutes on a MEDIUM warehouse, but at month-end the same workload takes more than 2 hours because several transformation queries process much larger tables. The team wants to reduce the month-end runtime without introducing query concurrency changes or redesigning the SQL. Which action is the most appropriate?
- A
Resize ETL_WH from MEDIUM to a larger warehouse size before the month-end run, then scale it back down afterward
- B
Enable auto-suspend on ETL_WH so the month-end queries get more compute while they are running
- C
Convert ETL_WH to a multi-cluster warehouse to make each individual transformation query run faster
- D
Increase the Time Travel retention period on the database so Snowflake can optimize month-end query execution
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, scaling up means increasing the size of a virtual warehouse (for example, from MEDIUM to LARGE or XLARGE), which provides more compute resources for query execution. This is the standard approach when a workload has grown in data volume and individual queries need more processing power. Scaling down later is a common best practice to manage credit consumption once the peak processing window has ended.
By contrast, multi-cluster warehouses are designed for scaling out to support concurrency, not for accelerating a single batch workload. Auto-suspend is a cost-management feature, and Time Travel retention is a data protection setting; neither addresses the root cause in this scenario. Snowflake documentation distinguishes warehouse resizing for performance of compute-intensive workloads from multi-cluster behavior for concurrent query demand.
- A. Correct.
Correct. Scaling up a virtual warehouse by increasing its size adds more compute resources to execute queries faster, which is the appropriate response when a workload needs more processing power for a limited period. In this scenario, the issue is longer runtime due to larger data volume, not increased concurrent users. Resizing up for the month-end batch and then resizing down afterward is a practical cost-performance approach.
- B. Incorrect.
Incorrect. Auto-suspend helps control cost by stopping a warehouse after a period of inactivity, but it does not provide additional compute capacity to active queries. It affects when credits stop being consumed, not how fast month-end transformations run.
- C. Incorrect.
Incorrect. Multi-cluster warehouses are primarily intended to handle higher concurrency by adding clusters when many queries compete for resources. They do not make a single large query complete faster in the way that increasing warehouse size can. Since the scenario explicitly says concurrency is not the problem, this is not the best choice.
- D. Incorrect.
Incorrect. Time Travel retention controls how long historical data can be accessed, which is useful for recovery and auditing. It does not improve the performance of large transformation queries or add compute resources to the warehouse.