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:
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:
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'
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'
Clustering: keep related rows together
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_ -- in dbt: sorts the rows at build time AND declares the key {{ config(date); 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.
Choosing the column to cluster on
Three questions, in this order. If you can answer them you have your key.
WHERE and JOIN on this table, day after day. Usually a date or a tenant id, rarely the primary key.CLUSTER BY (to_date(created_at)).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_ ); -- { -- "total_date)' partition_ -- "average_count" : 3200, 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_ , '(item_id)'); -- { -- "total_lines' partition_ -- "average_count" : 5100, 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_ -- what will keeping it organised cost? ask before committing SELECT SYSTEM$ESTIMATE_lines CLUSTER BY (item_id, order_date); AUTOMATIC_ ('fct_CLUSTERING_ COSTS order_ , '(item_id, order_date)');lines'
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.
Writing SQL that actually prunes
Pruning is something you enable by how you write the query, not a switch you flip:
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= incremental_'order_ id', 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.
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_ -- substring search: WHERE page_url LIKE '%checkout%' ALTER TABLE events ADD SEARCH OPTIMIZATION ON SUBSTRING(id, email); page_ -- what will it cost to build and maintain? ask first SELECT SYSTEM$ESTIMATE_url); SEARCH_ ('events', 'EQUALITY(OPTIMIZATION_ COSTS 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.
=, IN) on columns you can't cluster by. Costs storage plus maintenance, so reserve it for tables where those lookups are frequent.
