agent ran the data backfill twice because the first run's checkpoint row

Not specified by the original author.

postgres ERROR: duplicate key value violates unique constraint during backfill re-run

TL;DR: The backfill re-ran over rows it already wrote - it is not idempotent. Add ON CONFLICT DO NOTHING (or DO UPDATE) to the backfill INSERT so re-runs skip existing rows, and track completed batches in a checkpoint table so a re-run skips finished work entirely.

postgres ERROR: duplicate key value violates unique constraint during backfill re-run

Steps

1. Identify the conflicting rows: the error names the constraint and the key value. Check whether those rows came from the first backfill run. Expected: confirmed - the re-run is re-inserting its own earlier output, not bad source data.

2. Make the write idempotent:

   INSERT INTO target (id, data)
   SELECT id, data FROM source_batch
   ON CONFLICT (id) DO NOTHING;

   Use DO UPDATE instead of DO NOTHING if a re-run should refresh changed values. Expected: re-running the same batch inserts zero new rows and raises no error.

3. Add a checkpoint: record completed batch ids in a small table and skip batches already marked done at the start of each run. Expected: a second full run finishes quickly with every batch skipped.

4. Re-run the backfill. Expected: completes with no duplicate-key errors; the target holds each source row exactly once.

Use this when

• Duplicate-key errors appear on the second (or later) run of a backfill or data migration.
• The first run partially completed before stopping.
• The conflicting rows match what the earlier run wrote.

Not for this skill when

• Duplicate-key errors appear on the FIRST run - the source data itself has duplicates; dedupe the source.
• Application writes race the backfill and collide - that is a locking/coordination problem, not backfill idempotency.
• You do not recognize the constraint name - find which table and key it guards before changing the write.

Variant phrasings

• duplicate key violates unique constraint backfill rerun
• postgres on conflict do nothing backfill idempotent
• data migration rerun duplicate rows

Why it happens

Without a conflict policy or a checkpoint, every re-run replays the same INSERT statements from the beginning. The unique constraint correctly refuses the second copy of each row - the error is the backfill's lack of idempotency, not corrupt data.

Edge cases

• DO NOTHING silently skips rows that changed upstream since the first run - use DO UPDATE when freshness matters.
• The checkpoint table needs its own transaction discipline: a checkpoint row that never commits recreates the double-run bug you just fixed.
• For very large backfills, keep each ON CONFLICT statement to one batch so a single statement never grows unbounded.

Provenance

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

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

Thread query: postgres ERROR: duplicate key value violates unique constraint during backfill re-run

TL;DR:

Not specified by the original author.

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.

2. 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.

3. 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.

4. 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.

5. 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

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

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

TL;DR:

Not specified by the original author.

TL;DR

A backfill planned from the requested dates alone, without checking what already exists, will happily re-run partitions that are already materialized and, in systems that allow parallel runs, create duplicate runs racing to write the same partitions. Stop the overlapping launches, work out which partitions now have competing runs or doubled data, let one canonical run per partition win, and make the planner subtract existing materializations and in-flight runs from any window before it launches anything.

agent's backfill window overlapped existing partitions, duplicate runs created

Use this when

• A backfill creates runs for dates that were already loaded and healthy.
• Two runs for the same partition exist at once, or a partition's data doubles after a backfill.
• The backfill window was computed from a request, not from the state of the target.

Not for this skill when

• The overlap is an intentional re-backfill after a logic fix, planned to replace the old partitions. That needs partition replacement discipline, not cancellation.
• The duplicates are historical runs in the log that never wrote data. History alone is untidy, not harmful.

Steps

1. List runs and materializations for the whole backfill window and mark each partition as untouched, already good, in flight once, or duplicated. Note the partition level map. Verify: the overlap is visible partition by partition
2. For partitions with competing live runs, stop all but one: keep the run with the correct code and inputs, cancel the rest, and note which run is canonical per partition. Verify: no partition has two writers racing
3. Check partitions that completed twice for doubled or mixed data by comparing counts and key uniqueness against the source for those partitions. Note the partitions needing a rebuild. Verify: data damage is separated from mere run duplication
4. Rebuild any damaged partition cleanly, from one run, and verify counts before moving on. Note the rebuilt set. Verify: every partition ends with exactly one load's worth of data
5. Fix the planner: before launching, it must fetch existing materializations and active runs for the window and launch only the missing or explicitly targeted partitions, recording the plan. Dry run the original request and note it now plans only the gap. Verify: the same request can no longer create the overlap

Variant phrasings

backfill overlapped partitions duplicate dagster airflow runs planner check existing

The planner check phrasing.

backfill created duplicate runs for already materialized partitions cleanup

The cleanup shaped search.

Compatibility: Partitioned pipelines in Dagster, Airflow, or script driven backfills against warehouses. The plan-before-launch pattern is orchestrator agnostic; the existence checks use each tool's run and materialization records.

Why it happens

Backfill requests are expressed in the language of need, the dates someone wants, while the system state lives in partitions and runs. An agent translating a request straight into launches skips the subtraction step a human planner does instinctively: what of this do we already have. Overlap is worst exactly where it looks most harmless, because a re-run of an append style load doubles data, and even a replace style load wastes compute and can race a twin. Planning against state is also what makes a backfill resumable, since the remaining work is always just the unfilled part of the plan.

Edge cases / pitfalls

• A partition that exists but was built by buggy code should be in the plan on purpose. The planner needs an explicit force list, separate from its gap detection.
• In-flight runs from another team or a schedule can collide with a backfill planned minutes earlier. Recheck state at launch time, not only at plan time.
• Cancel carefully in shared systems: a run that looks like your duplicate may be someone else's deliberate re-run. Coordinate before cancelling what you did not launch.
• Keep the plan and the outcome together. When a partition is later questioned, the plan shows whether it was a gap fill or an overlap.

Provenance

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

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

Thread query: agent's backfill window overlapped existing partitions, duplicate runs created

TL;DR: A backfill planned from the requested dates alone, without checking what already exists, will happily re-run partitions that are already materialized and, in systems that allow parallel runs, create duplicate runs racing to write the same partitions. Stop the overlapping launches, work out which partitions now have competing runs or doubled data, let one canonical run per partition win, and make the planner subtract existing materializations and in-flight runs from any window before it launches anything.