Data quality, idempotency, backfills: safe pipeline reruns

In this article

1. Before you start
2. Why interviewers ask this
3. Data quality, idempotency, backfills from first principles
4. Worked example
5. Trade-offs and edge cases
6. How to answer it in an interview
7. Common mistakes
8. Follow-up questions
9. Sources

Before you start

This page is part of the data engineer interview guide.

Why interviewers ask this

A pipeline that runs once is easy. A pipeline that is rerun after a failure, a bug fix or a late file is the one interviewers care about, so "how do you make this job safe to rerun?" separates a candidate who writes jobs from one who operates them.

A weak answer says "we add retries" and stops. A retry repeats a write, and whether the repeat is harmless depends on how the write is designed. A strong answer says what a rerun does to the output table, how a bad batch is stopped before readers see it, and how months of history are reloaded without duplicates. A Staff-level answer adds the failure mode of each write pattern and the limit on how many backfill runs go at the same time. The rest of this page builds that answer from zero.

Data quality, idempotency, backfills from first principles

Idempotency

Idempotency is the property of a pipeline step that makes running it again on the same logical input leave the same final state as running it once, with no duplicate rows and no changed totals, even though the step may do the work a second time.

Data quality check

A data quality check is an assertion about data, such as a column staying inside a range, that a pipeline tests before it publishes the data, so a bad batch stops the run instead of reaching readers.

In plain words

Think of a school register. Each morning has one attendance page. If Monday's page must be entered again, a sensible clerk erases Monday's page and writes it fresh. She does not staple a second Monday page into the folder. That habit is idempotency. A checker who counts the pupils against the roll before the page goes in the folder is the data quality check. Reprinting last term's pages after the roll was corrected is a backfill.

The analogy breaks in one place. A clerk sees the page she replaces. A pipeline replaces only what a rule in the job tells it to replace.

In one precise sentence, a safe rerun is a job whose output depends only on its input interval, whose write replaces instead of adds, and whose result is checked before anyone reads it.

Step 1: Key every run to its data interval, not to the clock

A data interval is the slice of time whose data one pipeline run is responsible for, such as one calendar day. A run keyed to its interval reads and writes only that slice, so running it again next week gives the same answer. A run keyed to the clock, such as "everything before now", gives a different answer each time and cannot be rerun safely.

Apache Airflow, a workflow scheduler, makes the interval explicit: each run of a directed acyclic graph (DAG) belongs to a data interval. The Airflow documentation says to turn catchup off when a DAG is not limited to its interval and works from the current time instead. Catchup is the scheduler's automatic creation of a run for every data interval not yet run since the DAG's start date. In Airflow 3 (documentation 3.3.2, read 2026-10-26) the scheduler by default creates a run only for the latest interval, so catchup is off unless you set catchup=True. Airflow 2 versions defaulted to catchup on, through the catchup_by_default setting, so check your version before you state the default.

Input: one interval. Output: a result that depends on nothing else. Every later step relies on this rule.

Step 2: Write so a rerun replaces data instead of adding it

Idempotency comes from how a job writes, not from how it is scheduled. A plain append adds the batch again on every rerun, so the table grows. With three days of 1,000 rows each (illustrative), one run leaves 3,000 rows and a second run of the same days leaves 6,000, of which 3,000 are duplicates.¹

Two patterns avoid that. A partition overwrite replaces the whole partition for the interval. In Spark's dynamic mode a write overwrites all existing data in each partition it commits new data to and leaves the other partitions unchanged. The default mode is static. In static mode an overwrite with no partition named replaces every partition the specification matches, which for a plain overwrite is the whole table.

Set the mode on purpose (Laskowski, Spark SQL internals). An upsert is a write that updates a row whose key already exists and inserts a row whose key does not, which Databricks provides as its merge operation (Databricks documentation). A third pattern, deleting the interval's rows and inserting the new ones in one transaction, works when both statements commit together.

