Choosing a Partition Key, and the Small-Files Problem

Choosing the partition key is the real skill

Partitioning is easy to do and easy to do badly. The interview question is never "can you partition," it is "what would you partition on, and what goes wrong if you choose wrong." Three rules cover most of it.

Rule 1: partition on the column you filter by, at low-to-moderate cardinality. The whole payoff is pruning, and you only prune on the partition key, so partition on what queries actually filter on. That is almost always a date (dt), sometimes a region or a category. A date has naturally low cardinality (365 values a year) and matches how analysts slice data.

Table

Partition on a low-cardinality column you filter on. High-cardinality keys shred the table into tiny files.

candidate key	cardinality	result
dt (date)	low (365 per year)	great: matches the common filter, healthy file sizes
country	low	good: prunes region queries
user_id	very high	bad: millions of tiny partitions, small-files problem
event_id	unique	terrible: one partition per row

Rule 2: avoid the small-files problem. Partition on a high-cardinality column like user_id and you get a huge number of tiny partitions, each with tiny files. This is pathological, because every file carries fixed overhead: the engine opens it, reads its footer, and tracks its metadata, and object stores start refusing requests when you hammer them (S3 returns "please reduce your request rate"). AWS measured reading 100,000 small files taking 11.5 seconds against 4.3 seconds for the same data in one file. The rule of thumb is to aim for files around 128 MB, not kilobytes. Many small files are slower than a few big ones, even for the same total bytes. (This 128 MB is a whole-file target, which is a different knob from the 128 MB row-group default inside a Parquet file from the last module: a single file holds one or more row groups.)

Rule 3: do not over-partition. Partitioning on a column you rarely filter on, or one with thousands of values, multiplies partition metadata and shreds your files without buying any pruning. If a would-be key is high cardinality, partition coarser (by month instead of day, or by a bucket of the value) or bucket it instead (next lesson).

Spotting the problem with SQL

A partition catalog that records each partition's file count and total bytes lets you compute the average file size and flag the partitions suffering the small-files problem. The exercises do exactly that: find the partitions whose average file is far below the 128 MB target, and measure how many excess tiny files they add.

Common mistake: partitioning on a high-cardinality column like user_id. It creates a partition per value, which shreds the table into millions of tiny files where per-file overhead dominates and object stores start throttling requests.

Interview nuance: "you have millions of tiny files and queries are slow, what happened" is a classic. The answer is over-partitioning on a high-cardinality key: too many partitions means too many tiny files, and per-file overhead plus metadata dominate. The fix is a coarser partition key (or bucketing) and compaction into roughly 128 MB files.

On a real platform this differs. Real engines expose file counts and sizes through catalog stats or a storage inventory, and compaction jobs (or Iceberg and Delta "optimize") merge small files for you. The partition_files table here gives you the file count and bytes per partition so you can compute the average file size the same way an audit query would.