ADA-C01 Question 214
Single answerConfigure warehouse properties (auto-suspend, auto-resume, max clusters)A data engineering team uses a Snowflake virtual warehouse named ETL_WH to run hundreds of short ELT jobs throughout the day. Recently, administrators noticed two issues: compute costs are higher than expected during idle periods, and users occasionally report that queued queries appear during peak load windows. The team wants to minimize charges when the warehouse is not in use, automatically restart it when new jobs arrive, and allow additional compute capacity during concurrency spikes without manually resizing. Which warehouse configuration best meets these requirements?
- A
Set AUTO_SUSPEND to a low value such as 60 seconds, AUTO_RESUME = TRUE, and configure the warehouse as a multi-cluster warehouse with MAX_CLUSTER_COUNT greater than 1
- B
Set AUTO_SUSPEND = 0, AUTO_RESUME = FALSE, and increase the warehouse size so that it stays available for all workloads
- C
Set AUTO_SUSPEND to a high value such as 3600 seconds, AUTO_RESUME = TRUE, and keep MAX_CLUSTER_COUNT = 1 to avoid additional cluster charges
- D
Disable AUTO_SUSPEND, enable AUTO_RESUME, and set MAX_CLUSTER_COUNT = 1 because query queuing is primarily controlled by auto-resume behavior
Show answer and explanation
Correct answer: A
Explanation
This scenario requires applying three warehouse properties together: AUTO_SUSPEND, AUTO_RESUME, and MAX_CLUSTER_COUNT. For cost control, Snowflake best practice is to use AUTO_SUSPEND so warehouses do not continue consuming credits while idle; for environments with many short, intermittent jobs, a relatively low setting is commonly appropriate. AUTO_RESUME allows the warehouse to start automatically when new queries arrive, which is important for operational continuity. To address queued queries caused by concurrency spikes, the warehouse must be configured as a multi-cluster warehouse with MAX_CLUSTER_COUNT greater than 1. Increasing size alone improves compute resources per cluster, but multi-cluster settings are specifically designed to add clusters for concurrent workloads. These behaviors are described in Snowflake documentation for virtual warehouse properties and multi-cluster warehouses.
- A. Correct.
Correct. A low AUTO_SUSPEND value helps reduce charges during idle periods by suspending the warehouse soon after activity stops. AUTO_RESUME = TRUE ensures incoming queries or jobs can start the warehouse automatically without administrator intervention. Setting MAX_CLUSTER_COUNT above 1 enables additional clusters for a multi-cluster warehouse, which is the appropriate property to address concurrency spikes and reduce query queuing for many simultaneous workloads.
- B. Incorrect.
Incorrect. AUTO_SUSPEND = 0 disables automatic suspension, which would likely increase idle compute cost rather than reduce it. AUTO_RESUME = FALSE would require manual resume actions or failed/blocked workloads if the warehouse is suspended. Increasing warehouse size can improve per-query performance, but it does not directly provide the same concurrency scaling behavior as allowing multiple clusters.
- C. Incorrect.
Incorrect. AUTO_RESUME = TRUE is appropriate, but AUTO_SUSPEND set to 3600 seconds leaves the warehouse running for up to an hour after activity ends, which does not align with the goal of minimizing idle-period charges. Keeping MAX_CLUSTER_COUNT = 1 also prevents the warehouse from adding clusters during concurrency spikes, so queued queries may still occur.
- D. Incorrect.
Incorrect. Disabling AUTO_SUSPEND increases cost during idle periods. Although AUTO_RESUME helps restart a suspended warehouse automatically, it does not solve query queuing caused by concurrency pressure once the warehouse is already running. MAX_CLUSTER_COUNT = 1 means no extra clusters can be added, so this does not address the stated peak-load problem.