Snowflake – Understanding and Controlling Cost

Snowflake bills for what you use, which is excellent right up to the month somebody leaves a warehouse running. This lesson is about knowing where the credits go, capping them before they become a conversation with finance, and the handful of changes that move the number most.

Where the money goes

Three meters, and their proportions are wildly uneven in practice.

So the first question when a bill surprises you is always about warehouses, and specifically about warehouses that were running rather than warehouses that were working. This track quotes credits rather than dollars throughout, because the price per credit varies by edition, cloud and region — Snowflake's pricing page has the current figures.

The queries that tell you

Everything below reads SNOWFLAKE.ACCOUNT_USAGE, which needs ACCOUNTADMIN or a role granted access to it. Its views lag reality by up to a few hours — fine for cost work, useless for live debugging, where INFORMATION_SCHEMA is the current-state equivalent.

-- 1. Credits by warehouse, last 30 days. Start here every time.
SELECT warehouse_name,
       ROUND(SUM(credits_used), 1) AS credits,
       ROUND(SUM(credits_used) / 30, 2) AS credits_per_day
FROM   snowflake.account_usage.warehouse_metering_history
WHERE  start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP  BY warehouse_name
ORDER  BY credits DESC;

-- 2. Idle waste: hours where the warehouse ran but ran nothing.
WITH busy AS (
    SELECT warehouse_name, DATE_TRUNC('hour', start_time) AS hour,
           COUNT(*) AS queries
    FROM   snowflake.account_usage.query_history
    WHERE  start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
      AND  warehouse_name IS NOT NULL
    GROUP  BY 1, 2
),
metered AS (
    SELECT warehouse_name, DATE_TRUNC('hour', start_time) AS hour,
           SUM(credits_used) AS credits
    FROM   snowflake.account_usage.warehouse_metering_history
    WHERE  start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
    GROUP  BY 1, 2
)
SELECT m.warehouse_name,
       ROUND(SUM(m.credits), 2) AS credits_with_no_queries
FROM   metered m
LEFT   JOIN busy b ON b.warehouse_name = m.warehouse_name AND b.hour = m.hour
WHERE  b.queries IS NULL
GROUP  BY 1
ORDER  BY 2 DESC;

That second query is the one that pays for itself. Credits burned in hours with zero queries are pure waste, and the fix is always the same: an auto-suspend that is too long, or absent.

-- 3. The expensive queries, and who ran them.
SELECT user_name,
       warehouse_name,
       LEFT(query_text, 80) AS query,
       ROUND(SUM(total_elapsed_time) / 1000 / 60, 1) AS total_minutes,
       COUNT(*) AS runs
FROM   snowflake.account_usage.query_history
WHERE  start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
  AND  warehouse_size IS NOT NULL
GROUP  BY 1, 2, 3
ORDER  BY total_minutes DESC
LIMIT  20;

Group by query text, not by individual query. The thing that costs you is rarely one heroic report; it is a mediocre query on a dashboard that refreshes every five minutes.

-- 4. Serverless features, in case one of them is the answer.
SELECT 'snowpipe' AS feature, ROUND(SUM(credits_used), 2) AS credits
FROM   snowflake.account_usage.pipe_usage_history
WHERE  start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
UNION ALL
SELECT 'auto clustering', ROUND(SUM(credits_used), 2)
FROM   snowflake.account_usage.automatic_clustering_history
WHERE  start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
UNION ALL
SELECT 'materialized views', ROUND(SUM(credits_used), 2)
FROM   snowflake.account_usage.materialized_view_refresh_history
WHERE  start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
UNION ALL
SELECT 'serverless tasks', ROUND(SUM(credits_used), 2)
FROM   snowflake.account_usage.serverless_task_history
WHERE  start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP());

Resource monitors

A resource monitor caps credits over a period and can suspend the warehouse — not merely email somebody, which is what most cost tooling elsewhere does.

USE ROLE ACCOUNTADMIN;

