Course contents

SnowPro Core COF-C03 Study GuidePart 2: Warehouses and performanceClustering Keys & Automatic Clustering

Clustering Keys & Automatic Clustering

Clustering Keys & Automatic Clustering

Definition

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.

Partition date ranges on ORDER_DATE before and after clustering, for the filter WHERE order_date = '2026-06-15' (illustrative). Before: each of the four partitions spans most of the year, so all four ranges contain June 15, all four are scanned and the clustering depth at that date is 4. After CLUSTER BY (order_date): each partition covers one quarter, only partition 2 (April to June) contains the date, one partition is scanned and the depth is 1.BEFORE CLUSTERINGAFTER CLUSTER BY (order_date)scanned 4 of 4 · depth 4MP 1MP 2MP 3MP 4JanFebMarAprMayJunJulAugSepOctNovDecscanned 1 of 4 · depth 1MP 1MP 2MP 3MP 4JanFebMarAprMayJunJulAugSepOctNovDecWHERE order_date = '2026-06-15' Partition date ranges on ORDER_DATE before and after clustering, for the filter WHERE order_date = '2026-06-15' (illustrative). Before: each of the four partitions spans most of the year, so all four ranges contain June 15, all four are scanned and the clustering depth at that date is 4. After CLUSTER BY (order_date): each partition covers one quarter, only partition 2 (April to June) contains the date, one partition is scanned and the depth is 1.BEFORE CLUSTERINGscanned 4 of 4 · depth 4MP 1MP 2MP 3MP 4JanFebMarAprMayJunJulAugSepOctNovDecAFTER CLUSTER BY (order_date)scanned 1 of 4 · depth 1MP 1MP 2MP 3MP 4JanFebMarAprMayJunJulAugSepOctNovDecWHERE order_date = '2026-06-15'
Figure 2.10 Pruning is decided by each partition's min/max range. Each bar is one partition's ORDER_DATE range and the dashed line is the filter value. Before clustering every range spans June 15, so nothing is skipped and the depth at that date is 4; after reclustering only one range contains the date. Clustering depth is exactly this overlap count: lower is better.
StatePartition ranges on ORDER_DATEPartitions scanned for one date
Before clusteringEvery partition spans most of the year (heavy overlap)4 of 4
After CLUSTER BY (order_date)Each partition covers a tight, distinct date range1 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:

MetricBeforeAfter reclustering
Partitions scanned8,140 of 8,200214 of 8,200
average_depth (SYSTEM$CLUSTERING_INFORMATION)453
Dashboard tile runtime95 s6 s
Ongoing costnonerecluster credits on each load, visible in AUTOMATIC_CLUSTERING_HISTORY

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.

LOW CARDINALITYHIGH CARDINALITY
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_USAGE.AUTOMATIC_CLUSTERING_HISTORY 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_count": 8200,
  "average_overlaps": 1.8,
  "average_depth": 2.4 }

-- Same table in July, after six months of scattered UPDATEs:
{ "total_partition_count": 9100,
  "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.

Exam traps
  • 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.
Key takeaways
  • 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.