skip to content

In Snowflake, what do a warehouse's AUTO_SUSPEND and AUTO_RESUME settings do, and what is the tradeoff?

level: middleimportance: should knowfreq 70%

answer

  1. compute is billed by running seconds
  2. idle timer stops the credit clock
  3. restart pays a floor and starts cold
  4. shorter is not always cheaper

basics

~20 s

AUTO_SUSPEND stops a Snowflake warehouse after that many idle seconds so it stops consuming credits; AUTO_RESUME starts it again on the next query. Short values save credits but discard the warehouse's local cache and pay a 60-second minimum on every restart.

solid answer

~50 s

A Snowflake warehouse only consumes credits while it is running, billed per second with a **60-second minimum each time it starts**. `AUTO_SUSPEND = <seconds>` tells Snowflake to suspend the warehouse after that much idle time (600 seconds by default); `AUTO_RESUME = TRUE` (the default) starts it automatically when the next query arrives, so users never see "warehouse is suspended". The tradeoff is cache and restart overhead versus idle credits. Suspending discards the warehouse's local SSD cache of table data, so the first queries after a resume re-read from remote storage and run slower. Very short values on a bursty workload can also cost *more*, because every resume re-bills the 60-second floor. Typical practice: a short value (about a minute) for spiky ad-hoc warehouses, a longer one for a warehouse serving a steady dashboard workload that benefits from a warm cache.

code

sql · 8 lines
sql
-- spiky ad-hoc warehouse: suspend fast, wake on demand
ALTER WAREHOUSE adhoc_wh SET
  AUTO_SUSPEND = 60,
  AUTO_RESUME  = TRUE,
  STATEMENT_TIMEOUT_IN_SECONDS = 1800;

-- steady dashboard warehouse: stay warm through the working day
ALTER WAREHOUSE bi_wh SET AUTO_SUSPEND = 600;

go deeper

for a junior

Know that a Snowflake warehouse bills only while running, that AUTO_SUSPEND stops it after idle seconds, and that AUTO_RESUME starts it again on the next query.

for a middle

Explain the 60-second minimum on every start, the loss of local cache on suspend, and why the right value depends on how often queries actually arrive.

for a senior

Show how you audit an account for warehouses with auto-suspend disabled, pair it with statement timeouts against runaway queries, and pick values per workload shape rather than one global number.

for a principal

Own the guardrails: default warehouse templates, whether AUTO_RESUME is allowed off as a cost gate, and how suspend policy interacts with SLAs on first-query latency.

