❄️ Warehouse & Modeling · Chapter 1

Snowflake: why storage and compute being separate changes everything

In an old warehouse, more query power meant more disks. Splitting those two apart is the whole reason the modern warehouse exists — and the whole reason your bill is a query-shape problem.

Study this lesson free in the app →

The basics

Snowflake is a cloud data warehouse: a SQL database built for analysing very large tables, where the data lives in cheap cloud storage and the computers that query it are rented by the second.

The problem it solves: in a traditional warehouse, storage and compute are one box. The finance team's month-end report and the ops dashboard fight over the same CPUs, and you pay for a machine big enough for the worst day, all year. Snowflake separates them: one copy of the data, and as many independent compute clusters as you need, each switched off when idle.

The mental model: a library with one shared stack of books and many reading rooms. Each team books its own room (a virtual warehouse) of whatever size it needs and pays only while the lights are on. How fast a team finishes depends less on the room size than on how many shelves they must pull books from — and the catalogue lets them skip most shelves.

Explained

Two teams query the same table at the same time. One runs a monster report, the other runs a dashboard.

In a classic warehouse they fight over the same machine. In Snowflake they are on different compute, reading the same storage, and neither can see the other.

That is not a feature. It is the architecture.

Three layers, and the separation between them is the point.

Storage — your data, sitting in cloud object storage (S3 or equivalent), compressed and columnar. Cheap, and there is exactly one copy.

Compute — "virtual warehouses": clusters you spin up, size, and turn off. Several can read the same data at once without contention.

Cloud services — the brain. Metadata, query planning, transactions, security.

Because compute is detached, you scale it per workload and pay only while it runs. You are not billed for storing data and thinking about it at the same time.

Micro-partitions and pruning — the thing that actually decides speed

Snowflake stores data in micro-partitions: immutable chunks of roughly 50–500MB uncompressed, columnar, created automatically as you load.

For every micro-partition it keeps metadata — the min and max of each column, distinct counts, nulls.

When you run WHERE ride_date = '2026-03-14', it checks that metadata first and skips every partition whose range cannot contain the value. That is pruning, and it is the difference between reading 40GB and reading 200MB.

So the performance question is never "how big is the table?" It is "how many micro-partitions must be opened?" — which is decided by how the data is clustered and what your WHERE clause filters on.

The query that costs 100× more, and looks the same

-- prunes: date is the natural clustering key, ranges are tight
SELECT count(*) FROM rides
WHERE ride_date = '2026-03-14';

-- cannot prune: the function hides the column from the metadata check
SELECT count(*) FROM rides
WHERE to_char(ride_date, 'YYYY-MM-DD') = '2026-03-14';

-- cannot prune: no filter on a clustered column at all
SELECT count(*) FROM rides
WHERE driver_phone LIKE '%4471%';

All three return the same answer. The first reads one day. The other two read the table.

Wrapping a filtered column in a function is the single most expensive habit in warehouse SQL.

At a few terabytes, none of this matters and everything is fast.

At a few hundred, three things start to hurt:

Clustering drift. As you load and update, natural ordering degrades and pruning gets worse. Snowflake offers automatic clustering — it costs credits, and it is worth it only on large tables filtered on a consistent key.

Warehouse sizing. Doubling the size roughly halves the runtime and costs the same total, if the query parallelises. If it does not, you paid double for nothing. Test, do not assume.

Idle credits. A warehouse with auto-suspend at 10 minutes, queried every 9 minutes, never suspends. This is a very common way to burn a five-figure sum on nothing.

Monday. Finance reports the dashboard is slow and the bill doubled.

Nothing changed in the dashboard. What changed is someone added WHERE upper(city) = 'SHILLONG' to a shared view.

upper() on the filtered column means the metadata check cannot be used. Every query through that view now reads every micro-partition. The dashboard is slow because it is scanning 2TB, and the bill doubled because it is scanning 2TB forty times a day.

The fix is one line: store the city already uppercased, or filter on the raw column.

Functions on the filtered column. Kills pruning silently.

SELECT * on a wide table. Columnar storage means you pay per column. Selecting 80 columns to use 3 costs about 26× what it should.

Sizing up to fix a bad query. A full scan on a bigger warehouse is a faster, more expensive full scan.

Auto-suspend set too high, or a query pattern that keeps resetting it.

Treating it like Postgres. Row-by-row updates are miserable here. Batch, or use MERGE.

Storage is cheap. Compute is the bill. Pruning decides the compute.

So the only question that matters for cost and speed is how many micro-partitions does this query have to open?

Filter on a clustered column, never wrap it in a function, and never SELECT *.

Words to know

In two minutes

• Snowflake separates storage (one columnar copy in object storage), compute (virtual warehouses) and cloud services (metadata, planning, security).
• Teams get separate warehouses on the same data, so workloads don't contend; you pay credits only while a warehouse runs.
• Tables are stored as micro-partitions with min/max metadata; filters skip partitions that can't match — pruning decides speed and cost.
• Wrapping a filtered column in a function, or filtering on an unclustered column, defeats pruning and scans the table.
• Bigger warehouses finish parallel work faster at similar cost, but don't fix a bad scan; size up when the profile shows spilling.
• Set auto-suspend short (about 60 s) for bursty workloads; a warehouse that never idles long enough bills 24/7.
• No B-tree indexes on standard tables: clustering, pruning, result caching and selecting only needed columns are the levers.

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.