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;

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.