SnowPro Associate: Platform Question 248
Single answer○ Scaling in or outA data engineering team runs a nightly ELT workload on a Snowflake warehouse named ETL_WH. The workload consists of a few very large transformation queries that run slowly, even when there are no other queries using the warehouse. The team wants to reduce the runtime of these individual queries without creating additional warehouses. Which action is the BEST choice?
- A
Scale ETL_WH up to a larger warehouse size
- B
Scale ETL_WH out by enabling multi-cluster warehouse mode
- C
Increase the auto-suspend time so the warehouse stays warm longer
- D
Create more concurrent sessions that submit the same queries in parallel
Show answer and explanation
Correct answer: A
Explanation
This scenario tests the distinction between scaling up and scaling out in Snowflake. Scaling up means increasing the warehouse size to provide more compute resources to execute heavy queries faster. Scaling out means using multi-cluster warehouses to add clusters for handling higher concurrency and reducing query queues. For a few long-running queries with little or no concurrency, scaling up is the recommended approach. Snowflake documentation and best practices consistently distinguish these two patterns: use a larger warehouse for faster execution of compute-intensive workloads, and use multi-cluster warehouses when many users or processes are competing for the same warehouse at the same time.
- A. Correct.
Correct. Scaling up increases the compute resources available to each query by moving to a larger warehouse size. This is the appropriate choice when a small number of individual queries are long-running and need more compute power. In Snowflake, increasing warehouse size generally improves performance for resource-intensive queries because more compute is available for execution.
- B. Incorrect.
Incorrect. Scaling out with a multi-cluster warehouse adds additional clusters to handle more concurrent queries, reducing queueing during concurrency spikes. It does not make a single query run faster because an individual query runs on one cluster. This is a common misconception: scale out helps concurrency, while scale up helps individual query performance.
- C. Incorrect.
Incorrect. Increasing auto-suspend time may reduce resume events and can help avoid startup latency, but it does not materially improve the execution speed of already running large transformation queries. The problem described is query performance, not warehouse startup delay.
- D. Incorrect.
Incorrect. Submitting more concurrent sessions would likely increase contention for warehouse resources and could worsen performance. More parallel user sessions do not speed up one query; they increase concurrency demands, which is the opposite of the stated requirement.