Bugs happen, requirements change, and source data gets corrected — backfilling is how data engineers fix the past without breaking the present. This lesson teaches you the core strategies for reprocessing historical data safely, including full, incremental, and partial backfills, plus the concept of idempotency that makes it all work.
Imagine you've been running a data pipeline for three months that calculates daily revenue summaries for your e-commerce company. Everything looks great until your analytics team discovers a bug: the pipeline has been excluding refunded orders from the revenue calculation, making your numbers look higher than they actually are. The fix is straightforward — two lines of code — but now you have a bigger problem. Three months of historical data is wrong, and the executive dashboard your CEO looks at every morning is lying to you.
This is the moment backfilling enters the picture. Backfilling is the process of reprocessing historical data through a corrected or updated pipeline to replace or supplement previously computed results. It's one of those skills that feels optional right up until the moment it becomes absolutely urgent. Every data engineer, analyst, or data scientist who maintains any kind of automated data process will eventually face a situation where historical data needs to be rerun. The question isn't if you'll need to backfill — it's whether you'll be prepared when you do.
By the end of this lesson, you'll understand exactly what backfilling is, why it's more complex than it sounds, and how to approach it strategically. You'll walk away with concrete techniques for planning and executing backfills safely, without blowing up your production systems or your weekend.
What you'll learn:
This lesson assumes you're comfortable with the general idea of a data pipeline — a process that moves and transforms data from one place to another on a schedule. You don't need experience with any specific tool. Familiarity with basic SQL will help you follow the examples, but it isn't strictly required. If you know what a database table is and have a rough sense of how data gets loaded into one, you're ready.
Before we go deep on backfilling, let's make sure we're aligned on what a data pipeline is and why it accumulates history in the first place.
A data pipeline is an automated sequence of steps that extracts data from one or more sources, transforms it in some way, and loads the results somewhere useful — typically a data warehouse or a reporting database. Most pipelines run on a schedule: hourly, daily, or weekly. Each time the pipeline runs, it processes a specific window of data — for example, "all orders placed yesterday" — and writes the output somewhere.
Over time, this creates a historical record. Your pipeline has processed data for every day in the past six months, and all those results are stored in a table somewhere. That's enormously valuable. It's also a liability, because if the pipeline had a bug for any of those days, the stored results are wrong.
Think of it like a bank's statement generation process. Every month, the bank runs a process that summarizes all transactions and produces a statement. If that process had a calculation error — say, it was double-counting ATM fees — every statement produced with the bug would need to be regenerated. The bank can't just fix the process going forward; they have to go back and fix the historical statements too. That's backfilling.
Understanding when you need to backfill is just as important as knowing how. Here are the most common situations you'll encounter.
Bug fixes in transformation logic. This is the scenario from our opening example. Your pipeline was doing math wrong, applying the wrong filter, or joining to the wrong table. You fix the code, but all the historical output produced by the buggy code needs to be recomputed.
New business requirements. Your stakeholders decide they want a new metric — say, "gross margin by product category" — that the pipeline wasn't previously tracking. You add the logic, but they also want the historical trend going back to the start of the year. You need to backfill to populate the historical data.
Source data corrections. Upstream systems sometimes retroactively correct data. Your CRM might fix a customer's address, or your order management system might update a shipment status that was recorded incorrectly. If your pipeline has already processed that data, you need to reprocess it to pick up the correction.
Schema or data model changes. You redesign your data model — perhaps splitting one wide table into two more focused tables, or changing how you represent a many-to-many relationship. Existing historical data needs to be migrated into the new structure.
New data source integration. You add a second data source that enriches your existing data. Now all historical records need to be reprocessed to incorporate the new enrichment.
Each of these scenarios creates a different shape of backfill: different date ranges, different data volumes, and different levels of urgency. Let's look at how to approach them.
There's no single "right" way to backfill — the best approach depends on your data volume, your pipeline design, and how much tolerance you have for disruption. Here are the three main strategies.
A full backfill means reprocessing the entire history of data from the beginning, replacing all previously computed results. This is the simplest strategy to reason about because you start fresh: drop or truncate the destination table, run the pipeline from day one, and rebuild the entire dataset with the corrected logic.
Full backfills are appealing because they're easy to verify — when they're done, you know every record has been processed by the new code. They're the right choice when:
The downside is cost and time. If you have two years of hourly data across multiple large tables, a full backfill might take days and cost significant money in compute and storage. In those cases, a more targeted approach is better.
An incremental backfill — sometimes called a windowed backfill — processes historical data in time-based chunks, one period at a time. Instead of running everything at once, you tell your pipeline: "Process January 1st. Now process January 2nd. Now January 3rd..." and so on until you've covered the affected range.
This is the most common strategy in practice because most data pipelines are already designed to process data in time windows. Your daily pipeline already knows how to process "one day's worth of data" — a backfill just means running that same logic many times for historical dates instead of just today's date.
Here's a simple example. Suppose you have a Python script that processes orders for a given date and writes to a summary table:
def process_daily_orders(processing_date: str):
"""
Extract orders for a given date, compute revenue metrics,
and write results to the revenue_summary table.
"""
query = f"""
INSERT INTO revenue_summary (date, total_revenue, order_count, avg_order_value)
SELECT
order_date,
SUM(order_total - COALESCE(refund_amount, 0)) AS total_revenue,
COUNT(order_id) AS order_count,
AVG(order_total - COALESCE(refund_amount, 0)) AS avg_order_value
FROM orders
WHERE order_date = '{processing_date}'
AND status != 'cancelled'
GROUP BY order_date
"""
run_query(query)
print(f"Processed orders for {processing_date}")
Notice that this function already accepts a processing_date parameter. That's intentional — a well-designed pipeline is parameterized by date from the start, precisely because it makes backfilling possible. A backfill script then becomes straightforward:
from datetime import date, timedelta
def run_backfill(start_date: date, end_date: date):
"""
Reprocess all days between start_date and end_date, inclusive.
"""
current_date = start_date
while current_date <= end_date:
date_str = current_date.strftime('%Y-%m-%d')
print(f"Backfilling {date_str}...")
process_daily_orders(date_str)
current_date += timedelta(days=1)
print("Backfill complete.")
# Reprocess the last 90 days
run_backfill(
start_date=date(2024, 1, 1),
end_date=date(2024, 3, 31)
)
This approach is gentle on your systems because you can control the rate — add a time.sleep(1) between iterations, run only during off-peak hours, or limit to a certain number of days per run.
A partial backfill targets a specific subset of data rather than an entire date range. This is appropriate when a bug or data issue only affected specific records — for example, only orders from a particular customer segment, or only transactions above a certain dollar amount.
Partial backfills require more careful planning because you need to precisely identify which records need to be reprocessed and ensure you don't accidentally corrupt records that were correct.
-- First, identify affected records
-- (example: orders where the wrong tax rate was applied)
SELECT DISTINCT order_date
FROM orders
WHERE customer_region = 'EU'
AND tax_rate_applied != 0.20
AND order_date BETWEEN '2024-01-01' AND '2024-03-31';
-- Then delete only the affected summary rows before reprocessing
DELETE FROM revenue_summary
WHERE date IN (
SELECT DISTINCT order_date
FROM orders
WHERE customer_region = 'EU'
AND tax_rate_applied != 0.20
AND order_date BETWEEN '2024-01-01' AND '2024-03-31'
);
-- Now re-run your pipeline only for those dates
Warning
Partial backfills are the trickiest strategy because they require you to be surgical. A mistake in the DELETE condition can remove correct data. Always test your deletion logic on a copy of the data first, and keep a backup.
Here's a word you'll hear constantly in data engineering: idempotency. An idempotent operation is one that can be run multiple times and produce the same result each time. In math, multiplying anything by 1 is idempotent — you can do it a thousand times and the result doesn't change. In data pipelines, idempotency means you can rerun the pipeline for a given date without creating duplicate data or corrupting existing results.
Why does this matter for backfilling? Because backfills often go wrong partway through, and you need to be able to restart them safely. If your pipeline for January 15th fails halfway through, you need to re-run it — and you need to trust that re-running it won't double-count all the records that were successfully processed in the first half.
A non-idempotent pipeline does a simple append:
-- DANGEROUS: Running this twice will create duplicate rows
INSERT INTO revenue_summary (date, total_revenue, order_count)
SELECT
order_date,
SUM(order_total),
COUNT(order_id)
FROM orders
WHERE order_date = '2024-01-15'
GROUP BY order_date;
If this runs twice, you get two rows for January 15th. Your revenue figures are now doubled.
An idempotent pipeline uses a pattern that handles existing data gracefully. The most common approach is delete-then-insert (sometimes called "replace"):
-- Step 1: Remove any existing results for this date
DELETE FROM revenue_summary
WHERE date = '2024-01-15';
-- Step 2: Insert the freshly computed results
INSERT INTO revenue_summary (date, total_revenue, order_count)
SELECT
order_date,
SUM(order_total - COALESCE(refund_amount, 0)),
COUNT(order_id)
FROM orders
WHERE order_date = '2024-01-15'
GROUP BY order_date;
Now it doesn't matter how many times you run this — you always end up with exactly one row for January 15th, computed with the latest logic. This is the foundation of a backfill-safe pipeline.
Many databases and warehouses also support MERGE or UPSERT statements that do this atomically:
-- PostgreSQL / BigQuery style MERGE
MERGE INTO revenue_summary AS target
USING (
SELECT
order_date AS date,
SUM(order_total - COALESCE(refund_amount, 0)) AS total_revenue,
COUNT(order_id) AS order_count
FROM orders
WHERE order_date = '2024-01-15'
GROUP BY order_date
) AS source
ON target.date = source.date
WHEN MATCHED THEN
UPDATE SET
total_revenue = source.total_revenue,
order_count = source.order_count
WHEN NOT MATCHED THEN
INSERT (date, total_revenue, order_count)
VALUES (source.date, source.total_revenue, source.order_count);
Tip
Build idempotency into your pipelines from day one, not as an afterthought. If every pipeline run is idempotent, backfilling is just "running the pipeline for old dates" — no special code needed.
One of the most underestimated risks of backfilling is what it does to your production systems. A backfill is essentially running your pipeline many times in rapid succession, which means:
Here are practical ways to protect your systems:
Throttle the backfill rate. Add deliberate pauses between processing each time window. A one-second sleep between days might seem slow, but it spreads a 90-day backfill over 90 seconds instead of hammering the database all at once.
import time
from datetime import date, timedelta
def run_backfill(start_date: date, end_date: date, sleep_seconds: float = 1.0):
current_date = start_date
total_days = (end_date - start_date).days + 1
days_processed = 0
while current_date <= end_date:
date_str = current_date.strftime('%Y-%m-%d')
process_daily_orders(date_str)
days_processed += 1
print(f"Progress: {days_processed}/{total_days} days complete")
current_date += timedelta(days=1)
time.sleep(sleep_seconds) # Be kind to the database
Use a separate read replica. If your source is a transactional database, read from a replica rather than the primary. This keeps the backfill's query load off the production database.
Run backfills during off-peak hours. Schedule intensive backfills for nights or weekends when regular pipeline runs and user queries aren't competing for resources.
Write to a staging table first. Instead of writing directly to the production table your dashboards read from, write to a separate staging table. Once the backfill is complete and validated, swap in the new data. This prevents stakeholders from seeing partial or inconsistent results during the backfill.
-- Write backfill results to a staging table
CREATE TABLE revenue_summary_backfill AS
SELECT * FROM revenue_summary WHERE 1=0; -- Empty copy with same schema
-- Run all your backfill logic into revenue_summary_backfill...
-- Once validated, swap it in
BEGIN;
DROP TABLE revenue_summary_old;
ALTER TABLE revenue_summary RENAME TO revenue_summary_old;
ALTER TABLE revenue_summary_backfill RENAME TO revenue_summary;
COMMIT;
Backfills for large date ranges can take hours or even days. You need a way to track where you are and resume from the right place if something fails.
A simple but effective approach is a backfill log table — a small table that records which dates have been successfully processed:
CREATE TABLE backfill_log (
backfill_id VARCHAR(50),
processing_date DATE,
status VARCHAR(20), -- 'pending', 'success', 'failed'
started_at TIMESTAMP,
completed_at TIMESTAMP,
error_message TEXT
);
Your backfill script checks this table before processing each date, skipping dates that are already marked as successful:
def date_already_processed(backfill_id: str, processing_date: date) -> bool:
result = run_query(f"""
SELECT COUNT(*) FROM backfill_log
WHERE backfill_id = '{backfill_id}'
AND processing_date = '{processing_date}'
AND status = 'success'
""")
return result[0][0] > 0
def run_resumable_backfill(backfill_id: str, start_date: date, end_date: date):
current_date = start_date
while current_date <= end_date:
if date_already_processed(backfill_id, current_date):
print(f"Skipping {current_date} — already processed")
current_date += timedelta(days=1)
continue
try:
log_start(backfill_id, current_date)
process_daily_orders(current_date.strftime('%Y-%m-%d'))
log_success(backfill_id, current_date)
except Exception as e:
log_failure(backfill_id, current_date, str(e))
print(f"Failed on {current_date}: {e}")
# Decide: stop or continue to next date?
current_date += timedelta(days=1)
This makes your backfill resumable — if it fails on day 47, you can restart the script and it will pick up at day 47 without re-running the 46 successful days.
Let's tie everything together with a practical exercise you can work through.
Scenario: You manage a pipeline that processes daily website traffic data and writes summary metrics to a traffic_summary table. A bug was discovered on March 15th: the pipeline was double-counting mobile sessions because it joined to an events table without deduplication. The bug existed from January 1st through March 14th (it was fixed on March 15th going forward).
Your task:
Design the idempotent insert logic. Write a SQL snippet that, for a given date, deletes existing rows from traffic_summary for that date and reinserts them with corrected logic. Your corrected query should deduplicate sessions using SELECT DISTINCT session_id before aggregating.
Write a backfill loop. In Python (or pseudocode if you prefer), write a loop that iterates from January 1st to March 14th and calls your processing function for each date. Add a 0.5-second sleep between iterations.
Add progress tracking. Extend your loop to print a progress message after each day is processed: "Processed YYYY-MM-DD (N of 73 days complete)."
Think through the failure case. If your backfill fails on February 10th, what should happen? Should the script stop and alert you, or should it log the failure and continue to February 11th? Write two or three sentences justifying your answer.
Take your time with this exercise. Even writing pseudocode forces you to confront the real decisions in a backfill: what does "correct" look like, how do you handle failures, and how do you verify the result is right?
Running a backfill without a backup. Before you delete and reinsert anything, take a snapshot of the table you're modifying. This takes five minutes and has saved countless data engineers from very bad days. Even a CREATE TABLE revenue_summary_backup AS SELECT * FROM revenue_summary is enough.
Forgetting that downstream tables also need updating. Your revenue_summary table might feed a monthly_revenue_rollup table, which feeds a quarterly_targets table. When you backfill revenue_summary, you might need to cascade the backfill downstream. Map your data lineage before starting.
Underestimating the time a backfill will take. Test on one day first and measure how long it takes. Then multiply by the number of days in your range and add 20%. If it's going to take 36 hours, plan accordingly.
Not communicating with stakeholders. If your dashboards pull from the tables you're backfilling, users will see numbers changing in real time during the backfill. This is alarming if they don't know it's happening. Send an email first.
Treating the backfill as the validation. Just because the backfill ran without errors doesn't mean the results are correct. Spot-check specific dates by manually calculating expected values and comparing them to what the pipeline produced. Pick at least three dates — beginning, middle, and end of the range.
Using production credentials for the backfill. Run your backfill under a separate service account with write access scoped only to the tables you need. If something goes wrong, the blast radius is limited.
Backfilling is one of those skills that reveals the difference between pipelines that were thoughtfully designed and pipelines that were just good enough for today. The core ideas to carry forward are:
Now that you understand backfilling as a concept, the natural next steps are to look at how orchestration tools like Apache Airflow and Dagster handle backfilling natively — they build these patterns directly into their scheduling systems, with support for running historical "DAG runs" with a few clicks. You'll also want to explore the concept of data lineage more deeply, because knowing which tables depend on which is essential for deciding how far downstream a backfill needs to propagate.
Great data engineering isn't just about building pipelines that work today — it's about building pipelines that you can correct, extend, and trust over time. Backfilling is what makes that possible.