Course contents

The Analytics Engineer’s Field GuideSnowflake: the engineStep 3: Pruning & clustering

Pruning & clustering

Part 4 · Snowflake · step 3

Pruning: why one query is fast and another isn't

Same table, two queries. One reads 2% of it, one reads all of it. The difference is how many micro-partitions Snowflake can skip.

· · ·

A dashboard query on a billion-row table comes back in two seconds. A nearly identical one takes four minutes and burns a pile of credits. The table is the same. What changed is how much of it Snowflake had to read, and that comes down to micro-partitions.

A quick recap, then the part that matters

From step 1: a table is stored as thousands of micro-partitions, compressed columnar blocks written automatically as data lands. You never create them, name them or tune their size.

Here's the property everything in this lesson rests on:

The whole trick
Every micro-partition records the min and max of each column inside it. Snowflake can therefore tell whether a block could contain the row you asked for without opening it.

So a table isn't a pile of rows to Snowflake. It's a list of blocks, each with a little label saying what range of values it holds:

ORDERS · one table, auto-split into micro-partitions
part 101-01 → 01-22
part 201-22 → 02-14
part 302-14 → 03-08
part 403-08 → 03-30
part 503-30 → 04-21
part 604-21 → 05-13
part 705-13 → 06-04
part 806-04 → 06-26
Each cell knows its own order_date range. That tiny bit of metadata is the whole trick.

Pruning: skip what can't match

When you filter, Snowflake checks each micro-partition's min/max first and skips every one that can't possibly hold a matching row. It reads only the partitions that survive. Reading 1 partition instead of 8 (or 3 instead of 10,000) is exactly why a good filter is fast. This is partition pruning.

WHERE order_date = '2026-07-18'

jun 02–07skipped
jun 08–13skipped
jun 14–19skipped
jul 08–13skipped
jul 14–19scanned ✓
jul 16–21scanned ✓
may 12–19skipped
may 20–27skipped
jun 20–27skipped
jun 28–jul 03skipped
jul 04–09skipped
may 28–jun 01skipped

each partition carries min/max per column · 10 of 12 can't contain jul 18, so they're never read

SELECT sum(amount_eur) FROM orders
WHERE order_date = '2024-05-25'
Filtering on order_date = 2024-05-25
part 705-13 → 06-04
scannedpruned (never read)
Only part 7 can hold that date, so it's the only one Snowflake reads. The other seven are skipped on the metadata alone.
Pruning needs order
Pruning only works if the rows you want sit together. If 2024-05-25 were scattered across every partition, then every partition's min/max would span the whole year, none could be skipped, and Snowflake would read the lot.

Tables are naturally ordered by how rows were written. An incremental model that appends each day's rows keeps the table in date order, and date pruning works for free. A table rebuilt in full every night is ordered however the SELECT happened to produce its rows, which is often close to the source's order but never guaranteed. But if you filter on something unrelated to load order, say customer_id, every partition holds a bit of every customer, so nothing prunes. A clustering key tells Snowflake to physically keep related rows together, and automatic clustering maintains it in the background as new data arrives.

-- in Snowflake
ALTER TABLE orders CLUSTER BY (order_date);

-- in dbt: sorts the rows at build time AND declares the key
{{ config(materialized='table', cluster_by=['order_date']) }}

The dbt version does a little more than the ALTER: it adds an ORDER BY on the key to the build, so a fresh table is born well clustered, then sets the key. That sort costs time on every full build. Here it's worth it, because the order is what buys pruning, which is the one exception to step 4's "don't sort mid-pipeline" rule. Once a table has a clustering key, Snowflake keeps reclustering it in the background on its own, and bills for it, whether you built it with dbt or not. ALTER TABLE ... SUSPEND RECLUSTER pauses that if a table turns out not to be worth it.

