Snowflake AUTO_SUSPEND: stop paying for idle warehouses

Your Snowflake bill is a clock, and nobody told the clock to stop. A virtual warehouse bills credits for every second it is running, whether it is crunching a query or sitting there waiting for one. Picture a Medium warehouse that stays started for ten hours a day but only executes queries for three of them. Those seven idle hours cost 28 credits a day, roughly 850 a month. At $3 a credit that is about $2,550 for compute that did nothing. Here is how to measure exactly how much of your bill is idle, and why the obvious fix saves less than you think.

Why an idle Snowflake warehouse still burns credits

Snowflake bills compute by warehouse-second, and the meter runs whenever the warehouse is up. The docs say virtual warehouses "consume credits while running, regardless of whether they are performing any work". A suspended warehouse costs nothing.

The rate depends only on size, and Snowflake publishes the multiplier for every warehouse size. Each step up the ladder doubles the credit burn.

That last column assumes 730 hours in a month and a warehouse that never suspends. Substitute your own contract rate for the $3. Snowflake publishes list prices per credit by edition and region on its pricing page, and negotiated capacity contracts land below list.

The setting that controls how much idle time a Snowflake warehouse bills is AUTO_SUSPEND, a per-warehouse timeout in seconds. Snowflake defaults it to 600, so a warehouse idles for ten minutes after its last query before shutting down. Set it to 0 or NULL and the warehouse never suspends at all, billing its full per-hour rate for every hour it stays started.

Its partner is AUTO_RESUME, which defaults to TRUE. With auto-resume on, the next query wakes the warehouse by itself and the suspension is invisible to whoever ran it.

Paying for reserved capacity you never use is a pattern that repeats across the stack. It is the same arithmetic behind Kubernetes pods that request far more CPU than they consume, and behind an Azure VM that reads as stopped while still billing full compute.

The 60-second minimum that caps your savings

Snowflake bills per second, "with a 60-second (i.e. 1-minute) minimum" charged every single time a warehouse starts or resumes. Their own worked example: a warehouse that runs 61 seconds, shuts down, then restarts and runs for less than 60 seconds is billed for 121 seconds (60 + 1 + 60).

So every resume costs you a full minute of that warehouse's size, no matter how brief the query. The savings curve therefore flattens at a level you can calculate in advance.

Take a Medium warehouse (4 credits an hour) serving one eight-second query every 90 seconds across an eight-hour window. That is 320 queries, billed three different ways depending on the timeout.

On that 320-query Medium warehouse, dropping AUTO_SUSPEND from 600 seconds to 60 seconds saves 24% of the bill. Dropping it to 10 seconds saves 33%, but only by forcing all 320 queries to trigger a fresh 60-second-minimum resume, and the savings curve stops there.

That floor is arithmetic. You cannot be billed less than 60 seconds per resume, so with a query arriving every 90 seconds your bill can never fall below 60/90, which is 67% of the wall clock. Tuning AUTO_SUSPEND on a warehouse queried every 90 seconds cannot save you more than a third of the bill, and any setting from 1 to 10 seconds buys exactly the same bill as any other. 0 sits outside that curve: it disables suspension entirely and bills the full 32 credits, the worst row in the table rather than the best. Snowflake warns about the pathological version directly: with regular gaps of two or three minutes and a low timeout, "your warehouse will be in a continual state of suspending and resuming", paying the 60-second minimum each round.

The cache is the other half of the trade. A running warehouse holds table data on local SSD, and per the docs that "cache is dropped when the warehouse is suspended, which might result in slower initial performance for some queries after the warehouse is resumed". Slower queries run longer and bill more credits. Push AUTO_SUSPEND too low on a dashboard warehouse and you can hand back your savings in re-scanned data. Under its own "Automating warehouse suspension" guidance, Snowflake recommends setting auto-suspend to "a low value (for example, 5 or 10 minutes or less) because Snowflake utilizes per-second billing". Match the number to the real gaps in your query traffic.

