The Snowflake Cost Optimization Playbook: Cut the Bill Without Anyone Noticing

The first time I was handed a Snowflake bill and told to make it smaller, I did what most people do. I looked for the biggest warehouse and shrank it. The bill went down four percent, three dashboards got slower, and two analysts filed tickets by Thursday.

That is the wrong end of the problem. Snowflake spend is dominated by warehouse-seconds, and warehouse-seconds are dominated by two things: compute that is running while nobody is using it, and a small set of queries that scan far more than they need to. Neither of those is fixed by picking a smaller size.

What follows is the order I work in now. It is ranked by credits-saved per hour-of-my-time, and every step ends with a number you can put in a Slack message. I have used this on accounts from $8k a month to just under $200k, and the ranking has not changed.

Know what you are actually buying

Snowflake charges you for three meters, and they are not close to equal. Virtual warehouse compute is usually 80 to 90 percent of the invoice. Storage is often under 10 percent and is close to irreducible. Serverless features — automatic clustering, Snowpipe, materialized view maintenance, search optimization — are the rest, and they are the line items that grow quietly because nobody owns them.

Start by proving that split for your own account rather than trusting mine:

-- Credit split by service for the last 30 days.
-- Run as ACCOUNTADMIN or a role with ACCOUNT_USAGE access.
select
  service_type,
  sum(credits_used) as credits,
  round(100 * ratio_to_report(sum(credits_used)) over (), 1) as pct_of_total
from snowflake.account_usage.metering_history
where start_time > dateadd('day', -30, current_timestamp())
group by 1
order by credits desc;

If WAREHOUSE_METERING is not the top line, you have an unusual account and the rest of this playbook applies in a different order. In every account I have worked on, it was the top line by a wide margin.

Kill idle time first, because it is free money

Idle warehouse time is compute you paid for while no query was running. It costs nothing to fix and breaks nothing. This is the only optimization on the list with no trade-off, so it goes first.

Two settings control it. AUTO_SUSPEND is how many seconds of inactivity Snowflake waits before shutting the warehouse down. AUTO_RESUME brings it back when a query arrives. The Snowsight default of 600 seconds is far too generous — a warehouse used twice an hour bills ten minutes of nothing after each use.

-- 60 seconds is my default for everything.
-- Interactive BI warehouses can go to 120 if users complain about resume latency.
alter warehouse transforming_wh set
  auto_suspend = 60
  auto_resume = true
  initially_suspended = true;

-- Find the warehouses still on a long fuse.
select warehouse_name, auto_suspend, auto_resume, warehouse_size
from snowflake.account_usage.warehouses
where deleted_on is null
  and auto_suspend > 120
order by auto_suspend desc;

The objection I always get is cold-start latency. On modern Snowflake, resume is typically a second or two, and warm cache loss is real but usually costs less than the idle time you are buying to preserve it. The exception is a warehouse serving a dashboard that fires every few minutes all day. There, keep it warm, but do it deliberately and write down why.

Quantify the idle before and after so the win is visible:

-- Approximate idle ratio per warehouse: metered hours vs. hours with query activity.
with metered as (
  select warehouse_name, sum(credits_used) as credits
  from snowflake.account_usage.warehouse_metering_history
  where start_time > dateadd('day', -14, current_timestamp())
  group by 1
),
busy as (
  select
    warehouse_name,
    sum(total_elapsed_time) / 1000 / 3600 as query_hours
  from snowflake.account_usage.query_history
  where start_time > dateadd('day', -14, current_timestamp())
    and warehouse_name is not null
  group by 1
)
select
  m.warehouse_name,
  m.credits,
  round(b.query_hours, 1) as query_hours,
  round(m.credits / nullif(b.query_hours, 0), 2) as credits_per_query_hour
from metered m
left join busy b using (warehouse_name)
order by m.credits desc;

credits_per_query_hour well above the warehouse’s nominal credit rate means you are paying for air. On one account this single query found a LOADING_WH billing 340 credits a month to run about nine hours of actual work.

Find the ten queries that own your bill

Cost is never evenly spread. In every account I have audited, the top ten query patterns accounted for somewhere between a quarter and a half of warehouse credits. Fixing ten things is a week of work. Fixing everything is a quarter you do not have.

The trick is grouping by query shape, not by query_id, because the same dashboard tile runs 4,000 times a day with different literals:

-- Top query patterns by estimated credit consumption.
-- Approximates credits as elapsed warehouse-seconds scaled by size.
select
  query_parameterized_hash,
  any_value(left(query_text, 200)) as sample_text,
  any_value(warehouse_name) as warehouse,
  count(*) as executions,
  round(sum(total_elapsed_time) / 1000 / 3600, 2) as warehouse_hours,
  round(avg(partitions_scanned / nullif(partitions_total, 0)), 2) as avg_scan_ratio,
  sum(bytes_spilled_to_local_storage) / power(1024, 3) as gb_spilled_local,
  sum(bytes_spilled_to_remote_storage) / power(1024, 3) as gb_spilled_remote
