How Do You Backfill?

Concepts covered: Backfill Patterns

Backfill is the question that exposes whether your pipelines are production-grade or demo-ware. A new column is added, a bug corrupted three weeks of data, a new downstream model needs historical features - all of these require reprocessing historical data through a pipeline designed to only look forward. The interviewer wants to hear that you design for backfill from day one, not bolt it on after the first incident.

Partition-Level Backfill

Your answer should start here: partition-level backfill. Instead of reprocessing the entire table (expensive, risky) or individual rows (complex, slow), you reprocess entire date partitions. This gives you a natural unit of work that is independently verifiable and restartable. The framework: "I delete the target partition, re-extract from source, and re-insert. Running it twice produces the same result."

-- Partition-level backfill: reprocess March 2026
DELETE FROM warehouse.events
WHERE event_date BETWEEN '2026-03-01'
                      AND '2026-03-31';

INSERT INTO warehouse.events
SELECT *
FROM transform_events(
  source_ref => 'raw.events',
  start_date => '2026-03-01',
  end_date   => '2026-03-31'
);

Delete-then-insert at the partition level is idempotent: running it twice produces the same result. This is the word the interviewer is listening for - idempotent. Say it early. Backfills fail partway through, get retried, and must not produce duplicates. If your backfill strategy can produce duplicates on retry, the interviewer will push back hard.

Idempotent Backfill Design

An idempotent operation produces the same result regardless of how many times it runs. For backfills, this means: if your pipeline crashes halfway through reprocessing January and you restart it, you get exactly the correct January data - not duplicates, not partial results. The delete-then-insert pattern achieves this naturally. MERGE-based backfills are also idempotent as long as your unique key is stable. Red flag: using INSERT INTO without a preceding DELETE - that appends duplicates on every retry.

Non-Idempotent

• INSERT INTO ... SELECT (appends duplicates on retry)
• UPDATE SET counter = counter + 1 (double-counts)
• Relies on external state to know 'where we left off'

Idempotent

• DELETE partition + INSERT (safe to retry)
• MERGE ON unique_key (upserts, no duplicates)
• Each partition is a self-contained unit of work

DAG Orchestration for Backfills

In Airflow, a backfill is triggered by clearing task instances for a date range. The scheduler re-executes each DAG run for the cleared dates. This works well when your DAG is parameterized by execution_date - each run only processes its own date partition. The trap the interviewer is testing: if your DAG hardcodes 'yesterday' instead of using the execution date, backfills silently produce wrong data. Say this unprompted and you pass.

Do

Avoid

About this lesson

This section is part of the Incremental Loading: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.