How do you read SYSTEM$CLUSTERING_INFORMATION output to decide whether a table needs reclustering?

## The function ```sql select system$clustering_information('analytics.public.events'); ``` It returns a JSON string. The fields you will be asked about: - **`cluster_by_keys`** — the key the measurement was taken against. - **`total_partition_count`** — how many micro-partitions the table has. - **`total_constant_partition_count`** — how many of those hold a *single value* for the clustering-key columns. - **`average_overlaps`** — the average number of other micro-partitions each one overlaps on the key. - **`average_depth`** — the average number of micro-partitions that overlap any given point in the key's value space. - **`partition_depth_histogram`** — the distribution of that depth across the table. A companion function, `SYSTEM$CLUSTERING_DEPTH`, returns just the average depth when you want one number to trend. ## What depth means Picture each micro-partition as an interval on the key's value axis, running from its recorded minimum to its maximum. Depth at a point is how many of those intervals cover that point. If you filter for exactly that value, that is how many micro-partitions cannot be pruned and must be read. So: - **Depth ≈ 1** — intervals barely overlap. A point predicate reads about one file. This is as good as it gets, and reclustering has nothing left to buy. - **Depth in the tens** — a point predicate reads tens of files. Usually acceptable on a table with tens of thousands of partitions; sometimes worth improving. - **Depth in the hundreds or thousands** — most of the table overlaps most of the table. The key is not separating the data, and a filter on it prunes almost nothing. Crucially, depth must be judged **relative to `total_partition_count`**. Average depth 40 on a table of 60 micro-partitions is terrible; average depth 40 on a table of 900,000 is excellent. Interviewers frequently probe exactly this. ## Constant partitions A constant micro-partition has one value across the clustering-key columns — say every row is `2026-08-01`. It is the ideal unit: it either matches a predicate entirely or is pruned entirely, and reclustering can never improve it. A high `total_constant_partition_count` relative to `total_partition_count` is the strongest single signal that the table is well organized and that the service should be left alone. ## Reading the histogram The histogram is where the diagnosis lives, because averages hide bimodality. Two common shapes: - **Most partitions at depth 1–2, a small tail at high depth.** The table's history is well clustered and a recent load or backfill arrived unclustered. This is normal; the service will absorb it. Do not panic and do not re-tune the key. - **A broad mass at high depth.** The key genuinely does not separate the data — often because it is too high-cardinality (a raw microsecond timestamp, a UUID), or because it does not correlate with how data arrives. Re-tuning the key beats spending more on reclustering. ## Evaluating a key before you commit to one The function takes an optional second argument: an explicit key expression to measure against, even on a table that has no clustering key at all. ```sql select system$clustering_information('analytics.public.events', '(to_date(event_ts), tenant_id)'); ``` This is the cheap, safe way to compare candidate keys. Measure the depth the table would have under two or three candidates *before* declaring one and paying to converge. It is the answer that separates people who have actually operated Snowflake from people who have read about clustering keys. ## Making the decision No single number decides. The workflow that holds up: 1. **Trend it.** Sample depth daily and watch the direction. Depth climbing steadily means DML is outrunning the service — investigate the load pattern or whether recluster is suspended. 2. **Compare against the queries.** The function measures layout; what you are actually paid to reduce is bytes scanned. Pull `partitions_scanned` versus `partitions_total` from `SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY` for the queries that matter. A poor depth on a key nobody filters on is irrelevant. 3. **Compare against the bill.** Check `SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY`. If the service is consuming heavily and depth is *still* poor, the table churns faster than clustering can converge and the key is not the right tool. 4. **Act on shape, not on the average.** Recent unclustered loads: wait. Broad high depth: change the key, usually by lowering its cardinality with an expression or reordering the columns to put the column you filter on first. ## The trap The common mistake is treating a depth number as a target and reclustering toward it. Depth is a *proxy*. If your dashboards already scan 200 of 800,000 micro-partitions, the table is doing its job whatever the average says, and every credit spent improving the metric is wasted.