Choose a clustering strategy
Rows can be physically ordered so that rows matching a query sit together, letting the engine skip most files or blocks. But data can be ordered only one way per copy, new writes wear the order down, and restoring it means rewriting. Which ordering pays its way here, if any?
You will end with a clustering strategy, or the decision not to cluster, and the rent it must keep paying.
Decide
Do the queries that consume your scan budget touch only a small fraction of rows?
Measure it. Under a few percent: yes.
YesGo to 2
Why
Clustering can at best cut the data read down to the fraction of rows a query needs. If queries need more than a few percent anyway, there is little left to skip; spend the effort on layout and encoding instead.
What do those queries filter or join on?
Count the columns that carry most of the scanned bytes in filters, not every column that ever appears.
One columnA single sort key
2–4 columnsZ-order or Hilbert curve
5 or moreDon't cluster
A join keyBucketing
Why
Ordering by several columns at once divides the skipping power between them: with d dimensions over U units, a query on one dimension must still touch at least U^(1−1/d) units. Two to four dimensions still skip usefully; at five or more you cannot cluster your way out.
Where you can land
A single sort key
Use it when one column carries most of the filtering (at least about 80% of scanned bytes).
Before you build it
Before adding a second sort column, check the first's distinct values
A second column in a composite sort key helps only if the first column has fewer distinct values than there are units (files or row groups). Otherwise each unit holds a slice of one first-column value, and the second column is decorative.
Z-order or Hilbert curve
Use it when two to four columns filter about equally.
Interleaves the columns so nearby values in all of them land in the same units. Hilbert usually touches somewhat fewer units than Z-order.
What it costs: each column skips less than it would as the only sort key; the floor above is a hard bound.
Bucketing
Use it when queries repeatedly join two large tables on the same key.
Hash both tables into the same number of buckets by the key, so matching buckets join without a shuffle.
Before you build it
Check skew, and the commitment
Bucketing concentrates a hot key into one bucket by design, and that cannot be parallelized away. Both tables must also keep the same bucket count for as long as both exist.
Don't cluster
Use it when queries touch a large share of rows, or filter on five or more columns.
Rely on secondary structures (indexes, per-block statistics) or accept scans. Clustering here burns rewrite budget for nothing.
Whichever you land on
Know which level of your format does what
Formats usually have two granularities: a large one (files) that makes I/O efficient, and a smaller one (row groups, pages) that decides what can be skipped. If your format has only one: it will be wrong for one of the two jobs; decide in advance which.
Compute the rent
Writes wear the order down, and only rewriting restores it. Reclustering cost per unit time ≈ write rate × (unit size ÷ change size). Compare it with the skipping saved × query rate. If the rent loses: do not cluster. Repeat the comparison when write volume changes, because the answer flips on write growth, not read growth.