Cluster sparingly
Automatic clustering costs compute to keep the order, plus storage, because every rewritten micro-partition is kept for Time Travel and Fail-safe. Snowflake's own guidance points at tables in the terabytes, filtered or joined often on the same column, and changed in big batches rather than constant trickles. Below a few hundred GB, or on a table that churns all day, clustering usually burns more credits than it saves.

Choosing the column to cluster on

Three questions, in this order. If you can answer them you have your key.

Ask
And pick
1. What do I filter on?
The column that shows up in WHERE and JOIN on this table, day after day. Usually a date or a tenant id, rarely the primary key.
2. How many distinct values does it have?
You want the middle. A boolean has too few to prune anything, a unique id or a raw timestamp has too many to cluster cheaply. When the column is a timestamp, cluster on an expression that keeps its order but cuts the values: CLUSTER BY (to_date(created_at)).
3. Is one column enough?
Almost always yes. Add a second only if you really filter on both together, never more than three or four, and order them from the fewest distinct values to the most.

Then check before you pay for it

Clustering costs compute to maintain, so measure first: SYSTEM$CLUSTERING_INFORMATION tells you how well a table is already sorted on a column. Two numbers matter. average_overlaps is how many other micro-partitions each one overlaps on that column. average_depth is the more practical one: for a given value, roughly how many partitions you'd have to open to find all its rows. A depth close to 1 means a filter on one value touches very few partitions, a depth in the thousands means it touches almost all of them. (The sample outputs below are trimmed: the real JSON has a few more keys.)

What that looks like on a real table

IsmaFrost's fct_orders is loaded nightly in date order. Ismail checks before touching anything:

SELECT SYSTEM$CLUSTERING_INFORMATION('fct_orders', '(order_date)');

-- {
--   "total_partition_count"      : 3200,
--   "average_overlaps"           : 1.4,      ← each partition overlaps ~1 other
--   "average_depth"              : 2.1,      ← ~2 partitions to open for one date
--   "partition_depth_histogram"  : { "1": 2600, "2": 480, "3": 120, ... }
-- }

Verdict: leave it alone. Loading in date order already grouped the rows, so a filter on one day opens about two partitions out of 3200. Clustering would cost credits every night and change nothing.

Now the same check on fct_order_lines, which Ismail wants to filter by item_id to answer "how has the margherita sold this year":

SELECT SYSTEM$CLUSTERING_INFORMATION('fct_order_lines', '(item_id)');

-- {
--   "total_partition_count"      : 5100,
--   "average_overlaps"           : 4870,     ← nearly every partition overlaps every other
--   "average_depth"              : 4102,     ← one item_id means opening ~4100 partitions
--   "partition_depth_histogram"  : { "4096": 4900, ... }
-- }

Verdict: worth clustering, carefully. Every pizza sold on a given evening lands in the same partition, so a single dish is smeared across the whole table and filtering on it reads almost everything. But this table is also queried by date ("last 7 days"), and today it gets date pruning for free from load order. Clustering on item_id alone would trade one away for the other. So Ismail keeps both, lowest cardinality first:

-- ~50 menu items first, then date inside each item
ALTER TABLE fct_order_lines CLUSTER BY (item_id, order_date);

-- what will keeping it organised cost? ask before committing
SELECT SYSTEM$ESTIMATE_AUTOMATIC_CLUSTERING_COSTS('fct_order_lines', '(item_id, order_date)');

Now "the margherita this year" reads the margherita's slice in date order, and "last 7 days" reads a couple of partitions per item instead of the whole table. Automatic clustering reorganises in the background, over hours, not instantly, and you can follow its spend in AUTOMATIC_CLUSTERING_HISTORY.

How to judge success: don't expect depth to reach 1. With 50 items and 5,100 partitions, a popular dish fills dozens of partitions even when perfectly sorted, so depth settles at the order of partitions per item, not single digits. The real test is the profile of the query you care about: partitions scanned for one item should fall from nearly all to roughly that item's share of the table. If it doesn't move after a day, the key was the wrong choice, and you stop paying with ALTER TABLE fct_order_lines DROP CLUSTERING KEY.

