Six things I check when a Snowflake bill goes up

Most cost conversations I have start the same way. Someone got the invoice, opened the usage page, and saw a line going up. The fix is rarely dramatic. It is the same six things, in the same order, and then a habit of running three queries once a month.

Where the money goes

Three buckets:

• Compute, meaning virtual warehouses. Usually three quarters of the bill or more. Billed per second while running, with a 60-second minimum each time a warehouse resumes.
• Storage. Compressed bytes on disk, including Time Travel history and Fail-safe. Cheap per terabyte, but it compounds quietly through clones and long retention.
• Cloud services and serverless. Metadata and compilation, plus serverless features like tasks, Search Optimization, automatic clustering and Cortex functions. Small until someone turns a serverless feature on for a very large table.

I check the mix for the account before touching anything:

SELECT service_type, SUM(credits_used) AS credits
FROM snowflake.account_usage.metering_history
WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP)
GROUP BY 1 ORDER BY 2 DESC;

1. Auto-suspend at 60 seconds

The default auto-suspend is ten minutes. A warehouse that gets a query every fifteen minutes never suspends, so you pay for it all day. I set it to 60 seconds on almost everything.

ALTER WAREHOUSE bi_wh SET AUTO_SUSPEND = 60;

The objection is cache loss. A suspended warehouse loses its local SSD cache, so the next query may read from remote storage. In practice the result cache, which is account-wide and free for 24 hours, covers repeated dashboards, and re-warming costs far less than idling. The exception is a warehouse under continuous load, and that one never suspends anyway.

2. Find the top ten queries

Credits follow a power law. A handful of queries or jobs account for most of the spend, so I find them before optimizing anything.

SELECT
  query_parameterized_hash,
  ANY_VALUE(query_text)              AS example,
  COUNT(*)                           AS runs,
  SUM(credits_attributed_compute)    AS credits
FROM snowflake.account_usage.query_attribution_history
WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP)
GROUP BY 1
ORDER BY credits DESC
LIMIT 10;

The usual suspects: a dashboard refreshing every minute against a raw table, a SELECT * into a Python job that needed two columns, a MERGE with no pruning, a scheduled job nobody remembers owning. Half the time the fix is to run it less often or not at all.

3. Right-size, starting small

Each size step doubles the hourly rate. If a query finishes in half the time on the bigger size, cost is a wash and you got the speed for free. If it does not, you paid double.

The signal for going bigger is spilling:

SELECT query_id, warehouse_size, total_elapsed_time / 1000 AS secs,
       bytes_spilled_to_local_storage, bytes_spilled_to_remote_storage
FROM snowflake.account_usage.query_history
WHERE bytes_spilled_to_remote_storage > 0
  AND start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP)
ORDER BY bytes_spilled_to_remote_storage DESC
LIMIT 20;

Remote spill means the query outgrew memory and local disk. Size up for that workload. If nothing spills and queries are fast, try a size down.

For concurrency, meaning many small queries queuing, do not size up. Use a multi-cluster warehouse with a low minimum so it scales out only when there is a queue.

4. Guardrails

Without guardrails the savings get undone by the next new hire.

• Resource monitors on every warehouse, notify at 75% and suspend at 100% of a monthly quota.
• Budgets for the serverless and Cortex spend that monitors do not catch.
• Statement timeouts so one runaway query cannot run all weekend.

CREATE RESOURCE MONITOR bi_monthly WITH CREDIT_QUOTA = 500
  TRIGGERS ON 75 PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND;
ALTER WAREHOUSE bi_wh SET RESOURCE_MONITOR = bi_monthly,
                          STATEMENT_TIMEOUT_IN_SECONDS = 1800;

5. Storage nobody reads

• Transient tables for staging and scratch. No Fail-safe, Time Travel capped at a day. Most ELT intermediates should be transient.
• Time Travel retention. Enterprise allows up to 90 days. Most tables need one. Ninety days on a table that is fully rewritten nightly means ninety copies.
• Orphan clones. A clone is free until it diverges. Six months later you own two full copies. Look in TABLE_STORAGE_METRICS by clone_group_id.

6. Consolidate, then go adaptive

Fifteen warehouses each idle after their own bursts. Four idle less. I merge by workload shape (BI, ELT, ad hoc, apps) rather than by team. Once consolidated, the bursty ones are good candidates for adaptive warehouses, which I wrote about separately.

The monthly check

Three queries, ten minutes:

1. Credits by warehouse, this month against last.
2. Top ten queries by attributed credits.
3. Warehouses with auto-suspend above 60 seconds or no resource monitor.

None of this costs anything to do. If the account is still over budget after all six, that is when I look at the features that spend credits to save credits: Search Optimization, materialized views, clustering. They work, but they are the second conversation.