❄️ Warehouse & Modeling · Chapter 8

Clustering keys, micro-partitions, pruning

Pruning only works if each micro-partition holds a narrow range of the column you filter on. A clustering key is how you buy that narrowness — and it costs credits every day you keep it.

Study this lesson free in the app →

The basics

Clustering is how well rows with similar values of a column sit together in the same micro-partitions. A clustering key tells Snowflake which column(s) to keep co-located, and automatic clustering re-organises micro-partitions in the background to maintain it.

Why it exists: the first Snowflake lesson showed that pruning skips micro-partitions whose min/max can't match a filter. But pruning is only as good as the ranges. If every partition holds rides from every month, every partition's range is Jan–Dec and nothing can be skipped. Data loaded in time order is naturally clustered on time; updates, late data, and filters on other columns break that.

The mental model: a filing cabinet. Each drawer label says "A–C". If drawers are sorted, asking for Bose opens one drawer. If papers were stuffed in randomly, every drawer's label reads "A–Z" and you open all of them.

Explained

fact_rides: 2 billion rows, 96,000 micro-partitions.

WHERE pickup_date = '2026-10-14' scans 310 partitions. 0.3 seconds.

WHERE driver_key = 4417 scans 95,880 partitions. 41 seconds.

Same table, same warehouse. The data is sorted by when it arrived — so the date filter prunes and the driver filter can't. Which column the table is organised by decides which questions are cheap.

The exam-paper cabinet

A college stores 50,000 answer sheets in 500 folders. Each folder's tab lists the lowest and highest roll number inside.

Filed in roll-number order: folder 37 says 18,001–18,100. Looking for 18,042? One folder.

Filed in the order they were marked: every folder holds sheets from all over; each tab says 1–50,000. Looking for 18,042? Open all 500.

Now the principal asks for every sheet from the Chemistry exam. Roll-number order doesn't help — chemistry sheets are in every folder. You can sort the cabinet by one thing well. Sorting it by roll number and subject and marker means re-filing every time a sheet comes in.

That's clustering: a choice of which question gets cheap, and a filing clerk you pay to keep it that way.

Measuring clustering

Overlap: two partitions overlap on a column if their [min, max] ranges intersect.

Depth at a value = how many partitions' ranges contain it. Average depth across the table is the headline number. Depth 1 = each value lives in exactly one partition's range — perfect. A table where every partition spans the full range has depth ≈ total partitions — useless.

SELECT SYSTEM$CLUSTERING_INFORMATION('fact_rides', '(pickup_date)');
-- returns JSON:
--  total_partition_count       96000
--  average_overlaps            1.8
--  average_depth               2.6
--  partition_depth_histogram   {"00001": 71000, "00002": 18000, ...}

SELECT SYSTEM$CLUSTERING_INFORMATION('fact_rides', '(driver_key)');
--  average_depth               95412   ← every partition contains every driver

You can measure any column expression this way, key or not — use it before choosing a key, not after.

The ground truth for a specific query: Query Profile → TableScan → Partitions scanned / Partitions total.

Choosing and setting a key

-- Good: expression lowers cardinality of a timestamp to days
ALTER TABLE fact_rides CLUSTER BY (TO_DATE(pickup_ts), city_key);
-- General rule is low → high cardinality (city would lead), but date
-- leads here: nearly every hot query filters on it and it matches load order.

-- Bad choices
-- CLUSTER BY (pickup_ts)   millions of distinct values: costly to maintain
-- CLUSTER BY (ride_id)     unique per row: maximum cost, helps only lookups
-- CLUSTER BY (is_airport)  2 values: barely prunes anything

-- Filters that use the key well
WHERE TO_DATE(pickup_ts) = '2026-10-14'
WHERE pickup_ts >= '2026-10-14' AND pickup_ts < '2026-10-15'   -- range on raw col

-- Filter that hides the column from pruning
WHERE TO_CHAR(pickup_ts, 'YYYY-MM') = '2026-10'

