Course contents

The Analytics Engineer’s Field GuideSnowflake: the engineStep 1: How Snowflake is built

How Snowflake is built

Part 4 · Snowflake · step 1

How Snowflake is built, and why that makes it fast

Ismail starts here. Once you see the three layers, half of Snowflake's behaviour stops being magic.

· · ·

Most of what Snowflake does, caching, cloning, pruning, scaling up and down, falls straight out of one design choice. Learn the shape once and the features stop being a list to memorise and start being things you could have predicted.

Storage and compute are separate

A classic database keeps your data and the machine that queries it in the same box. Want to query more? Buy a bigger box, which also happens to hold the data. Everyone queries that same box, and they fight over it.

Snowflake splits the two apart, and everything else follows from that:

  • Your data sits once, in cheap cloud storage. No machine owns it. It's just there, compressed, and it doesn't queue.
  • Compute is rented separately. A warehouse is a cluster you switch on for a job and off afterwards, in whatever size that job needs.
  • Ten warehouses can read the same table at once. They don't slow each other down, because they aren't sharing a machine, only the data underneath.
  • Two meters, not one. Storage is billed like disk and costs little. Compute is billed by the second while a warehouse is resumed, whether or not a query is running on it, with a one-minute minimum each time it starts. An awake warehouse doing nothing still bills.

The three layers

Every Snowflake account is these three layers stacked. Each does one job:

Cloud Services
the brain: optimizer · metadata · security · transactions · result cache
Virtual Warehouses (compute)
ELT_WH · LBI_WH · MADHOC_WH · XSindependent · resizable · suspendable
Storage
one copy of your tables, as compressed columnar micro-partitions, in cloud object storage

What the separation buys, in one concrete scene: Monday 9am, the ETL warehouse is rebuilding fct_orders, heavy and slow. At the same second, finance runs quarter-end queries on the same table from their own warehouse, and the BI tool refreshes forty dashboards from a third. Nobody queues behind anybody, because the data lives once in storage and each team brings its own compute:

-- three sessions, running at the same moment, each with its own compute
-- session 1 (Airflow's dbt run)   USE WAREHOUSE ETL_WH;      -- the rebuild churns here
-- session 2 (finance analyst)     USE WAREHOUSE FINANCE_WH;  -- quarter-end reads, unbothered
-- session 3 (BI service user)     USE WAREHOUSE BI_WH;       -- dashboards refresh, unbothered

On a classic database those three workloads share one machine and take turns slowing each other down. Here they don't, because the only thing they share is the data, and reading data doesn't queue.

And what does finance see while the rebuild is halfway through? The last committed version of fct_orders, complete and consistent. A rebuild writes new micro-partitions on the side and swaps them in at commit, so a reader never sees half a table. The limit is writes: two jobs updating the same table do wait for each other's locks, which is one more reason one model has one owner.

What each layer actually does:

Layer
Its job
You pay
Cloud Services
The brain you never see. Plans your queries, remembers what's in every table (which is what makes pruning possible), checks permissions, serves the result cache.
Usually nothing: billed only above 10% of that day's warehouse credits
Virtual warehouses
The muscle. Clusters of machines that read from storage and run your SQL. Have as many as you like, size each one for its job, suspend the ones you're not using.
Per second, while running
Storage
Your data, kept once, compressed. It's your cloud provider's object store (S3, Azure Blob, GCS) managed by Snowflake, which is why it's effectively bottomless.
Like disk, cheap

What storage actually holds: micro-partitions

Snowflake doesn't store a table as one big file, and you never manage its layout. On the way in, rows are chopped automatically into micro-partitions: compressed, columnar blocks of roughly 50 to 500 MB of uncompressed data each, usually around 16 MB once compressed. A table Snowsight shows as one terabyte (that's the compressed size) is tens of thousands of them.

Two properties of that format explain most of part 4:

  • Each block carries its own summary. Snowflake records the min and max of every column inside it, so it can tell whether a block could possibly contain what you asked for before reading a single byte.
  • Blocks are immutable. Changing rows writes new blocks rather than editing old ones. The old blocks are kept for a while, which is what makes Time Travel possible (query or restore a table as it was yesterday) and zero-copy cloning cheap (a clone is a set of pointers to blocks that already exist, not a copy of your data). Those kept blocks are also storage you pay for, step 9 comes back to it.

One more word you'll meet: serverless. Some features (Snowpipe loading, automatic clustering, materialized view refresh, search optimization) run on compute Snowflake manages for you rather than on one of your warehouses. You never size or suspend it, and it shows up as its own line on the bill.

The running example
Ismail's examples come from IsmaFrost, a pizzeria's warehouse: fct_orders (one row per order), fct_order_lines (one row per pizza sold), dim_menu_item, and a busy fct_events table of app clicks. The same pizzeria comes back in the capstone at the end of the guide.

Where each lever lives

Every lesson that follows pulls a lever on one of the three layers. Keep this map open and you'll always know which layer you're touching, and which line of the bill moves:

Layer
Levers in this part
Lesson
Storage
How rows are grouped into micro-partitions: pruning, clustering, search optimization. Time Travel and Fail-safe bytes.
steps 3 and 9
Warehouses
How much data a query reads (SQL), size, clusters, auto-suspend, the local cache, pre-baked tables.
steps 4 to 7
Cloud Services
The result and metadata caches, the Query Profile, query history, monitors and budgets.
steps 2, 6, 8 and 9

Which edition are you on?

Snowflake sells the same engine in editions, and a handful of features in this part need Enterprise or higher. Check yours in Snowsight (Admin → Accounts, or the account menu) before you plan around one of them. A trial account lets you pick the edition at signup, so pick Enterprise if you want to try everything here.

Feature
Edition
Without it
Multi-cluster warehouses
Enterprise
One cluster per warehouse: split workloads across warehouses instead.
Materialized views
Enterprise
A dbt table or a dynamic table.
Search Optimization, Query Acceleration
Enterprise
Clustering and better filters.
Time Travel beyond 1 day
Enterprise
1 day of Time Travel, Fail-safe still applies.
Pruning, clustering, caches, dynamic tables, resource monitors, budgets
Standard
Everything else in this part works on any edition.

That's the whole foundation. Before pulling any lever, Ismail learns to read the one screen that says which lever to pull: the Query Profile, next.

Lesson quiz

  1. Why can ten warehouses query the same table without slowing each other down?

    1. The cloud services layer queues them and runs one at a time
    2. Each warehouse is assigned its own separate set of micro-partitions
    3. Snowflake gives each warehouse its own private copy of the table
    4. Each warehouse is separate compute; they share only the stored data
  2. Which layer keeps the per-partition metadata that makes pruning possible?

    1. The result cache
    2. Cloud services
    3. The local SSD cache
    4. Virtual warehouses
  3. Thomas clones the 2 TB fct_orders table to a dev table, then runs a DELETE on the clone that removes half its rows. What happens to storage?

    1. It doubles, since the DELETE copies the table first
    2. Nothing changes, since a clone can't own any blocks
    3. It grows only by the new blocks the DELETE wrote
    4. It drops by about 1 TB, since half the rows are gone