Your columnar fact table allows one physical order but three teams filter on different columns — how do you decide?

## Frame it as an allocation problem One table, one physical order. Block skipping is driven by how tightly block key ranges bound the filtered column, and only one column (or one correlated group) can lead. So the question is never "how do I make all three fast by sorting?" — it is "who gets the sort order, and how do I fund the others?" Saying that out loud is most of the answer at this level. ## Step 1 — measure, do not survey Ask the engine, not the teams. From query history you want, per access pattern: - bytes (or blocks) scanned per query, and the ratio of scanned to total table size; - query frequency, and its trend; - the latency requirement — an interactive lookup and an overnight batch job have very different value per second saved; - rows scanned versus rows returned, which exposes the patterns that prune badly today. Multiply scanned bytes by frequency by the platform's price per unit scanned (or by the compute time it occupies) and you have a ranked list in currency. Almost always it is lopsided: one pattern is most of the spend, one is most of the complaints, and one is loud but cheap. Those are three different problems. ## Step 2 — give the order to the dominant pattern, with the right granularity The winner takes the leading key, chosen coarse enough that blocks can reach tight or constant ranges — an hour or day bucket rather than a millisecond timestamp, a tenant id rather than a request id — because a key so fine that every row is unique cannot cluster blocks well and leaves nothing for trailing columns. ## Step 3 — fund the others, each with a named cost **Partition on a second axis.** Partitioning is elimination at a coarser granularity and is *independent* of the sort key, so partition-by-date plus sort-by-tenant genuinely serves two dimensions. The cost is file proliferation: too many partitions produce small files, weaken compression and add metadata overhead. This is usually the cheapest second dimension and should be considered first. **Interleave the key.** Some engines order rows by a space-filling curve over several columns, so each participating column prunes moderately instead of one pruning brilliantly. Choose it only when two or three columns are filtered with genuinely comparable frequency; it is a net loss when one pattern dominates, and it typically raises maintenance cost because keeping the interleaving good requires more rewriting. **Block-level skipping structures.** For a selective *equality* lookup on a scattered column, a per-block bloom or set structure recovers most of the skipping without touching the order. It costs write-time CPU, storage and metadata memory, and it does nothing for range filters or unselective predicates. **A second physically ordered copy.** A derived table, produced by the same pipeline, ordered for the minority pattern. Storage is far cheaper than repeatedly scanning the wrong layout, so this is a legitimate engineering answer, not a hack. The real costs are pipeline complexity, freshness lag, and two objects to keep semantically identical — which means one owner and one definition, not a copy someone forked. **A pre-aggregate.** Often the third team does not need the fact table at all; they need daily totals by a dimension. A rollup collapses their scan by orders of magnitude and removes them from the layout argument entirely. Always ask whether the raw grain is genuinely required. **Say no.** Some patterns should not be served by the warehouse: an interactive per-key point lookup at high QPS is an operational-store access pattern, and answering it from a columnar fact table is expensive at any layout. ## Step 4 — account for the write side Every choice above has an ingest and maintenance bill. Sorting on a column uncorrelated with arrival order means every load lands out of order and clustering degrades continuously, so maintenance becomes a steady-state cost proportional to ingest rate rather than a one-off. Derived copies double pipeline work and add a failure mode. Quantify these next to the scan savings; a layout that saves 30% of scan and adds 50% to ingest spend is a bad trade. ## Step 5 — make the decision durable Write it down where the table is documented: which pattern owns the physical order, what the measured saving was, what the other patterns were given instead, and what evidence would justify revisiting. Add monitoring on bytes scanned per pattern so drift is visible before it becomes a complaint. Without this, the decision is re-argued every quarter by whoever is loudest, and each re-sort costs a full table rewrite. ## What a strong answer sounds like "Layout is zero-sum, so I rank the patterns by scanned bytes times frequency, give the sort order to the top one at a granularity coarse enough to cluster, add a partition axis for the second, and serve the third with a rollup or a second ordered copy funded by storage. I price the ingest and maintenance side of each option, and I record who owns the order so the next request is a measurement, not a debate."