Backfills Reprocessing and Late-Arriving Data

Purpose: repair history without corrupting it

Backfills, reprocessing, and late-arriving data are the tools data engineers use when a batch pipeline must change what it believes about the past. A backfill populates or repopulates data for a historical time range, usually because a new table, new column, bug fix, or upstream source became available after the original processing window. Reprocessing reruns transformation logic over data that has already been processed. Late-arriving data is data whose event time belongs to an older period even though the record reaches the pipeline after that period has closed. The outcome of this lesson is the ability to design a batch pipeline that can safely recompute selected history, absorb delayed events, and prove which partitions changed.

In ingestion and batch pipelines, this matters because most analytical facts are interpreted by event time: the order was placed on Monday, the device reading was measured at 10:01, the subscription renewed in March. Processing time only says when the pipeline noticed the record. If the pipeline treats processing time as truth, reports drift, month-end numbers change without explanation, and retries can duplicate facts. Good historical repair is not just rerunning a job. It is a controlled write pattern with deterministic inputs, stable keys, explicit time windows, and validation before replacement.

Mechanism: event time, partitions, and replacement

The core mechanism is to separate the raw, append-only record of what arrived from the derived tables that are optimized for analysis. Raw landing tables should retain the original payload, ingestion timestamp, source offset or filename, and a stable record identifier when available. Derived fact tables are commonly partitioned by event date or another business period. When a backfill runs, it reads raw or normalized source data for a bounded event-time interval, applies the current transformation logic, writes results to a staging location, validates them, then atomically swaps or overwrites only the affected partitions.

A watermark is the pipeline’s statement about completeness. If the watermark for event time is 12:00 with a 30 minute lateness allowance, events at or after 11:30 are still eligible for normal processing; older events need a correction path. Watermarks are not magic clocks. They are operational thresholds chosen from source behavior, business tolerance, and compute cost. A payment provider that resends settlements after bank holidays needs a different lateness policy from a web clickstream where nearly all events arrive within minutes.

Idempotence is the second internal requirement. Running the same backfill twice over the same input and code should produce the same target state, not double the measures. Pipelines achieve this with stable natural keys or generated hashes, deduplication inside the selected time window, and replace-style writes such as partition overwrite, merge by primary key, or delete-then-insert in one transaction. Append-only writes are acceptable for raw ingestion, but they are dangerous for reprocessed facts unless the table has a later compaction or merge step.

API and configuration anatomy

A practical backfill interface has a small set of explicit parameters: start event date, end event date, target dataset, transform version, source snapshot or source filter, write mode, and validation mode. The date range controls blast radius. The transform version records which code produced the output. The source snapshot makes the run reproducible when raw data can change. The write mode distinguishes dry run, staging only, partition overwrite, merge, and rollback. Validation mode defines whether the job blocks on row-count, key-uniqueness, nullability, balance checks, or comparison against prior output.

The job should also emit a run manifest. A manifest is metadata about the repair: run id, requested window, actual partitions written, input files or offsets, code revision, row counts, checksums, and operator. This is more useful than a log line because downstream teams can query it when a dashboard number changes. In warehouse SQL engines, the implementation might be an INSERT OVERWRITE per partition, a MERGE INTO keyed by event_id, or creation of a replacement table followed by a metadata swap. In orchestration systems, the same idea appears as parameterized tasks with backfill ranges and dependency clearing.

Example 1: classifying late records with a watermark

The first example shows how a pipeline decides whether a record belongs in the normal micro-batch or in a late-data correction path. The cutoff is watermark minus allowed lateness. Records before that cutoff are not discarded; they are routed differently because they may require reopening an older partition.

from datetime import datetime, timedelta, timezone

watermark = datetime(2026, 2, 1, 12, 0, tzinfo=timezone.utc)
allowed_lateness = timedelta(minutes=30)
cutoff = watermark - allowed_lateness

