What is a clustering key and how does it reduce partition pruning cost for large tables?

A clustering key is a column (or expression) declared on a Snowflake table that tells Snowflake to physically sort and co-locate rows with similar values into the same micro-partitions. When the key is a date column, all rows for a given month land in the same small set of partitions. A query filtering on that month then only reads those partitions — the rest are skipped by partition pruning.

Without a clustering key, data arrives in load order. On a table loaded from hundreds of daily files over several years, any given month's rows may be scattered across thousands of micro-partitions. A query for one month must scan all of them. With an effective clustering key, average_overlap_depth drops toward 1.0 — meaning each predicate matches only ~1 partition-layer deep.

The key metric is from SYSTEM$CLUSTERING_INFORMATION(): average_overlap_depth. A value near 1.0 = excellent clustering; a value near the total partition count = no clustering benefit. Automatic Clustering (Enterprise+) runs a background service that continuously re-clusters data as new rows arrive.

-- Define clustering key on a multi-billion-row fact table
ALTER TABLE sales CLUSTER BY (order_date, region);

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

-- This query now prunes away non-January partitions
SELECT product_id, SUM(revenue)
FROM sales
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'
  AND region = 'APAC'
GROUP BY product_id;