❄️ Warehouse & Modeling · Chapter 7

Virtual warehouses, caching, credits — the bill

Snowflake doesn't bill you for queries. It bills you for seconds a warehouse is awake. Most surprise invoices are warehouses that were awake while nobody was asking anything.

Study this lesson free in the app →

The basics

A virtual warehouse is a compute cluster that Snowflake bills in credits for every second it is running — whether it is busy or idle. Sizes double each step (XS, S, M, L, XL…), and each step doubles the credits per hour.

Why it matters: compute is usually 80–90% of a Snowflake bill. The levers are few and concrete: how big the warehouse is, how quickly it goes to sleep (auto-suspend), how many copies run at once (multi-cluster), and how often work is skipped entirely because a cache already has the answer.

The mental model: a taxi with the meter running. You pay for time with the engine on, not for distance travelled with passengers. A bigger cab costs more per minute but may finish the trip in half the time. Leaving it idling outside the office all day is the classic waste.

Explained

Meghalaya Cabs' Snowflake bill jumps from ₹2.1 lakh to ₹5.6 lakh in one month.

No new data. No new team. One change: the ops wallboard in the Shillong office was set to refresh every 5 minutes, on a Small warehouse with auto-suspend at 10 minutes.

The warehouse never slept again. 24 hours × 2 credits × 30 days = 1,440 credits (≈ ₹3.6 lakh at ₹250/credit) — for a screen nobody looks at after 8 pm.

The taxi meter

You hire a taxi by the minute. The meter runs whenever the engine is on.

A bigger taxi costs twice as much per minute — but if it gets you there in half the time, the fare is the same and you arrive sooner.

Auto-suspend is telling the driver: "If I'm not back in a minute, switch off." Tell him "wait ten minutes" and pop out every five, and the engine never stops.

There's a flag-fall: every restart costs at least one minute, even for a 10-second hop.

And the result cache is your friend who took the same trip an hour ago: "It's ₹340, I just paid it" — no taxi needed at all.

How credits are counted

Rate by size (standard warehouses, credits per hour):

XS 1 · S 2 · M 4 · L 8 · XL 16 · 2XL 32 · 3XL 64 · 4XL 128

Billing rule: per second while the warehouse is running, with a 60-second minimum each time it starts or resumes. Suspended = zero compute cost.

Cost of a query ≈ credits/hour × seconds awake ÷ 3600 — and 'seconds awake' includes the idle tail before auto-suspend kicks in.

Multi-cluster warehouses (Enterprise edition): MIN_CLUSTER_COUNT / MAX_CLUSTER_COUNT. Each running cluster bills like its own warehouse of that size. Scaling policy Standard adds clusters as soon as queries queue; Economy waits until there's enough load to keep a new cluster busy for several minutes.

Other meters: cloud services (only above 10% of daily warehouse credits), serverless features (Snowpipe, automatic clustering, search optimisation, serverless tasks) billed separately, and storage per compressed TB per month.

The ₹ price of a credit depends on edition, region and contract. For back-of-envelope maths in these lessons, use ₹250 per credit.

Three caches, three layers

              ┌───────────────────────────────────────────┐
 CLOUD        │ RESULT CACHE   full query results         │
 SERVICES     │  • reused if SQL text identical, data     │
              │    unchanged, role allowed, no volatile   │
              │    functions (CURRENT_TIMESTAMP, UUID)    │
              │  • 24 h, renewed on reuse (max 31 days)   │
              │  • NO warehouse, NO credits               │
              │ METADATA       row counts, min/max/nulls  │
              └───────────────────────────────────────────┘
              ┌───────────────────────────────────────────┐
 COMPUTE      │ WAREHOUSE (SSD) CACHE  raw micro-partition│
 (per WH)     │  files read by earlier queries            │
              │  • speeds up *different* queries on the   │
              │    same data                              │
              │  • lost when the warehouse suspends       │
              └───────────────────────────────────────────┘
 STORAGE        object storage — always the slowest read

The settings that control the bill

CREATE WAREHOUSE bi_wh
  WAREHOUSE_SIZE      = 'SMALL'
  AUTO_SUSPEND        = 60        -- seconds idle before sleeping
  AUTO_RESUME         = TRUE
  MIN_CLUSTER_COUNT   = 1
  MAX_CLUSTER_COUNT   = 3         -- scale OUT for Monday 10 am rush
  SCALING_POLICY      = 'STANDARD'
  INITIALLY_SUSPENDED = TRUE;

