agent ran the data backfill twice because the first run's checkpoint row never committed - now the table has...

TL;DR: Clean up the duplicates first, then make the backfill re-runnable: write each batch with an idempotency key (insert only where the key is absent) and commit the checkpoint in the same transaction as the batch - or store progress where the batch write itself is the progress. The root bug is not the double run; it is a backfill that is unsafe to run twice.

agent ran the data backfill twice because the first run's checkpoint row never committed - now the table has duplicate rows

1. Assess the damage: count duplicates by grouping on the natural key (the business key, not the surrogate id).

Expected: a concrete number of duplicated keys, which scopes the cleanup.

1. Dedupe: delete the extra rows, keeping one per natural key (keep the row with the lowest id per key is a common rule). Run this in a transaction and verify the count before committing.

Expected: zero duplicate keys remain.

1. Add a uniqueness guard: put a unique constraint or unique index on the natural key (or the idempotency key) so the database itself rejects double-inserts.

Expected: a manual double-insert test now fails with a unique violation instead of creating a duplicate.

1. Rewrite the backfill to be idempotent: insert with "only if the key does not already exist" semantics per batch.

Expected: running the backfill twice in a row changes nothing on the second run.

1. Fix the checkpointing: commit the checkpoint row in the same transaction as its batch, or store progress where the batch write itself is the progress (the presence of the rows IS the checkpoint).

Expected: a killed-and-resumed backfill skips already-written batches.

Use this when

• a backfill produced duplicate rows after running twice
• the progress or checkpoint row did not survive a crash or kill
• an agent needs to re-run a data migration safely

Not for this skill when

• the duplicates came from the application writing bad data, not the backfill
• the backfill is still running - stop it before deduping
• the table has no natural key at all - define one before anything else

Variant phrasings

• backfill ran twice, duplicate rows in table
• checkpoint never committed, data migration double-applied
• re-ran backfill and now rows are duplicated

Why it happens

The backfill tracked progress in a checkpoint row that lived in a different transaction (or no committed transaction) from the batch writes. When the run died, the rows were committed but the checkpoint was not, so the resume logic believed nothing had been done and wrote everything again. A checkpoint that can diverge from the data it describes is not a checkpoint - it is a guess.

Edge cases

• Dedupe carefully when duplicates differ in non-key columns (timestamps, sources) - decide which row is canonical before deleting, and record the rule.
• A unique constraint on a huge table takes a long lock to build - use a concurrent index build where your database supports it.
• If downstream consumers already read the duplicates, deduping the table is not enough - check materialized views, caches, and exports.
• Per-row "insert if not exists" is slow at scale - batch it, and consider a staging table plus a set-based merge instead.

Provenance

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