---
name: idempotent-backfill
description: Rebuild historical batch partitions so retries produce the same rows instead of duplicates. Use for partitioned data with a stable business key and a fixed source snapshot. Do not use for streams, mutable current-state tables, or data without a reliable key.
---

# Idempotent backfill

A safe backfill converges. Run it twice from the same source and the result does not change.

The default is full partition replacement. Never append.

## Process

1. Inspect the source and target read-only.
2. Declare the partitions, keys, ordering, and source snapshot.
3. Build each partition twice in staging.
4. Compare the two builds.
5. Stop for approval.
6. Replace and verify one partition at a time.

## Fix the inputs

Confirm:

- the database engine and version;
- the immutable source snapshot or file checksum;
- the target and exact partitions;
- the partition key and business key;
- the newest-wins ordering, including a stable final tiebreak;
- the ordered target columns and transform version.

If the source is changing, take a snapshot first. If a key or tiebreak is unknown, profile the source and propose one. Do not guess.

If a record can move between partitions, rebuild both its old and new partitions. Stop if you cannot identify the old partition.

Read credentials from a named environment variable. Never print or store the credential. Quote validated identifiers with the database driver and bind values as parameters.

## Build twice

Build two independent stages from the same source snapshot and transform.

The transform must:

- produce one row per non-null business key inside the partition;
- pick the newest row with the declared ordering;
- break every ordering tie;
- avoid `now()`, `random()`, unstable sorting, and mutable external state.

Back the target grain with `UNIQUE (partition_key, business_key)` or an equivalent constraint.

For each stage, record:

- row count;
- null and duplicate key counts;
- an additive total when one exists;
- a SHA-256 hash of every material column in business-key order.

Use a canonical hash format that preserves column order, types, nulls, and escaping. The two stages must match on every reading.

If they differ, stop. Do not replace the target.

## Stop for approval

Show the user the target, partitions, source snapshot, transform version, row count, total, and content hash. State that the operation will replace those partitions.

Continue only after the user approves that exact plan.

## Replace and verify

Use one transaction per partition:

1. Take a lock for the target and partition. Set finite timeouts.
2. Recheck the approved stage. Its fingerprint must still match.
3. Delete the target partition and insert the stage with explicit column names.
4. Commit with the engine's atomic operation.
5. Read the target and confirm that its fingerprint matches the stage.

Do not use `SELECT *`. Do not touch an unapproved partition. If verification fails, stop before processing the next partition.

Use the engine's native partition overwrite for Hudi, Iceberg, Delta, Snowflake, or BigQuery. Verify its locking and atomicity first. Use a keyed merge only when it also removes rows that disappeared from the source.

## Record the result

Save the run ID, source snapshot, target, partition, transform version, row count, total, content hash, and final status.

## Invocation example

> Apply the idempotent-backfill skill. Read the connection from `DATABASE_URL` and do not print it. Rebuild `TARGET` from `SOURCE_SNAPSHOT` for `PARTITIONS`. Use `PARTITION_KEY`, `BUSINESS_KEY`, and `ORDERING_FIELDS`. Build twice, show me the fingerprints, and stop for approval before replacing the target.