ADA-C01 Question 406
Select 2Manage costs for virtual warehousesA company runs an ELT pipeline every 15 minutes using a dedicated virtual warehouse named ETL_WH. Administrators notice that warehouse credit consumption is much higher than expected, even though each pipeline run usually completes in 2-3 minutes. ETL_WH is configured as X-SMALL with AUTO_SUSPEND = 600 seconds and AUTO_RESUME = TRUE. The team wants to reduce compute cost without significantly affecting job reliability or completion time. Which TWO actions should the administrator take?
- A
Reduce AUTO_SUSPEND on ETL_WH to a much lower value, such as 60 seconds.
- B
Disable AUTO_RESUME so the warehouse does not start unless an administrator manually resumes it.
- C
Keep the warehouse running continuously to avoid repeated resume events that increase Snowflake compute charges.
- D
Review whether ETL_WH can be resized down further or consolidated with another appropriately governed warehouse if workload characteristics allow it.
- E
Increase STATEMENT_TIMEOUT_IN_SECONDS so failed queries do not need to be retried and consume additional credits.
Show answer and explanation
Correct answers: A, D
Explanation
The primary issue is that ETL_WH runs briefly every 15 minutes but remains active for up to 10 idle minutes after each run because AUTO_SUSPEND is set too high. Snowflake best practice for intermittent workloads is to use AUTO_SUSPEND aggressively so warehouses stop quickly when no longer needed, while keeping AUTO_RESUME enabled so jobs can start automatically. Administrators should also evaluate warehouse sizing and whether isolated workloads truly need a dedicated warehouse, since rightsizing and thoughtful consolidation can reduce credits when performance and governance requirements still permit it. In Snowflake documentation, warehouse cost optimization guidance emphasizes suspending idle warehouses, enabling auto-resume for operational efficiency, and choosing appropriate warehouse sizes based on workload behavior.
- A. Correct.
Correct. With jobs running for only 2-3 minutes every 15 minutes, an AUTO_SUSPEND setting of 600 seconds causes the warehouse to remain idle for long periods between executions, consuming credits unnecessarily. Lowering AUTO_SUSPEND helps suspend the warehouse sooner after each pipeline completes, which is a common and effective cost-control measure for intermittent workloads.
- B. Incorrect.
Incorrect. AUTO_RESUME is generally desirable for scheduled or event-driven pipelines because it allows jobs to start without manual intervention. Disabling AUTO_RESUME may reduce reliability and cause job failures or operational overhead, but it does not address the main inefficiency in this scenario: the warehouse staying idle too long before suspending.
- C. Incorrect.
Incorrect. Keeping a warehouse continuously running for intermittent workloads usually increases cost, not decreases it. Resume operations are not a reason to leave a warehouse running unnecessarily. The key cost issue is billed compute time while the warehouse remains active but idle between short job runs.
- D. Correct.
Correct. Cost management for virtual warehouses includes right-sizing and workload design. If the ETL workload can run on a smaller warehouse or share an existing warehouse without causing contention or governance issues, that can reduce total credit usage. This must be evaluated carefully based on concurrency, SLAs, and workload isolation requirements, but it is a valid cost-optimization step.
- E. Incorrect.
Incorrect. Increasing STATEMENT_TIMEOUT_IN_SECONDS is primarily about query execution limits, not warehouse cost optimization for idle time. It could even allow inefficient queries to run longer and consume more credits. It does not address the scenario's root cause of excessive idle warehouse runtime.