# Backfill a data pipeline: a month of runs, a corrected day and parallel runs

A backfill runs a pipeline again for a range of past dates, 1 run per interval, each given its own date. It is safe when each run is idempotent: it loads only the day its date names and replaces that day in the table. Correcting 1 day is the same thing for a range of 1. Runs that write the same table at once can fail with a write conflict, so run a backfill into 1 Delta table one run at a time, or partition the table by the day so each run writes its own partition.

- Page: https://tecyfy.com/data-engineering/course/orchestration/backfill
- Course: Data engineering course (https://tecyfy.com/data-engineering/course), Module 10: Orchestration, Backfills and corrections
- Parts of this module: Run dates, safe reruns and retries (https://tecyfy.com/data-engineering/course/orchestration); Lakeflow Jobs, a daily job on Databricks (https://tecyfy.com/data-engineering/course/orchestration/lakeflow-jobs); Backfills and corrections (https://tecyfy.com/data-engineering/course/orchestration/backfill); Apache Airflow, the same job as a DAG (https://tecyfy.com/data-engineering/course/orchestration/airflow); Build it: backfill a month, rerun the failed day (https://tecyfy.com/data-engineering/course/orchestration/build-it)

At the end of this part you can:

- Backfill a month of a daily load, 1 run per day, and check the result
- Correct 1 past day by running it again, and read the change in the table's history
- Run a backfill in Lakeflow Jobs or Airflow with the run date passed to each run, and choose how many runs go at once

[Part 2](/data-engineering/course/orchestration/lakeflow-jobs) put the daily load in a Lakeflow job. The job only runs from now on; the shop wants its September in `orders_daily` too. Running a pipeline for past dates is a backfill, and this part does one locally, corrects a day, and then runs it in Lakeflow Jobs and Airflow. It uses part 1's scripts in a fresh folder. To rebuild from nothing, run `python make_days.py`.

## A month, 1 run per day

An orchestrator splits a backfill's range into the job's own interval and starts 1 run for each, with its date. Locally, a loop does the same, in 1 Spark session so it does not start Spark 30 times. Save this as `backfill.py`:

```python
"""Run the daily load and its check for every run date from the first to the last, in 1 Spark session,
as an orchestrator's backfill would run the job once per day."""
import sys
from datetime import date, timedelta
from delta_session import delta_spark
from load_day import load_day
from check_day import check_day

first, last = date.fromisoformat(sys.argv[1]), date.fromisoformat(sys.argv[2])
spark = delta_spark("backfill")
failed = []
run = first
while run <= last:
    load_day(spark, str(run))
    ok, message = check_day(spark, str(run))
    if not ok:
        failed.append(str(run))
        print(message)
    run += timedelta(days=1)
days = spark.table("orders_daily").select("order_date").distinct().count()
print(f"ran {(last - first).days + 1} runs, {len(failed)} failed; orders_daily has {days} days, {spark.table('orders_daily').count()} orders")
```

The runs for September's 30 days are dated 2 September to 1 October, because each loads the day before its date:

```bash
python backfill.py 2026-09-02 2026-10-01
```

```text
ran 30 runs, 0 failed; orders_daily has 30 days, 130 orders
```

30 days and 130 orders, the 2001 to 2130 that `make_days.py` wrote. Because each run replaces only its own day, the same command can run again, or stop halfway and start over, and the table ends the same.

## Correct 1 day

A correction is a backfill of 1 day. Say the till's file for 15 September was missing an order, sent late:

```bash
echo "2131,C02,19.99,2026-09-15T20:30:00" >> landing/orders/2026-09-15.csv
python load_day.py 2026-09-16
python check_day.py 2026-09-16
python history.py
```

```text
run 2026-09-16 loaded 2026-09-15: 4 rows, orders_daily has 131
ok 2026-09-15: 4 rows for the file's 4 orders, 0 missing
+---------+-------------------------+------------+
|operation|predicate                |rows_written|
+---------+-------------------------+------------+
|WRITE    |order_date = '2026-09-15'|4           |
|WRITE    |order_date = '2026-09-30'|6           |
|WRITE    |order_date = '2026-09-29'|5           |
+---------+-------------------------+------------+
```

The run for 16 September replaced 15 September's 3 rows with 4, and the history names the day it replaced. The other 29 days were not touched. A load that took its day from the clock could not do this: run today, it would load yesterday.

## Runs at the same time

30 runs one after another take a while, and orchestrators can run several at once. Runs that write the same Delta table at once can conflict, though. Save this as `parallel.py`, which starts 4 runs together:

```python
"""Run 4 days' loads at the same time into orders_daily, as a backfill with 4 concurrent runs would."""
import re
from concurrent.futures import ThreadPoolExecutor
from delta_session import delta_spark
from load_day import load_day

spark = delta_spark("parallel")


def run(run_date: str) -> str:
    try:
        return load_day(spark, run_date)
    except Exception as error:
        # The error class in square brackets is the same in classic Spark and Spark Connect.
        found = re.search(r"\[([A-Z_.]+)\]", str(error))
        return f"run {run_date} failed: {found.group(1) if found else error}"


with ThreadPoolExecutor(4) as pool:
    for result in pool.map(run, ["2026-09-02", "2026-09-03", "2026-09-04", "2026-09-05"]):
        print(result)
```

`python parallel.py` printed:

```text
run 2026-09-02 failed: DELTA_CONCURRENT_APPEND.WITHOUT_HINT
run 2026-09-03 loaded 2026-09-02: 5 rows, orders_daily has 131
run 2026-09-04 failed: DELTA_CONCURRENT_APPEND.WITHOUT_HINT
run 2026-09-05 failed: DELTA_CONCURRENT_APPEND.WITHOUT_HINT
```

`DELTA_CONCURRENT_APPEND` is the error class of Delta's `ConcurrentAppendException`, a write conflict. Which run wins changes from try to try; in every try, 1 of the 4 succeeded. Each `replaceWhere` reads the table to find the rows it replaces. `orders_daily` is not partitioned, so Delta counts each run as having read the whole table, and rows another run committed in the meantime as a change to what it read. It fails those commits rather than risk a wrong result. The failed runs changed nothing, because a Delta write commits whole or not at all, and running them again loads them.

2 ways out. Run the backfill 1 run at a time, which for a table this small costs little. Or partition the table by `order_date`, with `.partitionBy("order_date")` on its first write: each run then replaces only its own partition, and the same 4 runs into a partitioned copy of the table all succeeded, 3 times in 3. Partitioning by day makes many small files when days are small, the cost [module 4](/data-engineering/course/delta-lake-and-iceberg/optimize-and-vacuum) dealt with.

## A backfill in Lakeflow Jobs

Open the job, click the arrow next to **Run now** and choose **Run backfill**. Set:

- **Date and time range**: 2 September to 1 October, the run dates, not the days loaded.
- **Every**: 1 day, which Databricks chooses from the job's schedule. The dialog shows how many runs the range makes; check it says 30. It will not start a range of more than 100 runs, so split a longer one into several backfills.
- **Job parameters**: override `run_date` with `{{backfill.iso_date}}`, the date at the start of each run's interval.

Backfill runs show in the job's runs with Backfill in their name. They go in parallel up to the job's maximum concurrent runs, which part 2 left at 1, the safe choice for this unpartitioned table. A backfill always runs every task of the job. If the job had a pipeline task, the pipeline would run as it is, with no parameters, and the runs would go one after another.

## A backfill in Airflow

Airflow's backfill creates a run for each date of the schedule in a range, and gives each its date as `{{ ds }}`:

```bash
airflow backfill create --dag-id shop_daily --from-date 2026-09-02 --to-date 2026-10-02 --max-active-runs 1
```

The dates are read as midnight, so the runs at 02:00 fall from 2 September to 1 October, and the end date is the day after the last run. `--reprocess-behavior` decides what happens to dates that already have a run: `none` creates no new run for them, `failed` creates one where the run failed, and `completed` creates one for every finished run, failed or not. A date whose run is still queued or running gets no new run either way. `--max-active-runs` limits how many of the backfill's runs go at once, separately from the DAG's own `max_active_runs`, and `--dry-run` prints the dates without creating anything. In the UI the same backfill is under **Trigger**, **Backfill**. [Part 4](/data-engineering/course/orchestration/airflow) writes the DAG that runs this, and part 5 runs this backfill.

## Check yourself

1. A Lakeflow backfill covers 2 September to 1 October with an interval of 1 day, and overrides run_date with {{backfill.iso_date}}. Which day does its first run load?

   A. 2 September
   B. 31 August
   C. 1 October
   D. 1 September

2. 4 runs of the load write orders_daily at once. 1 succeeds and 3 fail with ConcurrentAppendException. What is in the table for the 3 failed days?

   A. Half of each day's new rows
   B. What was there before; a failed Delta write changes nothing
   C. Nothing, because replaceWhere deleted the old rows before failing
   D. The new rows, because the error is only a warning

### Answers

1. D. {{backfill.iso_date}} is the start of each run's interval, so the first run gets 2026-09-02 as its run date. The load always takes the day before its run date, so it loads 1 September, as the scheduled run on 2 September would have.
2. B. Each write is 1 Delta transaction, which commits whole or not at all. The 3 that lost the conflict never committed, so their days still hold the rows from before. Running them again, 1 at a time, loads them.

## Questions

### What is a backfill in data engineering?

Running a pipeline again for a range of past dates, to load data from before the pipeline existed or to fix days that were wrong or missing. An orchestrator splits the range into the pipeline's own intervals, such as 1 run per day, and gives each run its date, so each loads the same data a scheduled run for that date would have.

### Can backfill runs run in parallel?

Yes, when they do not get in each other's way. Lakeflow Jobs runs as many backfill runs at once as the job's maximum concurrent runs, and Airflow's backfill as many as its --max-active-runs. Runs that write the same Delta table at once can fail with a write conflict such as ConcurrentAppendException; partitioning the table by the run's day, so each run replaces its own partition, lets them run together.

## Sources

- [Backfill jobs](https://docs.databricks.com/aws/en/jobs/backfill-jobs)
- [What is a dynamic value reference?](https://docs.databricks.com/aws/en/jobs/dynamic-value-references)
- [Backfill](https://airflow.apache.org/docs/apache-airflow/stable/core-concepts/backfill.html)
- [Isolation levels and write conflicts](https://docs.databricks.com/aws/en/optimizations/isolation-level)
- [Selectively overwrite data with Delta Lake](https://docs.databricks.com/aws/en/delta/selective-overwrite)