Backfills and data quality checks that keep you honest

Backfills are normal runs

A backfill is any run that recomputes data for a past range. Many teams treat it as an emergency, so the backfill ends up as a one-off script that only the person who wrote it understands. That script is the first thing to break when someone needs it again a quarter later.

Make backfill a first-class operation. The job takes a start date and an end date as parameters, and the same code path serves the daily run and the historical run. If a daily run and a backfill can produce different results for the same day, one of them has a bug, and you want to find out before a stakeholder does.

One unit of work per partition

Split the range into one unit per partition, usually one day. Each unit reads its inputs, writes its output, and can be retried or rerun alone. This matters most when a run fails on day 37 of 400. You want to restart day 37, not redo the first 36.

Rerun safety comes from overwriting, not appending. Write the partition to a temporary location, then replace the existing partition in one step. Running the same date twice then gives the same answer, and that property is what makes retries safe.

Throttle the work. A backfill that runs 400 days at once will load the source database and the warehouse that serves dashboards. Cap concurrency at a level the source can tolerate, and pause between batches if the source has its own rate limits. Start slow and measure. The loop below shows the basic shape:

from datetime import date, timedelta
from time import sleep

THROTTLE_SECONDS = 5

def days_between(start: date, end: date):
    current = start
    while current <= end:
        yield current
        current += timedelta(days=1)

for day in days_between(date(2026, 1, 1), date(2026, 3, 31)):
    run_partition(day)  # overwrites that day's output, safe to repeat
    sleep(THROTTLE_SECONDS)

In a real system the scheduler would run these units as tasks, but the unit itself should look exactly like this.

Model the backfill as a DAG

Express the work as a directed acyclic graph where each partition is its own task. Tasks for different days have no dependencies on each other, so the scheduler can run them in parallel up to your concurrency limit. A downstream task that needs a whole month waits for every day in that month.

Avoid dependencies between days unless the logic demands them. A running balance needs yesterday's result. A daily count does not. Every link you add between days turns a parallel backfill into a sequential one, and it lets a single failure block more work than it should.

Four checks that catch most problems

Most data quality problems fall into four kinds. Row counts tell you whether a partition is roughly the size it should be, measured against the source or a recent baseline. Null rates tell you whether a required column has started to empty out. Uniqueness checks confirm that the business key appears once. Freshness checks confirm that the newest record is recent enough for the partition to be useful.

Write each check as a SQL query that returns offending rows, and treat any returned row as a failure. This convention keeps the checks readable and makes failures easy to inspect. Keep thresholds, such as the acceptable null rate, in configuration so that changing one does not require a code change. A check for duplicate orders looks like this:

-- Any row returned means an order_id was loaded more than once.
SELECT order_id, count(*) AS copies
FROM staging.orders
WHERE order_date = DATE '2026-03-14'
GROUP BY order_id
HAVING count(*) > 1
LIMIT 10;

Write, audit, publish

Write the new partition to a staging location that consumers do not read. Run every check against that staging copy. If all checks pass, publish by moving the partition into the table consumers query. If any check fails, consumers keep yesterday's good data, and the failure lands in an alert that names the partition.

The cost is extra storage and a copy step. Publishing also has to be atomic, or a reader can catch a partition half replaced. Many warehouses can swap a partition in one statement. Where they cannot, write each partition to its own directory and update a pointer that readers follow. Either way, the gate between staging and published is the part worth paying for.

Late and duplicate events from CDC

If your source is a change data capture stream, expect two things a batch extract does not produce. The same row can arrive more than once, because the stream redelivers after a retry. Updates can also arrive after the partition they belong to has been published. Uniqueness checks catch the duplicates. Freshness checks do not catch late updates, so count how many rows land in a partition after it was published, and keep a reprocessing window, such as the last seven days, that the backfill can rerun cleanly.