ARA-C01 Question 415
Single answerImpact of costsA retail company runs a nightly ELT pipeline in Snowflake. The workload consists of hundreds of short SQL statements submitted by orchestration tools over a 3-hour window. The architect observes that warehouse credit consumption is much higher than expected, even though query execution time is low. Query history shows frequent warehouse start and stop events during the pipeline window. The business wants to reduce compute cost without materially increasing end-to-end processing time. Which change is the MOST appropriate?
- A
Increase the warehouse size so each statement finishes faster, then keep AUTO_SUSPEND at 60 seconds
- B
Disable AUTO_SUSPEND for the warehouse used by the nightly pipeline so it stays running throughout the batch window
- C
Enable Query Acceleration Service on the warehouse to reduce the amount of billed compute for short queries
- D
Convert the pipeline to use a multi-cluster warehouse with MAX_CLUSTER_COUNT = 3 so resume overhead is distributed across clusters
Show answer and explanation
Correct answer: B
Explanation
The key architectural issue is matching warehouse behavior to workload shape. Snowflake charges compute based on warehouse usage, and frequent suspend/resume activity can create avoidable cost inefficiency for pipelines composed of many short statements separated by brief idle periods. In this scenario, the warehouse is being cycled too aggressively during a known nightly processing window. The most appropriate action is to keep the warehouse running for that batch period, either by disabling AUTO_SUSPEND during the window or setting a much longer suspend timeout.
This aligns with Snowflake cost optimization guidance: right-size warehouses, configure AUTO_SUSPEND/AUTO_RESUME appropriately, and use separate warehouses for workloads with different usage patterns. Multi-cluster warehouses are best for concurrency scaling, not resume-cost optimization. Query Acceleration Service is a performance feature for eligible queries and is not a remedy for excessive warehouse lifecycle churn. Relevant Snowflake documentation areas include virtual warehouse cost management, AUTO_SUSPEND/AUTO_RESUME behavior, and warehouse sizing/concurrency best practices.
- A. Incorrect.
Incorrect. Increasing warehouse size may reduce query runtime for some statements, but it also increases the credit rate while still allowing frequent suspend/resume cycles if AUTO_SUSPEND remains low. For a workload made up of many short statements with gaps between them, the main cost issue is often warehouse churn and the minimum billing/resume behavior, not insufficient compute power. This is a common misconception: faster queries do not necessarily mean lower total cost when warehouse lifecycle settings are the real problem.
- B. Correct.
Correct. For a predictable 3-hour batch window with hundreds of short statements, keeping the warehouse running during the active period can reduce unnecessary resume events and the cost inefficiency caused by frequent suspend/resume cycles. Snowflake best practice is to tune AUTO_SUSPEND according to workload pattern; for bursty pipelines with small idle gaps, a longer suspend period or leaving the warehouse running during the batch window is often more cost-effective than repeatedly restarting it.
- C. Incorrect.
Incorrect. Query Acceleration Service is designed to accelerate eligible queries by offloading portions of scan and filter processing, not to reduce the billed compute cost of many short warehouse resumes. In fact, using additional acceleration features can increase cost if applied unnecessarily. This option targets query performance, not the observed warehouse start/stop cost pattern.
- D. Incorrect.
Incorrect. Multi-cluster warehouses are intended primarily to handle concurrency by adding clusters when needed. They do not solve the core issue of repeated warehouse suspension and resumption for a sequential or lightly concurrent batch pipeline made up of short tasks. In many cases, enabling additional clusters would increase compute cost rather than reduce it.