Designing Safe Reconciliation Jobs

Draft. An outline of something I worked out in practice and intend to write up properly.

What this will cover

A reconciliation job is easy to write and easy to write dangerously. The happy path — read source, read destination, diff, apply — is a morning’s work. Everything that makes it safe to schedule is the rest of it.

The intended shape of the article:

• Dry run as a first-class mode, reporting the exact create, update and delete plan without writing. Not a debug flag; the thing you run first against production.
• Idempotence, so re-running is uneventful and a partial failure is recoverable by running again
• Scoping, when several sources feed one destination. Records outside a run’s scope must be invisible to it rather than looking like deletions.
• Delete guardrails, because a query that fails its filter returns zero rows, and a faithful mirror will then empty the destination. Bound the damage a bad source can do.
• Orphan policy as configuration — ignore, delete, or flag inactive — since the right answer depends on whether humans also maintain the destination
• Change detection that fails toward doing more work, so an optimization can only ever skip unnecessary work, never necessary work
• Type coercion against the destination’s real schema rather than assuming everything is text

The through-line

Ask what happens when the source is wrong, not just when it is unavailable. Unavailable is easy — the job fails and someone looks. Wrong is the case that quietly destroys data while every log line says success.