events = [
    {"id": "a", "event_time": datetime(2026, 2, 1, 11, 45, tzinfo=timezone.utc)},
    {"id": "b", "event_time": datetime(2026, 2, 1, 11, 20, tzinfo=timezone.utc)},
    {"id": "c", "event_time": datetime(2026, 2, 1, 12, 2, tzinfo=timezone.utc)},
]

for event in events:
    status = "on-time" if event["event_time"] >= cutoff else "late"
    print(event["id"], status)

The deterministic output is a on-time, b late, and c on-time. Event b occurred before 11:30, so the current processing window should not silently append it to a closed aggregate. A production version would write b to a late-events table or enqueue a partition repair for 2026-02-01.

Example 2: reprocessing a partition idempotently

The next example models a partition aggregate. Event e2 appears twice, which is common when sources retry delivery. The aggregation tracks seen event identifiers before summing amounts. The important design choice is that the function returns the full replacement result for one event-date partition.

from collections import defaultdict

raw_events = [
    {"event_id": "e1", "event_date": "2026-03-01", "customer": "A", "amount": 25},
    {"event_id": "e2", "event_date": "2026-03-01", "customer": "A", "amount": 10},
    {"event_id": "e2", "event_date": "2026-03-01", "customer": "A", "amount": 10},
    {"event_id": "e3", "event_date": "2026-03-02", "customer": "B", "amount": 7},
]

def aggregate_partition(events, event_date):
    seen = set()
    totals = defaultdict(int)
    for event in events:
        if event["event_date"] != event_date or event["event_id"] in seen:
            continue
        seen.add(event["event_id"])
        totals[event["customer"]] += event["amount"]
    return dict(sorted(totals.items()))

print(aggregate_partition(raw_events, "2026-03-01"))
print(aggregate_partition(raw_events, "2026-03-02"))

The output is {'A': 35} for 2026-03-01 and {'B': 7} for 2026-03-02. Repeating the function gives the same result because duplicates are removed within the partition. In a warehouse, this would map to writing a staging partition and then replacing the target partition, not inserting another copy of customer A’s total.

Example 3: planning a targeted backfill

A backfill should usually repair the smallest range that explains the defect. This example compares the target table’s existing partitions to the requested historical window and emits overwrite statements only for missing partitions.

from datetime import date, timedelta

existing_partitions = {date(2026, 4, 1), date(2026, 4, 3)}
start = date(2026, 4, 1)
end = date(2026, 4, 4)

def daterange(start_day, end_day):
    day = start_day
    while day <= end_day:
        yield day
        day += timedelta(days=1)

missing = [day for day in daterange(start, end) if day not in existing_partitions]
for day in missing:
    partition = day.isoformat()
    print(f"INSERT OVERWRITE fact_orders PARTITION (event_date='{partition}') SELECT ... WHERE event_date='{partition}';")

The output contains commands for 2026-04-02 and 2026-04-04 only. In a real system the SELECT ... would be a compiled transformation that reads raw orders for exactly one event date. Planning at partition granularity keeps runtime predictable and makes rollback simpler because each replacement maps to a named slice of data.

Design choices and trade-offs

The first trade-off is overwrite versus merge. Partition overwrite is simple and fast when partitions are independent and moderately sized. It can be wasteful when a single late event changes one row inside a very large daily partition. Merge is narrower and preserves unaffected rows, but it depends on reliable keys and can be slower or harder to reason about under concurrent writes. Delete-then-insert is understandable, but it must be transactional or readers may see an empty partition.

The second trade-off is how far to keep windows open. A longer lateness allowance reduces correction jobs but delays final numbers and increases the amount of data scanned in every routine batch. A shorter allowance produces faster reporting but more late-event repairs. Many teams use tiered handling: normal windows remain open for a short period, automatic repair handles the last few days, and older periods require an explicit approved backfill.

The third trade-off is source-of-truth selection. Reprocessing from raw immutable data is usually preferred because it replays the facts through current logic. Reprocessing from an already transformed table is cheaper, but it may preserve old bugs or missing columns. When raw data is unavailable, the limitation should be recorded in the manifest so consumers know that the backfill corrected only part of the lineage.

