SnowPro Associate: Platform Question 235
Single answer3.2 Explain virtual warehouses.A data engineering team runs a nightly ETL pipeline on a Snowflake virtual warehouse named ETL_WH. Most of the time, the pipeline finishes within 20 minutes, but on month-end processing days it takes more than 2 hours because many large transformation queries run at the same time. The team wants to reduce the month-end runtime without changing the SQL logic and without affecting other workloads in the account. Which action is the best solution?
- A
Convert ETL_WH to a multi-cluster warehouse so additional clusters can start when query concurrency increases
- B
Increase the Time Travel retention period for the ETL tables so Snowflake can process more queries in parallel
- C
Move the ETL pipeline to a database with more micro-partitions to improve warehouse concurrency
- D
Create materialized views on all ETL source tables because materialized views automatically increase virtual warehouse compute capacity
Show answer and explanation
Correct answer: A
Explanation
Virtual warehouses provide the compute resources used to execute queries, DML, and ETL operations in Snowflake. When performance issues are caused by many queries running at the same time, the key warehouse concepts are sizing and concurrency. A larger warehouse can make individual queries run faster, while a multi-cluster warehouse can help when concurrency causes query queuing. In this scenario, the month-end slowdown is explicitly tied to many large transformation queries running simultaneously, so multi-cluster is the best fit. Snowflake documentation describes multi-cluster warehouses as a way to support concurrent users and workloads by automatically managing additional clusters. By contrast, features such as Time Travel, micro-partitions, and materialized views do not directly add warehouse compute capacity for concurrent execution.
- A. Correct.
Correct. A multi-cluster warehouse is designed to handle higher concurrent query workloads by automatically adding clusters when needed, depending on the warehouse configuration. In this scenario, the slowdown happens because many large transformation queries run at the same time during month-end. Converting the warehouse to multi-cluster can reduce queueing and improve elapsed runtime without changing SQL logic. It also isolates the solution to the ETL workload rather than affecting unrelated workloads.
- B. Incorrect.
Incorrect. Time Travel retention controls how long historical data versions are available for querying and recovery. It does not increase compute resources, reduce query queueing, or improve concurrency for a virtual warehouse.
- C. Incorrect.
Incorrect. Micro-partitions are part of Snowflake's storage architecture, and users do not improve warehouse concurrency by moving workloads to a database with 'more micro-partitions.' Query concurrency and compute capacity are handled by the virtual warehouse configuration, not by selecting a database based on partition count.
- D. Incorrect.
Incorrect. Materialized views can improve performance for some repeated query patterns by precomputing results, but they do not automatically increase the compute capacity of a virtual warehouse. They also require design changes and may not be appropriate for all ETL source tables. The question asks for a solution without changing SQL logic.