ARA-C01 Question 414
Single answerImpact of costsA retail company runs a daily ELT pipeline in Snowflake. Source files arrive every 5 minutes and are loaded into staging tables continuously. A large transformation job then runs once per hour using a dedicated MEDIUM warehouse. Finance reports that Snowflake costs increased sharply after the team changed the warehouse setting to AUTO_SUSPEND = 600 seconds to reduce startup delays between tasks. Query history shows the transformation workload itself has not changed. As the architect, which action is MOST likely to reduce compute cost without materially affecting the hourly transformation SLA?
- A
Reduce AUTO_SUSPEND on the transformation warehouse to a much lower value, such as 60 seconds, so the warehouse stops billing sooner between hourly runs
- B
Enable Search Optimization Service on the staging tables so the warehouse can suspend faster after each transformation job
- C
Increase the transformation warehouse size from MEDIUM to LARGE so each hourly run completes faster and total credits decrease
- D
Convert the staging tables to transient tables so hourly transformation queries consume fewer warehouse credits
Show answer and explanation
Correct answer: A
Explanation
The key architectural issue is the impact of warehouse idle time on compute cost. Snowflake virtual warehouses accrue compute charges while running, including periods when they are idle but not yet suspended. For periodic workloads such as an hourly transformation pipeline, setting AUTO_SUSPEND too high can create avoidable idle billing after each run. Snowflake best practices generally recommend using the lowest practical auto-suspend interval for intermittent workloads, balanced against resume latency and the one-minute minimum charge when a warehouse starts. In this scenario, the workload pattern did not change, but the warehouse remained running longer between jobs, so reducing AUTO_SUSPEND is the most direct and reliable way to lower cost without materially affecting the SLA. Relevant Snowflake guidance includes warehouse sizing and cost optimization best practices, warehouse auto-suspend/auto-resume behavior, and understanding the difference between compute costs, storage costs, and optional services such as Search Optimization.
- A. Correct.
Correct. Snowflake bills virtual warehouse compute per second, with a minimum charge when a warehouse resumes. If an hourly transformation job finishes and the warehouse remains idle for up to 600 seconds before suspending, that idle time still incurs compute charges. Lowering AUTO_SUSPEND to something like 60 seconds is a common cost-control best practice when workloads are periodic and can tolerate resume latency. In this scenario, the transformation runs only once per hour, so a 10-minute idle period after each run can significantly increase cost without improving throughput.
- B. Incorrect.
Incorrect. Search Optimization Service can improve the performance of highly selective queries on supported tables, but it does not control warehouse suspension behavior. It is also a separate cost feature and may increase overall spend if enabled unnecessarily. The misconception is assuming all performance features reduce compute cost in every workload; here the identified issue is idle warehouse time caused by a long auto-suspend setting.
- C. Incorrect.
Incorrect. A larger warehouse may complete work faster, but it consumes credits at a higher rate. Cost may go down only if runtime decreases enough to offset the larger warehouse size, which is not guaranteed. In this case, finance observed costs rose after changing AUTO_SUSPEND, while the workload itself stayed constant. That points to idle billing, not insufficient warehouse size. Increasing size does not directly solve idle time between hourly runs.
- D. Incorrect.
Incorrect. Transient tables reduce data protection costs by limiting or eliminating Fail-safe storage compared with permanent tables, but they do not reduce the compute credits consumed by transformation queries. This option reflects a common confusion between storage-related cost controls and warehouse compute cost controls.