Set AUTO_SUSPEND a little below your typical gap between queries.

The multi-cluster minimum that auto-suspend cannot fix

MIN_CLUSTER_COUNT produces idle credits too, and no AUTO_SUSPEND value will touch them.

A Snowflake multi-cluster warehouse runs between MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT clusters, and every running cluster bills at the full warehouse rate. Set the minimum to 2 on a Medium and you pay 8 credits an hour whenever that warehouse is up, whether concurrency ever needed the second cluster or not. At ten hours a day that is about 1,217 extra credits a month, roughly $3,650 at $3 a credit, for a cluster that may have never queued a single query.

Worse, setting the minimum equal to the maximum puts the warehouse in Maximized mode, where Snowflake "starts all the clusters so that maximum resources are available while the warehouse is running." That is occasionally what you want for a known concurrency spike, but as a permanent setting it is often a leftover from a launch-day load test.

Auto-scale mode is the alternative, and it is what you get whenever the maximum is greater than the minimum. Leave the minimum at 1 and Snowflake adds clusters when queries actually queue, then drops them when the queue clears.

Find your idle credits by hand

Everything below is read-only SQL against SNOWFLAKE.ACCOUNT_USAGE. That schema is restricted by default, so grant your role access first from ACCOUNTADMIN.

GRANT DATABASE ROLE SNOWFLAKE.USAGE_VIEWER TO ROLE ANALYST_RO;

Start with how many of your credits bought nothing. WAREHOUSE_METERING_HISTORY carries a column built for exactly this. CREDITS_ATTRIBUTED_COMPUTE_QUERIES is the "credits attributed to queries in the hour", and the docs state that warehouse idle time is not included in it. Subtract it from total compute and the remainder is your idle burn.

SELECT
  warehouse_name,
  ROUND(SUM(credits_used_compute), 1)                AS credits_compute,
  ROUND(SUM(credits_attributed_compute_queries), 1)  AS credits_on_queries,
  ROUND(SUM(credits_used_compute)
        - SUM(credits_attributed_compute_queries), 1) AS credits_idle,
  ROUND(100 * (SUM(credits_used_compute)
        - SUM(credits_attributed_compute_queries))
        / NULLIF(SUM(credits_used_compute), 0), 1)    AS pct_idle
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
HAVING SUM(credits_used_compute) > 0
ORDER BY credits_idle DESC;

Sort by credits_idle, not by pct_idle. A tiny warehouse that is 95% idle is a rounding error, while a Large one at 40% idle is your invoice.

Now pull the settings behind those numbers. SHOW WAREHOUSES returns them, and RESULT_SCAN lets you filter the output like a table. The column names come back lowercase, so they need double quotes.

SHOW WAREHOUSES;

SELECT "name", "state", "size", "auto_suspend", "auto_resume",
       "min_cluster_count", "max_cluster_count", "started_clusters"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "auto_suspend" IS NULL
   OR "auto_suspend" = 0
   OR "auto_suspend" > 600
   OR "min_cluster_count" > 1
ORDER BY "name";

Three kinds of row come back. An auto_suspend of null or 0 is a warehouse that never suspends, and it is the most expensive thing on this list. A value above 600 is a warehouse lingering past Snowflake's own default. A min_cluster_count above 1 is the multi-cluster problem from the previous section.

Before you tighten anything, check whether the warehouse is already thrashing. Every row in WAREHOUSE_EVENTS_HISTORY with an event name of RESUME_WAREHOUSE is a 60-second minimum you paid.

SELECT
  warehouse_name,
  COUNT(*)                        AS resumes_7d,
  ROUND(COUNT(*) / 7.0, 1)        AS resumes_per_day,
  ROUND(COUNT(*) * 60 / 3600.0, 2) AS warehouse_hours_of_minimums
FROM snowflake.account_usage.warehouse_events_history
WHERE timestamp >= DATEADD('day', -7, CURRENT_TIMESTAMP())
  AND event_name = 'RESUME_WAREHOUSE'
