❄️ Warehouse & Modeling · Chapter 6

Snowflake architecture: storage, compute, services

Follow one query from the moment you press Run to the moment rows come back, and you'll know exactly which of Snowflake's three layers to blame when it's slow — or expensive.

Study this lesson free in the app →

The basics

Snowflake is a cloud data warehouse built from three independent layers: storage (where table data lives), compute (virtual warehouses that run queries) and cloud services (the coordinating brain).

Why it matters: the earlier lesson showed that storage and compute are separate. This one shows what each layer does to your query, step by step. When a query is slow, the cause sits in one layer: planning and metadata (services), scanning and joining (compute), or how the data is laid out (storage). Knowing the path tells you where to look.

The mental model: a library. Storage is the stacks — every book, in one place, never moved. Cloud services is the front desk — checks your card, looks up the catalogue, decides which shelves to visit, and remembers answers it gave recently. Compute is the team of runners you hire by the hour to fetch and read the books.

Explained

An analyst at Meghalaya Cabs runs SELECT COUNT(*) FROM fact_rides; — 2 billion rows.

It returns in 80 milliseconds. Her warehouse was suspended the whole time.

No cluster scanned anything. The answer came from a layer most people never think about. Understanding why is understanding Snowflake.

The library with a front desk

A city library. The stacks hold every book, catalogued, never moved. Nobody browses them directly.

The front desk checks your membership, looks up the catalogue, and — because the catalogue notes the first and last word on every shelf — tells you "only shelves 112 to 118 can have what you want". If someone asked the identical question an hour ago and no book has changed since, the desk just hands you a photocopy of that answer.

Runners are hired by the hour. They walk to shelves 112–118, read the pages you asked for, and bring back a summary. Hire more runners and big jobs finish faster. Send them home and the meter stops.

Stacks = storage. Desk = cloud services. Runners = virtual warehouse.

What each layer owns

Storage layer

• Table data in the cloud provider's object storage (S3, Azure Blob, GCS)
• Split into micro-partitions: immutable, columnar, compressed; roughly 50–500 MB of uncompressed data each
• You never touch the files; you pay per compressed TB per month (including time-travel and fail-safe copies)

Cloud services layer

• Authentication, roles and privileges
• Metadata store: for every micro-partition, per-column min/max, null count, distinct estimates, row count
• Parser and cost-based optimiser: join order, pruning, which partitions to scan
• Transaction manager: snapshot isolation over immutable files
• Result cache: results of recent queries
• Billed only for the portion above 10% of that day's warehouse credits

Compute layer

• Virtual warehouses: clusters of nodes with CPU, memory and local SSD
• Read only the columns and partitions the plan names; cache those files on SSD while running
• Independent: two warehouses never share CPU, only the same storage

One query's path

Steps 1–4 and 6 run in cloud services. Only step 5 burns warehouse credits.

Which layer answers this?

-- Services only: answered from micro-partition metadata
SELECT COUNT(*) FROM fact_rides;
SELECT MIN(pickup_date), MAX(pickup_date) FROM fact_rides;
SHOW TABLES IN SCHEMA marts;
DESCRIBE TABLE dim_driver;

-- Services only, on repeat: result cache hit
-- (same text, same role, data unchanged, deterministic)
SELECT city, SUM(fare_inr) FROM fact_rides GROUP BY city;

-- Needs a warehouse: must read column data
SELECT COUNT(*) FROM fact_rides WHERE fare_inr > 1000;
SELECT driver_key, AVG(fare_inr) FROM fact_rides GROUP BY 1;

-- Never cacheable: non-deterministic function
SELECT COUNT(*) FROM fact_rides
WHERE pickup_ts > DATEADD(hour, -1, CURRENT_TIMESTAMP());

COUNT(*) with a filter on fare_inr needs the warehouse — metadata knows each partition's row count and min/max, not how many rows exceed ₹1,000.

What an UPDATE really does

Before:  fact_rides → [MP-1] [MP-2] [MP-3]

UPDATE fact_rides SET fare_inr = 320 WHERE ride_id = 'R-77';
  (R-77 lives in MP-2)

1. warehouse reads MP-2, writes a NEW file MP-2'  with the change
2. services commits: table version 42 → 43

After:   v43 → [MP-1] [MP-2'] [MP-3]
         v42 → [MP-1] [MP-2 ] [MP-3]   ← kept for time travel

One changed row rewrote a whole micro-partition. Files are never edited in place — which is why readers never block writers, why time travel and zero-copy clones are cheap, and why row-by-row updates are expensive.

Diagnosing by layer

Open the Query Profile and read the phases:

• Long compilation time (seconds before execution starts) → services. Usually huge SQL generated by BI tools, thousands of tiny partitions, deeply nested views, or heavy metadata after millions of small inserts
• Queued time → compute. The warehouse is saturated by concurrent queries; size up doesn't help — add clusters (multi-cluster) or separate the workload
• Partitions scanned ≈ partitions total → storage layout. Filters can't prune; fix clustering or the filter (next lessons)
• Bytes spilled to local / remote storage → compute memory. The warehouse is too small for that join or sort; sizing up helps here
• Percentage scanned from cache low on a dashboard warehouse → it suspends between loads and loses its SSD cache

At Meghalaya Cabs: 60 small INSERTs per minute into fact_rides created ~86,000 micro-partitions a day and slowed planning. Batching into one load per 5 minutes fixed compile time without touching the warehouse.

Gotchas

• Thinking a suspended warehouse means nothing works — metadata queries, SHOW commands and result-cache hits still return
• Assuming COUNT(*) WHERE … is free — only the unfiltered count (and min/max) come from metadata
• Row-by-row DML — each UPDATE rewrites whole micro-partitions; use set-based MERGE in batches
• Blaming the warehouse for slow compile — a bigger warehouse does nothing for planning time
• Treating storage as 'just files' — you cannot read or edit Snowflake's micro-partitions with other tools; external tables and Iceberg tables are the explicit exceptions
• Forgetting services can bill — it's free only up to 10% of daily warehouse credits; huge numbers of tiny queries or metadata-heavy operations can exceed it

Words to know

In two minutes

• Storage: table data as immutable, compressed, columnar micro-partitions in cloud object storage, written and managed only by Snowflake.
• Cloud services: authentication, access control, metadata, query parsing and optimisation, transactions, and the result cache — always on, shared.
• Compute: virtual warehouses (MPP clusters with local SSD cache) that actually read micro-partitions and execute joins, aggregates and sorts.
• A query's path: authenticate → parse → result-cache check → optimise and prune with metadata → warehouse scans needed columns of needed partitions → result cached and returned.
• Some queries never touch a warehouse: result-cache hits, metadata-answerable queries like COUNT(*) on a table, and DDL/SHOW commands.
• DML never edits files in place: it writes new micro-partitions and swaps metadata pointers, which is what makes time travel and cloning cheap.
• Slow planning or a stuck 'compiling' phase points at services; scanning, spilling and queuing point at compute; pruning efficiency is decided by storage layout.

Test yourself

There are 8 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.