Data & Decisions
Run a Data Backfill Without Double-Counting Previously Processed Records
A complete synthetic SeedLedger backfill fixes October 1–2 partitions and an October 26 input cutoff, reconciles revisions and duplicate inputs, and recovers a failed second partition. Twenty executed SQLite/Python checks show the scoped total changing from 9 to 11 and remaining unchanged on rerun, while an append control wrongly reaches 20 then 31. Source snapshots, transaction states, and consumer limits stay explicit.
A safe data backfill needs a fixed historical scope, reproducible inputs, and a write rule that recognizes records already processed. Reconcile the staged data before writing, make each publication boundary explicit, and rerun a failed partition against the same input version. After a successful task, repeat the fixed backfill and verify that business identities, values, and scoped totals stay unchanged.
This LandOffer.ai guide includes a complete synthetic partition reconciliation and an executed failure-and-retry record. The example uses a disposable SQLite database and retained source snapshots. It does not operate Airflow, BigQuery, Databricks, a production warehouse, or any scheduled job. The target metric is a local count of synthetic seed-packet checkout units, not real inventory or customer activity.

Fix the historical scope before rerunning the task
In the fictional SeedLedger report, a corrected checkout event and a late-arriving event need to be included in October 1–2 totals. The existing target already contains three events in that interval. Simply appending the corrected extract would mix old and new versions and duplicate records that were already processed.
Analyst Dina and engineer Leon write the backfill contract before running the local example. The dates below are UTC event dates, while the ingestion cutoff determines which source records belong to this snapshot. Those are different filters with different purposes.
| Contract item | Fixed choice for this example |
|---|---|
| Historical event scope | October 1 inclusive through October 3 exclusive, 2026 |
| Source availability | Ingested before October 26, 2026, at 00:00 UTC |
| Input identity | Retained source CSV with a recorded SHA-256 hash |
| Transformation | seedlog-reconcile-v1 |
| Record grain | One current row per source system and event ID |
| Revision rule | Higher source revision replaces the prior value; identical copies collapse |
| Publication boundary | One SQLite transaction per event-date partition |
| Consumer condition | Do not call the whole interval complete while a partition is failed |
Airflow’s current best-practices guidance recommends consistent rerun outcomes, specific input and output partitions, and upsert behavior rather than insertions that create duplicates. Its warning about reading the latest available input matters here: the same logical task can produce different output if the source changes between attempts.
The ingestion cutoff prevents a retry from silently including E06 after the original snapshot was selected. It does not move E04 into its ingestion-day partition: E04 still belongs to its October 2 event date even though it arrived later. State whether each boundary uses event time, processing time, or a source revision so another engineer can reproduce the same selection.
A partition date alone is not always an immutable input reference. A corrected file can replace an object at the same path, or a query can see newly arrived rows for yesterday. Preserve the input version or snapshot and transformation version needed to explain the rerun. A later correction should produce a separately identified backfill, not silently alter this run’s evidence.
Reconcile the staged extract against existing records
The supplied source snapshot contains eight rows. Six pass the event-date and ingestion-cutoff filters. Within those six are an old and a corrected revision of E01, plus two identical copies of E04. Resolving revisions and collapsing identical copies leaves four logical records for the backfill.
Here is the first completed asset: the source-to-target reconciliation. Every record in this reconciliation belongs to the synthetic seedlog source system, and the units represent the latest absolute value of each event, not an amount to add on every attempt.
| Event | Existing target | Selected staged record | Required result |
|---|---|---|---|
| E01, October 1 | Revision 1, 4 units | Revision 2, 5 units | Replace 4 with 5; do not keep both revisions in this current-state table |
| E02, October 1 | Revision 1, 2 units | Same revision and value | Keep one unchanged row |
| E03, October 2 | Revision 1, 3 units | Same revision and value | Keep one unchanged row |
| E04, October 2 | Absent | Revision 1, 1 unit, supplied twice identically | Insert one logical row |
| E05, October 3 | Revision 1, 7 units | Outside the selected event-date range | Leave the target row unchanged |
| E06, October 2 | Absent | Ingested October 28, after the cutoff | Exclude from this snapshot’s backfill |
The scoped target starts at 4 + 2 + 3 = 9 units. The reconciled result is 5 + 2 + 3 + 1 = 11. That increase of two has an explanation: one additional unit from E01’s correction and one from late E04. It is not the sum of every row encountered in the extract.
The executed append control starts with the three old in-scope rows and appends the already deduplicated four-row stage. Its total becomes 20: the original nine plus the staged eleven. Appending that same stage again raises it to 31. The append control still double-counts existing records even after the incoming batch is deduplicated.
Databricks’ merge guidance separately notes that incoming data needs deduplication within itself and describes ambiguity when multiple source rows match a target update. Those are documented Delta behaviors, not evidence that this SQLite fixture tested a Delta runtime. Inspect the semantics of the engine and version you actually use.
A matching hash establishes which input file was used, not that its contents are correct or complete. Reconcile the chosen source version against the business expectation before treating reproducibility as acceptance. A perfectly repeatable pipeline can consistently reproduce the wrong total if its grain, time window, or source revision rule is wrong.
Do not select an arbitrary winner when two rows have the same event ID and source revision but different values. The fixture adds a conflicting E03 candidate in a negative test and rejects staging before any target mutation. A producer-defined revision order can resolve older versus newer data; it cannot make contradictory equal-version payloads trustworthy.
Use an identity-aware write that replaces values
The local target has a composite primary key on source and event ID. Its upsert inserts a new identity or replaces the date, revision, and absolute unit value only when the incoming revision is higher. Equal matching records are no-ops. A same-revision conflict with the existing target is rejected rather than silently accepted.
SQLite’s UPSERT documentation explains that conflict handling responds to uniqueness constraints and that a condition can make an update a no-op. The exact syntax and guarantees are engine-specific. The retained fixture includes the complete SQL and schema; the article does not present it as portable warehouse syntax.
INSERT INTO target VALUES (?, ?, ?, ?, ?)
ON CONFLICT(source, event_id) DO UPDATE SET
event_date = excluded.event_date,
revision = excluded.revision,
units = excluded.units
WHERE excluded.revision > target.revision;
The replacement expression matters as much as the conflict target. An upsert that adds incoming units to the existing units can still double-count a replay. In this current-state model, the selected source revision defines the whole event value, so replacing it matches the contract. An append-only event history or compensating-delta design would need a different grain and reconciliation rule.
The example assumes an event’s date is stable across its revisions. Moving an event to another partition, deleting it, or receiving a tombstone requires additional handling; this fixture does not solve those cases. Define whether absence from a partial extract means deletion before using a broad replace or delete-not-in-source operation.
Rerun a failed partition and inspect the actual state
The second completed asset is the local partition journal and failure record. Publishing October 1 updates E01 and commits that partition’s completion status in the same transaction. Publishing October 2 then performs its candidate writes but deliberately raises an exception before commit. The transaction rolls back; a separate journal update records failure.
| Stage | October 1 target | October 2 target | Scoped total | Recorded state |
|---|---|---|---|---|
| Before backfill | E01 4 + E02 2 = 6 | E03 3 = 3 | 9 | Both pending |
| First partition commits | E01 5 + E02 2 = 7 | E03 3 = 3 | 10 | October 1 complete; October 2 pending |
| Second partition fails before commit | 7 | Still 3; E04 absent | 10 | October 2 failed; interval incomplete |
| Retry October 2 with the same stage | 7 | E03 3 + E04 1 = 4 | 11 | Both partitions complete |
| Rerun both partitions | Same rows and values | Same rows and values | Still 11 | Business-row fingerprint unchanged |
The retained snapshots show rollback after an exception injected between the second partition’s candidate writes and commit. The test checks that E04 remains absent after failure, then appears exactly once after the retry. It also checks the full target-row reconciliation, the unchanged out-of-scope E05 row, the excluded post-cutoff E06 record, and protection against downgrading E01 with an older revision.
Twenty executed Python and SQLite checks retain the source CSV, validated stage, SQL, per-stage target and journal snapshots, and input hash. Repeating both partitions preserves the target’s business-row fingerprint. Attempt logs or status records can legitimately change between runs; the idempotency claim concerns the intended data result, not byte-identical operational history.
The interval is visibly partial between the first commit and successful recovery of the second partition. A real publication design must define what consumers may read during that partial interval. This example records the state but does not implement a downstream view switch or a warehouse-wide transaction. If consumers require an all-or-nothing interval, stage and validate an appropriate publishable version before switching them to it.

