Course contents

The Analytics Engineer’s Field GuideSnowflake: operateStep 9: Cost control

Cost control

Part 4 · Snowflake · step 9

Controlling the bill: pay for work, not idle time

Ismail's other job. Performance and cost are the same lever pulled twice: do less work, on a smaller warehouse, for less time.

· · ·

A Snowflake bill has four lines, and their weights are very uneven:

Line
What drives it
Warehouse compute
Credits burned while warehouses run, by size and seconds. Usually the bulk of the bill, and where this lesson spends most of its time.
Serverless
Snowpipe, automatic clustering, materialized view refresh, search optimization, query acceleration. Snowflake's compute, not yours, billed per use.
Storage
Terabytes per month, including Time Travel and Fail-safe copies. Cheap per TB, but it grows quietly (see the end of this lesson).
Cloud services
Free up to 10% of each day's warehouse compute. You only see it when something hammers the metadata layer: thousands of tiny queries, heavy SHOW or COPY loops.

So cost control is mostly about warehouses doing only the work they need to, and sleeping the rest of the time, with an eye on the serverless line and storage drifting up.

Everything here is in credits, so translate once. A credit's price depends on your edition, cloud, region and contract: on-demand list prices sit roughly between 2 and 4 dollars, Enterprise costing more per credit than Standard, and capacity contracts discount that. Admin → Cost Management in Snowsight shows your actual rate and spend in currency. Use that number, not a blog's, when you tell a manager what "41 credits a week" means.

Where the money actually goes

A warehouse bills per second while it runs, priced by size, with a 60-second minimum every time it resumes. Three things drive the total:

Driver
What runs up the bill
Size
An XL costs 16× an XS per second. Over-sizing a warehouse that didn't need it is pure waste.
Runtime
Slow, full-scanning queries burn credits the whole time they run. Faster queries are cheaper queries.
Idle time
A warehouse left awake doing nothing still bills. Most surprise invoices are idle warehouses, not big queries.
SIZEXS → XL, 1× → 16×
×
RUNTIMEseconds of work
+
IDLEawake, doing nothing
=
CREDITSthe invoice
SIZE × RUNTIME: the compute you asked for · IDLE: the compute nobody asked for

The levers, in order of payoff

  • Auto-suspend short. 60 seconds for ELT and ad-hoc warehouses, a few minutes for BI where queries arrive every couple of minutes (step 5 explains why). Never an hour. This one setting fixes most runaway bills.
  • Right-size, don't over-size. Match the warehouse to the job. A dashboard reading a pre-baked table doesn't need an XL, an XS or S is plenty.
  • One warehouse per workload. Separate ELT, BI and ad-hoc, so a heavy load can't drag the dashboards into a bigger warehouse, and you can see exactly what each costs.
  • Hunt the expensive queries. Rank by credits in QUERY_ATTRIBUTION_HISTORY and fix the top few. Cost is almost always concentrated in a handful of queries.
  • Queueing means clusters, not size. When many users queue, add clusters (scale out). Sizing up makes each query faster but doesn't add a single seat.
  • Optimize the ELT. Incremental models over full rebuilds, batched loads over tiny frequent ones that keep a warehouse awake all day.
  • Cap runaway queries. STATEMENT_TIMEOUT_IN_SECONDS defaults to two days. Set it per warehouse (say an hour for BI, a few hours for ELT) and a forgotten cross join dies on its own instead of billing all weekend. STATEMENT_QUEUED_TIMEOUT_IN_SECONDS does the same for queries stuck waiting.
ALTER WAREHOUSE bi_wh SET STATEMENT_TIMEOUT_IN_SECONDS = 3600;

Resource monitors: a hard cap

The seatbelt. A resource monitor is a credit budget with triggers: it watches credit consumption for one or more warehouses (or the whole account) against a quota over an interval, monthly by default, and fires actions at percentage thresholds. Only ACCOUNTADMIN can create one, though other roles can be granted MONITOR or MODIFY on it.

One setup step people miss: notifications are off by default. Each admin who should get the emails has to enable them in their Snowsight profile and verify their email address, otherwise the 80% warning below fires into the void.

The three actions, and what happens to running queries

The whole design fits in one escalation, and the difference between the two suspend flavours is the part people get wrong:

80%NOTIFY
Email to the admins. Nothing else happens.
running queries: continue · new queries: accepted
100%SUSPEND
The graceful stop. Everything currently running finishes: not cancelled, not queued, completed.
running queries: finish · new queries: refused
110%SUSPEND_IMMEDIATE
The hard stop. Every running statement is cancelled, the warehouse stops on the spot.
running queries: cancelled · warehouse: off

NOTIFY warns · SUSPEND drains then stops · SUSPEND_IMMEDIATE kills

