SnowPro Associate: Platform Question 236
Single answer3.2 Explain virtual warehouses.A data engineering team uses a Snowflake virtual warehouse named ETL_WH to run nightly transformation jobs. Recently, the team noticed that ETL_WH is accumulating a long queue of statements during the nightly load window, but query performance is acceptable when statements actually start running. The team wants to reduce queuing without rewriting SQL or changing the size of individual queries. Which action is the best fit for this requirement?
- A
Convert ETL_WH to a multi-cluster warehouse so Snowflake can add clusters to handle higher concurrency
- B
Increase the warehouse size from MEDIUM to LARGE so each query uses more storage capacity
- C
Enable Time Travel retention on the database so queued queries can resume faster after waiting
- D
Create a materialized view on all source tables so the warehouse no longer needs to execute concurrent statements
Show answer and explanation
Correct answer: A
Explanation
Virtual warehouses in Snowflake provide the compute resources used to execute queries, DML, and other data processing tasks. When the main issue is statement queueing caused by high concurrency, Snowflake best practice is to consider a multi-cluster warehouse, which can automatically start additional clusters to support more simultaneous workloads. By contrast, resizing a warehouse primarily changes the compute power available to each executing query and is more appropriate when queries themselves run too slowly. Time Travel is a data retention feature, not a compute feature, and materialized views are workload-specific optimization objects rather than a concurrency-management mechanism. This aligns with Snowflake documentation on virtual warehouses and multi-cluster warehouses, which distinguishes scaling up for query performance from scaling out for concurrency.
- A. Correct.
Correct. Multi-cluster warehouses are designed to address concurrency and queuing by automatically adding additional compute clusters when demand increases. In this scenario, the issue is that too many statements are waiting to start, not that individual running queries are slow. A multi-cluster warehouse is the most appropriate solution when acceptable query performance exists once execution begins but concurrency demand causes queueing.
- B. Incorrect.
Incorrect. Increasing warehouse size gives each running query more compute resources and can improve execution speed for compute-bound workloads, but it does not directly target concurrency the way multi-cluster warehouses do. Also, warehouse size affects compute, not storage capacity. This option reflects a common misconception that larger warehouses are always the answer to queueing.
- C. Incorrect.
Incorrect. Time Travel controls access to historical data versions for a retention period; it has nothing to do with virtual warehouse concurrency, statement queueing, or execution scheduling. This distractor targets confusion between storage/data recovery features and compute behavior.
- D. Incorrect.
Incorrect. Materialized views can improve performance for certain repeated query patterns, but they do not solve a general warehouse concurrency problem for nightly ETL job queues. In addition, creating materialized views on all source tables would not be a practical or appropriate response to queueing caused by many simultaneous statements.