SnowPro Associate: Platform Question 245
Single answer● Warehouse scalingA data engineering team runs a nightly ELT pipeline on a Snowflake virtual warehouse named ETL_WH. Most steps are short, independent transformation queries submitted concurrently by an orchestration tool. Recently, the pipeline has been missing its SLA because many queries spend several minutes in the warehouse queue, even though each query runs quickly once it starts. The team wants to reduce queueing during the nightly peak without significantly changing the performance of individual queries. Which action should they take?
- A
Increase ETL_WH to a larger warehouse size so each individual query gets more compute resources
- B
Convert ETL_WH to a multi-cluster warehouse with additional clusters to handle bursts of concurrent queries
- C
Disable auto-suspend so the warehouse stays running and avoids all queueing delays
- D
Reduce the statement timeout so queued queries fail faster and free resources for other queries
Show answer and explanation
Correct answer: B
Explanation
This scenario tests the difference between scaling up and scaling out in Snowflake. Increasing warehouse size scales up a single cluster and is most useful when individual queries need more compute, memory, or faster execution. In contrast, multi-cluster warehouses scale out and are intended to improve concurrency by adding clusters when many queries run at the same time. Because the pipeline's queries are short and independent and the problem is queueing during bursts, the best practice is to use a multi-cluster warehouse. Snowflake documentation on virtual warehouses describes resizing for query performance and multi-cluster warehouses for concurrency scaling.
- A. Incorrect.
Incorrect. Resizing a warehouse to a larger size adds more compute resources to a single cluster, which often improves the performance of individual queries. However, the scenario states that queries already run quickly once they start and that the main problem is queueing caused by many concurrent submissions. A larger single-cluster warehouse does not address concurrency as effectively as adding clusters.
- B. Correct.
Correct. Multi-cluster warehouses are designed to handle higher concurrency by starting additional clusters when query demand increases. This is the appropriate scaling approach when many independent queries are queued during peak periods, especially when the goal is to reduce queueing rather than speed up each individual query.
- C. Incorrect.
Incorrect. Disabling auto-suspend may reduce resume latency, but it does not solve query queueing caused by insufficient concurrent processing capacity. The issue here is not warehouse startup delay; it is that too many queries are competing for the same cluster at once.
- D. Incorrect.
Incorrect. Lowering the statement timeout does not increase available compute or reduce contention in a meaningful way. It may cause queued or long-running queries to fail sooner, but that does not address the root cause of concurrency bottlenecks and could disrupt the pipeline.