Snowflake
Clustering Keys for Large Table Optimization
Manually improve pruning efficiency on very large or poorly-ordered tables.
A clustering key is a subset of columns (or expressions) explicitly designated to co-locate similar data within the same micro-partitions, improving pruning efficiency beyond what natural ingestion order provides. Snowflake recommends clustering keys only for very large tables (typically multi-terabyte) where query performance has measurably degraded due to poor clustering.
It's like periodically reshuffling a filing cabinet so folders for the same month end up in the same drawer, even after years of inserting new folders in random order.
Key Concepts
1
Unlike traditional indexes, a clustering key doesn't create a separate structure — it influences how automatic reclustering reorganizes existing micro-partitions in the background over time. Snowflake runs this reclustering as a managed service, consuming credits proportional to the amount of data reorganized.
automatic reclustering
2
Choosing a good clustering key requires balancing cardinality: a key with too few distinct values (low cardinality) won't meaningfully separate partitions, while one with too many distinct values (like a unique ID) creates excessive overlap and expensive-to-maintain reclustering. Common candidates are date/timestamp columns, or composite expressions combining a low-cardinality dimension with a date.
3
Because reclustering has an ongoing cost, clustering keys should be added deliberately after confirming — via SYSTEM$CLUSTERING_INFORMATION and query profile analysis — that pruning is genuinely the bottleneck, not warehouse sizing or query design.
SYSTEM$CLUSTERING_INFORMATION