Run any of these twice and the table still holds 3,000 rows.¹

Step 3: Check the data before you publish it

Quality checks belong around the load, not after readers have seen the data. Great Expectations describes its checks as "basically unit tests for your data"; its introduction (version 0.18, where the current interface differs, so this page uses it for the concept only) gives the example of asserting that a passenger count column stays between 1 and 6.

The pattern: write the interval's output to a staging table, run the checks on that staged copy, and publish only when they pass. Useful checks for a rerun are that the row count equals the expected count, that key columns hold no NULL values and no duplicate keys, and that the newest timestamp is recent enough. A failed check stops the run, so readers keep the last good data. Because step 1 keyed the run to its interval, you can fix the cause and rerun that interval alone.

Input: staged rows. Output: publish or stop. Whether the final swap from staging to the live table is atomic depends on the destination, so check what your storage supports.

Step 4: Backfill the range in controlled chunks

A backfill is explicit, while catchup is automatic. Airflow's backfill command reruns the DAG for every interval between a start and an end date. In the stable documentation read on 2026-10-26 the command is airflow backfill create with --dag-id, --from-date, --to-date, --reprocess-behavior failed and --max-active-runs 3. The Airflow 2.2.4 documentation shows airflow dags backfill, so state the version when you quote a command. That page also says you may want to backfill even when catchup is disabled.

The limit on parallel runs matters. Astronomer's example is that fifteen catchup runs with a limit of three run in five chunks of three.¹ If each interval reads only its own input, the chunks can run in any order. If a run reads the previous interval's output, as in a running total or a history table, set depends_on_past=True (Astronomer) and backfill oldest first.

Read together, the four steps say: each run processes one data interval, not the current time. A rerun replaces the interval's output instead of adding to it. Quality checks run on staged data before it is published. A backfill reruns many intervals in chunks of limited size.

1 · Key to the interval2 · Replace the output3 · Check, then publish4 · Backfill in chunksReplay

Step-by-step description

1. Each run handles one data interval.
2. A rerun replaces data instead of adding it.
3. Quality checks gate the staged data.
4. Many intervals rerun a few at a time.

• Each run processes one data interval, not the current time.
• A rerun replaces the interval's output instead of adding to it.
• Quality checks run on staged data before it is published.
• A backfill reruns many intervals in chunks of limited size.

Worked example

The script applies the same three-day batch, 1,000 rows a day (illustrative), twice to an empty table with each write pattern.¹ Appending the same three days twice gives 6,000 rows. Overwrite and merge stay at 3,000 rows after two runs.¹

Text description of the diagram

• Appending the same three days twice gives 6,000 rows.
• Overwrite and merge stay at 3,000 rows after two runs.

The Databricks merge page gives a small case: the target holds ids 1, 2 and 3, and the source holds id 2 with salary 95000 (the target has 88000) and a new id 4. The merge updates id 2 and inserts id 4, leaving four rows. Running the same merge again leaves four rows and id 2 at 95000, because the match on id finds the same row.¹

Two failures are computed as well. If the source batch holds id 4 twice and the merge only inserts unmatched rows, both copies go in, giving five rows with id 4 twice; the page says to deduplicate the new data first.¹ If a dynamic overwrite carries one stray row for a past partition that held 1,000 rows (illustrative), that partition ends with one row, a loss of 999.¹

After a backfill, compare the rows with the expected count: three days times 1,000 rows is 3,000, so an observed 6,000 fails the row-count check.¹

Trade-offs and edge cases

Append is not safe to rerun because it adds the same rows again. Dynamic partition overwrite replaces only the partitions that the write contains, so it needs complete partitions in each delivery; in the default static mode an overwrite can replace the whole table. Merge on a key needs a deduplicated batch, and it leaves stale rows in place.

Text description of the diagram

• Append is not safe to rerun because it adds the same rows again.
• Dynamic partition overwrite replaces only the partitions that the write contains.
• Merge on a key needs a deduplicated batch.

