The DuckDB team wrote a great post on why you might worry about the write order in tables prepared for analytical purposes: Faster Dashboards with Multi-Column Approximate Sorting. It's a great introduction to space-filling curves and why they're relevant for optimizing queries that could target multiple columns - well worth a read. The gist is that the multiple columns used for some downstream purpose (e.g. querying or joins) form a multi-dimensional space, but the row order - imperative for skipping row groups - is inherently one-dimensional. The task for optimizing for multi-column queries becomes one of effectively reducing this multi-dimensional space to a one-dimensional order.

Getting this right is especially important for multi-step data transformation pipelines, where the same table might be used for multiple purposes downstream.

The key insight of this article is that space-filling the potential values in the dataset is sub-optimal for an uneven distribution of values. By first ranking the values from each column, and interleaving those ranks, much better performance can be achieved. This is especially valuable where it's fine for a row's key to change as the table does, such as when the whole table is rewritten. Here I present a variety of alternative methods for comparison on the TPC-DS dataset.