What is data clustering in Snowflake and when should you define a clustering key?

Data clustering describes how closely the physical order of rows across micro-partitions aligns with the columns used in query predicates. When data is well-clustered on a column, rows with similar values land in the same micro-partitions. A range predicate on that column then prunes away most partitions because their min/max ranges do not overlap the filter value.

A clustering key is an explicit declaration telling Snowflake to sort and co-locate data based on one or more columns or expressions. The Automatic Clustering background service (Enterprise+) periodically re-clusters micro-partitions to maintain this sort order as new data arrives. Snowflake charges for the background compute used.

The key metric is average_overlap_depth from SYSTEM$CLUSTERING_INFORMATION(): a value of 1.0 means perfect clustering (no partition overlap); higher values mean more overlap and worse pruning efficiency.

Define a clustering key when: the table is larger than ~1 TB; queries frequently filter on a predictable column set (date, region, category); and data does not arrive pre-sorted. Skip it when: the table is small, accessed randomly, or frequently truncated and reloaded.

-- Define clustering key on sales table
ALTER TABLE sales CLUSTER BY (order_date, region);

-- Check current clustering quality
SELECT PARSE_JSON(
  SYSTEM$CLUSTERING_INFORMATION('sales', '(order_date, region)')
):average_overlap_depth::FLOAT AS overlap_depth;

-- Query that benefits from clustering-based pruning
SELECT product_id, SUM(amount)
FROM sales
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'
  AND region = 'EMEA'
GROUP BY product_id;