Building Reliable Backfills in Data Pipelines: A Practical Guide

In this article, let us look at building reliable backfills in data pipelines — what backfills actually are, the patterns that work in practice, the things that go wrong, and how to make them boring and reliable instead of stressful and manual.

If you have worked with data pipelines for more than a few months, you have probably been asked to “just rerun last month’s data” or “backfill from January.” The first time, you might have done it manually and it worked. The tenth time, something broke, and you spent hours debugging at 7 PM. That is the difference between a one-off script and a reliable backfill process.

What Is a Backfill

A backfill is when you reprocess historical data through a pipeline. This might be because:

• A business rule changed and old data needs to be recalculated
• A bug was discovered in the pipeline that produced incorrect results
• A new column or metric was added that needs to be computed for all historical records
• The source system had a data fix and you need to pick up the corrected records

The core problem with backfills is that pipelines are usually designed for incremental forward processing. They assume today’s data is the only new data. When you ask them to go back in time, they often break in surprising ways.

How Backfills Go Wrong

Before we get to solutions, let us talk about what makes backfills tricky. I have seen the same problems come up across different projects.

Late-arriving data overlaps. If you backfill from March 1 to March 7, but your pipeline normally picks up data that arrived up to three days late, you get double-counting at the edges. Records from March 7 that arrived on March 9 get processed twice — once by the backfill and once by the normal run.

Stateful pipelines get confused. If your pipeline maintains state — like a watermark, a checkpoint, or a cumulative counter — running it out of order can corrupt that state. The backfill might update the watermark to some old date, and then the normal run skips data it should have processed.

Downstream dependencies break. Your backfill updates Table A, which triggers a view refresh on Table B, which invalidates a cache on Dashboard C. Nobody told the downstream teams, and suddenly their dashboards show weird numbers for a few hours.

Rate limiting and quotas. A pipeline that normally processes 100K records per hour suddenly tries to process 10 million in the same window. API rate limits kick in, the database connection pool exhausts, or your cloud quota gets hit.

The “idempotency was not built in” problem. Most pipelines built for production eventually get idempotency right for the normal incremental case. But backfills often reveal edge cases where the deduplication logic does not hold — like when the same source data is replayed with a different timestamp.

Patterns for Reliable Backfills

Here are the patterns I have found to work well in practice. None of these are universally right — you pick based on your pipeline architecture.

1. Parameterised Pipeline Runs

Instead of a pipeline that always processes “yesterday,” make the date a parameter. Your normal scheduler passes --run-date=2025-07-28, and for a backfill, you pass a range like --start-date=2025-01-01 --end-date=2025-06-30.

This is the simplest pattern and works well for batch pipelines. In Apache Airflow, you can do this with conf overrides:

# Normal DAG — always processes the execution date
with DAG('daily_aggregation', schedule_interval='@daily') as dag:
    process = BashOperator(
        task_id='run',
        bash_command='python pipeline.py --date='
    )

For a backfill run, you trigger the DAG with a config override:

airflow dags trigger --conf '{"start_date": "2025-01-01", "end_date": "2025-06-30"}' daily_aggregation

Your pipeline script then loops over the range. Simple, but it puts the looping logic in the pipeline itself rather than the orchestrator, which can make individual date failures harder to isolate.

2. Orchestrator-Level Backfill Support

Some orchestrators have built-in backfill support. Airflow’s catchup=True is one example — it creates DAG runs for every interval between the start date and now. Prefect and Dagster have their own patterns.

The advantage is that each date gets its own run with its own logs, its own retry, and its own monitoring. If one date fails, the others keep going. The disadvantage is that creating thousands of runs can overwhelm the scheduler.

For most backfills under a few hundred partitions, this is my preferred approach. It gives you visibility into each run and makes debugging much easier than a single giant job.

3. Separate Backfill Job with Its Own Output Table

This is the safest pattern for critical pipelines. Instead of having the backfill write to the same table as the normal pipeline, you write to a separate “backfill” table. Once the backfill is complete and validated, you swap it in.

The rough steps:

1. Create metrics_backfill as a copy of metrics schema
2. Run the backfill, writing to metrics_backfill
3. Run validation queries comparing old and new data
4. Rename or swap the tables atomically

In BigQuery, you can do a snapshot-and-swap using table copy or CREATE OR REPLACE TABLE. In Snowflake, SWAP WITH does an atomic swap. In PostgreSQL, you do it inside a transaction with ALTER TABLE ... RENAME.

This costs more in storage and compute but gives you a rollback path. If the backfill was wrong, you just drop the backfill table and nothing was affected downstream.

4. Idempotent Overwrite by Partition

If your data lake or warehouse is well-partitioned by date, the backfill can be as simple as overwriting specific partitions:

-- Spark / Databricks
INSERT OVERWRITE TABLE sales_daily
PARTITION (sale_date BETWEEN '2025-01-01' AND '2025-06-30')
SELECT * FROM backfill_source;

Or in dbt with BigQuery, processing partition by partition:

DELETE FROM 
WHERE sale_date = '';

INSERT INTO 
SELECT ... FROM source WHERE sale_date = '';

This works beautifully when your table design supports it. But if your pipeline produces cross-partition aggregates — like rolling 7-day averages — you cannot just overwrite a clean date boundary. The neighbouring partitions are affected too, and you need a broader backfill window.

Comparison of Approaches

