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:
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:
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.
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:
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.
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.

