Backfills and Reprocessing

Scenario

Example company: a bug in the currency conversion has inflated revenue in non-US markets for 18 months. After the fix, an engineer writes a script that loops over every day since the bug started and reruns the transformation.

What you see in production

• The script appends rows, so the backfilled days now have both the old wrong rows and the new correct rows.
• It runs sequentially and will take three days. While it runs, the nightly job writes to the same partitions, and some days end up with the nightly version, others with the backfill version.
• The warehouse bill for the week doubles.

The naive code

for day in days_between("2024-04-01", "2025-10-01"):
    run_transform(day)        # appends; no locking; one day at a time

Reproduce it

The model compares an appending, sequential backfill with partition overwrites run in parallel with a concurrency limit.

days = [f"d{i:02d}" for i in range(1, 9)]
wrong = {d: 120 for d in days}          # inflated values already in the warehouse
right = {d: 100 for d in days}          # values after the fix

# Naive: append corrected rows next to the old ones
table = {d: [wrong[d]] for d in days}
for d in days:
    table[d].append(right[d])
print("append backfill, day d01 rows:", table["d01"], "-> reported", sum(table["d01"]))

# Fix: overwrite each partition (idempotent), run in parallel with a limit
table = {d: [wrong[d]] for d in days}
for d in days:
    table[d] = [right[d]]
print("overwrite backfill, day d01 rows:", table["d01"], "-> reported", sum(table["d01"]))

hours_per_day = 0.5
for workers in [1, 4]:
    waves = -(-len(days) // workers)      # ceiling division
    print(f"{workers} worker(s): {waves * hours_per_day:.1f} hours for {len(days)} days")

append backfill, day d01 rows: [120, 100] -> reported 220
overwrite backfill, day d01 rows: [100] -> reported 100
1 worker(s): 4.0 hours for 8 days
4 worker(s): 1.0 hours for 8 days

Root cause

The transformation was built for "process today", not "process any day": it appended instead of overwriting, ran outside the orchestrator so nothing prevented two runs on the same day, and had no parallelism control.

The fix

• Make every task partition-scoped and idempotent: transform(day) overwrites exactly that day's partition.
• Run backfills through the orchestrator, so it knows which runs exist, retries failures and prevents overlap. Airflow, for example, creates a DAG run per logical date and supports backfills and catch-up.
• Limit concurrency (max_active_runs, pools, warehouse queues) so a backfill doesn't starve daily jobs.
• Pause or coordinate the scheduled job for the affected partitions, or make tasks take a per-partition lock.
• Version the transformation logic, so you know which code produced each partition.

Trade-offs

• Parallel backfills finish faster but cost more at once and can saturate the warehouse or source systems.
• Full-table rebuilds (create a new table, swap it in) are simpler for small or medium tables than per-partition backfills.

Detect it in production

• Duplicate-key tests after each backfilled partition.
• Compare backfilled totals with an independent source for a sample of days.
• Watch warehouse queue time and cost during backfills.

Key points

• Design tasks as "process partition X" with overwrite semantics from day one.
• Run backfills through the orchestrator with concurrency limits and no overlap with scheduled runs.
• Record which logic version produced each partition.