Your Snowflake warehouse may never be sleeping

Check your own account in ten minutes

Two read-only queries. The first measures the gap your warehouses actually see and puts it next to their settings; the others answer the same question from the other end — how often each one really suspended, and what the idling cost. You need a role that can read the SNOWFLAKE database. On most accounts that is ACCOUNTADMIN; to hand it to someone else, grant imported privileges on database snowflake to role <theirs>.

-- Median gap between queries per warehouse, last 14 days,
-- next to what that warehouse is configured to do.
-- Read-only. Run the SHOW first: the second query reads its result set.
show warehouses;

with settings as (
  select "name"          as warehouse_name,
         "size"          as warehouse_size,
         "auto_suspend"  as auto_suspend_s
  from table(result_scan(last_query_id(-1)))
),
gaps as (
  select warehouse_name,
         -- end-to-start: the gap the warehouse actually sits idle for.
         -- start-to-start would fold the previous query's runtime into it.
         datediff(
           'second',
           lag(end_time) over (partition by warehouse_name order by start_time),
           start_time
         ) as gap_s
  from snowflake.account_usage.query_history
  where start_time >= dateadd(day, -14, current_timestamp())
    and warehouse_name is not null
    and upper(execution_status) = 'SUCCESS'
    -- Statements that actually run on the warehouse. A metadata-only query or
    -- a result-cache hit does not wake one, so it should not count as traffic.
    and query_type in ('SELECT','INSERT','UPDATE','DELETE','MERGE','COPY')
),
measured as (
  select warehouse_name,
         count(*)                                as gaps_measured,
         median(gap_s)                           as median_gap_s
  from gaps
  where gap_s is not null
  group by 1
)
select s.warehouse_name,
       s.warehouse_size,
       s.auto_suspend_s,
       m.median_gap_s,
       m.gaps_measured,
       case
         when s.auto_suspend_s is null or s.auto_suspend_s = 0 then 'never suspends (setting)'
         when m.median_gap_s is null                           then 'too few queries to judge'
         when m.median_gap_s < s.auto_suspend_s                then 'effectively always on'
         else 'suspends normally'
       end as verdict
from settings s
left join measured m on m.warehouse_name = s.warehouse_name
order by s.auto_suspend_s desc nulls first;

How to read it. A median gap shorter than auto_suspend_s usually means that warehouse never suspends — you are paying for its whole active day. An auto_suspend_s of 0 or null means auto-suspend is off entirely, which is the same bill arrived at honestly.

Where it lies: a bursty workload. A dashboard that fires ten tiles at once every half hour has a median gap near zero and suspends perfectly well between bursts. That is what the next two queries are for — and if they disagree with this one, believe them. They count what happened; this one infers it.

-- And the direct check: did it ever actually go to sleep?
select warehouse_name, count(*) as autosuspends
from snowflake.account_usage.warehouse_events_history
where timestamp >= dateadd(day, -14, current_timestamp())
  and event_name   = 'SUSPEND_WAREHOUSE'
  and event_reason = 'WAREHOUSE_AUTOSUSPEND'
group by 1
order by autosuspends;

A warehouse that burned credits for a fortnight and appears with a zero — or does not appear at all — never went to sleep once. That is not an inference; it is the absence of the event.

And then the number itself. Snowflake publishes a query for this on its own metering history page: subtract the credits it can attribute to running queries from the credits the warehouse burned, and what is left is what you paid for it to sit there. Not my arithmetic — theirs, against your account.

-- What the idling actually cost. Snowflake's own query.
select warehouse_name,
       sum(credits_used_compute)                as compute_credits,
       sum(credits_attributed_compute_queries)  as query_credits,
       sum(credits_used_compute)
         - sum(credits_attributed_compute_queries) as idle_credits
from snowflake.account_usage.warehouse_metering_history
where start_time >= dateadd(day, -30, current_date())
  and end_time   <  current_date()
group by 1
order by idle_credits desc;

Multiply idle_credits by your cost per credit and you have the real version of the estimate further up this page. If the two disagree, believe this one.

• QUERY_HISTORY keeps 365 days and lags by up to 45 minutes, so do not run this and expect the last half hour.
• WAREHOUSE_EVENTS_HISTORY also keeps 365 days but lags by up to three hours.
• SHOW WAREHOUSES reports auto_suspend in seconds, and its output can be queried with RESULT_SCAN — which is the only reason these two things can be joined in one statement.
• Cost per credit varies by edition, region and contract. Check yours before turning credits into money.