Data Clustering
Snowflake · Storage & Optimization
Theory
Clustering describes how well rows with similar values sit together in the same micro-partitions. Data loaded in date order is naturally well clustered on date, but after many loads and updates the value ranges of partitions can overlap, and pruning gets worse. A clustering key, set with CLUSTER BY (order_date, region), tells Snowflake which columns to keep co-located.
Automatic Clustering is a serverless background service that reorganises partitions when the table drifts, and it consumes credits. SYSTEM$CLUSTERING_INFORMATION shows metrics like average clustering depth, where lower means less overlap and better pruning.
Why we use it
On very large tables, poor clustering means filters cannot skip partitions, so queries scan far more data than they return. A good clustering key restores pruning and can cut scan time dramatically.
When to use it
Consider a clustering key for multi-terabyte tables that are queried often with filters on the same columns, and where Query Profile shows poor pruning. Skip it for small tables or tables that change constantly, where reclustering cost outweighs the benefit.
What a Clustering Key Does
Tells Snowflake which columns to use when co-locating similar rows together across micro-partitions, sharpening the metadata-based pruning that micro-partitions already provide.
When It Actually Matters
Mainly relevant on very large, multi-terabyte tables with a clear, frequently-filtered column like a date or customer_id — small tables rarely benefit enough to justify it.
Automatic Reclustering
Snowflake reclusters automatically in the background as new data arrives and skews the physical layout, so you don't need to manually rebuild the clustering over time.
How it works
- Large table with slow filtered queries
- Check pruning in Query Profile and SYSTEM$CLUSTERING_INFORMATION
- Is the table very large and filtered on the same columns? Yes: Choose a key: low to medium cardinality filter columns. No: Do not cluster; tune elsewhere.
- ALTER TABLE ... CLUSTER BY (key columns)
- Automatic Clustering reorganises partitions in the background
- Monitor clustering depth and credit usage
Example
ALTER TABLE orders
CLUSTER BY (order_date, region);Key points
- Most useful on multi-terabyte tables with consistent filter columns
- Automatic Clustering runs serverless in the background and uses credits
- Choose keys from common filter columns, not unique IDs
- Put lower-cardinality columns first in a multi-column key
- Check SYSTEM$CLUSTERING_INFORMATION before and after