Re-run a backfill without doubling the total

A backfill revisits a historical slice that may already be present in the reporting table. The scheduler can choose the dates, retry a failed task and run the same task again. It cannot make an append-only warehouse write safe by itself. That decision belongs to the write contract.

The target in this note has one row per order_date, channel. Its source is the current order fact: one winning row per order after validation, duplicate removal and version selection. The aggregate stores an order count and an integer amount in cents. That grain is the boundary that must remain true when a historical slice is recomputed.

This is the same synthetic retail-orders project used by the flagship pipeline. The flagship explains event validation and current-state construction. The replay-safety note owns exact event identity. The late-correction note explains why an order can move between reporting days. This note owns the persisted aggregate write boundary.

The source facts include a duplicate event, invalid rows, a repeated follow-up event, a late correction, a stale event and a new order. The corrected current facts put the web group for 2026-01-02 at 2 orders and 19000 cents. The amount is synthetic teaching data, not accounting revenue.

The operational failure is easy to miss. A job recomputes a correct slice, then inserts that slice beside the rows already in daily_orders. A report that sums rows at the target grain now sees two contributions for the same date and channel. The SQL ran successfully, the task may be marked successful and the total is still wrong.

The dbt incremental model guidance makes the same boundary visible through a unique key and a deliberate incremental strategy. Airflow's best-practice guidance treats repeatable tasks as a requirement because retries and reruns happen. Its backfill documentation describes the scheduling side. None of those scheduler choices replace a target write that is safe to repeat.

The wrong version is an append of the recomputed aggregate. The query groups current facts correctly, but it does not remove the old rows or match them to a stable target key. That distinction matters: a correct SELECT can still produce an incorrect table.

The teaching sketch in sql/append.sql takes a closed date range and inserts the recomputed rows into daily_orders. It is deliberately wrong for a rerunnable target. The application does not run this write. The existing retail-orders pipeline exposes daily_channel_mart as a view over current facts, so daily_orders is a teaching target introduced here to make the warehouse boundary inspectable.

Start with the current target, then run the same closed slice once. The 2026-01-02 web group contributes another 2 orders and another 19000 cents, so the downstream group reads 4 orders and 38000 cents. Run the identical slice again and the same mistake contributes once more: the grouped result reads 6 orders and 57000 cents. Nothing about the source facts changed between these runs. Only the target write allowed duplicate aggregate rows.

The failure is not that append is always forbidden. Append is a reasonable contract for an immutable event ledger or a table whose key includes a unique batch identity. It is wrong for a persisted daily aggregate when the downstream grain is only date and channel and a rerun is expected to replace the same slice. If the table has an enforced unique key, the same SQL may fail instead of doubling the sum. That is a useful failure, but it is not the replacement behaviour the backfill needs.

The trap is broader than one product. An incremental model without a meaningful unique_key can append a recomputed row. An unconstrained INSERT can do the same. A MERGE with a non-unique source can error or update more than the author intended. A scheduler retry is only as safe as the storage contract it calls.

-- Teaching sketch: append the recomputed daily slice to the target.-- This is intentionally wrong for a rerunnable aggregate because it leaves-- existing rows in place and lets downstream sums count them again.INSERT INTO daily_orders (order_date, channel, order_count, amount_cents)SELECT order_date,       channel,       CAST(COUNT(*) AS BIGINT) AS order_count,       CAST(SUM(amount_cents) AS BIGINT) AS amount_centsFROM orders_factWHERE order_date BETWEEN CAST($1 AS DATE) AND CAST($2 AS DATE)GROUP BY order_date, channel;

The right version makes the target key and replacement boundary explicit. It stages the recomputed slice, checks that the grouped result has one row per (order_date, channel), deletes only the requested date range and inserts the staged rows in one transaction. The sql/replace_slice.sql file is a teaching sketch. The application does not run this write.

The date predicate is closed at both ends. That is a small detail with a large effect: a backfill for 2026-01-01 through 2026-01-02 must not remove rows outside that slice or leave an old row inside the slice because the end condition was open. The staged query uses the same predicate as the delete so the rows removed and rebuilt describe the same boundary.

The transaction is the publication point. Before commit, a failed statement can roll the replacement back so readers do not observe a target with the old rows deleted and the new rows absent. The transaction does not make a bad slice correct. It only makes the chosen replacement atomic. Validation still needs to check the source scope, row grain, null policy, non-negative amounts and reconciliation to the current facts.

