Partitioning and File Pruning

Concepts covered: Partitioning Strategies

Columnar files let a query skip columns. Partitioning lets it skip whole files. A partitioned table keeps its files in a directory per value of a chosen column, and the engine compares the query's filter to those directory values before it lists a single data file.

page_views holds 450 days of history. An hourly load writes each day as 24 columnar files of roughly 230MB, 5.5GB in all. A query filtering on view_date = DATE '2026-10-21' reads 1 directory. The other 449 are never even listed, and skipping them that way is partition pruning.

SELECT
  page_type,
  count(*) AS views
FROM page_views
WHERE view_date = DATE '2026-10-21'
GROUP BY page_type

Choose the partition column from how the table is filtered

A partition key pays off only when queries filter on it. Partition page_views by load_hour, the hour the pipeline wrote each row, while analysts filter on view_date, and every query still opens every partition, because no filter mentions load_hour. Choosing well starts from the query log: count which columns appear in WHERE clauses and how many distinct values each holds.

view_date wins on both counts. Almost every query filters on it, and a single value per day gives each partition 5.5GB, enough for files in the hundreds of MB. session_id is the opposite case: 9 million partitions a day would hold a few hundred bytes each. country sits in between, and the third of queries filtering on it tempts teams to add it as a second level.

Country partitions mean thousands of tiny files

Add country under view_date, keep the hourly load, and each run now writes 1 file per country it saw. With 200 countries across 24 runs, that is up to 4,800 files a day. The 5.5GB splits unevenly: the biggest countries get hourly files of tens of MB, and most get files well under 1MB.

Each file carries costs that do not shrink with its size. The engine has to list it and read its footer, and it schedules a task for every file. On object storage, a single request to storage often takes tens to hundreds of milliseconds. A 90-day query on the date-only table pays those costs 2,160 times; on the country-partitioned table it pays them up to 432,000 times, and on a 1MB file the footer is a sizeable share of the bytes. Small files also compress worse and hold row groups too small for their statistics to skip much. The third of queries that filter on country got faster, and all the rest got slower.

Do

• Partition on the column most queries filter on
• Keep each partition in the hundreds of MB or more
• Sort within files by secondary filter columns
• Read the query log before choosing a partition column

Avoid

• Partition on high-cardinality columns like session_id
• Partition on a column no query filters on
• Stack partition levels until files shrink below 1MB
• Pick the partition column from the load schedule instead of the filters

A payments table lands 3GB of compressed files per day. In the query log, 90% of queries filter on paid_date, 40% on merchant_id (60,000 merchants) and 8% on currency (30 values). Which layout fits?

• Partition by paid_date and merchant_id so merchant queries prune
• Partition by merchant_id alone, since it has the most distinct values
• Partition by paid_date and currency, since 30 values stay manageable
• Partition by paid_date and sort each day's rows by merchant_id

About this lesson

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