Clustering Depth

Clustering depth is Snowflake’s measure of how well a table’s data is organized for skipping on particular columns. Snowflake’s documentation defines it as the average depth of overlapping micro-partitions for specified columns of a table: a value of 1 or greater, where smaller means better clustered. An empty table has a depth of zero.

What overlap means

Snowflake records the range of each column’s values in every micro-partition and skips those whose range cannot match a filter. Skipping works well only when ranges are narrow and do not overlap much.

Take a simplified example with four micro-partitions and an order_date column. If each one covers a different week, a filter on one date overlaps a single micro-partition — depth is low, and three quarters of the data is skipped. If every micro-partition covers the whole month, any date falls inside all four ranges — depth is high, and nothing is skipped. A rough intuition for depth, then, is how many micro-partitions a typical value could be in.

When no ranges overlap at all, the documentation says the micro-partitions are in a constant state: clustering cannot improve them any further.

How it is used

Depth is reported by the system functions SYSTEM$CLUSTERING_DEPTH and SYSTEM$CLUSTERING_INFORMATION. Its main use is tracking a large table over time: as rows are inserted, updated, and deleted, the layout can drift, and a rising depth on the columns queries filter by is an early sign that pruning is getting less effective.

The documented remedy for a high depth that is hurting queries is a clustering key — columns or expressions Snowflake uses to keep similar values together. Snowflake lists a large clustering depth as one signal for defining one, alongside queries that are slower than expected or have degraded, and notes that clustering keys suit very large tables. For columns with very many distinct values, the documentation suggests clustering on an expression, such as a timestamp cast to a date, to reduce them.

What it does not tell you

Snowflake is direct about the limits: clustering depth is not an absolute or precise measure of whether a table is well clustered, and query performance is the best indicator. Three practical cautions follow.

• Depth is measured for a column or a combination. It is computed for the columns or expressions you name, or for the table’s clustering key, and a table can be well clustered on a date and poorly clustered on a customer ID. The depth of a combination such as (order_date, customer_id) is not the depth of either column alone, so measure the combination important queries filter by. What matters is depth on the columns important queries actually filter by.
• A high depth may not matter. If queries rarely filter on that column, or the table is small, improving depth buys nothing.
• Improving it costs money. Clustering and keeping a table clustered consume credits and create storage from rewritten micro-partitions, so the documentation states clustering keys are not intended for all tables. The payoff has to show up as faster queries.

How clustering fits into Snowflake’s storage layer and its cost model is worked through in Snowflake Architecture: Three Layers and What Each One Bills For.

Further reading

• Snowflake Documentation, Micro-partitions & Data Clustering
• Snowflake Documentation, Clustering Keys & Clustered Tables
• Snowflake Documentation, SYSTEM$CLUSTERING_DEPTH