ADA-C01 Question 396
Single answerApply techniques for cost optimizationA Snowflake administrator reviews monthly costs and finds that a reporting warehouse named BI_WH is consuming far more credits than expected. Query History shows hundreds of short dashboard queries during business hours, but Warehouse Metering History shows the warehouse stays running overnight and on weekends with very little activity. Users also complain about occasional slowdowns when many dashboard users connect at the same time. The company wants to reduce compute cost without hurting the daytime dashboard experience. Which action is the BEST recommendation?
- A
Increase BI_WH to a larger warehouse size and disable auto-suspend so queries finish faster
- B
Enable auto-suspend with a short timeout, enable auto-resume, and configure BI_WH as a multi-cluster warehouse with auto-scaling
- C
Convert BI_WH to a single-cluster warehouse and increase the statement timeout so queued queries can finish
- D
Keep BI_WH running continuously, but reduce costs by lowering the data retention period for the reporting schema
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use warehouse lifecycle and scaling features together: auto-suspend, auto-resume, and multi-cluster auto-scaling. In Snowflake, virtual warehouses consume credits while running, even when idle, so long idle periods are a common and important cost-optimization target. Auto-suspend minimizes idle compute usage, and auto-resume ensures users do not need manual intervention. For dashboard and BI workloads with many concurrent short queries, a multi-cluster warehouse with auto-scaling is often more cost-effective than simply sizing up a warehouse and leaving it running continuously, because additional clusters can be added only when concurrency requires them. Snowflake documentation and best practices consistently recommend monitoring Query History and Warehouse Metering History to identify idle time, tuning auto-suspend settings carefully, and using multi-cluster warehouses for concurrency-heavy workloads rather than for purely long-running single-query performance problems.
- A. Incorrect.
Incorrect. Increasing warehouse size may improve performance for some workloads, but disabling auto-suspend would likely increase costs because the warehouse would continue consuming credits while idle overnight and on weekends. This option addresses performance only partially and directly conflicts with the cost-optimization goal.
- B. Correct.
Correct. Auto-suspend and auto-resume are standard Snowflake cost-optimization techniques for intermittent workloads because warehouses do not consume compute credits while suspended. A short suspend timeout reduces idle credit usage, and auto-resume preserves usability for dashboard users. Configuring the warehouse as multi-cluster with auto-scaling helps absorb concurrency spikes from many simultaneous dashboard sessions without permanently oversizing the warehouse. This combination addresses both excessive idle spend and occasional daytime contention.
- C. Incorrect.
Incorrect. Changing to a single-cluster warehouse would generally reduce the warehouse's ability to handle concurrency spikes, which is already a problem in this scenario. Increasing statement timeout does not optimize costs; it can simply allow queued or long-running statements to wait longer. This does not address unnecessary idle runtime overnight.
- D. Incorrect.
Incorrect. Lowering data retention can reduce storage-related costs in some cases, especially with Time Travel and Fail-safe implications, but the scenario specifically identifies compute waste from an idle warehouse remaining running. Keeping the warehouse running continuously ignores the main source of excess spend. This option targets the wrong cost category.