ADA-C01 Question 216
Single answerGiven a scenario, manage warehouse usage in sessions, and size the warehouse accordinglyA Snowflake administrator is troubleshooting inconsistent query performance for a reporting team. Users connect through a BI tool that keeps sessions open for hours, and many dashboards run short queries every 10-15 minutes. The team currently uses a MEDIUM warehouse with AUTO_SUSPEND = 600 seconds and AUTO_RESUME = TRUE. Query history shows most statements complete in under 5 seconds, but warehouse load history shows the warehouse stays running for long periods with low utilization between bursts. Finance has asked to reduce compute cost without noticeably increasing dashboard latency. Which change is the BEST recommendation?
- A
Reduce AUTO_SUSPEND to a much lower value, such as 60 seconds, and keep AUTO_RESUME enabled on the existing warehouse
- B
Resize the warehouse from MEDIUM to LARGE so queued dashboard requests can start faster after idle periods
- C
Disable AUTO_SUSPEND so the BI sessions do not need to wait for warehouse startup when queries arrive
- D
Keep the current suspend setting, but enable statement queuing timeout so idle sessions release warehouse resources sooner
Show answer and explanation
Correct answer: A
Explanation
The best answer is to lower AUTO_SUSPEND while leaving AUTO_RESUME enabled. In Snowflake, client sessions and virtual warehouse execution are separate concepts: a session can remain connected even when the warehouse is suspended. For workloads generated by BI tools that keep sessions open but submit short queries intermittently, cost is often driven by warehouses remaining active during idle gaps rather than by query execution time. Reducing AUTO_SUSPEND helps the warehouse stop sooner after activity ends, and AUTO_RESUME allows new requests to restart it automatically with minimal operational overhead. This aligns with Snowflake best practices for intermittent workloads: size the warehouse for the actual query performance and concurrency needs, and use auto-suspend/auto-resume to avoid paying for idle compute. Relevant Snowflake documentation includes guidance on virtual warehouses, auto-suspend/auto-resume behavior, and monitoring warehouse utilization through query history and warehouse load metrics.
- A. Correct.
Correct. Open user sessions do not by themselves require a warehouse to keep running; a warehouse only needs to be active to execute statements. In a BI workload with brief, periodic queries and long idle gaps, lowering AUTO_SUSPEND reduces the amount of time the warehouse runs unnecessarily between bursts. Keeping AUTO_RESUME enabled minimizes user impact because Snowflake can automatically restart the warehouse when the next dashboard query arrives. This is the most direct cost optimization for session-based usage patterns with low-latency but intermittent workloads.
- B. Incorrect.
Incorrect. Increasing the warehouse size to LARGE would increase cost and does not address the main issue described: long periods of low utilization while the warehouse remains running. The problem is idle time caused by session behavior and suspend settings, not insufficient compute capacity. A larger warehouse is appropriate when queries are CPU-, memory-, or concurrency-constrained, which is not supported by the scenario.
- C. Incorrect.
Incorrect. Disabling AUTO_SUSPEND would likely increase costs further because the warehouse would continue running throughout long idle periods created by persistent BI sessions. While this may avoid occasional resume delays, it contradicts the requirement to reduce compute spend. Persistent sessions are a common misconception here; sessions can remain open even when the warehouse is suspended.
- D. Incorrect.
Incorrect. Statement queuing timeout controls how long queued statements wait before timing out; it does not suspend a warehouse faster or release compute during session idle time. Idle sessions do not consume warehouse compute unless they are running statements. This option confuses session management with warehouse lifecycle controls.