Why the escalation exists: SUSPEND leaks. Queries already running at 100% keep burning credits until they finish, which is exactly how consumption reaches 110% at all. The immediate trigger is the backstop for that overrun, and thresholds above 100% are legal and common for precisely this reason.

CREATE RESOURCE MONITOR month_guard
  WITH CREDIT_QUOTA = 500
  FREQUENCY = MONTHLY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 80  PERCENT DO NOTIFY              -- warn, touch nothing
           ON 100 PERCENT DO SUSPEND             -- drain, then stop
           ON 110 PERCENT DO SUSPEND_IMMEDIATE;  -- the backstop

ALTER WAREHOUSE etl_wh SET RESOURCE_MONITOR = month_guard;

The rules that bite in production

A warehouse can be governed by at most one warehouse-level monitor, while one monitor can govern many warehouses, the relationship only bends in one direction. The account-level monitor sits on top and counts every warehouse, including those with their own monitor: it doesn't replace them and they don't escape it. Both apply, and whichever quota is hit first acts. And the big blind spot: monitors control warehouse credits only (plus the cloud services those warehouses use, which they can count but not stop). Serverless spend, Snowpipe, automatic clustering, materialized view refresh, runs outside any warehouse and escapes them entirely. That gap is what the next object exists for.

ACCOUNT MONITOR
watches everything · does not replace the ones below
both layers apply
month_guard
one monitor
ETL_WHBI_WHADHOC_WH
A real one
A dev warehouse was left on AUTO_SUSPEND = 3600 (a full hour) and someone's open worksheet kept it awake all weekend, doing nothing, for hundreds of credits. Two fixes: a 60-second suspend, and a resource monitor that would have suspended it at the cap. Idle time, not a heavy query, is the usual culprit.

Budgets: the alarm that sees everything

In plain words: a budget is a spending alarm, not a circuit breaker. You create one, give it a monthly limit in credits, and attach what it should watch: the whole account, or a hand-picked group of objects. The power is in what it can see. A resource monitor only sees warehouse credits, but at IsmaFrost the real bill also includes Snowpipe ingesting all night, automatic clustering reorganising the fact table, and materialized view maintenance, none of which runs on a warehouse Ismail controls. A budget watches all of it in one place, forecasts where the month is heading, and alerts when the projection crosses the limit: email, a webhook to Slack or Teams, or a cloud queue. On its own it never suspends anything, which is also why it's safe to put on production pipelines: the alarm rings, nothing stops. If you do want teeth, a budget can call a stored procedure of yours at a threshold (a custom action), and that procedure can suspend a warehouse or pause a pipe.

It isn't real time. A budget refreshes every few hours (up to about six and a half by default), so it's a daily steering tool, not a tripwire. A one-hour "low latency" refresh exists, at a higher cost for the budget itself.

Resource monitor
Budget
Watches
Warehouse (and account) credit usage
Any supported objects, including serverless spend
Can suspend compute
Yes, built in (suspend / suspend immediately)
Not by itself: a custom action can call your own procedure
Reacts within
Minutes
Hours (refresh every few hours)
Cycle
Configurable frequency and start date
Calendar month
Serverless costs
Not covered
Covered

The syntax is the giveaway that budgets aren't resource monitors: a budget is an instance of a Snowflake class, so you create it like an object and configure it by calling methods on it with !. It lives in a schema, so run it with a database and schema selected, a role allowed to create budgets, and APPLYBUDGET on each object you attach. Expect to ask an admin the first time.

-- create a custom budget (a schema-level object)
CREATE SNOWFLAKE.CORE.BUDGET marketing_budget();

-- set the monthly limit, in credits
CALL marketing_budget!SET_SPENDING_LIMIT(500);

-- attach what it watches: warehouses, but also pipes and serverless features
CALL marketing_budget!ADD_RESOURCE(
  SELECT SYSTEM$REFERENCE('WAREHOUSE', 'ETL_WH', 'SESSION', 'applybudget'));
CALL marketing_budget!ADD_RESOURCE(
  SELECT SYSTEM$REFERENCE('PIPE', 'raw.events_pipe', 'SESSION', 'applybudget'));

-- tell it who to warn: first an email integration (once, by an admin;
-- recipients must have verified their email in Snowsight) ...
CREATE NOTIFICATION INTEGRATION budgets_email_int
  TYPE = EMAIL
  ENABLED = TRUE
  ALLOWED_RECIPIENTS = ('ismail@ismafrost.com');

-- ... then point the budget at it
CALL marketing_budget!SET_EMAIL_NOTIFICATIONS(
  'budgets_email_int', 'ismail@ismafrost.com');

The account-level budget, the one watching everything, isn't created this way at all: it ships with the account and just gets activated once, in Snowsight under Admin → Cost Management.