GROUP BY warehouse_name
ORDER BY resumes_7d DESC;

Multiply warehouse_hours_of_minimums by the warehouse's credits per hour to price the thrash. If that number is already large, your AUTO_SUSPEND is too low, and the fix runs the other direction.

ACCOUNT_USAGE views lag real activity by up to three hours, so today's last few hours will read low. Query a window of at least a week and the lag stops mattering.

AUTO_SUSPEND is not in ACCOUNT_USAGE

Look for a settings view next to the metering one and you will come up empty. SNOWFLAKE.ACCOUNT_USAGE ships three warehouse views, WAREHOUSE_METERING_HISTORY, WAREHOUSE_LOAD_HISTORY and WAREHOUSE_EVENTS_HISTORY. All three record what a warehouse did, and none carries a configuration column such as AUTO_SUSPEND. There is no ACCOUNT_USAGE.WAREHOUSES view, and INFORMATION_SCHEMA has no warehouse view either.

That leaves SHOW WAREHOUSES as the only place the setting is published, which is why the query above reaches it through RESULT_SCAN instead of a plain SELECT. It also sets up the next problem. SHOW WAREHOUSES filters its output by privilege while the metering views cover the whole account, so you cannot sidestep that filter by joining to an account-wide settings table, because no such table exists.

Two things follow that are worth planning for. Reading settings across the whole account needs a role holding a privilege on every warehouse, so either the sweep runs as ACCOUNTADMIN or you grant MONITOR one warehouse at a time. And SHOW WAREHOUSES reports the configuration as it stands right now, so a warehouse somebody retuned last week shows you today's value while the metering history still carries the credits the old value burned.

The warehouses your settings query cannot see

The settings query above can return a shorter list than the idle-credits query that fed it.

The two read from different places under different rules. SNOWFLAKE.ACCOUNT_USAGE visibility comes from the database role you granted at the start of the last section, and it covers the whole account. SHOW WAREHOUSES "only returns objects for which the current user's current role has been granted at least one access privilege".

Follow this post literally and you have built that gap yourself. SNOWFLAKE.USAGE_VIEWER gives a role nothing on any individual warehouse. So ANALYST_RO ranks every warehouse in the account by idle credits, then lists settings for only the ones it happens to hold a privilege on. Nothing raises an error. The absent rows look exactly like warehouses that were already fine, so count them before you trust the worklist.

SHOW WAREHOUSES;

WITH visible AS (
  SELECT "name" AS warehouse_name
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
),
metered AS (
  SELECT DISTINCT warehouse_name
  FROM snowflake.account_usage.warehouse_metering_history
  WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
    AND credits_used_compute > 0
)
SELECT m.warehouse_name AS billed_but_not_listed
FROM metered m
LEFT JOIN visible v ON v.warehouse_name = m.warehouse_name
WHERE v.warehouse_name IS NULL
ORDER BY 1;

Each row burned credits in the last 30 days and never reached your settings query. Two different things put it there.

The common one is a missing grant, and MONITOR on that warehouse clears it. Snowflake has no bulk or future grant for warehouses, so this is one statement per name.

GRANT MONITOR ON WAREHOUSE FINANCE_WH TO ROLE ANALYST_RO;

The other is a warehouse that no longer exists. Account usage views "include records for all objects that have been dropped", so a warehouse someone deleted last week still carries its credits here and will never appear in SHOW WAREHOUSES again. Those credits are spent and no setting remains to change. Work out which case you have before you go asking for grants.

SELECT warehouse_name, event_name, timestamp
FROM snowflake.account_usage.warehouse_events_history
WHERE event_name = 'DROP_WAREHOUSE'
  AND timestamp >= DATEADD('day', -30, CURRENT_TIMESTAMP())
ORDER BY timestamp DESC;

A name in both result sets was dropped inside your window. Everything else is a live warehouse you cannot see yet.

