Partitioning, Clustering and File Sizing

Query cost on a data lake is mostly determined before any query is written. Three physical choices, made at write time, decide how many bytes a filter has to touch, and the difference between good and bad choices is routinely two orders of magnitude.

Partitioning

Partitioning splits a table into directories or metadata groups by the value of one or more columns, so a filter on those columns eliminates whole groups without reading them. It is the coarsest and most powerful pruning mechanism available.

It is also the easiest to get catastrophically wrong. Partitioning on a high-cardinality column, user ID being the canonical example, creates one tiny partition per distinct value: millions of directories, millions of small files, metadata that no longer fits comfortably in a catalogue, and query planning that takes longer than the scan it was meant to avoid.

The rule that holds is that a partition should be large enough to be worth being a partition, meaning a substantial multiple of the target file size. Date is the most common partition key because time-range filters are near-universal and daily volumes are usually the right magnitude. If daily partitions would be a few megabytes, partition monthly instead. If they would be terabytes, add a second key.

Clustering

Clustering, or sorting on write, orders rows within each partition so that min-max statistics become selective. It is the finer-grained complement to partitioning and it is the tool for columns whose cardinality is too high to partition on.

Sorting by a column makes filters on it highly selective, since each file spans a narrow range. Sorting by several columns is lexicographic, so the first column dominates and later ones give diminishing selectivity. Where filters use different columns unpredictably, a space-filling curve such as Z-order or Hilbert order gives moderate locality on all of them, which is worse for any single column than sorting on it and much better than nothing.

File sizing

The two forces are opposed. Files that are too small mean per-file open overhead, poor compression because dictionaries and encodings are built per file, and metadata volume. On object storage a request has tens of milliseconds of latency, so a query touching 10,000 tiny files spends most of its time waiting.

Files that are too large mean coarse skipping, because the row group is the unit of pruning, and poor parallelism, because a single file limits how many workers can share the work.

The usual target is 128 MB to 1 GB, with 256 MB to 512 MB a common default. Streaming ingestion cannot produce these directly, so compaction jobs merge small files after the fact. The right way to think about it is that streaming writes optimise for latency and compaction re-optimises for read cost, and any streaming table without compaction is trading a permanent read penalty for write simplicity.

When it breaks

Partition skew wrecks parallelism. Partitioning on a column with a dominant value, a default country code or a null placeholder, puts most of the data in one partition that one worker must process. The job's runtime becomes the runtime of that partition, and adding workers does nothing.

Filters that do not match the layout silently full-scan. A table partitioned by ingestion date, queried by event date, cannot prune at all. This is invisible except in bytes scanned, and it is why the physical layout must be chosen against actual query patterns rather than against the schema's apparent structure.

Over-partitioning is harder to undo than under-partitioning. Adding a partition key later usually means rewriting the table. Modern table formats support partition evolution for new data while leaving old data alone, which helps and leaves the historical layout as it was.

Sorting costs at write time. A global sort requires a shuffle, which for large writes is the most expensive part of the job. Sorting within partitions is much cheaper and usually sufficient, since partition pruning has already narrowed the search.

Compaction competes with readers. Rewriting files consumes IO and, in formats using optimistic concurrency, can conflict with concurrent writes. Scheduling it during low-traffic windows is standard, and treating it as a background detail rather than a first-class job is how tables end up degraded.