Controlling serverless spend
"Put a resource monitor on Snowpipe" isn't a thing: serverless costs live outside warehouses, so monitors can't see them. A budget monitors them. If you need hard enforcement of serverless spend, there's no built-in switch: you write the reaction yourself, as a budget custom action that pauses the pipe (ALTER PIPE ... SET PIPE_EXECUTION_PAUSED = TRUE) or suspends the task, and you accept it fires hours after the spike, not seconds.

The other meter: storage that grows quietly

Storage is cheap per terabyte, but you pay for more copies than you think. When a micro-partition is replaced (an update, a delete, a full rebuild), the old version isn't gone. It's kept for Time Travel (1 day by default, up to 90 on Enterprise), so you can query or restore the past, then for 7 more days of Fail-safe, Snowflake's own disaster recovery, which you can't query but still pay for.

So a 2 TB permanent table rebuilt in full every night carries the old copies too: a day of Time Travel plus a week of Fail-safe is roughly eight copies sitting in storage. (dbt's default transient tables skip Fail-safe, see below, which is why most dbt projects never notice.) Check it before guessing:

-- where the terabytes really are
SELECT table_catalog, table_schema, table_name,
       active_bytes      / power(1024, 4) AS active_tb,
       time_travel_bytes / power(1024, 4) AS time_travel_tb,
       failsafe_bytes    / power(1024, 4) AS failsafe_tb
FROM snowflake.account_usage.table_storage_metrics
WHERE NOT deleted
ORDER BY time_travel_bytes + failsafe_bytes DESC
LIMIT 20;

Three levers, all cheap to pull:

  • Transient tables for anything you can rebuild. They keep Time Travel but skip Fail-safe. dbt on Snowflake already builds tables as transient by default (+transient: true), which is the right call for staging and intermediate models. Set +transient: false only on the marts you'd want Snowflake to recover.
  • No Time Travel on what you can rebuild. The default retention is already 1 day, but someone may have raised it (up to 90 on Enterprise). DATA_RETENTION_TIME_IN_DAYS = 0 on a staging table rebuilt every night removes its Time Travel copies entirely. It doesn't touch Fail-safe, only making the table transient does that.
  • Incremental over full refresh. A merge rewrites the partitions that changed. A full rebuild rewrites all of them, every night, and every old version goes into Time Travel and Fail-safe.

Seeing idle time: metered minus attributed

The lesson's top culprit is idle time, and attribution history (step 8) deliberately leaves idle out. So measure it as the gap: what each warehouse was billed, minus what its queries used.

-- last 8 full days, ending yesterday (attribution can lag by hours)
WITH metered AS (
  SELECT warehouse_name, sum(credits_used_compute) AS billed
  FROM snowflake.account_usage.warehouse_metering_history
  WHERE start_time >= dateadd('day', -8, current_date())
    AND start_time <  current_date()
  GROUP BY warehouse_name
), used AS (
  SELECT warehouse_name, sum(credits_attributed_compute) AS used
  FROM snowflake.account_usage.query_attribution_history
  WHERE start_time >= dateadd('day', -8, current_date())
    AND start_time <  current_date()
  GROUP BY warehouse_name
)
SELECT m.warehouse_name, m.billed, coalesce(u.used, 0) AS used,
       m.billed - coalesce(u.used, 0)                          AS idle_credits,
       round(100 * (m.billed - coalesce(u.used, 0)) / nullif(m.billed, 0)) AS idle_pct
FROM metered m LEFT JOIN used u USING (warehouse_name)
ORDER BY idle_credits DESC;

A warehouse with an idle percentage in the high double digits is sleeping too late: shorten its auto-suspend, or merge it with a busier one.

The one habit
Once a week: run the idle query above, open attribution history grouped by query shape and sorted by credits, and glance at the serverless line in Snowsight's cost management page. Idle, expensive queries, serverless drift: those three catch the large majority of waste.

Lesson quiz

  1. Where do most surprise Snowflake bills come from?

    1. One huge query that runs once a month
    2. Storage growing from Time Travel copies
    3. Cloud services charges from metadata queries
    4. Warehouses left awake with nothing to do
  2. A resource monitor reaches its 100% SUSPEND threshold. What happens to queries already running?

    1. They finish; new queries are refused
    2. They're cancelled on the spot
    3. They keep running; new ones wait in the queue
    4. They pause until the next cycle
  3. Ismail wants the dev warehouse stopped at once if it burns 500 credits in a month, and an alert when Snowpipe spend climbs. Which setup covers both?

    1. A budget on the warehouse, plus STATEMENT_TIMEOUT on the pipe
    2. One account-level resource monitor, which also counts Snowpipe
    3. A budget on both, since budgets suspend warehouses natively
    4. A warehouse resource monitor, plus a budget for Snowpipe