-- One warehouse per workload → cost per team is readable
CREATE WAREHOUSE elt_wh WAREHOUSE_SIZE = 'MEDIUM' AUTO_SUSPEND = 60;

-- Hard ceiling
CREATE RESOURCE MONITOR bi_monthly WITH CREDIT_QUOTA = 300
  FREQUENCY = MONTHLY START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 80  PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND;            -- finish running queries
ALTER WAREHOUSE bi_wh SET RESOURCE_MONITOR = bi_monthly;

-- Where did the credits go? (last 30 days)
SELECT warehouse_name, SUM(credits_used) AS credits
FROM   snowflake.account_usage.warehouse_metering_history
WHERE  start_time > DATEADD(day, -30, CURRENT_TIMESTAMP())
GROUP  BY 1 ORDER BY 2 DESC;

Size up or scale out?

Monday, 10:05 am. 40 city managers open the weekly dashboard at once. Each query takes 4 s alone, but the Query Profile shows 18 s queued. → Scale out: max clusters 3. Extra clusters appear only during the rush and suspend after.

Monday, 2 am. The nightly fact_rides rebuild joins 2B rides to dimensions, takes 52 min on Medium, and spills 300 GB to remote storage. → Size up to Large (or XL). Spilling disappears; it might finish in ~20 min — 8 cr/h × 20/60 ≈ 2.7 credits versus 4 × 52/60 ≈ 3.5 credits. Faster and cheaper, because spilling was wasting the Medium's time.

Wrong pairings: a bigger warehouse doesn't stop queuing as well as more clusters do; more clusters don't make one slow query faster at all.

The FinOps playbook at 300 credits a day

1. Split warehouses by workload — ELT, BI, data science, ad-hoc. You can't fix what you can't attribute
2. Auto-suspend 60 s for bursty BI and ELT. Keep longer (5–10 min) only where repeated queries clearly benefit from the SSD cache — measure percentage scanned from cache before deciding
3. Let the result cache work — dashboards whose SQL changes every refresh (an embedded CURRENT_TIMESTAMP(), a random cache-buster) never hit it. Have the BI tool pass literal dates as parameters instead
4. Right-size by experiment — run the nightly job on M, L, XL; pick the size where runtime stops halving
5. Resource monitors on everything, plus QUERY_HISTORY reviews for the top 20 most expensive queries each week
6. Watch the serverless line items — automatic clustering and materialised views quietly bill while nobody's looking at a warehouse

Gotchas

• Auto-suspend longer than the query interval — the warehouse never sleeps
• Auto-suspend = 0 / NULL — means never suspend, not 'immediately'
• Resume-per-query scripts — 500 tiny queries each resuming a suspended warehouse pay the 60 s minimum 500 times; batch them
• Sizing up to fix a full scan — faster, same bytes, roughly the same or more credits
• Expecting the result cache after a load — any change to the underlying table invalidates it
• Expecting the SSD cache after suspend — it's gone; the first query after resume reads from object storage
• One shared warehouse for everyone — the data scientist's 3XL experiment and the CFO's dashboard fight, and nobody knows who spent what

Words to know

In two minutes

• Warehouses bill per second while running, with a 60-second minimum every time they start or resume; idle-but-awake time costs the same as busy time.
• Each size step doubles credits per hour (XS 1, S 2, M 4, L 8, XL 16…); if a query scales well, double the size halves the time and the cost stays flat.
• Auto-suspend is the biggest single lever: a dashboard that refreshes every 5 minutes with a 10-minute auto-suspend keeps a warehouse awake 24×7.
• Three caches: result cache (services layer, 24h, free), warehouse SSD cache (raw data, lost on suspend), metadata (counts, min/max).
• Scale up (bigger size) for big, slow, spilling queries; scale out (multi-cluster) for many concurrent queries that queue.
• Separate warehouses per workload (ELT, BI, data science) so you can size, suspend and attribute cost independently.
• Resource monitors put a hard ceiling on credits: notify at 80%, suspend at 100%.

Test yourself

There are 9 questions, 3 puzzles and 5 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.