Edge cases:

• Duplicates inside the batch. Databricks documents that a merge can fail when several source rows match one target row, and that duplicate rows in the new data are inserted, so deduplicate before merging.
• Rows the fix removed. A merge updates and inserts but does not delete, so a backfill after a bug that produced extra rows leaves those rows in the target. Scope a when not matched by source delete to the interval (Databricks merge page), or use a partition overwrite. A merge whose update sets a run-time value, such as a load timestamp, is not idempotent either, because each run writes a different value.
• A stray row. The Microsoft Learn page warns that a single row in the incorrect partition can overwrite an entire partition, and advises validating that a dynamic overwrite touches only the expected partitions.
• Side effects. A job that sends an email or calls an outside service is not made idempotent by replacing a table; the side effect needs its own key.
• Streams. Continuous jobs face the same problem with different tools; see streaming vs batch.
• Keys. An upsert needs a trustworthy unique key, which comes from the table design; see data modeling interview questions.

For the Spark write options themselves, the PySpark tutorial and the Spark interview questions cover the surrounding application programming interface (API).

How to answer it in an interview

The spoken structure: say the interval rule, then the replace write, then the check before publish, then the chunked backfill and the one risk you watch. In a design round, place this inside the larger picture of a senior data engineering system design answer.

Common mistakes

• "Retries make the task safe." (wrong) A retry repeats the write, and a repeated append duplicates rows. (right) "I make the write idempotent, then retries are harmless."
• "Merge removes all duplicates." (wrong) Duplicates inside the incoming batch are still inserted. (right) "I deduplicate the batch on the key, then merge."
• "Overwrite only replaces the partitions I write." (wrong) That holds only in dynamic mode, and the default is static. (right) "I set dynamic mode on purpose and check which partitions the batch touches."
• "Idempotent means the job does nothing the second time." (wrong) The job may do all the work again. (right) "The final state is the same as after one run."
• "Backfill and catchup are the same." (wrong) Catchup is automatic from the start date. (right) "A backfill is an explicit range I choose."

Follow-up questions

What is the difference between a backfill and catchup?

Catchup is automatic: the scheduler creates a run for every interval not yet run since the start date, when catchup is on. A backfill is a deliberate rerun of a range you choose, and it can be used even when catchup is off.

How do you make a data pipeline idempotent?

Key each run to its data interval and write with a partition overwrite or a merge on a unique key. Run the job twice on the same input and compare the final table; the two states must match.

Does a merge remove all duplicates?

No. It matches source rows to target rows on a key, but duplicate rows inside the source batch are still inserted, and several source rows matching one target row can make the merge fail. Deduplicate the batch first.

When does dynamic partition overwrite go wrong?

It goes wrong when a batch carries a row for a partition it should not touch, because the whole partition is replaced by that row. Validate the partitions in the batch before the write.

What if the job depends on yesterday's output?

Then the intervals are not independent. Set depends_on_past=True and backfill oldest first, so each run finds the previous interval's output already in place.

Where do data quality checks belong in a pipeline?

Between the staging write and the publish step, so a failed check stops the run before readers see the data. The data pipeline overview shows where those stages sit.

Sources

1. Dag Runs (Catchup, Backfill), Apache Airflow documentation — Apache Airflow, 26 Oct 2026
2. Dag Runs, Apache Airflow 2.2.4 documentation — Apache Airflow, 26 Oct 2026
3. Rerun Airflow Dags and tasks — Astronomer, 26 Oct 2026
4. Upsert into a Delta Lake table using merge — Databricks, 11 Sep 2026
5. Selectively overwrite data with Delta Lake — Microsoft Learn, 26 Oct 2026
6. Dynamic Partition Inserts, The Internals of Spark SQL — Jacek Laskowski, 26 Oct 2026
7. About Great Expectations OSS (0.18 documentation) — Great Expectations, 26 Oct 2026