Change the settings with no downtime

ALTER WAREHOUSE takes effect immediately and never interrupts a running query. Nothing here requires a maintenance window.

Confirm auto-resume is on before you shorten any timeout. With AUTO_RESUME = FALSE, a suspended warehouse rejects queries until somebody resumes it by hand, which turns a cost fix into an incident.

ALTER WAREHOUSE ANALYTICS_WH SET AUTO_RESUME = TRUE;

Then set the timeout to sit just under the real gap between queries on that warehouse. For a BI warehouse fielding dashboard traffic every minute or two, 120 seconds is a reasonable landing spot. For an ETL warehouse that runs a batch and then goes quiet for hours, 60 is fine.

ALTER WAREHOUSE ANALYTICS_WH SET AUTO_SUSPEND = 120;

Reset a pinned multi-cluster minimum in the same way. This releases the extra clusters and lets auto-scale add them back only when queries queue.

ALTER WAREHOUSE REPORTING_WH SET MIN_CLUSTER_COUNT = 1;

If a warehouse is sitting started right now with no work in flight, you can stop the meter this second.

ALTER WAREHOUSE ANALYTICS_WH SUSPEND;

Rollback is the same statement with the old value, and it applies just as instantly.

ALTER WAREHOUSE ANALYTICS_WH SET AUTO_SUSPEND = 600;
ALTER WAREHOUSE REPORTING_WH SET MIN_CLUSTER_COUNT = 2;

All of this scripts cleanly from the Snowflake CLI if you would rather keep it in version control than in a worksheet.

snow sql -c prod -q "ALTER WAREHOUSE ANALYTICS_WH SET AUTO_SUSPEND = 120"

Change one warehouse, wait a week, then re-run the idle-credits query. Give it a full week because ACCOUNT_USAGE lags, and because a Monday morning looks nothing like a Friday afternoon. Watch query durations on that warehouse at the same time. If they climbed noticeably, the cache is telling you the timeout went too low.

A quick decision tree

graph TD
  A[Warehouse with idle credits] --> Z{Listed by SHOW WAREHOUSES?}
  Z -- No --> Y{Name in DROP_WAREHOUSE events?}
  Y -- Yes --> Y1[Dropped in the window: credits are spent and no setting remains to change]
  Y -- No --> Y2[Live but invisible: GRANT MONITOR on that warehouse]
  Z -- Yes --> B{min_cluster_count > 1?}
  B -- Yes --> C[Set MIN_CLUSTER_COUNT = 1 and let auto-scale add clusters]
  B -- No --> D{auto_suspend null or 0?}
  D -- Yes --> E[Set AUTO_SUSPEND, confirm AUTO_RESUME = TRUE first]
  D -- No --> F{Resumes per day already high?}
  F -- Yes --> G[Raise AUTO_SUSPEND: 60-second minimums are eating the saving]
  F -- No --> H{Typical gap between queries?}
  H -- Minutes --> I[Set AUTO_SUSPEND just under the gap, around 120 s]
  H -- Hours --> J[Set AUTO_SUSPEND to 60 s]

Related reading

Idle credits are one version of a wider habit, paying full rate for volume nobody consumes.

• Cut Datadog log costs: indexing bills 17x ingestion is the observability version. Datadog meters a log twice, once to ingest it and again to index it, so the debug chatter nobody has ever searched bills the same per event as the errors you page on.
• Find unused Postgres indexes and stop paying to keep them asks the same question of Postgres. The storage is the cheap part, because every insert and update also pays to keep an index current that no query plan has touched in months.

Common questions

Does a Snowflake warehouse cost money when it is idle?

Yes. Snowflake bills a virtual warehouse for every second it is in the started state, and the docs say warehouses consume credits while running regardless of whether they are performing any work. A Medium warehouse burns 4 credits an hour whether it runs a thousand queries or none. Only a suspended warehouse costs zero.

