The Snowflake bill was 58x the work. The query that finds it, the two settings that fix it, and the numbers after.
Snowflake's pricing page is accurate and most teams still misread it. One credit an hour sounds like a price for an hour of queries. It is a price for an hour of a warehouse being awake, billed per second with a 60-second minimum each time it wakes. This is the full loop on a trial account: reproduce the problem, find it with a query you can run on your own account, change two settings, measure again, and one mistake on the way that is worth more than the rest.
1. The problem, and who has it
The workloads that get hurt are the ones nobody thinks of as heavy. A dashboard that refreshes a few tiles every two minutes. An alerting job that checks a threshold every 30 seconds. An AI agent that answers a question with three small queries, forty times a day. A dynamic table promising near-real-time freshness to a downstream team. Each query runs in milliseconds, so each looks free, and the bill does not match anyone's mental model. The gap is predictable once you know which line of the meter to read, and it can be measured in an afternoon.
The vocabulary, because the bill is the interaction between these words. A warehouse is the compute; an X-Small costs one credit an hour while running. Billing is per second with a 60-second minimum every time a suspended warehouse resumes. Auto-suspend is how long it idles before it stops billing. A dynamic table is a materialised query Snowflake refreshes on a schedule derived from its TARGET_LAG, on a warehouse you name.
2. The query that finds it
Two table functions in INFORMATION_SCHEMA hold everything you need, readable by any role that can see the warehouses. One gives credits per warehouse per hour, the view the bill is computed from. The other gives execution time per query. Divide billed seconds by executed seconds and you have the number that explains the bill:
-- Billed seconds vs executed seconds per warehouse, last 6 days (INFORMATION_SCHEMA keeps 7).
-- Billed seconds assume X-Small (1 credit/hour); scale by warehouse size for larger ones.
-- On a busy account swap QUERY_HISTORY for SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY (no 10k-row cap, ~45 min lag).
WITH billed AS (
SELECT warehouse_name, SUM(credits_used_compute) * 3600 AS billed_seconds
FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_METERING_HISTORY(DATEADD('day', -6, CURRENT_TIMESTAMP())))
GROUP BY 1),
executed AS (
SELECT warehouse_name, SUM(execution_time) / 1000 AS executed_seconds
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
END_TIME_RANGE_START => DATEADD('day', -6, CURRENT_TIMESTAMP()), RESULT_LIMIT => 10000))
WHERE warehouse_name IS NOT NULL
GROUP BY 1)
SELECT b.warehouse_name, b.billed_seconds, e.executed_seconds,
b.billed_seconds / NULLIF(e.executed_seconds, 0) AS billed_per_executed
FROM billed b LEFT JOIN executed e USING (warehouse_name)
ORDER BY 4 DESC NULLS LAST;Run on the trial account after the experiments below, it prints this. Every warehouse ran the same tiny query; the ratio is entirely about when the warehouse was awake.
| warehouse | traffic pattern | billed seconds | executed seconds | billed per executed |
|---|---|---|---|---|
| LEDGER_WARM | one query per 90 s, auto-suspend 600 (the common default) | 4,861 | 1.3 | 3,798x |
| LEDGER_TIGHT | one query per 30 s, auto-suspend 60 | 1,582 | 1.3 | 1,199x |
| LEDGER_SPREAD | one query per 90 s, auto-suspend 60 | 2,798 | 6.7 | 420x |
| LEDGER_BATCHED | forty queries back to back | 81 | 0.9 | 95x |
The second query is for dynamic tables: how many scheduled refreshes found nothing to do. An empty refresh still resumes the warehouse and pays the minute.
-- Dynamic-table refreshes that found nothing new; each one still resumed its warehouse.
SELECT name, COUNT(*) AS refreshes, SUM(IFF(refresh_action = 'NO_DATA', 1, 0)) AS empty_refreshes
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY())
GROUP BY 1 ORDER BY 3 DESC;3. The numbers behind the ratio
The set-up: an Enterprise trial on Google Cloud, one table of 100,000 random rows, one query, SELECT SUM(v) FROM probe WHERE v > :n, result caching off so every run hits the warehouse. Forty runs under each of four traffic patterns, each pattern on its own fresh X-Small warehouse so the meter attributes credits cleanly. Total execution time for the forty queries: 0.1 seconds, in every pattern.
| pattern | wall span | credits metered | $ at list | vs batched |
|---|---|---|---|---|
| batched, back to back | 1.8 min | 0.023 | $0.07 | 1x |
| tight, 30 s gaps, suspend 60 | 20 min | 0.441 | $1.32 | 19x |
| spread, 90 s gaps, suspend 60 | 60 min | 0.779 | $2.34 | 34x |
| warm, 90 s gaps, suspend 600 | 59 min | 1.351 | $4.05 | 58x |
Spread is the scheduled-job trap: every query lands on a sleeping warehouse, wakes it, and pays the 60-second minimum. Forty queries, forty billed minutes, and the arithmetic matches the metered 0.78 credits to two decimals. Warm is worse in a different way: with ten minutes of auto-suspend the warehouse never sleeps between 90-second gaps and bills the whole hour. Tight looks cheaper than spread despite firing three times as often, because with a query every 30 seconds the warehouse never suspends and never pays a resume penalty. The cheapest way to run a query every 30 seconds is the same as the most expensive way to run one every 90.
4. The fix, measured
Two settings, no query changed. Auto-suspend from 600 seconds to 60, and the scheduled queries collected into one burst instead of spread across the hour. The warm row becomes the batched row: 1.351 credits to 0.023 for the same forty queries, 58x. If the queries genuinely must run every 90 seconds and cannot be batched, auto-suspend at 60 alone takes warm to spread, 1.351 to 0.779, and the remaining cost is the resume minimum, which only batching removes.
5. The near-real-time case: the TARGET_LAG curve
The freshness experiment uses a heartbeat. A writer inserts one row every 10 seconds carrying the client's clock. The row flows through two chained dynamic tables with the same TARGET_LAG, so the nominal worst case at the far end is twice the lag. A reader queries the far end every 5 seconds and records the true age of the newest row it can see. Three settings, 45 minutes each, run in parallel, each pair of dynamic tables on its own warehouse, and the writer and reader on a fourth warehouse of their own.
| TARGET_LAG per hop | nominal worst case | median age | p90 age | max age | refreshes | DT warehouse credits per hour |
|---|---|---|---|---|---|---|
| 1 minute | 2 min | 35 s | 52 s | 179 s | 61 | 1.35 |
| 5 minutes | 10 min | 106 s | 184 s | 205 s | 16 | 0.50 |
| 15 minutes | 30 min | 6.3 min | 11.7 min | 13 min | 5 | 0.16 |
Two readings. Snowflake beats its own nominal worst case by about 3x at every setting, so the promise holds and then some. And the refresh cost falls 8x between one minute and fifteen, because a one-minute lag resumes the warehouse roughly every 48 seconds and pays the minimum each time, while a fifteen-minute lag resumes four times an hour. Set the lag to what the consumer of the table actually needs. The difference between "one minute" and "five minutes" was 70 seconds of median freshness and 0.85 credits an hour, per pair of tables, forever.
6. The mistake, which is the real lesson
My first run put the heartbeat writer and reader on the same warehouse as the dynamic tables. The cost curve came back flat: 1.4 credits an hour at one minute, at five, and at fifteen. I had the sentence "dynamic tables never sleep" drafted. They do. My probe, one insert every 10 seconds and one select every 5, kept the warehouse awake regardless of the refresh schedule, and the meter attributed all of it to the warehouse, which is the only thing the meter can attribute to. Moved onto its own warehouse, the probe cost 1.0 credits over the 45 minutes, and the dynamic tables' own cost appeared.
The general form: Snowflake bills per warehouse, so anything sharing a warehouse with the thing you measure becomes the thing you measure. That applies to your production account exactly as it did to my trial. If dashboards, ETL and dynamic tables share a warehouse, the ratio query above tells you the warehouse is expensive and nothing about why. Split them before you diagnose, or the diagnosis is fiction.
7. What to do with this
- Run the ratio query. Anything above a few hundred on a warehouse that serves scheduled or agent traffic is the 60-second minimum at work.
- Auto-suspend at 60 on those warehouses. The 600-second default is the 3,798x row.
- Batch what can be batched. Forty small queries once an hour cost 0.023 credits; the same forty spread across the hour cost 0.78.
- Give dynamic tables their own warehouse, then set TARGET_LAG from the consumer's need, not from "as fresh as possible". Measured freshness runs about 3x better than the nominal worst case.
- Run the empty-refresh query monthly. Empty refreshes are minutes billed for nothing.
Reproduce it
The scripts are in the snowflake-meter repository linked from this page: the meter patterns, the heartbeat with its lag and warehouse parameters, the report that prints the tables above, the two diagnostic queries, and a kill switch that suspends everything that bills. It runs on a free trial account in an afternoon and used 13 of the 400 credits the trial gives you.
Written from production experience running data platforms and the cost observability around them. Related: what a million LLM tokens actually costs · freshness SLAs measured against an independent reference · LangChain vs raw SDK, measured on the wire.