How Would You Partition?

Concepts covered: Partitioning Strategies

What a partition physically is

A partition is a directory. A table partitioned by event_date stores the 20 October rows under events/event_date=2026-10-20/ and the next day's rows under the next directory, with the partition column absent from the files themselves because its value is the directory name. When a query filters on a constant, the engine matches it against directory names and lists only the directories that match. That pruning happens before any file is opened, which places it one level above the row group statistics in the previous section: partitions decide which directories to list, statistics decide which row groups inside those files to read.

CREATE TABLE events (
  event_id BIGINT,
  user_id BIGINT,
  country VARCHAR,
  status VARCHAR,
  event_ts TIMESTAMP,
  event_date DATE
)
WITH (
  format = 'PARQUET',
  partitioned_by = ARRAY['event_date']
)

Cardinality analysis

The partition count is the product of the distinct values of every partition key, and each partition should hold enough data to fill files of 128 MB to 1 GB. Those 2 facts turn the question into arithmetic. Before choosing, measure the candidates against the data you have.

SELECT
  event_date,
  country,
  COUNT(*) AS row_count,
  APPROX_DISTINCT(user_id) AS distinct_users
FROM events_raw
WHERE event_date >= DATE '2026-10-01'
GROUP BY event_date, country
ORDER BY row_count DESC

Take the events table at 50 GB of Parquet per day and score each candidate key set by partitions per year, average partition size, and skew between the largest and smallest partition.

The over-partition trap

Over-partitioning costs in 3 places. Listing: on object storage each directory is a list call that takes tens of milliseconds, so a query spanning 73,000 country partitions spends minutes planning before it reads a byte. Small files: every writer that touches a partition produces at least 1 file there, so 24 hourly loads into date-country partitions produce 24 files of about 400 MB in the largest country, 24 files near 10 MB in a mid-sized one, and 24 files of a few bytes in the smallest, and each file costs a footer read and a request. Statistics: when a partition holds 1 row group, min and max cover the whole partition and skip nothing. The symptom in the interview scenario is a dashboard showing a 30-day trend across all countries: it touches 6,000 directories and 144,000 files, and spends minutes on list calls and footer reads before it scans any data.

• 50 ms

High-cardinality keys: sort or bucket, never partition

Queries that filter on user_id still need help, and the help lives inside the partition. Sorting each day's files by user_id makes every row group hold a narrow range of ids, so the footer min and max prune most row groups for a single-user lookup: with 50 GB in 400 row groups per day, a lookup touches 1 or 2 of them. Bucketing hashes user_id into a fixed number of files per partition, which gives joins between 2 tables bucketed the same way a file-to-file match with no shuffle, and gives lookups a single file to open.

CREATE TABLE events_by_user (
  event_id BIGINT,
  user_id BIGINT,
  country VARCHAR,
  status VARCHAR,
  event_ts TIMESTAMP,
  event_date DATE
)
WITH (
  format = 'PARQUET',
  partitioned_by = ARRAY['event_date'],
  bucketed_by = ARRAY['user_id'],
  bucket_count = 64
)

Partition on it when

• Queries filter on it with constants most of the time
• Distinct values stay in the hundreds or low thousands
• Each value holds at least 1 GB over the table's life
• Old values stop receiving writes, so a date or a month

Sort or bucket on it when

• Distinct values run to millions, such as user or order ids
• It is the join key between 2 large tables
• Lookups need one file rather than one directory
• The value is spread across all time, so no partition would ever close

About this lesson

This section is part of the The Storage Question: 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.