The short version
Cluster the column you filter on, only on tables big enough to care (think terabytes, not gigabytes), only when the measurement says the table is messy on that column. Everything else is paying for sorting you didn't need.

Writing SQL that actually prunes

Pruning is something you enable by how you write the query, not a switch you flip:

you write
what Snowflake reads
result
WHERE order_date = d
only the matching partitions
fast
WHERE year(order_date) = y
may or may not prune: depends on the function
check
SELECT amount, date
two columns off disk
light
SELECT *
every column off disk
heavy

Filter on the bare column: Snowflake sees through some functions on a column but not all, and a range on the bare column always prunes. Select only the columns you need. Step 4 shows both rewrites.

In incremental dbt models there are two reads to prune, and people only fix the first. The filter inside is_incremental() prunes the source. The MERGE into the target still scans the target to match on unique_key, unless you also tell it where to look:

{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_predicates=["DBT_INTERNAL_DEST.order_date >= dateadd(day, -3, current_date)"]
) }}

That predicate lets the merge skip every target partition older than three days, which on a big fact table is most of the run's cost. The price is a promise you must keep: the window has to cover the oldest row that can still change. If an order from last week gets updated, the merge can't see its existing row, finds no match, and inserts a duplicate. Size the window from how late your updates really arrive, and keep a unique test on the key to catch the day you got it wrong.

The one number to check
Run the query, then open its Query Profile (step 2). In the Statistics panel, compare partitions scanned with partitions total. On a filtered query, scanned should be a small fraction. If it's close to total, your filter isn't pruning, so fix the filter or cluster the table on that column.
· · ·

Search Optimization Service: for the needle-in-a-haystack lookup

Clustering helps range and join filters. But some queries are point lookups, "find the one row where email = ...", on a column the table isn't clustered by. For those, the Search Optimization Service builds a separate search access path that lets Snowflake jump to the matching micro-partitions for highly selective equality and IN lookups.

-- point lookups: WHERE email = ... / WHERE user_id IN (...)
ALTER TABLE events ADD SEARCH OPTIMIZATION ON EQUALITY(user_id, email);

-- substring search: WHERE page_url LIKE '%checkout%'
ALTER TABLE events ADD SEARCH OPTIMIZATION ON SUBSTRING(page_url);

-- what will it cost to build and maintain? ask first
SELECT SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('events', 'EQUALITY(user_id, email)');

Search optimization is Enterprise edition and runs serverless, so it bills on its own line: check the estimate first, then SEARCH_OPTIMIZATION_HISTORY once it's on.

Use
For
Clustering
Range and join filters on one main column (dates, tenant), large tables scanned in slices.
Search Optimization
Highly selective point lookups (=, IN) on columns you can't cluster by. Costs storage plus maintenance, so reserve it for tables where those lookups are frequent.

Lesson quiz

  1. What does each micro-partition record that makes pruning possible?

    1. A B-tree index on the table's primary key
    2. A sorted copy of its clustering column
    3. The queries that recently read it
    4. The min and max of every column inside it
  2. fct_orders is loaded nightly in date order, and SYSTEM$CLUSTERING_INFORMATION shows a low average depth on order_date. Should you add a clustering key on it?

    1. No: clustering keys only work on string columns, not on dates
    2. No: it's already well clustered, so a key costs credits for nothing
    3. Yes: every large table that's filtered by date needs a clustering key
    4. Yes: a clustering key lets Snowflake skip the min/max check
  3. Ismail's query on fct_events filters WHERE to_char(event_ts, 'YYYY-MM') = '2026-10' and scans every partition. Which rewrite is the safe way to make it prune?

    1. WHERE event_ts::string LIKE '2026-10%'
    2. WHERE to_char(event_ts, 'YYYY-MM') LIKE '2026-10%'
    3. WHERE year(event_ts) = 2026 AND month(event_ts) = 10
    4. WHERE event_ts >= '2026-10-01' AND event_ts < '2026-11-01'