SnowPro Associate: Platform Question 244
Single answer● Warehouse scalingA data engineering team runs a nightly ELT workflow on a Snowflake virtual warehouse named ETL_WH. The workflow includes a few very large transformation statements that run serially, followed by dozens of smaller independent SQL statements submitted at the same time by an orchestration tool. Recently, the team noticed that the small statements spend a long time queued, even though the large serial transformations perform adequately. The team wants to reduce queueing for the concurrent small statements while avoiding unnecessary cost increases for the large serial steps. Which change is the most appropriate?
- A
Resize ETL_WH to a larger warehouse so both the serial transformations and the concurrent small statements use more compute in a single cluster
- B
Convert ETL_WH to a multi-cluster warehouse with auto-scaling so additional clusters can start when many small statements run concurrently
- C
Enable auto-suspend with a shorter timeout so queued statements can resume the warehouse faster
- D
Create a materialized view on the largest source table so Snowflake can eliminate warehouse queueing during peak concurrency
Show answer and explanation
Correct answer: B
Explanation
Snowflake warehouse scaling can be approached in two main ways: resizing a warehouse to a larger size, or using a multi-cluster warehouse to add clusters for concurrency. Resizing improves compute power for queries running on a single cluster and is most useful when individual queries are slow. Multi-cluster warehouses are designed to address high concurrency and reduce queueing by adding clusters as demand increases. In this scenario, the large serial transformations already perform adequately, while the concurrent smaller statements queue, which strongly indicates a concurrency problem rather than an individual query performance problem. Therefore, converting the warehouse to multi-cluster with auto-scaling is the best fit. This aligns with Snowflake guidance that larger warehouses improve query performance, while multi-cluster warehouses help manage concurrent workloads and reduce queuing.
- A. Incorrect.
Incorrect. Resizing to a larger warehouse adds more compute resources to a single cluster, which can improve the performance of individual queries, especially large serial transformations. However, the main issue described is queueing caused by many concurrent small statements. A larger single-cluster warehouse does not address concurrency as effectively as adding clusters. This is a common misconception: warehouse size helps query speed, while multi-cluster scaling is designed to help with concurrent workload pressure.
- B. Correct.
Correct. A multi-cluster warehouse with auto-scaling is specifically intended to handle increased concurrency by starting additional clusters as query demand rises. In this scenario, the large serial transformations already perform adequately, so the problem is not primarily query execution speed but queued concurrent statements. Multi-cluster auto-scaling is the most appropriate way to reduce queueing for bursts of parallel work while avoiding permanently overprovisioning a larger warehouse for the entire workflow.
- C. Incorrect.
Incorrect. Auto-suspend controls how quickly an idle warehouse shuts down to save cost, and auto-resume allows it to restart when new queries arrive. These settings do not solve queueing caused by too many concurrent statements on an active warehouse. Someone might choose this option because it sounds related to responsiveness, but the bottleneck here is concurrency capacity, not resume latency.
- D. Incorrect.
Incorrect. Materialized views can improve performance for some query patterns by precomputing results, but they do not directly solve warehouse concurrency queueing. The issue described is that many small statements are submitted simultaneously and wait for execution slots. Even if some queries became faster, materialized views are not the primary or most reliable solution for a concurrency bottleneck in warehouse scaling.