A clustering key organizes a table’s data in micro-partitions so rows with similar key values are colocated and micro-partition value ranges overlap less. It is a targeted performance option for large, complex, or resource-intensive query workloads—not routine tuning for every Snowflake query. section 1.2.3 section 8.4
Its purpose is to improve micro-partition pruning. When a query filters on the key and needs only a small portion of a large table, Snowflake can avoid scanning partitions that cannot contain matching rows. Queries that must read all table data do not benefit from pruning. section 8.3.2 section 8.4.2
Add a clustering key only after checking that the workload and table characteristics justify its cost:
- Target a table with many micro-partitions, where pruning can remove meaningful scan work. section 8.4.2
- Choose a column frequently used by queries that retrieve a small subset of rows; date or time filters are common candidates. section 8.4.2
- Consider columns with enough distinct values for pruning but not so many that colocating matching rows becomes costly; heed high-cardinality warnings in production. section 8.4.1 section 8.4.2
- Account for automatic clustering and reclustering credits rather than treating clustering as a free optimization. section 8.4.2 section 8.4.3
Cluster a retailer sales table by store
A retailer sales table has more than 700 million records, while analysts repeatedly filter it for a small number of stores. After confirming ineffective pruning and poor clustering for STORE_ID, add the key with: alter table RETAILER_SALES cluster by (store_id); Snowflake clusters in the background without disrupting table queries. In the measured store-filtered aggregation, scanning fell to 5 of 1,271 micro-partitions and execution time fell from 6.1 seconds to 567 milliseconds. section 8.4.2 section 8.4.4
Inspect a candidate key before and after clustering with SYSTEM$CLUSTERING_INFORMATION. Higher constant-partition counts and lower average overlaps and average depth indicate better clustering. Once a key exists, Snowflake periodically reclusters after inserts, updates, and deletes; use AUTOMATIC_CLUSTERING_HISTORY to review credits, bytes, and rows affected. section 8.4.1 section 8.4.3
Listing 8.3 Output of the SYSTEM$CLUSTERING_INFORMATION function
{
"cluster_by_keys" : "LINEAR(store_id)",
"total_partition_count" : 1271,
"total_constant_partition_count" : 25,
"average_overlaps" : 7.6271,
"average_depth" : 5.0433,
"partition_depth_histogram" : {
"00000" : 0,
"00001" : 25,
"00002" : 0,
"00003" : 0,
"00004" : 510,
"00005" : 349,
"00006" : 210,
"00007" : 113,
"00008" : 34,
"00009" : 23,
"00010" : 7,
...
},
"clustering_errors" : [ ]
}
Compare post-clustering partition counts, constant partitions, overlaps, and depth to see the signals of improved clustering. section 8.4.3
