COF-C03 Question 78
Single answerSizing (up, down)A data engineering team runs a nightly ELT job on a Snowflake virtual warehouse named ETL_WH, sized MEDIUM. Most nights the workload finishes in 40 minutes, but at month-end it runs for over 2 hours because several large transformation queries spill to local and remote storage. The team wants to reduce month-end runtime without changing the SQL logic and without increasing compute cost on normal days. Which action is the MOST appropriate?
- A
Resize ETL_WH to LARGE only for the month-end run, then resize it back to MEDIUM afterward
- B
Enable auto-suspend with a shorter timeout so the month-end queries get more CPU when they resume
- C
Create additional schemas so the month-end workload is distributed across more database objects
- D
Convert ETL_WH to a multi-cluster warehouse so each single query gets more compute resources
Show answer and explanation
Correct answer: A
Explanation
This scenario tests the distinction between scaling up and scaling out in Snowflake. Sizing up a warehouse increases resources available to each query and is the recommended approach when individual queries need more compute or memory, especially when queries spill to local or remote storage. Sizing out with a multi-cluster warehouse helps when many queries are contending for resources at the same time, improving concurrency rather than single-query speed. Auto-suspend is a cost-control setting for idle warehouses, not a performance tuning mechanism. Snowflake best practices generally recommend increasing warehouse size for large, resource-intensive queries and using multi-cluster warehouses for concurrent workloads.
- A. Correct.
Correct. Increasing warehouse size adds more compute resources, memory, and cache capacity per cluster, which can help large queries complete faster and reduce spilling. Because the slowdown happens only during month-end, temporarily sizing up just for that period is a practical cost-conscious approach. After the run, resizing back to MEDIUM avoids paying for a larger warehouse on normal days.
- B. Incorrect.
Incorrect. Auto-suspend controls when an idle warehouse stops consuming credits; it does not increase CPU, memory, or performance for running queries. Shortening the suspend interval may reduce cost during idle periods, but it will not address long-running month-end queries that are spilling to storage.
- C. Incorrect.
Incorrect. The number of schemas or database objects does not determine compute power for query execution. Query performance in this scenario is constrained by warehouse resources, not by logical object organization. Creating more schemas would add administrative complexity without improving the month-end workload.
- D. Incorrect.
Incorrect. Multi-cluster warehouses are designed primarily to improve concurrency by adding clusters when many queries run at the same time. They do not make an individual query run faster, because a single query executes on one cluster. Since the issue described is large transformation queries spilling during a batch run, sizing up is more appropriate than adding clusters for concurrency.