Choosing the Partition Column

A partition column is worth having when 3 tests pass: the reads that matter filter on it, it has few enough values to keep the directory count manageable, and each directory holds enough bytes for right-sized files. Run them in that order. A column no query filters on prunes nothing, however neatly it sizes.

Start from the predicates

Collect the WHERE clauses of the queries that read the table, the scheduled jobs as well as the dashboards, and count how often each column appears. For events, a typical dashboard read looks like this:

SELECT
    country,
    count(*) AS errors
FROM events
WHERE event_date = DATE '2024-03-15'
    AND event_type = 'playback_error'
GROUP BY country

Tallied across 1,000 recent reads, with many reads filtering on more than 1 column:

Count the values

Distinct values over the table's whole retention become directories, so measure them before writing. Run this against user_events to get the row count beside the distinct count of each candidate. Rows divided by a column's count gives the average rows of the directory each value would get, and a count close to the row count marks a column of near-unique values.

Do the file-size math

Bytes decide whether a directory can hold right-sized files. events lands 40GB a day and keeps 2 years, 29.2TB in total. Divide by each candidate's distinct values and compare the result against a common target of files between 128MB and 1GB:

event_date wins on every test. It serves 85% of reads, and each of its 730 directories has room for about 300 files at 128MB. The 30% of reads that add country still prune to 1 day and filter the country inside it. A second level would split each day into 190 slices, and for most countries the slice is too thin to fill a single file.

Deriving the column from a timestamp

Event data usually carries a timestamp, far too fine to partition by. Derive a date at write time and partition by that, then point every reader at the derived column, as the pruning section showed with event_ts.

daily = raw_events.withColumn("event_date", F.to_date("event_ts"))

daily.write.mode("append").partitionBy("event_date").parquet("/lake/events")

A daily run loads 1 date, so every write task holds rows for that date alone and writes 1 file into its directory. The run's file count equals its task count, which the next section shows how to set.

Do

• Pick the column the most important reads filter on, as stored
• Keep distinct values in the hundreds or low thousands over the table's life
• Check that each directory holds at least a few hundred MB
• Put the coarser column first when nesting: date, then something finer

Avoid

• Don't partition by an ID, a timestamp, or any value that rarely repeats
• Don't add a second level whose directories drop under 100MB
• Don't partition a table smaller than a few GB at all

A payments table lands 6GB a day and is kept 3 years. Almost every read filters on settlement_day, and many also filter on merchant_id, which has 400,000 values. Which layout fits?

• partitionBy("merchant_id"), since the merchant filter is the most selective
• partitionBy("settlement_day", "merchant_id") to prune on both at once
• partitionBy("settlement_day"), about 1,095 directories of 6GB each
• No partitioning, since 6GB a day is too small to split into directories

About this lesson

This section is part of the Partitioning: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.