Pattern	Best For	Watch Out For
Parameterised runs	Simple batch pipelines, small backfills	Looping logic inside pipeline, harder to parallelise
Orchestrator backfill	Medium backfills (~50-500 partitions)	Scheduler overhead, many small runs can overwhelm the orchestrator
Separate output table	Critical pipelines, large backfills	Extra storage cost, swap timing coordination
Partition overwrite	Well-partitioned tables, incremental loads	Cross-partition dependencies break this approach

A Concrete Walkthrough: Airflow Backfill with Safety Checks

Let me walk through how we set this up for a pipeline that processes daily sales data into BigQuery. The pipeline normally runs at 3 AM and processes the previous day.

Step 1: Make the DAG parameter-aware

from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime, timedelta

default_args = {
    'owner': 'data-team',
    'retries': 1,
    'retry_delay': timedelta(minutes=5),
}

def process_sales(**context):
    run_date = context['dag_run'].conf.get('run_date', context['ds'])
    # Normal pipeline logic reads data for run_date
    # and writes to sales_daily partition
    ...

with DAG(
    'sales_pipeline',
    schedule_interval='0 3 * * *',
    start_date=datetime(2024, 1, 1),
    catchup=False,
    default_args=default_args,
) as dag:
    process = PythonOperator(
        task_id='process_sales',
        python_callable=process_sales,
    )

Step 2: Create a backfill controller script

Instead of triggering many runs manually, we wrote a small controller script:

# backfill_controller.py
import subprocess
from datetime import date, timedelta

START = date(2025, 1, 1)
END = date(2025, 6, 30)
CONCURRENCY = 4  # Number of parallel runs

current = START
while current <= END:
    date_str = current.isoformat()
    cmd = f"airflow dags trigger -c '{{\"run_date\": \"{date_str}\"}}' sales_pipeline"
    subprocess.run(cmd, shell=True)
    current += timedelta(days=1)

We ran this manually from a terminal — not fancy, but it worked. For production, you would want this wrapped in a script with error handling, retry logic, and logging to a file you can review later.

Step 3: Validation queries

Before declaring the backfill done, we ran checks:

-- Row count comparison
SELECT sale_date, COUNT(*) as row_count
FROM sales_daily
WHERE sale_date BETWEEN '2025-01-01' AND '2025-06-30'
GROUP BY sale_date
ORDER BY sale_date;

-- Check for nulls in critical columns
SELECT sale_date, COUNT(*)
FROM sales_daily
WHERE sale_date BETWEEN '2025-01-01' AND '2025-06-30'
  AND (revenue IS NULL OR customer_id IS NULL)
GROUP BY sale_date;

-- Compare totals with old data (if kept as backup)
SELECT 
    old.period_total,
    new.period_total,
    (new.period_total - old.period_total) / old.period_total * 100 as pct_diff
FROM ...

These are basic checks but they catch the most common issues — missing partitions, null contamination, and gross calculation errors. Run them before telling anyone the backfill is complete.

Production Considerations

Here are the things that matter when you move from a one-off backfill to something you expect to do regularly.

Lock the destination table during backfills. If your normal pipeline runs at the same time as a backfill, they will conflict. Either pause the normal schedule or use a locking mechanism. In Airflow, a sensor that checks for a “backfill_in_progress” flag works well enough for small teams.

Set up alerting thresholds. A backfill that takes 2 hours when you expected 30 minutes should raise an alert. Same for one that processes zero rows on a day that should have data. Wire this into your existing monitoring — PagerDuty, Slack, whatever you use.

Keep the old data until validation passes. I mentioned the separate table approach earlier. Even if you do not swap tables, keep a snapshot or an export of the data you are about to overwrite. Storage is cheap. Regenerating lost data from raw sources is expensive and stressful.

Document the backfill commands. Your future self — or the person on call — should not have to reverse-engineer how to backfill. Write the exact commands with examples in your runbook:

# Backfill single date:
airflow dags trigger -c '{"run_date": "2025-03-15"}' sales_pipeline

# Backfill a range:
python backfill_controller.py --start 2025-01-01 --end 2025-06-30

Handle schema changes across the backfill window. If you changed a column type in March 2025 and you are backfilling from January, your pipeline might try to insert old-format data into new-format columns. Either your pipeline handles this gracefully with versioned transforms, or you backfill in segments with the correct schema for each segment.

Things That Still Go Wrong

Even with all this, here are some real-world gotchas I have hit more than once.

Timezone mismatches. The source system logs in UTC, the warehouse partition key is in local time, and the backfill date parameter gets interpreted differently by different components. Pick UTC for everything internally and only convert at the presentation layer.

Source data retention. You run a backfill for January data in July, but the source only keeps six months of raw logs. The backfill silently produces partial results because the source data was already purged. Check your data retention policies before starting a backfill that goes back further than your usual window.

API pagination changes under load. A backfill hits an API that was fine for incremental loads but times out or returns truncated results when requesting 100x the usual volume. Add rate limiting and smaller batch sizes specifically for backfill workloads.

Cost surprises. In cloud data warehouses, a full table scan for a backfill can cost significantly more than the daily incremental scan. Check your query plan and estimate the bytes processed before running a large backfill — a one-hour backfill should not generate a surprise four-figure bill.

Backfills are one of those things that seem trivial until you have been burned by one. The patterns are not complicated — parameterise your runs, make writes idempotent, validate before swapping, and document the process. What makes the difference is treating backfills as a first-class operation rather than an afterthought you figure out when someone asks for it. Get your team to the point where running a backfill is a boring command typed into a terminal, not a Slack thread that starts with “hey, anyone seen weird numbers in the dashboard?”