Failure modes and troubleshooting

A common symptom is totals doubling after a rerun. The cause is usually append-style writes into a derived table. Diagnose it by comparing the count of distinct business keys with total rows in the affected partition and by checking the job’s write mode. Correct it by rebuilding the partition from raw input with deduplication and replacing the partition atomically.

Another symptom is a dashboard total changing days after publication with no matching deployment. The cause may be late data entering a closed period through a merge job. Diagnose by querying records whose ingestion timestamp is much later than event time and joining them to the run manifest. Correct it by publishing a late-data policy, tagging correction runs, and ensuring reports can distinguish preliminary and finalized periods.

A third symptom is a backfill that succeeds for old dates but fails for recent dates. The cause is often schema drift: historical raw files lack a field that current code assumes, or newer files contain a value the old parser cannot handle. Diagnose by sampling raw payloads from the failing partition and comparing schema versions. Correct it with explicit defaults, schema-version branches, or a normalization layer that produces a stable intermediate format before facts are built.

Reliability, performance, and access implications

Backfills can be expensive because they deliberately scan history. Bound every run, cap concurrency, and avoid launching one task per tiny partition without considering scheduler and warehouse overhead. Use staging tables so validation happens before users see changes. Store previous partition locations or table snapshots when the storage system supports them; rollback is then a metadata operation instead of another long computation.

Access control matters because backfills can rewrite trusted historical data. Limit production overwrite permissions to service accounts and require parameters to be visible in orchestration logs. Sensitive raw data may have retention or masking rules, so a backfill should not bypass the same transformations used by normal processing. Reliability also includes communication: downstream models, metrics, and extracts may need to be refreshed after the repaired partitions are published.

Hands-on lab: repair two late order partitions

Prerequisites: a SQL warehouse or local analytical database, a raw orders table with event_id, event_time, ingested_at, customer_id, and amount, and permission to create a staging table. Step 1: choose a bounded event-date range, such as 2026-05-01 through 2026-05-02. Step 2: create a staging aggregate grouped by event date and customer, deduplicating by event_id. Step 3: run validation queries for row count, distinct key count, null customer ids, and total amount compared with the existing fact table. Step 4: in a transaction or atomic table operation, replace only those two event-date partitions. Step 5: insert a manifest row containing the range, code revision, validation results, and partition names.

Verification should include three checks. First, rerun the same repair in dry-run mode and confirm the staged checksums match the published partitions. Second, query raw events where ingested_at is after the original partition close time and confirm their amounts are included exactly once. Third, compare unaffected partitions before and after the run; their checksums should not change. Cleanup depends on the platform: drop temporary staging tables, expire scratch files, and retain the manifest and validation results. If validation fails after publication, rollback by restoring the prior table snapshot or by overwriting the affected partitions from the saved pre-run copy.

Assessment exercises

1. A daily revenue table is partitioned by processing date, but business users ask for revenue by order date. Explain how late orders will appear and propose a safer partitioning and repair strategy.
2. A source sends duplicate records with the same event_id and different ingestion timestamps. Design the deduplication rule for a backfill and explain what should happen if payload values conflict.
3. You need to correct a tax calculation for the previous quarter. Choose overwrite, merge, or delete-then-insert, and justify the choice based on keys, partition size, and rollback needs.
4. Write the validation checks you would require before publishing a backfill that changes executive revenue dashboards.
5. A backfill is running too slowly. Identify which measurements would tell you whether the bottleneck is source scanning, shuffle or grouping, target writes, or scheduler overhead.

Summary

Backfills repair historical gaps, reprocessing applies corrected logic to already processed periods, and late-arriving data forces the pipeline to reconcile event time with arrival time. The reliable pattern is append-only raw storage, bounded event-time repair windows, deterministic transformations, idempotent writes, validation in staging, and a manifest that explains exactly what changed. With those pieces in place, historical correction becomes an engineered operation instead of an uncontrolled rerun.