Run the same replacement twice. The first run removes the requested slice and writes the current groups. The second run removes those same groups and writes equivalent rows. The 2026-01-02 web group remains at 2 orders and 19000 cents after both runs. The physical write happened again, but the committed result did not accumulate another contribution.

The result block lets the reader choose only the reviewed append or replace-slice pattern, one of two closed date ranges and one or two runs. It returns grouped rows and totals from the note's closed result set. There is no free-form SQL input. The comparison highlights amount_cents, because the inflated amount is the clearest symptom while the target key explains the cause.

-- Teaching sketch: replace the requested daily slice atomically.-- The staged rows, delete and insert share the same closed date boundary.BEGIN TRANSACTION;CREATE OR REPLACE TEMP TABLE staged_daily ASSELECT order_date,       channel,       CAST(COUNT(*) AS BIGINT) AS order_count,       CAST(SUM(amount_cents) AS BIGINT) AS amount_centsFROM orders_factWHERE order_date BETWEEN CAST($1 AS DATE) AND CAST($2 AS DATE)GROUP BY order_date, channel;DELETE FROM daily_ordersWHERE order_date BETWEEN CAST($1 AS DATE) AND CAST($2 AS DATE);INSERT INTO daily_orders (order_date, channel, order_count, amount_cents)SELECT order_date, channel, order_count, amount_centsFROM staged_daily;COMMIT;

The independent oracle reads the two fixture arrays directly. It rejects malformed rows for this calculation, chooses one current row per order by source version then updated timestamp then event id and aggregates only those winners. It then starts from the current target, applies append or replace-slice for one or two runs and checks the current groups, every simulated target group, target row counts and overall totals shown by the result block. DuckDB is useful for executing the teaching sketches, but it is not the correctness oracle for the note.

Replace-by-slice is easy to reason about because the replacement boundary matches the reporting boundary. It also handles disappearance: if recomputation shows that a group no longer has rows, deleting the slice removes the obsolete target row before the new groups are inserted. The cost is that the date predicate must be correct and the operation needs an atomic boundary supported by the warehouse.

A MERGE can avoid rewriting unaffected rows and can fit a target with a stable business key. Its source must be unique on the match key. A target row absent from the recomputed source is not matched, so ordinary matched-delete semantics cannot remove it, unless the engine supports a WHEN NOT MATCHED BY SOURCE THEN DELETE branch scoped to the closed backfill range, or the obsolete slice is deleted separately. That is why MERGE is an alternative to evaluate, not a universal synonym for idempotence.

Partition replacement can reduce the amount of data touched and improve isolation when the warehouse supports it. It adds platform-specific rules around partition alignment, concurrent readers and failed publication. For a small teaching proof, a transaction around a staged slice makes the invariant easier to inspect. For a larger system, cost, lock duration, partition design and concurrent writers need measured treatment.

The stable key is the non-negotiable part. If the aggregate grain is date and channel, the target must enforce or otherwise validate that grain. If the business needs multiple versions of a daily result, version or batch identity must be part of the key and every downstream query must say which version it reads. Quietly mixing those contracts is how a retry turns into a duplicate total.

This note does not prove that Airflow will schedule a backfill correctly, that a warehouse will survive a node failure or that delivery is exactly once. It does not prove safe concurrency when a live writer and a backfill writer touch the same slice. Those concerns need a write-owner policy, isolation testing, recovery steps and downstream reconciliation.

It also does not prove that every warehouse implements transactions, partition replacement or MERGE with the same semantics. The SQL is a teaching sketch with DuckDB syntax. The application does not run either write. Adaptation to a production engine requires checking date types, transaction behaviour, partition rules, constraints, permissions and the engine's handling of duplicate source keys.

The evidence is narrower and useful: one synthetic daily aggregate, one date boundary, one current-fact selection rule and an independent plain-TypeScript calculation agree on the append failure and the replace result. The test checks duplicate events, invalid rows, the correction for o100, the stale e9 event and the new o106 order before calculating the groups. It also checks that the note's numeric claims are present in the oracle's output.

For a production backfill, record the requested scope, validate the staged grain, compare source and target counts at their own grains, inspect rows that disappear and keep a rollback or restore path. Then run the same slice again. The useful question is not whether the scheduler says the task succeeded. It is whether the committed target is unchanged at the declared grain.

Further reading: the retail-orders studio, the bundled pipeline SQL and Canvas.