-- Point lookups on a high-cardinality column → different tool
ALTER TABLE fact_rides ADD SEARCH OPTIMIZATION ON EQUALITY(ride_id);

-- Watch what automatic clustering costs
SELECT TO_DATE(start_time) d, SUM(credits_used)
FROM snowflake.account_usage.automatic_clustering_history
WHERE table_name = 'FACT_RIDES' GROUP BY 1 ORDER BY 1;

The economics at 2 billion rows

• Natural clustering is free. Rides loaded hourly are already clustered on pickup time; depth ~2 without any key. Don't pay automatic clustering to maintain an order you already have
• What erodes it: UPDATE/MERGE that rewrites old partitions (fare corrections, SCD-style changes), late-arriving data (a driver app syncing three days of offline rides), and backfills inserted out of order
• Automatic clustering cost scales with churn. A table updated all over every hour gets reclustered all day. Stable, append-mostly tables are cheap to maintain
• One sort order per table. If finance filters by date and support looks up by driver, cluster by date and either add search optimisation for driver lookups or keep a second, driver-sorted table/materialised view for that access path
• Rule of thumb: a clustering key should cut scanned partitions by an order of magnitude on queries that run often. If it doesn't, drop it — ALTER TABLE ... DROP CLUSTERING KEY

Gotchas

• Clustering on raw timestamps or ids — extreme cardinality makes maintenance expensive; use TO_DATE(ts) or a bucket
• Clustering on booleans or status — 3 values can't split 96,000 partitions meaningfully
• Too many key columns — beyond 3–4, later columns barely affect layout and maintenance cost climbs
• Wrong order — very high-cardinality column first means the second column is effectively ignored
• Filter shape doesn't match the key — key TO_DATE(pickup_ts) but queries filter YEAR(pickup_ts) = 2026; prove it in the Query Profile
• Forgetting it bills forever — automatic clustering keeps spending as long as data churns
• Clustering a small table — a 2 GB dimension scans in milliseconds; clustering is pure cost

Pruning needs narrow ranges. Clustering buys narrow ranges. Automatic clustering bills you to keep them.

Key = selective, moderate-cardinality filter column, ≤ 4 columns, low → high.

Ranges → clustering key. Needles → search optimisation.

Prove it with partitions scanned / total.

How to answer

"A 20 TB table is slow to filter. Would you add a clustering key?"

"Not first. I'd check the Query Profile for partitions scanned versus total and run SYSTEM$CLUSTERINGINFORMATION on the filter columns. If queries filter on date and the data is loaded in date order, it's probably already naturally clustered and the issue is the filter itself — a function on the column, or no selective filter at all. If pruning really is poor on a column that frequent queries filter on, I'd add a key like TO_DATE(event_ts), maybe plus a low-cardinality column like city, and watch automatic clustering credits against query savings for a couple of weeks. For point lookups on ids I'd use search optimisation instead."_

Follow-up: "What's clustering depth?" — The number of partitions whose ranges overlap at a value, averaged. 1 is perfect; near total partition count means no pruning possible.

Words to know

In two minutes

• Pruning compares a filter to each micro-partition's min/max; it only skips partitions whose range excludes the value, so narrow, non-overlapping ranges are everything.
• Data is naturally clustered on load order (usually time); updates, late-arriving rows, and filters on other columns are what erode or bypass it.
• Measure with SYSTEM$CLUSTERING_INFORMATION: average depth and overlaps near 1 mean good clustering; and with 'partitions scanned vs total' in the Query Profile.
• Choose a clustering key from columns used in selective filters, with moderate cardinality: TO_DATE(pickup_ts), not the raw timestamp or a UUID; not a boolean.
• Put up to 3–4 columns in the key, ordered from lower to higher cardinality.
• Automatic clustering is serverless and bills credits continuously; it pays only on large (multi-TB), frequently queried tables with a stable filter pattern.
• For needle-in-haystack lookups on high-cardinality columns (ride_id, phone), use the search optimisation service, not a clustering key.

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.