SnowPro Associate: Platform Question 252
Single answer○ Scaling up or downA data engineering team runs a nightly ELT pipeline on a Snowflake virtual warehouse named ETL_WH. Most of the load completes quickly, but one transformation step that joins several large tables consistently takes much longer than expected. The team wants to reduce the runtime of this bottlenecked step immediately without changing the SQL logic or adding more concurrent clusters. Which action is the most appropriate?
- A
Resize ETL_WH to a larger warehouse before running the transformation step, then resize it back down afterward
- B
Enable auto-suspend with a shorter timeout so the warehouse uses more compute during the transformation
- C
Convert ETL_WH to a multi-cluster warehouse so the single long-running query gets more compute resources
- D
Increase the warehouse statement timeout so Snowflake allocates additional CPU and memory to the query
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) to provide more compute resources for query execution. This is the appropriate response when a single query or transformation is resource-intensive and needs better performance. Scaling down afterward is a practical cost-control measure. By contrast, multi-cluster warehouses address concurrency scaling, which helps when many users or workloads compete for the same warehouse, not when one query is slow. Auto-suspend and statement timeout are administrative settings that affect cost management and query cancellation behavior, not compute power. This aligns with Snowflake documentation and best practices on virtual warehouse sizing, resizing, and the distinction between scaling up for performance versus scaling out for concurrency.
- A. Correct.
Correct. Scaling up a virtual warehouse means increasing its size, which provides more compute resources to execute a query. For a single long-running, resource-intensive transformation, resizing the warehouse to a larger size is the direct way to improve performance. After the heavy step finishes, resizing back down helps control cost. This is a common Snowflake best practice for handling temporary periods of high compute demand.
- B. Incorrect.
Incorrect. Auto-suspend controls when an idle warehouse shuts down to save cost; it does not increase compute power for an active query. A shorter auto-suspend setting may reduce wasted spend when the warehouse is idle, but it will not speed up a long-running transformation step.
- C. Incorrect.
Incorrect. Multi-cluster warehouses are primarily used to handle concurrency by adding clusters when many queries run at the same time. They do not make a single query run faster by splitting that one query across clusters. In this scenario, the team explicitly does not want to add more concurrent clusters, and concurrency is not the problem described.
- D. Incorrect.
Incorrect. Statement timeout settings determine how long a statement can run before being canceled. Increasing the timeout may allow a slow query to continue running longer, but it does not provide more compute resources or improve query performance.