What is clustering depth in a columnar table, and how does it show that pruning has degraded?

## The metric and why it exists Declaring a sort or cluster key tells the engine what order you *want*. It does not guarantee the order you *have*. Every table receiving writes drifts, and the interesting question in production is: how well is this table clustered right now, on the key that matters? **Clustering depth** answers it. Consider the key column's domain, and the interval `[min, max]` each block covers for that column. For a given point in the domain, the depth is the number of block intervals covering that point; the table's depth is that count averaged (or sampled) across the domain. - **Depth 1** — the intervals are disjoint. A point filter must open exactly one block; a range filter opens exactly the blocks the range crosses. This is the theoretical best. - **Depth 5** — five blocks must be opened for a single key value; four of them will contribute nothing. - **Depth in the hundreds** — the key is effectively decorative: nearly every block covers nearly every value and you scan the table. Depth is more useful than "is the table sorted?" because it is continuous, comparable over time, and directly proportional to the bytes a selective query will read. Related numbers engines report alongside it are the count of blocks with *constant* key values (perfectly clustered blocks) and the histogram of depth across the key domain, which shows whether the problem is the whole table or only the recent tail. ## Why depth degrades Columnar blocks are immutable, so "maintaining order" means rewriting. Depth rises whenever new blocks land whose ranges straddle existing ones: 1. **Continuous ingest with late or out-of-order data.** Each small load writes blocks spanning whatever arrived in that window. If the cluster key is not the arrival order, every new block spans a wide range and overlaps everything. 2. **Many small loads.** Frequent micro-batches produce many blocks, each covering the full key spread of its batch. Depth is roughly "how many batches contain this key". 3. **Updates and deletes.** A row-level change rewrites the block (or writes a delete marker plus a new block), and the rewritten data usually lands out of order at the tail of the table. 4. **Backfills.** Reloading two years of history into a table clustered by customer produces blocks that each cover the full customer range, in one shot. A table can be beautifully clustered for its first month and useless after six, with no schema change and no query change — which is why an interviewer asks about this: it is a monitoring problem, not a design-time one. ## Detecting it in production Three signals, in order of directness: - **The clustering metric itself.** Mature platforms expose depth (and an overlap histogram) for a table's key; some do it as a function you call, others through system tables of block metadata. Where the engine exposes only per-block min/max, you can compute a serviceable approximation yourself by sampling that metadata. - **Bytes scanned per query, tracked over time.** The same dashboard query scanning 400 GB this month and 40 GB last month, with unchanged SQL and roughly unchanged data volume, is degraded clustering until proven otherwise. - **Scan-side row counts.** Rows read by the scan far exceeding rows that survive the filter, and that gap widening month over month. Check depth on the column queries actually filter on. A table can be perfectly clustered on a key nobody filters by, which is a cost with no benefit. ## Fixing it, and what the fix costs Re-establishing order means rewriting blocks — sorting the affected data and writing new, disjoint blocks. Platforms differ in packaging: some run it as an automatic background service that continuously reclusters the worst-overlapping blocks and bills you for the work (Snowflake's automatic clustering is the well-known example); others require an explicit maintenance operation or a full rewrite of the table or partition. Either way the costs are the same and must be stated: - **Write amplification.** Data that has not changed is read, sorted and written again. A table under continuous unordered ingest can be re-sorted forever without ever reaching depth 1. - **Compute spend**, continuous rather than one-off, if the service runs automatically. - **Churn on the storage layer**, including new versions of files that time-travel or snapshot retention keeps alive, so storage grows during the process. ## The judgment call Good engineering here is not "keep depth at 1". It is: 1. Cluster on the column with the highest *weighted* benefit — bytes scanned per query times query frequency — not on the primary key by reflex. 2. Reduce cardinality of the key where possible: cluster on a truncated timestamp or a coarse bucket rather than a millisecond value, so blocks can actually reach disjoint ranges. 3. Prefer fewer, larger loads over many tiny ones, since batch shape drives depth directly. 4. Bound the problem with a partition axis so re-sorting only ever touches the recent partition rather than the whole table. 5. Stop reclustering a table whose maintenance cost exceeds the scan savings — and measure both sides before deciding.