## The billing model these settings exist for A Snowflake virtual warehouse consumes credits **only while it is running**, whether or not it is executing a query. Billing is per second, with a **minimum of 60 seconds every time the warehouse starts or resumes**. Storage is billed separately and is unaffected. So the entire cost-control question for compute reduces to: how many seconds is this warehouse in the running state? `AUTO_SUSPEND` and `AUTO_RESUME` are the two warehouse properties that answer it automatically instead of relying on humans to remember. ## AUTO_SUSPEND `AUTO_SUSPEND = <number of seconds>` suspends the warehouse after it has been idle — no queries running — for that long. The default is 600 seconds (10 minutes). Setting it to `0` or `NULL` disables auto-suspension entirely, which means the warehouse runs, and bills, until someone suspends it explicitly. That misconfiguration is the classic Snowflake bill shock. Suspension releases the compute nodes back to the cloud provider. Two consequences follow: 1. **The credit clock stops.** This is the point of the setting. 2. **The warehouse's local disk cache is gone.** While running, a warehouse caches the table data it reads on its nodes' local SSDs. Those nodes are released on suspend, so after a resume the warehouse starts cold and re-reads from remote storage. Queries immediately after a resume are therefore slower than the same queries on a warm warehouse. Note what suspension does *not* affect: results already computed are cached in the cloud services layer, independent of any warehouse, and metadata operations keep working with no warehouse running at all. ## AUTO_RESUME `AUTO_RESUME = TRUE` (the default) makes Snowflake start the warehouse automatically when a statement is submitted to it. Resume takes a few seconds in the normal case. With `AUTO_RESUME = FALSE`, a query against a suspended warehouse fails rather than waiting, and someone must run `ALTER WAREHOUSE ... RESUME` first. Teams occasionally set it false as a hard cost guard on an expensive warehouse — the deliberate friction is the feature — but it is a poor default for anything users touch. ## Choosing a value: the two opposing costs The naive reasoning is "shorter is always cheaper." It is not, because of the 60-second minimum: - **Bursty micro-queries.** A BI tool fires a 3-second query every few minutes against a warehouse with `AUTO_SUSPEND = 60`. Each burst costs the query plus the idle wait — roughly 65 seconds — and the cache is cold each time. Forty such wakeups in an hour bill far more than simply letting one small warehouse stay warm. - **Long idle gaps.** A warehouse used for a 20-minute nightly load with `AUTO_SUSPEND` disabled bills 24 hours a day for 20 minutes of work. So the rule of thumb is shaped by the workload's arrival pattern, not by a universal number: - **Ad-hoc / occasional** — short values (about 60 seconds) are right; idle gaps are long and cache warmth is worth little. - **Steady interactive dashboards** — a longer value (several minutes) keeps the local cache warm across the working day and avoids paying repeated restart minimums. - **Scheduled ETL** — short values are fine; jobs run back to back and the next job re-reads different data anyway. A useful sanity check: if the warehouse's queries arrive more often than the suspend delay, the warehouse effectively never suspends and the setting is doing nothing except protecting you overnight. ## Common failure modes - **Auto-suspend disabled "temporarily"** during an investigation and never re-enabled. Audit for warehouses with `AUTO_SUSPEND` null/zero. - **A hung or runaway query.** Auto-suspend only fires when the warehouse is *idle*; a query stuck for hours keeps it running. Note that an idle *connection* or open session does **not** keep a warehouse alive — only executing statements do. Pair auto-suspend with `STATEMENT_TIMEOUT_IN_SECONDS` so a stuck statement cannot hold compute forever. - **Tasks and scheduled jobs.** A task firing every minute on a warehouse means the warehouse never gets an idle window long enough to suspend. - **Multi-cluster confusion.** In a multi-cluster warehouse, extra clusters shut down on their own as load falls; auto-suspend governs the warehouse as a whole once it is entirely idle. ## What the interviewer is testing That you understand *why* the settings exist (compute is billed by running seconds), that you know suspension throws away warm local cache, and that you can articulate the non-obvious case where a shorter suspend delay makes the bill worse. Reciting "set it to 60 seconds to save money" without the restart minimum and cache cost is the shallow answer.

  • Why can setting AUTO_SUSPEND to 60 seconds increase the bill for a warehouse hit by frequent short queries?
    Because every start bills a 60-second minimum. A warehouse woken 40 times an hour for three-second queries bills roughly 40 minutes of compute plus the idle waits, where one warehouse left warm would have billed the hour once. Frequent wakeups also lose the local cache each time, so the queries themselves run slower.
  • Does an idle client session connected to a Snowflake warehouse prevent it from auto-suspending?
    No. Auto-suspend is driven by whether statements are executing, not by open connections or sessions. A connected but idle client does not hold the warehouse. What does hold it is a statement still running — a hung or runaway query — which is why teams pair auto-suspend with STATEMENT_TIMEOUT_IN_SECONDS.
  • What is lost when a Snowflake warehouse suspends, and what survives?
    The warehouse's local SSD cache of table data is lost, because the compute nodes are released; the next queries re-read from remote storage and run cold. What survives is everything outside the warehouse: the data itself, all metadata, and results cached in the cloud services layer, which do not depend on any warehouse being up.

saying these in an interview costs you the question

  • Believing a suspended warehouse still bills compute credits
  • Claiming an idle open session prevents auto-suspend
  • Assuming the shortest auto-suspend value is always cheapest
  • Forgetting that suspend discards the warehouse's warm local cache
  • Disabling auto-suspend so dashboards "stay fast"

context

resolving links…