COF-C03 Question 55
Single answer1.4 Configure virtual warehousesA data engineering team uses a Snowflake virtual warehouse named ETL_WH to run hourly transformation jobs. Recently, job duration has become inconsistent because several ad hoc analyst queries run on the same warehouse at the top of each hour. The team wants to improve concurrency for ETL workloads without permanently increasing compute costs during idle periods. Which configuration change best addresses this requirement?
- A
Enable multi-cluster on ETL_WH with auto-scale and set MIN_CLUSTER_COUNT = 1 and MAX_CLUSTER_COUNT greater than 1
- B
Increase the warehouse size from MEDIUM to XLARGE and disable auto-suspend
- C
Convert ETL_WH to a Snowpark-optimized warehouse so that analyst queries and ETL jobs can share memory more efficiently
- D
Set STATEMENT_TIMEOUT_IN_SECONDS to a lower value so long-running analyst queries do not affect ETL jobs
Show answer and explanation
Correct answer: A
Explanation
When multiple users or processes submit queries to the same virtual warehouse at the same time, concurrency can become a bottleneck. Snowflake recommends multi-cluster warehouses for handling higher concurrency, especially for user-facing or mixed workloads where bursts occur. In auto-scale mode, Snowflake automatically starts additional clusters up to the configured MAX_CLUSTER_COUNT when demand increases, then scales back down when demand subsides. This aligns with the scenario's goal of improving ETL consistency during peak overlap without paying for continuously elevated compute capacity. By contrast, simply resizing a warehouse improves per-query resources but is less targeted for concurrency management, and disabling auto-suspend would increase cost during idle periods. Relevant Snowflake documentation includes guidance on virtual warehouses, warehouse sizing, auto-suspend/auto-resume, and multi-cluster warehouses for concurrency scaling.
- A. Correct.
Correct. A multi-cluster warehouse in auto-scale mode is designed to handle concurrency by automatically adding clusters when query demand increases and reducing clusters when demand drops. Setting MIN_CLUSTER_COUNT = 1 keeps one cluster available for baseline ETL processing, while allowing MAX_CLUSTER_COUNT to scale above 1 helps absorb overlapping analyst queries at peak times. This improves concurrency without requiring the organization to pay for extra clusters when they are not needed.
- B. Incorrect.
Incorrect. Increasing warehouse size can improve the performance of individual queries, but it does not directly solve concurrency contention as effectively as multi-cluster scaling. Disabling auto-suspend would also increase costs during idle periods, which conflicts with the requirement to avoid permanently increasing compute costs.
- C. Incorrect.
Incorrect. Snowpark-optimized warehouses are intended for workloads that need large memory resources, such as certain Snowpark and machine learning use cases. They are not the standard solution for improving mixed-query concurrency between ETL and BI-style workloads.
- D. Incorrect.
Incorrect. Lowering statement timeout may cancel some long-running analyst queries, but it does not address the root problem of concurrent workload contention. It is also operationally risky because legitimate queries may be terminated, and it is not a warehouse scaling strategy.