from snowflake.account_usage.query_history
where start_time > dateadd('day', -14, current_timestamp())
  and execution_status = 'SUCCESS'
  and warehouse_size is not null
group by 1
order by warehouse_hours desc
limit 20;

Now read the extra columns, because they tell you which fix to apply. A high avg_scan_ratio on a large table is a pruning problem — go read micro-partitions and pruning and check whether someone wrapped a timestamp column in TO_DATE. Remote spill means the warehouse is too small for the working set. A pattern with 40,000 executions and a two-second runtime is a caching or scheduling problem, not a SQL problem.

I sort the results into four buckets and work them in this order:

flowchart TD
  top["Top 10 query patterns by warehouse hours"] --> q{"What does the profile say?"}
  q -->|"scan ratio near 1.0"| prune["Fix predicate or clustering"]
  q -->|"remote spill"| size["Size up one step"]
  q -->|"huge execution count"| sched["Cache, materialize, or slow the schedule"]
  q -->|"none of the above"| rewrite["Rewrite the SQL"]
  prune --> measure["Re-measure in 7 days"]
  size --> measure
  sched --> measure
  rewrite --> measure

Four buckets, four fixes. The re-measure step is what keeps the work honest.

The scheduling bucket is the one teams skip and it is often the cheapest. A dbt job that runs hourly because someone typed 0 * * * * in 2023, feeding a report nobody opens before 9 a.m., is 23 wasted runs a day. I have cut more credits by reading cron expressions than by tuning SQL.

Right-size, but size for the work, not the wish

Once idle time is gone and the top offenders are named, warehouse size becomes a real question. The short version: size up when queries spill to remote storage, size out with multi-cluster when queries queue. I wrote the full signal-by-signal version in the warehouse sizing guide, so here I will only state the cost consequence.

Sizes double in credit rate at each step. That is not automatically more expensive, because a query that finishes in half the time on the larger size costs exactly the same. Linear scaling means size is free. The failure mode is sizing up a query that does not scale — a single-threaded UDF, a small scan, a query bottlenecked on a remote fetch — where you pay double for the same duration.

-- Spill and queue signals, which tell you up versus out.
select
  warehouse_name,
  count(*) as queries,
  sum(iff(bytes_spilled_to_remote_storage > 0, 1, 0)) as remote_spill_queries,
  round(avg(queued_overload_time) / 1000, 2) as avg_queue_seconds,
  round(max(queued_overload_time) / 1000, 2) as max_queue_seconds
from snowflake.account_usage.query_history
where start_time > dateadd('day', -7, current_timestamp())
  and warehouse_name is not null
group by 1
having remote_spill_queries > 0 or avg_queue_seconds > 1
order by remote_spill_queries desc;

Remote spill on a meaningful share of queries: go up one size and re-measure. Queueing with no spill: add clusters, do not add size. Both: split the workload, because you have batch transforms and interactive queries fighting over one warehouse.

Separate workloads so you can see them

You cannot optimize what you cannot attribute. One warehouse serving dbt, BI, ad hoc analysts, and a reverse-ETL tool is a single number nobody owns. Four warehouses is four numbers with four owners, and owners optimize.

My default topology is a warehouse per workload class, sized independently, each with an AUTO_SUSPEND of 60 and a resource monitor. Add query tags so you can slice further without creating more warehouses:

-- Set at session start by the orchestrator; dbt does this via query-comment.
alter session set query_tag = '{"job":"dbt_hourly","env":"prod","team":"growth"}';

-- Then attribute spend to teams.
select
  try_parse_json(query_tag):team::string as team,
  round(sum(total_elapsed_time) / 1000 / 3600, 1) as warehouse_hours
from snowflake.account_usage.query_history
where start_time > dateadd('day', -30, current_timestamp())
  and query_tag like '{%'
group by 1
order by warehouse_hours desc;

Chargeback does not have to be real money. Publishing the table is usually enough. The month I started posting a team-by-team credit report, the growth team found and deleted a Looker schedule that was costing more than their tooling budget, and I did not have to ask.

Resource monitors: guardrails, not brakes

Every warehouse should have a resource monitor. Most of them should never fire. The point is the notification, not the suspension — you want to hear about a runaway backfill at 40 percent of budget, not discover it on the invoice.

create or replace resource monitor rm_transforming
  with credit_quota = 400            -- monthly ceiling for this warehouse
  frequency = monthly
  start_timestamp = immediately
  triggers
    on 60 percent do notify           -- early warning
    on 85 percent do notify           -- someone should be looking
    on 100 percent do suspend         -- let running queries finish
    on 110 percent do suspend_immediate;

