Reading clustering depth and system functions to judge micro-partition health

Snowflake stores data in immutable micro-partitions (50–500MB uncompressed) with metadata tracked automatically for pruning and clustering. Two system functions—SYSTEM$CLUSTERING_INFORMATION and SYSTEM$CLUSTERING_DEPTH—let you analyze how well a table's data is organized relative to specified clustering keys. Understanding their output helps you decide whether to define or reclustered a clustering key for query performance.

1 · Learn the must-know

• 1SYSTEM$CLUSTERING_DEPTH returns a single number representing the average depth of overlapping micro-partitions for given columns; lower values (closer to 1) indicate better clustering.
• 2SYSTEM$CLUSTERING_INFORMATION returns a JSON object with clustering_depth plus additional detail: total partition count, average overlaps, average depth, and a histogram showing the distribution of overlap depths across partitions.
• 3Both functions accept a table name and an optional column list (or expression) as arguments; if no columns are specified, the table's defined clustering key is used, and an error occurs if none exists.
• 4These functions require querying actual table metadata, so results reflect the table's current state at time of execution, not a cached or historical snapshot.
• 5A high average_depth or a histogram skewed toward higher overlap values signals poor clustering, suggesting a clustering key should be added or the table reclustered (manually or via automatic clustering).
• 6These functions are diagnostic only—they don't change data or clustering; you must separately define/alter a clustering key or rely on automatic reclustering to improve the metrics they report.

2 · Check your understanding