Backfills and Reprocessing in Data Pipelines

Data pipelines have to rerun. A bug corrupted last month's output, a schema change needs historical records updated, or analysts want a new metric across all history. Backfilling (reprocessing data the pipeline already handled) isn't an exception; it's routine. The question is how gracefully your pipeline does it.

Why you reprocess

• Bug fixes: a logic error produced wrong output for the past month; after the fix, reprocess the affected data.
• Schema evolution: a new field in the model must be filled in for existing records from raw data.
• New derived data: a metric that didn't exist when the pipeline was built must cover history, not just new events.
• System migration: a new warehouse or storage system must be populated with history.
• Compliance or audit: specific historical records must be corrected for regulatory reasons.

Strategy 1: full reload

Delete the output and rerun the pipeline from the beginning of time.

Works when the dataset is small enough to reprocess quickly, consumers can tolerate a complete replacement, and simplicity beats speed. Trade-offs: slow for years of data, the destination may be empty or inconsistent during the reload, and it costs a lot of compute.

Strategy 2: incremental backfill

Reprocess in bounded chunks, usually time partitions (day, week, month), and record which chunks are done so a failure resumes instead of restarting. To backfill 90 days:

1. List the partitions to redo: 2024-01-01 through 2024-03-31.
2. Process each partition independently into its own output location.
3. Record each finished partition in a checkpoint table.
4. If the job dies on day 45, restart at day 45, not day 1.

from datetime import date, timedelta

def backfill(start: date, end: date, run_partition, checkpoints):
    """Reprocess one day at a time; skip days already done so reruns resume."""
    day = start
    while day <= end:
        if not checkpoints.is_done(day):
            run_partition(day)          # must be idempotent: overwrites that day's output
            checkpoints.mark_done(day)  # only after the partition fully succeeded
        day += timedelta(days=1)

# backfill(date(2024, 1, 1), date(2024, 3, 31), rebuild_sales_summary, checkpoint_table)

Works when a full reload would take too long, you want progress and time estimates, and you can't afford long downtime (data becomes correct day by day). Trade-offs: more coordination logic, partial state if you crash mid-partition, and dependencies between partitions (such as slowly changing dimensions) need careful ordering. Orchestrators like Airflow run backfills per partition natively; see pipeline orchestration.

Strategy 3: parallel versioning

Build a complete new output beside the old one, validate it, then switch atomically:

1. Production reads sales_summary_v1.
2. The backfill writes sales_summary_v2 (same schema).
3. Validation queries compare v1 and v2.
4. When satisfied, repoint the view or alias to v2.
5. Keep v1 briefly for rollback, then delete it.

-- Validate before switching
SELECT date, v1.total AS old_total, v2.total AS new_total
FROM sales_summary_v1 v1 FULL JOIN sales_summary_v2 v2 USING (date)
WHERE v1.total IS DISTINCT FROM v2.total;

-- Atomic switch (and an equally quick rollback)
CREATE OR REPLACE VIEW sales_summary AS SELECT * FROM sales_summary_v2;

Works when you need zero downtime, validation before users see results, and easy rollback. Trade-offs: double storage during the migration, an atomic swap mechanism (view, alias, symlink, table rename), and consumers that read through that indirection. Kappa architecture uses the same idea for streams.

The foundation: idempotent writes

Whatever the strategy, reprocessing is only safe if processing the same input twice gives the same result, with no duplicates. See idempotency.

-- Not idempotent: every rerun adds another row
INSERT INTO sales_summary (date, total) VALUES ('2024-01-15', 1500);

-- Idempotent: a rerun overwrites the same row
INSERT INTO sales_summary (date, total) VALUES ('2024-01-15', 1500)
ON CONFLICT (date) DO UPDATE SET total = EXCLUDED.total;

Batch pipelines usually partition output by time and replace whole partitions atomically (/output/date=2024-01-15/ is swapped as a unit). Each partition is entirely the old version or entirely the new one, never a mix, and rewriting it twice just produces the same data.

# Spark: overwrite only the partitions being reprocessed, atomically per partition
spark.conf.set("spark.sql.sources.partitionOverwriteMode", "dynamic")
(fixed_df.where("date BETWEEN '2024-01-01' AND '2024-03-31'")
    .write.mode("overwrite").partitionBy("date")
    .parquet("s3a://warehouse/sales_summary/"))

Designing for reprocessing from day one

• Keep the source data. If you only store transformed output, you can't recompute with new logic. ELT helps because raw data stays in the warehouse; see ETL vs ELT.
• Version transformations. Record which logic version produced each output (a version column or a path segment), so you know exactly which outputs are stale after a change.
• Partition by time. Replacing one day without touching others makes incremental backfill natural and matches "reprocess the last N days" workflows.
• Test replays regularly. Periodically run history through the pipeline to catch non-idempotent writes or missing source retention before an incident forces you to.