What is a good AUTO_SUSPEND value for Snowflake?

Set it a little below the typical gap between queries on that specific warehouse. An ETL warehouse that runs a batch then goes quiet is fine at 60 seconds. A BI warehouse fielding dashboard traffic every minute or two does better around 120. Snowflake recommends a low value, for example 5 or 10 minutes or less, because billing is per second. The default is 600 seconds.

Can setting AUTO_SUSPEND too low increase my Snowflake bill?

It can stop helping entirely, and it can raise query costs indirectly. Every resume is billed at a 60-second minimum, so a warehouse that suspends and resumes constantly pays a full minute each time. Suspension also drops the warehouse's local data cache, so the next queries re-scan from storage and run longer, which bills more credits.

How do I find idle credits in Snowflake?

Query SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY and subtract CREDITS_ATTRIBUTED_COMPUTE_QUERIES from CREDITS_USED_COMPUTE. The attributed column covers only credits spent executing queries and excludes warehouse idle time, so the difference is the idle burn per warehouse. Group by WAREHOUSE_NAME over a 30-day window and sort by that difference.

Does changing AUTO_SUSPEND require downtime?

No. ALTER WAREHOUSE ... SET AUTO_SUSPEND applies immediately and does not interrupt queries already running. Rolling back is the same statement with the previous value. Confirm AUTO_RESUME is TRUE before shortening a timeout, because a suspended warehouse with auto-resume off will reject queries until somebody resumes it by hand.

Which ACCOUNT_USAGE view has the Snowflake AUTO_SUSPEND setting?

None of them. SNOWFLAKE.ACCOUNT_USAGE carries three warehouse views, WAREHOUSE_METERING_HISTORY, WAREHOUSE_LOAD_HISTORY and WAREHOUSE_EVENTS_HISTORY, and all three record activity rather than configuration. INFORMATION_SCHEMA has no warehouse view at all. Read the setting with SHOW WAREHOUSES, then wrap it in RESULT_SCAN(LAST_QUERY_ID()) to filter the output like a table. That command returns only the warehouses your current role holds a privilege on, so an account-wide sweep needs ACCOUNTADMIN or a MONITOR grant per warehouse.

Why does SHOW WAREHOUSES return fewer warehouses than my idle-credits query?

Because the two read from different places. SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY covers the entire account once your role holds the USAGE_VIEWER database role, while SHOW WAREHOUSES returns only objects your current role has been granted at least one access privilege on. Granting USAGE_VIEWER by itself gives a role no privilege on any warehouse, so the metering query can rank a warehouse that the settings query never lists. The second cause is a dropped warehouse, because account usage views keep records for objects that no longer exist.

Why does MIN_CLUSTER_COUNT above 1 cost so much?

Each running cluster in a multi-cluster warehouse bills at the full warehouse rate. A Medium with a minimum of 2 costs 8 credits an hour instead of 4 for as long as it is up, even if the second cluster never takes a query. At ten hours a day that second cluster alone adds roughly 1,217 extra credits a month, about $3,650 at $3 a credit. Setting the minimum equal to the maximum also puts the warehouse in Maximized mode, which starts every cluster whenever the warehouse runs.

How OhChimp approaches this

Every change in this post is one ALTER WAREHOUSE away. Working out which warehouse needs which change means re-running the metering queries every time a schedule shifts or a team spins up a warehouse of its own.

OhChimp reads your warehouse metering and settings through a read-only role and ranks every warehouse by the credits it consumed. It flags the warehouses that never suspend, the ones that suspend too slowly, and the multi-cluster minimums holding idle clusters online. Each finding arrives as an ALTER WAREHOUSE plan you review, with its confidence score and risk level attached and the rollback statement written out beside it. Nothing changes in your account until you click apply.

OhChimp then measures the saving against your real Snowflake invoice, so you see the credits that actually stopped being spent.

The Snowflake integration page has the details of the read-only connection.

All OhChimp posts