ARA-C01 Question 237
Single answerCostsA retail company runs hundreds of ELT jobs overnight in Snowflake. The jobs are orchestrated by an external scheduler and execute many short SQL statements with unpredictable gaps of 1-3 minutes between bursts. The current design uses a Medium warehouse with AUTO_SUSPEND = 600 seconds and AUTO_RESUME enabled. Finance reports that compute cost is higher than expected, even though query execution time is relatively low. The architect must reduce cost without materially increasing job completion time or adding operational complexity. Which change is the BEST recommendation?
- A
Reduce AUTO_SUSPEND to 60 seconds so the warehouse stops sooner during idle gaps, while keeping AUTO_RESUME enabled
- B
Disable AUTO_RESUME so the scheduler can control exactly when the warehouse starts and stops
- C
Resize the warehouse from Medium to Large so each query finishes faster and total credits decrease
- D
Convert the warehouse to a multi-cluster warehouse with a minimum of 2 clusters to improve elasticity and lower cost
Show answer and explanation
Correct answer: A
Explanation
The key issue is idle warehouse time. Snowflake charges credits for virtual warehouses while they are running, not only while queries are actively executing. In this scenario, short bursts of work are separated by 1-3 minute gaps, so a 10-minute AUTO_SUSPEND causes the warehouse to remain active and billable during many idle periods. The most effective low-complexity optimization is to lower AUTO_SUSPEND to a shorter interval, such as 60 seconds, while keeping AUTO_RESUME enabled so jobs restart compute automatically when needed. This balances cost control with operational simplicity and usually has minimal impact on completion time for bursty workloads. By contrast, disabling AUTO_RESUME adds management overhead, resizing upward does not address idle cost, and multi-cluster warehouses are a concurrency feature rather than a cost-saving mechanism for sporadic batch execution. These recommendations align with Snowflake cost management guidance around right-sizing warehouses, minimizing idle runtime, and using auto-suspend/auto-resume appropriately.
- A. Correct.
Correct. Snowflake warehouses bill compute while running, including idle time. In a workload with many short statements and idle gaps of 1-3 minutes, an AUTO_SUSPEND setting of 600 seconds keeps the warehouse running through much of the idle period, unnecessarily consuming credits. Lowering AUTO_SUSPEND to 60 seconds reduces billed idle time while AUTO_RESUME minimizes operational overhead and restarts the warehouse automatically when the next statement arrives. This is a standard cost-optimization best practice for intermittent workloads.
- B. Incorrect.
Incorrect. Disabling AUTO_RESUME increases operational complexity and risks job failures or delays if the scheduler does not start the warehouse at exactly the right time. It does not inherently reduce cost more effectively than using a shorter AUTO_SUSPEND interval with AUTO_RESUME enabled. For externally orchestrated but bursty workloads, Snowflake best practice is usually to keep AUTO_RESUME enabled.
- C. Incorrect.
Incorrect. A larger warehouse may reduce elapsed runtime for some queries, but it also consumes credits at a higher rate per second. For many short ELT statements with significant idle gaps, the dominant waste described in the scenario is idle compute time, not insufficient performance. Upsizing could increase cost if the workload is not CPU- or I/O-bound enough to benefit proportionally.
- D. Incorrect.
Incorrect. Multi-cluster warehouses are intended primarily for handling concurrent query workloads by adding clusters when concurrency increases. They do not reduce the cost of intermittent idle periods; in fact, setting a minimum of 2 clusters guarantees more baseline compute consumption. This would generally raise cost for the described overnight batch pattern.