COF-C03 Question 79
Single answerScaling (in, out)A data engineering team runs a nightly ELT workload on a Snowflake virtual warehouse named ETL_WH. The workload consists of a few long-running transformation queries that are not blocked by other sessions, but they frequently exceed the batch window. During the day, ETL_WH also serves many short BI queries from multiple users, and those users sometimes experience queueing during peak periods. The team wants to improve performance while controlling cost. Which action should the architect recommend?
- A
Increase ETL_WH to a larger warehouse size for the nightly ELT workload, and use multi-cluster on the same warehouse to address the long-running transformations.
- B
Keep ETL_WH at its current size, because scaling out with additional clusters improves the performance of individual long-running queries.
- C
Use a larger warehouse size to improve the nightly ELT queries, and consider multi-cluster during daytime BI peaks to reduce concurrency-related queueing.
- D
Create a materialized view on every ELT target table, because materialized views eliminate the need to scale warehouses for both batch and BI workloads.
Show answer and explanation
Correct answer: C
Explanation
Snowflake supports two key warehouse scaling approaches relevant here: scaling up by increasing warehouse size, and scaling out by using multi-cluster warehouses. Scaling up gives a single cluster more compute resources and is the right choice when individual queries are long-running and need more processing power. Scaling out adds additional clusters to handle more concurrent queries and is most useful when users experience queueing due to concurrency. A common SnowPro exam distinction is that multi-cluster improves throughput for many simultaneous queries, but it does not make one individual query execute faster. Snowflake documentation and best practices describe warehouse resizing for query performance and multi-cluster warehouses for concurrency management.
- A. Incorrect.
Incorrect. Increasing warehouse size can help the nightly long-running ELT queries, because scaling up provides more compute resources per query. However, using multi-cluster on the same warehouse is intended primarily to handle concurrency by adding clusters for additional simultaneous queries, not to make a single long-running query run faster. This option mixes a correct idea for the ELT workload with an incorrect reason for multi-cluster.
- B. Incorrect.
Incorrect. Scaling out with additional clusters in a multi-cluster warehouse is designed to reduce queueing and improve throughput for concurrent workloads. It does not speed up an individual query that is already running on a single cluster. This reflects a common misconception that more clusters automatically parallelize one query across clusters.
- C. Correct.
Correct. For a small number of long-running transformation queries, scaling up to a larger warehouse size is the appropriate recommendation because a bigger warehouse provides more compute resources to each query. For daytime BI spikes with many concurrent short queries, multi-cluster can automatically add clusters to reduce or avoid queueing caused by concurrency. This aligns Snowflake scaling approaches to the specific workload patterns.
- D. Incorrect.
Incorrect. Materialized views can help some repeated query patterns, but they are not a universal replacement for warehouse scaling. They do not eliminate the need to size compute appropriately for ETL transformations or to handle BI concurrency. In addition, creating materialized views on every ELT target table would add maintenance cost and is not justified unless there is a clear query-performance use case.