A real cost optimization engagement: the exact queries we ran to find waste, the warehouse changes we made, and the monitoring we set up to keep costs down.
A logistics client came to us spending $14,000/month on Snowflake. Their data was growing, their team was growing, and the bill was growing faster than both. They asked us to figure out why.
Six weeks later, the bill was $8,400/month. Same data, same queries, same dashboards. Here is exactly what we did.
Before changing anything, we needed to understand the spend. Snowflake charges for two things: compute (warehouses running queries) and storage (data sitting on disk). For this client, compute was 87% of the bill.
We ran this query on day one:
-- Top 10 most expensive warehouses (last 30 days)
select
warehouse_name,
sum(credits_used) as total_credits,
round(sum(credits_used) * 3.00, 2) as estimated_cost_usd,
round(avg(credits_used_compute), 4) as avg_credits_per_execution,
count(*) as total_executions
from snowflake.account_usage.warehouse_metering_history
where start_time >= dateadd(day, -30, current_timestamp())
group by warehouse_name
order by total_credits desc
limit 10;
The results told us everything:
| Warehouse | Credits | Est. Cost | Avg per Execution |
|---|---|---|---|
ANALYTICS_WH | 2,100 | $6,300 | 0.82 |
TRANSFORM_WH | 980 | $2,940 | 0.15 |
LOADING_WH | 410 | $1,230 | 0.08 |
REPORTING_WH | 340 | $1,020 | 1.45 |
| Others | 180 | $540 | — |
Two warehouses — ANALYTICS_WH and REPORTING_WH — were responsible for 72% of the bill.
Snowflake warehouses cost money when they are running, not when they are suspended. The default auto-suspend is 10 minutes, which means a warehouse that runs one query every 8 minutes never suspends.
-- Warehouse suspend settings and actual idle time
select
warehouse_name,
warehouse_size,
auto_suspend, -- in seconds
round(avg(
case when query_count = 0 then 1 else 0 end
) * 100, 1) as pct_idle_minutes
from snowflake.account_usage.warehouse_metering_history
where start_time >= dateadd(day, -7, current_timestamp())
group by 1, 2, 3
order by pct_idle_minutes desc;
ANALYTICS_WH was idle 64% of the time but set to auto-suspend after 10 minutes (600 seconds). REPORTING_WH was X-Large and ran fewer than 20 queries per day.
Most Snowflake warehouses are oversized. Teams pick Medium or Large "just in case" and never revisit. We ran the query execution profile to see if smaller warehouses would actually slow things down:
-- Query performance by warehouse (are we over-provisioned?)
select
warehouse_name,
warehouse_size,
count(*) as query_count,
round(avg(total_elapsed_time) / 1000, 2) as avg_duration_sec,
round(median(total_elapsed_time) / 1000, 2) as median_duration_sec,
round(percentile_cont(0.95) within group (order by total_elapsed_time) / 1000, 2) as p95_duration_sec,
round(avg(bytes_scanned) / power(1024, 3), 2) as avg_gb_scanned
from snowflake.account_usage.query_history
where start_time >= dateadd(day, -14, current_timestamp())
and execution_status = 'SUCCESS'
group by 1, 2
order by query_count desc;
REPORTING_WH was X-Large and the median query scanned 200MB of data and ran in 1.8 seconds. That is a Small warehouse workload. We downsized it and the median query time went from 1.8s to 2.3s — a difference no dashboard user would notice.
TRANSFORM_WH ran dbt jobs. We tested the full dbt build on a Small warehouse: it went from 12 minutes to 18 minutes. Since dbt runs at 5 AM and nobody is waiting, the 6-minute increase was a fine trade for a 50% cost reduction on that warehouse.
Cost is not just warehouse size — it is also how many queries run and how much data they scan. We found two patterns burning credits:
-- Find queries scanning the most data
select
query_id,
user_name,
warehouse_name,
round(bytes_scanned / power(1024, 3), 2) as gb_scanned,
round(total_elapsed_time / 1000, 1) as duration_sec,
substr(query_text, 1, 200) as query_preview
from snowflake.account_usage.query_history
where start_time >= dateadd(day, -7, current_timestamp())
and bytes_scanned > 1e10 -- >10 GB
order by bytes_scanned desc
limit 20;
We found a Looker explore that was generating SELECT * across a 400GB table without a date filter. Adding a required order_date filter in the LookML brought the scan down from 400GB to ~2GB per query.
The dbt project was running a full dbt build every hour. Most models only needed to run once a day. We switched to tag-based runs:
# dbt_project.yml
models:
project_name:
marts:
finance:
+tags: ["daily"]
realtime:
+tags: ["hourly"]
# Hourly job: only run models tagged 'hourly'
dbt build --select tag:hourly
# Daily job at 5 AM: run everything
dbt build
This alone cut TRANSFORM_WH credits by 35%.
Optimization without monitoring is temporary. Costs creep back up as new queries and users get added. We set up a lightweight monitoring layer:
-- Daily cost tracking (schedule this as a dbt model or Airflow task)
create or replace table monitoring.daily_warehouse_costs as
select
date_trunc('day', start_time) as cost_date,
warehouse_name,
sum(credits_used) as credits,
round(sum(credits_used) * 3.00, 2) as cost_usd
from snowflake.account_usage.warehouse_metering_history
where start_time >= dateadd(day, -90, current_timestamp())
group by 1, 2;
We piped this into a Metabase dashboard with alerts: if any warehouse exceeds 120% of its 30-day average daily spend, the data team gets a Slack notification. We caught three runaway queries in the first month alone.
After six weeks:
| Change | Credit Savings | Monthly Savings |
|---|---|---|
| Auto-suspend 600s → 60s (all warehouses) | ~800 credits | $2,400 |
| Downsize REPORTING_WH (XL → M) | ~220 credits | $660 |
| Downsize ANALYTICS_WH (M → S) | ~500 credits | $1,500 |
| Tag-based dbt runs | ~340 credits | $1,020 |
| Fix full table scan in Looker | ~120 credits | $360 |
| Total | ~1,980 credits | $5,940 |
None of these changes required rewriting pipelines. None broke dashboards. The hardest part was getting the client to agree to downsize warehouses — there is always a fear that smaller means slower. Benchmarking on real workloads eliminates that fear.
Snowflake cost optimization is not a one-time project. It is a practice. Run the diagnostic queries monthly. Review warehouse sizes quarterly. And always, always set auto-suspend to 60 seconds unless you have a specific reason not to.