Every warehouse cost audit I have run has started the same way, and ended with the same finding at the top of the list: the warehouse was running when nothing was querying it.
It is unglamorous. It is also usually the largest single number on the page.
Why it happens
Snowflake bills per second while a warehouse is running, with a sixty-second minimum each time it resumes. AUTO_SUSPEND decides how long it idles before shutting down. The default in most projects is whatever someone typed the day they created it, and it is rarely revisited.
The trap is that a long suspend time feels prudent. Nobody wants a cold warehouse in front of a dashboard. So it gets set to ten minutes, or an hour, or disabled entirely. And then a warehouse that does eleven minutes of real work a day is billed for 8 hours.
Measure it before you change it
Do not touch the setting first. Get the number, so the change has evidence behind it.
-- Credits burned per warehouse, last 30 days
select
warehouse_name,
round(sum(credits_used), 1) as credits,
round(sum(credits_used_compute), 1) as compute,
round(sum(credits_used_cloud_services),1) as cloud_services
from snowflake.account_usage.warehouse_metering_history
where start_time >= dateadd(day, -30, current_timestamp())
group by 1
order by credits desc;That gives you the ranking. Now find out how much of it was idle:
-- Query seconds vs running seconds, per warehouse
with running as (
select warehouse_name,
sum(datediff('second', start_time, end_time)) as running_s
from snowflake.account_usage.warehouse_events_history
where event_name = 'RESUME_WAREHOUSE'
and timestamp >= dateadd(day, -30, current_timestamp())
group by 1
),
querying as (
select warehouse_name,
sum(total_elapsed_time) / 1000 as query_s
from snowflake.account_usage.query_history
where start_time >= dateadd(day, -30, current_timestamp())
group by 1
)
select q.warehouse_name,
round(q.query_s / 3600, 1) as query_hours,
round(r.running_s / 3600, 1) as running_hours,
round(100 * q.query_s / nullif(r.running_s, 0), 1) as utilisation_pct
from querying q
join running r using (warehouse_name)
order by utilisation_pct;Sort ascending. The warehouses at the top of that list are the ones you are paying for and not using. I have seen utilisation under five percent on a warehouse that cost more than the rest of the account combined.
Three changes to make
Set AUTO_SUSPEND to 60 seconds on everything except a warehouse fronting an interactive dashboard where a person would notice the cold start.
alter warehouse transforming set auto_suspend = 60;Separate the workloads. One warehouse serving dbt runs, ad-hoc analysts and a BI tool cannot be tuned for any of them. Split it, then tune each.
Do not size up to fix a slow query. A larger warehouse makes a badly written query cost more, faster. Read the query profile first: spilling to remote storage is a query problem, not a sizing problem.
The counter-argument, honestly
Sixty seconds is not free. Every resume has a minimum billing period and a cold cache, so a warehouse that wakes fifty times an hour can cost more than one that idles. That is a real case, and it is why you measure the resume count before you decide:
select warehouse_name, count(*) as resumes
from snowflake.account_usage.warehouse_events_history
where event_name = 'RESUME_WAREHOUSE'
and timestamp >= dateadd(day, -7, current_timestamp())
group by 1 order by resumes desc;High resume counts with short queries: raise the suspend a little. Low resume counts with long idle: drop it to 60. Either way the decision comes from the data, not from a default someone left behind three years ago.
Where it usually lands
Idle compute is normally the largest single item, but it is rarely more than a third of the total. The rest is full refreshes that should have been merges, and objects that are rebuilt nightly and read never. Those take longer to find and longer to fix, which is why I do the auto-suspend pass first. It funds the rest of the work in the first week.