Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 36 ยท Ingestion and Batch Processing

Partitioning and Backfills

Learn how partitioning data by date speeds queries and enables safe reruns, and how to run backfills for history using logical dates and idempotent partition jobs.

A table with five years of events is not queried as one blob. Most questions ask about a day, a week or a month. Partitioning splits a dataset into separate chunks along a column, most often a date, so queries and jobs can touch only the chunks they need. Partitioning also gives batch jobs a natural unit of work: "process the 2026-03-10 partition". That unit is what makes backfills (recomputing history) manageable.

What partitioning does

Imagine one folder per day:

Text
events/
  day=2026-03-08/part-0.csv
  day=2026-03-09/part-0.csv
  day=2026-03-10/part-0.csv

A query for March 9th reads one folder and skips the rest. This is called partition pruning. Warehouses and lakehouses do the same thing with internal partitions or clustering.

Why partition?

Pruning Read only needed slices.
Safe reruns Replace one slice at a time.
Cheap deletes Drop old partitions fast.
Retention Expire by date.
1 / 2

Partitioning in Python

Here we partition a dataset by day, store the partitions in a dict standing in for folders, and show that a one-day query touches one partition.

Python Partition by day and prune

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Choosing a partition key

Good partition keys are columns that queries filter on, with a moderate number of distinct values:

  • Date is almost always right for event data.
  • Too fine (one partition per user) creates millions of tiny files, which are slow to list and open. This is the "small files problem".
  • Too coarse (one partition for all time) gives no pruning.
  • Skewed keys (country, where one country holds 90% of rows) give one huge partition and many tiny ones.

A rough target for file-based lakes is partitions holding at least hundreds of megabytes of data, not kilobytes.

Python Check partition balance

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

A single value holding 90% of the data is a warning that a country partition would be skewed.

Event time versus processing time

Partition by when the event happened (event time), not when it arrived (processing time), if analysts ask questions about when things happened. But late data then lands in old partitions, so those partitions must be rewritable. The streaming chapter covers lateness in detail; for batch, plan to reprocess the last few days regularly.

Partition-level idempotence

Because each run owns one partition, the safe load is "replace that partition". This is the technique from Idempotent Pipeline Design applied at scale.

Python Dynamic partition overwrite

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Backfills

A backfill recomputes data for a past date range: because you found a bug, added a column, or loaded a new source with history. With partitioned, idempotent jobs, a backfill is just "run the daily job once for each missing or wrong day".

flowchart LR
    A["Fix the code"] --> B["List dates<br/>to recompute"]
    B --> C["Run job for each date"]
    C --> D["Replace partitions"]
    D --> E["Validate counts"]

A backfill is the daily job, run for many dates.

Python Run a job for a range of dates

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice that daily_job receives the date as an argument instead of calling date.today(). That is what lets the same function serve both the normal schedule and the backfill.

Backfill practicalities

  • Rate-limit parallelism. Running 365 days at once can overwhelm the source or the cluster; run a few at a time.
  • Order matters sometimes. If day N depends on day N-1 (a running balance), backfill in order.
  • Protect downstream. Dashboards should not show half-backfilled data; write to staging and swap, or backfill in a quiet period.
  • Validate afterwards. Compare row counts and totals with the source for the range.
  • Record it. Note what was backfilled, why and by whom.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is partition pruning?

    Pruning lets the engine skip partitions whose key values cannot match the filter.
  2. 2. Which partition key would create the 'small files problem'?

    Millions of users would produce millions of tiny partitions, which are slow to list and open.
  3. 3. Why must a backfillable job take the run date as an argument?

    If the job reads the current date, it can only ever process 'now'.
  4. 4. Which is a sensible backfill practice?

    Validation catches mistakes; limits and quiet windows protect systems and users.

Summary

  • Partitioning splits data by a key (usually date) so queries prune and jobs own a clear unit of work.
  • Choose keys with moderate cardinality that match query filters; avoid tiny and skewed partitions.
  • Partition by event time and plan for late data in recent partitions.
  • Backfills are the daily job run over many dates, safe when jobs are idempotent and take the logical date.

Next: Streaming Fundamentals