Clustering and Sort Order

• A columnar file records, per column and per row group, a minimum and a maximum — and often a null count and sometimes a bloom filter. A reader compares the predicate against those and skips the unit when a match is provably impossible (The Parquet Read Path).
• That test is only useful when the recorded range is narrow relative to the predicate. If every row group spans nearly the whole domain of customer_id, every test passes and nothing is skipped. The statistics are correct and worthless, which is the single most important idea in this lesson.
• Sorting narrows the ranges. After a sort on customer_id, each file covers a contiguous slice of the id space, so a predicate on one id is excluded from all but one or two files. This is the same intuition as a sorted array, one granularity coarser (Binary Search).
• Multi-column sorting is lexicographic: it clusters the leading column tightly, the second only within ties of the first, and the third only within ties of the first two. Ordering the sort columns is therefore the whole decision, exactly as it is for a composite index — and space-filling-curve orderings such as Z-order exist to trade that away, clustering several columns moderately instead of one column well (Composite Indexes and the Leftmost-Prefix Rule).
• Clustering is statistical, not structural. Unlike a partition, which provably cannot contain a non-matching value, a sorted file merely tends to exclude one. That is a weaker promise and it is why sort order degrades gracefully rather than failing outright (Bloom Filters: Skipping Files That Cannot Contain the Key).
• The physical benefit compounds with hardware behaviour: a sorted column also compresses better, because run-length and dictionary encodings exploit adjacent repetition, and a contiguous read is what storage and prefetchers are built for (Dictionary, Run-Length, Delta and Bit Packing, Spatial Locality).

ARRIVAL ORDER (unsorted)          SORTED BY customer_id
file  customer_id min .. max      file  customer_id min .. max
---------------------------      ---------------------------
 0    c-0000012 .. c-9999871       0    c-0000012 .. c-1240883
 1    c-0000047 .. c-9999903       1    c-1240884 .. c-2488104
 2    c-0000004 .. c-9999996       2    c-2488105 .. c-3701550
 3    c-0000021 .. c-9999812       3    c-3701551 .. c-4990233
 ...                               ...
 11   c-0000009 .. c-9999944      11    c-8842001 .. c-9999996

WHERE customer_id = 'c-8842014'
  unsorted: 12 of 12 files pass the min/max test -> all read
  sorted:    1 of 12 files passes             -> 11 skipped

same rows. same format. same statistics written.
different arrangement.

• Choose the sort column as the most common non-partition filter. That is usually an entity id — customer, account, device — or a secondary timestamp (The Partitioning Decision).
• Sort during compaction rather than during ingestion when data arrives continuously. The rewrite is already paying for a read and a write, so the sort is nearly free at that point, and ingestion stays cheap (File Compaction).
• Keep the sort key short and order it by the leading filter rather than by business importance. One column does most of the work and two is often justified; beyond that the later columns cluster so weakly that they mostly cost write-time sorting, because everything after the prefix rides on ties (Composite Indexes and the Leftmost-Prefix Rule).
• Consider a curve-based ordering only when several columns are filtered with comparable frequency and no single one dominates. Where an engine offers it, it is a real technique; where it does not, do not emulate it by hand.
• Treat clustering as something that decays and schedule its maintenance. Appends, merges and deletes all degrade it, and nothing reports that they have (Data Observability).
• Verify on the read side. Files or row groups skipped versus available, for a representative filtered query, is the only evidence that sorting is producing anything (Reading EXPLAIN ANALYZE).

• Sort order guarantees nothing about correctness or about results — the same rows come back in the same aggregate regardless. It also guarantees nothing about row order in a query result: an analytical engine is free to return rows in any order unless you write ORDER BY, and a physically sorted table does not change that.
• Skipping is best-effort and probabilistic in effect. A well-clustered file *tends* to exclude non-matching values; nothing promises a given query will skip anything (Partition Pruning).
• The guarantee is only as good as the statistics, which are written by the writer. A writer that omits them, or a rewrite that regenerates them over concatenated unsorted data, removes the mechanism entirely (Parquet).
• Nothing preserves clustering across writes. Every append, upsert and delete degrades it, and no format promises to keep a table sorted for you (Upserts and Merges).