Backfill prompts: bound batches and make retries idempotent

Last updated: 5 Oct 202611 min read

tutorial

A backfill prompt should state the ordering key, batch size, idempotent write rule, checkpoint contents, retry behavior, and rate limit. A checkpoint means a range was processed under a mapping version, not that its values are correct. The migration runner executes reviewed code; a model merely drafts a runbook. Preserve row versions where concurrent writes matter. A retry must not create a second target row or overwrite a newer source change with an older snapshot value.

Operational case

Harbor processes the 46,940 mappable rows in bounded key ranges. If a worker stops after committing a range, it resumes from the last durable checkpoint, and replaying that range yields the same target identity. The 60 quarantined rows remain in a separate owner queue. A source status update during the backfill is applied by the later change tail rather than being erased by a stale retry.

Batch key: tenant_id + order_id
Checkpoint: committed key + mapping version + watermark
Retry: same target identity, no duplicate insert
Concurrent change: compare version or replay tail

Performance and review cost

Processing N rows in batches of B is O(N) row work with about O(N/B) checkpoint writes, aside from index and network cost. Small batches shorten locks but increase overhead. Monitor latency and replication lag; a compact generated loop can still overload a live database.

Common Mistakes

Do not call checkpoint completion value parity.
Do not let retries create new target IDs.
Do not overwrite a newer change with stale snapshot data.

Connected lessons

prompt engineering

database migration