CREATE OR REPLACE RESOURCE MONITOR adhoc_monitor
  WITH CREDIT_QUOTA = 100
       FREQUENCY    = MONTHLY
       START_TIMESTAMP = IMMEDIATELY
  TRIGGERS
    ON 75  PERCENT DO NOTIFY
    ON 90  PERCENT DO NOTIFY
    ON 100 PERCENT DO SUSPEND            -- let running queries finish
    ON 110 PERCENT DO SUSPEND_IMMEDIATE; -- kill them

ALTER WAREHOUSE adhoc_wh SET RESOURCE_MONITOR = adhoc_monitor;

SHOW RESOURCE MONITORS;

Three things about them are easy to get wrong. Only ACCOUNTADMIN can create one. Notifications go to users who have enabled notifications on their own profile — without that, DO NOTIFY notifies nobody. And a monitor assigned at account level covers every warehouse, while one assigned to a warehouse covers only that warehouse; the usual arrangement is a generous account-level monitor as a backstop plus tight ones on the warehouses analysts touch.

Put a monitor on every warehouse that a human can drive. A pipeline's usage is predictable; a person exploring is not, and a suspended warehouse at month's end is a much better outcome than a bill nobody budgeted for.

Attributing cost to teams

Once more than one team uses the account, "the bill went up" is only useful if you can say whose half of it moved. Two mechanisms do that, and they compose.

The blunt one is a warehouse per team. Warehouse metering is per warehouse, so this makes attribution exact with no extra machinery, and suspended warehouses cost nothing — splitting has no downside beyond a slightly longer SHOW WAREHOUSES.

The finer one is object tagging, which attaches key–value metadata to warehouses, databases, roles and users, and shows up in the usage views:

USE ROLE ACCOUNTADMIN;

CREATE TAG IF NOT EXISTS governance.tags.cost_center;

ALTER WAREHOUSE bi_wh      SET TAG governance.tags.cost_center = 'analytics';
ALTER WAREHOUSE loading_wh SET TAG governance.tags.cost_center = 'data_platform';

-- Credits grouped by cost centre rather than by warehouse name.
SELECT t.tag_value           AS cost_center,
       ROUND(SUM(w.credits_used), 1) AS credits
FROM   snowflake.account_usage.warehouse_metering_history w
JOIN   snowflake.account_usage.tag_references t
       ON t.object_name = w.warehouse_name
      AND t.tag_name    = 'COST_CENTER'
WHERE  w.start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP  BY 1
ORDER  BY 2 DESC;

Tagging needs Enterprise edition, and it is worth setting up before you need it rather than after — a tag applied today tells you nothing about last month.

The third question people ask is what a single query cost, which the usage views do not answer directly. You can approximate it well enough for a conversation: take the query's execution time as a share of the warehouse's total running time in that window, and apportion the credits. It is an estimate, not an invoice line, and it is usually enough to identify the offender.

The changes that actually move the number

In rough order of how much they typically return for the effort:

1. Fix auto-suspend everywhere. Any warehouse with it disabled is burning credits overnight. This is usually most of the problem, and it takes one statement per warehouse.
2. Split workloads onto separate warehouses. Not because it saves credits directly — a suspended warehouse is free — but because it makes the credit report attributable, and you cannot fix what you cannot see.
3. Find the dashboard that refreshes too often. Query 3 above surfaces it. Halving a refresh interval nobody depends on is free money.
4. Let the result cache work. A dashboard whose queries embed CURRENT_TIMESTAMP() never hits it. Rounding the timestamp to the hour often makes an entire dashboard nearly free.
5. Make transient anything you can rebuild. Staging tables carrying 7 days of Fail-safe plus Time Travel are paying for recovery of data you would regenerate anyway.
6. Set retention deliberately. Thirty days of Time Travel on a table rewritten nightly is thirty copies. Lesson 12's storage query shows which tables those are.
7. Review clustering. Automatic clustering is a permanent serverless charge. If the table it was added for is no longer queried that way, drop the key.

Two habits keep it from drifting back. Run query 1 weekly and look for anything new near the top, and put a resource monitor on every warehouse before anyone uses it rather than after. Both take minutes; the alternative is finding out a month late.

Next: putting it in production.