Clustering Keys & Automatic Clustering
A clustering key tells Snowflake to co-locate rows with similar values of chosen columns into the same micro-partitions, so pruning works for filters on those columns. Snowflake's serverless Automatic Clustering then maintains that order in the background as data changes, consuming credits outside any warehouse.
| State | Partition ranges on ORDER_ | Partitions scanned for one date |
|---|---|---|
| Before clustering | Every partition spans most of the year (heavy overlap) | 4 of 4 |
| After CLUSTER BY (order_ | Each partition covers a tight, distinct date range | 1 of 4 |
A worked example: from 95 seconds to 6
Alice's regional dashboard filters the 2.5 TB sales fact table the same way in every tile: a date range plus a region code. The Query Profile shows the disease precisely: TableScan at 88% of runtime, 8,140 of 8,200 partitions scanned for a query that keeps 2% of the rows. Rows arrived in load order, so every partition contains every region and a wide slice of dates. One statement changes the physical layout:
ALTER TABLE sales_fact CLUSTER BY (region_ code, TO_ DATE( order_ ts));
Low cardinality first (5 region codes), then the expression-truncated date, exactly the two rules from above. Automatic Clustering spends a few hours of serverless credits regrouping partitions, and then:
| Metric | Before | After reclustering |
|---|---|---|
| Partitions scanned | 8,140 of 8,200 | 214 of 8,200 |
| average_ | 45 | 3 |
| Dashboard tile runtime | 95 s | 6 s |
| Ongoing cost | none | recluster credits on each load, visible in AUTOMATIC_ |
The last row is the honest part of the example: the key isn't free, it's a trade of continuous serverless credits for pruning, worth it here because 90% of the workload filters on exactly these columns and the table changes slowly.
Natural clustering (data arriving in load order, usually time order) is free and often enough. You add an explicit key when three conditions hold at once: the table is large (think TB scale, many thousands of micro-partitions), the dominant query pattern filters consistently on the same columns, and the table is relatively stable. The textbook scenario: a 2.5 TB fact table where 90% of queries run WHERE order_date BETWEEN ... AND region_code = .... That's where a key wins. It loses when changes keep scattering rows across old partitions, such as constant random UPDATE, DELETE or MERGE churn, which causes endless costly reclustering, and when the workload joins to a small dimension table without filtering (not what clustering is for). Ordinary daily appends add a little reclustering work each day and don't rule a key out.
ALTER TABLE sales_fact CLUSTER BY (region_ code, order_date); SELECT SYSTEM$CLUSTERING_INFORMATION('sales_fact', '( region_ );code, order_date)' -- average_depth near 1 = well clustered; large depth = overlapping partitions
Cardinality, explained clearly
Cardinality is just the number of distinct values a column has relative to the row count. A column with few possible values is low cardinality; a column where almost every row is unique is high cardinality. This one idea decides whether a clustering key will actually help.
region_code (5 values) · order_status (4 values)
order_date (thousands) · customer_id (millions) · order_id (unique)
Why the low end of that scale matters for co-location: with only 5 distinct region codes, Snowflake can group all "EMEA" rows into a small, tight set of micro-partitions, and a filter on region_code = 'EMEA' prunes cleanly to just those. Push cardinality too high and the logic breaks down. Cluster by customer_id with a million distinct values spread across a 50 million row table, and each micro-partition ends up holding rows for dozens of different customers anyway, because there simply aren't enough rows per customer to fill a partition on their own. Pruning barely improves, but you still pay full reclustering cost every time new data lands.
This is also why Snowflake's rule of thumb, "order clustering key columns from lower to higher cardinality," works the way it does. CLUSTER BY (region_code, order_date) first buckets rows into a handful of wide region groups, then sorts each bucket by date. Reverse it to (order_date, region_code) and the date column already fragments the table into thousands of narrow slices before region ever gets a say, which spreads each region's rows more thinly and prunes worse for region-only filters.
Expression-based clustering
Extremely high-cardinality columns like a raw timestamp or a UUID are usually better wrapped in an expression: CLUSTER BY (TO_DATE(event_ts)) truncates a near-unique column down to a much lower-cardinality one that clusters cleanly. This is the standard workaround for timestamp columns, and the reasoning is pure cardinality: a raw timestamp is effectively unique per row, so clustering on it directly buys nothing, while the date it truncates to groups thousands of rows per value, exactly the granularity partitions can prune on.
Two more design rules beyond cardinality ordering: keep the key to a few columns (Snowflake recommends 3 to 4 at most; each extra column adds maintenance cost without adding pruning benefit), and remember that reclustering rewrites micro-partitions, which costs serverless credits and temporarily grows Time Travel storage. The storage growth has a mechanical cause worth knowing: partitions are immutable, so reclustering writes new ones and the replaced versions stay alive for the table's full Time Travel retention window (up to 90 days on Enterprise), meaning a heavy recluster stores two editions of the data for a while. The credits are auditable too: ACCOUNT_ breaks the spend down per table, and a budget can watch it, which matters precisely because serverless spend is what resource monitors can't see.
Measuring clustering health
Two functions report the same health signal in different shapes: SYSTEM$CLUSTERING_DEPTH returns a single number, while SYSTEM$CLUSTERING_INFORMATION returns a structured JSON string whose average_depth field carries that number alongside overlap histograms. Both measure how many micro-partitions overlap for a given value of the key columns. A depth of 1 means any value lives in exactly one partition: perfect pruning. A large or growing depth means partitions overlap heavily and scans widen. Depth is the number to watch over time; a table drifting from depth 2 to depth 40 after months of churn is the signal that reclustering costs are fighting a losing battle.
ALTER TABLE sales_fact SUSPEND RECLUSTER pauses Automatic Clustering and keeps the key; RESUME RECLUSTER restarts it. Tables first clustered since September 1, 2026 use Optima Clustering, billed on the data you ingest: resuming adds a catch-up charge for data loaded while paused, so pausing doesn't save money there. Tables clustered before that stay on Clustering Classic, billed on the reclustering work actually done, so a pause there skips work while it lasts. To stop paying for good on either, drop the key.
Reading SYSTEM$CLUSTERING_INFORMATION, with numbers
SELECT SYSTEM$CLUSTERING_INFORMATION('SALES_FACT', '( order_ );date)' -- January, freshly loaded in date order:{ "total_partition_ : 8200,count" "average_overlaps" : 1.8,"average_depth" : 2.4 }-- Same table in July, after six months of scattered UPDATEs:{ "total_partition_ : 9100,count" "average_overlaps" : 44.6,"average_depth" : 38.1 }
Read the January numbers as: a typical date value lives in about 2 partitions, so a one-day filter scans 2 of 8,200. By July the same filter scans roughly 38 partitions, a 16x wider read for the identical query, and Alice's dashboard went from 3 seconds to 40 without anyone changing a line of SQL. Overlaps counts how many partitions share value ranges with a given partition; depth counts how many partitions a single value must check. Both should be small, both trend up under churn, and the July snapshot is the evidence Thomas brings when proposing either a recluster budget or a redesign of the update pattern.
- A clustering key is not an index and enforces nothing: no uniqueness, no constraint. It only influences physical row co-location.
- Lower clustering depth is better. Distractors imply a high depth means "deeply clustered"; it means the opposite.
- Reclustering is non-blocking. It never locks the table or interrupts DML; it rewrites partitions in the background on serverless compute.
- Materialized views can carry their own clustering key, a detail worth remembering.
- Cluster when the table is TB-scale, the filter columns are consistent, and churn is low. Any missing leg makes clustering the wrong answer.
- Depth of 1 is ideal; rising depth means degrading pruning. SYSTEM$CLUSTERING_INFORMATION is the diagnostic.
- Costs are clustering credits (by data ingested under Optima Clustering, by reclustering work under Clustering Classic) plus temporary Time Travel storage growth. SUSPEND RECLUSTER pauses the work; DROP CLUSTERING KEY stops the cost.