alter warehouse transforming_wh set resource_monitor = rm_transforming;

Two things people get wrong here. First, notifications only reach users who have verified email and have notifications enabled in their Snowsight preferences — test it by setting a throwaway quota. Second, an account-level monitor that suspends everything is a production incident waiting to happen. Use warehouse-level monitors for enforcement and an account-level one for notify-only visibility.

Storage and serverless: small, but not zero

Storage rarely deserves attention, with two exceptions.

Time Travel retention multiplies the storage of high-churn tables, because every rewritten micro-partition is retained for the retention window plus seven days of fail-safe. A 90-day retention on a table that is fully rewritten nightly is a very expensive setting. I cover the arithmetic in Time Travel and cloning, but the rule is simple: 1 day for staging and dev, 7 for most production tables, longer only where a compliance requirement names a number.

Serverless features are the other quiet grower. Automatic clustering in particular bills forever and often outlives the query pattern that justified it:

select
  'automatic_clustering' as feature, table_name as object, sum(credits_used) as credits
from snowflake.account_usage.automatic_clustering_history
where start_time > dateadd('day', -30, current_timestamp())
group by 1, 2
union all
select
  'snowpipe', pipe_name, sum(credits_used)
from snowflake.account_usage.pipe_usage_history
where start_time > dateadd('day', -30, current_timestamp())
group by 1, 2
order by credits desc
limit 25;

Anything on that list without a named owner and a measured benefit gets suspended. The clustering keys guide has the full argument for when a key earns its rent.

Pitfalls

Shrinking warehouses as the first move. It is the most visible lever and the least effective one. You will make queries slower and save single-digit percentages. Idle time and bad scans come first.

Setting auto-suspend to 600 and forgetting it. This is the default and it is wrong for nearly every warehouse. Audit ACCOUNT_USAGE.WAREHOUSES quarterly, because new warehouses keep appearing with the default.

One warehouse for everything. You lose attribution, you mix batch and interactive workloads, and the single credit number can only ever be argued about, never optimized.

Resource monitors that only suspend. By the time the suspension fires you have already spent the money and now you have an outage too. Notify early, suspend late.

Optimizing a query that runs twice a month. Sort by total warehouse hours, not by worst single runtime. The 40-minute monthly report is not your problem; the 8-second query running 30,000 times a day is.

Declaring victory from one week of data. Month-end close, quarterly backfills, and Black Friday all distort. Compare 28-day windows against the previous 28 days, and keep the report running so the savings do not silently regress.

FAQ

How much can I realistically save?

On an account nobody has tuned, 25 to 40 percent within a month is normal, and roughly half of that comes from auto-suspend and scheduling changes alone. On an account that has been through this once, expect 5 to 10 percent a year of ongoing drift management. If someone promises 60 percent without touching workload, they have not seen your queries.

Should I use a smaller warehouse or a shorter query?

A shorter query, every time. Warehouse size changes shift cost between two axes that often cancel out; removing scanned bytes reduces the work itself. Fix the predicate, then decide the size.

Do multi-cluster warehouses cost more?

Only when extra clusters are actually running. With ECONOMY scaling policy Snowflake is conservative about spinning clusters up, which saves credits at the cost of some queueing; STANDARD favors latency. I use STANDARD for BI and ECONOMY for anything batch.

Is the query result cache free?

Yes, and it is the cheapest optimization available. A repeated query against unchanged data returns from the result cache with no warehouse time at all. This is one reason not to sprinkle CURRENT_TIMESTAMP() into dashboard SQL — it defeats the cache on every run.

How do I stop spend from creeping back?

Schedule the queries in this post as a weekly report into a table, and put the top-ten offender list somewhere people read. Cost work that is not on a recurring schedule decays within two quarters, which I have now watched happen at three companies.

What this means for your pipelines

Snowflake cost work is not an austerity project, it is an attribution project. Nearly every dollar of waste I have found was invisible before someone made it visible: a warehouse with no owner, a schedule nobody re-read, a clustering key from a migration two years ago, a dashboard hitting a table with a wrapped date filter. None of that requires hard trade-offs to fix. It requires someone to look.

So build the looking into the pipeline itself. Tag every session with job, team, and environment. Give every warehouse a monitor and a name that says what it is for. Materialize the ACCOUNT_USAGE queries above into a small cost mart, run it daily, and alert on week-over-week deltas rather than absolute thresholds. Your dbt project is a perfectly good home for this — it is just another set of models.

The trade-offs only start after the free wins are gone. When you get there, you will be making them with numbers in hand instead of guessing, and you will be able to tell an analyst exactly what their dashboard costs and exactly what you propose to change. That conversation goes much better than the one where you quietly shrank their warehouse and waited to see who complained.