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;

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull! Receive free stock by signing up using the link: Webull signup.