ARA-C01 Question 389
Single answerAuto-suspend/resumeA data platform team runs a nightly ELT workflow on a dedicated Snowflake virtual warehouse. The workflow starts at 1:00 AM, executes 8-12 SQL statements over about 20 minutes, and then remains idle until the next night. The team notices compute charges are higher than expected because the warehouse often stays running between statements and after the workflow completes. They also want the workflow to start automatically without requiring operators to manually resume the warehouse. Which warehouse configuration best meets these requirements while minimizing unnecessary compute usage?
- A
Set AUTO_SUSPEND to 60 seconds and AUTO_RESUME to TRUE on the warehouse
- B
Set AUTO_SUSPEND to 0 and AUTO_RESUME to TRUE so the warehouse only runs when queries are submitted
- C
Disable AUTO_SUSPEND and schedule a task to suspend the warehouse at 2:00 AM every day
- D
Set AUTO_SUSPEND to 600 seconds and leave AUTO_RESUME disabled to reduce resume events
Show answer and explanation
Correct answer: A
Explanation
For virtual warehouses, AUTO_SUSPEND controls how long a warehouse waits while idle before suspending, and AUTO_RESUME determines whether it automatically starts when a query needs compute. For bursty or scheduled workloads with limited execution windows, Snowflake best practice is typically to enable AUTO_RESUME and use a relatively short AUTO_SUSPEND interval so compute is not consumed during idle periods. Setting AUTO_SUSPEND to 0 disables auto-suspend, which is a common misconception. In this scenario, the warehouse is used once per night and should start automatically, so a short suspend interval plus AUTO_RESUME=TRUE is the most appropriate configuration.
- A. Correct.
Correct. A short AUTO_SUSPEND value such as 60 seconds helps stop the warehouse shortly after periods of inactivity between statements or after the workflow ends, reducing billed compute time. Enabling AUTO_RESUME allows the warehouse to start automatically when the first statement of the workflow runs, which removes the need for manual intervention. This is a common best-practice configuration for intermittent workloads.
- B. Incorrect.
Incorrect. AUTO_SUSPEND set to 0 disables automatic suspension rather than making it suspend immediately. That means the warehouse can continue running indefinitely when idle, which would increase compute costs. AUTO_RESUME would still work, but without suspension there is little benefit for this use case.
- C. Incorrect.
Incorrect. Disabling AUTO_SUSPEND is not appropriate for an intermittent workload with unpredictable gaps between statements. A fixed-time suspend job at 2:00 AM may leave the warehouse running unnecessarily if the workflow finishes earlier, and it would not help with idle gaps during the run. This option also adds operational complexity compared to native warehouse behavior.
- D. Incorrect.
Incorrect. A 600-second AUTO_SUSPEND keeps the warehouse running for 10 minutes after becoming idle, which is longer than necessary for a short nightly workflow focused on cost control. In addition, disabling AUTO_RESUME means the first query would fail unless something explicitly resumes the warehouse beforehand, which conflicts with the requirement for automatic startup.