backfill strategy that doesn't double-count

TL;DR

Run backfills as delete-plus-insert per partition, or as a merge keyed on a stable natural key, never as a blind append. Every backfill run gets a run id stamped into a log table, and re-running the same date range must produce the same row count. It works because appends are not idempotent: a backfill that dies halfway and gets retried will land the same interval twice unless the write path itself prevents it.

backfill strategy that doesn't double-count

Use this when

• A pipeline had an outage and you need to refill missed days
• Incremental loads overlapped and you suspect stacked intervals
• You replay historical data into a table that already has rows
• An agent or cron retried a load and you are not sure what landed

Not for

• Streaming dedup, where exactly-once needs a different design
• Schema migrations, where the table shape itself is changing
• One-off ad hoc fixes where rebuilding the whole table is simpler

Steps

1. Pick the partition grain and define the window explicitly:

-- backfill window: one row per partition to refill
SELECT generate_series('2026-09-20'::date, '2026-09-26'::date, '1 day') AS refill_date;

Expected output: one row per date in the window, so the scope of the backfill is visible before anything writes.

1. Snapshot the current state of the affected partitions:

SELECT event_date, COUNT(*) AS rows_now
FROM events
WHERE event_date BETWEEN '2026-09-20' AND '2026-09-26'
GROUP BY event_date ORDER BY event_date;

Expected output: per-partition row counts. Save these; they are your before picture for the verification step.

1. For each partition, delete then insert inside one transaction:

BEGIN;
DELETE FROM events WHERE event_date = '2026-09-21';
INSERT INTO events SELECT * FROM staging_events WHERE event_date = '2026-09-21';
COMMIT;

Expected output: the transaction commits and the partition now holds exactly the staged rows. Re-running the same block is a no-op in effect, because the delete wipes whatever the previous attempt inserted.

1. If the table cannot be partitioned by date, merge on a stable natural key instead:

MERGE INTO events AS t
USING staging_events AS s
ON t.event_id = s.event_id AND t.event_date = s.event_date
WHEN MATCHED THEN UPDATE SET t.payload = s.payload
WHEN NOT MATCHED THEN INSERT VALUES (s.event_id, s.event_date, s.payload);

Expected output: existing keys get updated in place, new keys get inserted, and running the merge twice changes nothing the second time.

1. Stamp every run into a backfill log so retries are traceable:

import uuid, datetime
run_id = str(uuid.uuid4())
# insert into backfill_log(run_id, table_name, window_start, window_end, started_at)
print(f"backfill {run_id} for events 2026-09-20..2026-09-26 started {datetime.datetime.utcnow().isoformat()}")

Expected output: a log row tying the run id to the exact window, so a later duplicate investigation can tell which run wrote which rows.

1. Verify with a duplicate check and a count reconciliation:

SELECT event_id, event_date, COUNT(*)
FROM events
WHERE event_date BETWEEN '2026-09-20' AND '2026-09-26'
GROUP BY event_id, event_date HAVING COUNT(*) > 1;

Expected output: zero rows. Then compare per-partition counts against the staging source; they must match exactly.

Variant phrasings

backfill without duplicates

Same pattern, different words. The delete-plus-insert transaction is the core move; the merge variant covers tables without a clean partition key.

replay historical data idempotently

Idempotent is the formal name for what this skill builds: running the load N times has the same effect as running it once. Test it by literally running the backfill twice and diffing counts.

delete insert vs merge backfill

Prefer delete-plus-insert when partitions are clean date boundaries; prefer merge when the natural key is stable but the data is not neatly partitioned. Both beat append.

Why it happens

Double counting is not a data bug, it is a write-path bug. Append-style loads assume each interval lands exactly once, but retries, overlapping schedules, and manual re-runs break that assumption constantly. The fix moves idempotency into the write itself: either the old rows for the interval are removed before the new ones land, or the write is keyed so duplicates collapse. Once the write is idempotent, the orchestration layer can retry freely without fear.

Edge cases

• Late-arriving data URIs a backfill window can miss events that arrive after the run; schedule a second pass or widen the window by a day on each side.
• Overlapping partitions: if two backfill windows share a date, run them serially or make the transaction cover the union, never interleave.
• Huge partitions: a single delete-plus-insert transaction can bloat WAL or time out; chunk by hour or use the merge form with batched keys.
• Restated source data URIs if the source itself corrected history, the backfill must cover the restatement window too, or old wrong rows survive next to new right ones.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_isgxMU38gbw0HPJJnE3SJg