Separate scheduler decisions from output correctness
An orchestrator can decide whether a historical task should run again, but that decision does not make the task’s writes safe. Airflow’s backfill documentation distinguishes reprocessing policies for existing runs and separately controls backfill concurrency. A scheduled run’s logical date or success state is not a substitute for the data contract above.
For a real backfill, inspect overlapping live jobs and other historical runs before writing. Agree on ownership, ordering, and any conflict protection needed when another writer can change the same records. Only serial local partition publications were executed; distributed concurrency and orchestrator behavior remain untested.
Plan recovery before a real target write. Retain the prior approved target version or another authorized restore point, and distinguish data restoration from reversal of notifications or external actions already triggered by consumers. The disposable fixture’s rollback covers only its local transaction. It does not demonstrate a warehouse restore, reverse a report sent to someone, or authorize either action.
If a partition-overwrite strategy is chosen instead, verify that the staged source represents the entire intended partition. Replacing a complete partition with an incomplete correction extract can remove valid records even while avoiding duplicates. The upsert case here makes no claim that partition replacement is inherently safer or worse; the source contract determines which write fits.
Keep downstream work in the plan. A corrected current-state table may require rebuilding an aggregate, invalidating a cache, or refreshing a report. Do not append the corrected total to an old aggregate as though it were entirely new activity. Reconcile those consumers at their own grain and publication boundary.
The fictional closing record says: “The fixed October 1–2 snapshot reconciles to four events and eleven units. The failed partition retry and repeated full backfill preserve those rows; October 3 remains unchanged. Keep any real warehouse run pending until source-version retention, concurrent-writer controls, consumer publication, and recovery permissions are verified.” This is an authored review, not an executed production change.
For your next backfill, retain one input snapshot, one expected partition reconciliation, and one deliberate failure-and-rerun record. Count identities and values after the second run before calling the work repeatable. More practical data guides are available from LandOffer.ai.


