data-migration-best-practices

Data Migration Best Practices

Treat migration as a temporary production system with explicit correctness, capacity, observability, and retirement criteria. It changes persisted behavior and is not automatically a refactoring.

Workflow

1. Define source authority, target contract, transformations, invariants, data classification, and tolerated downtime/staleness.
2. Choose a stable snapshot boundary or high-water mark and start durable capture of live changes before they can escape the snapshot.
3. Expand the target schema compatibly; keep old and new application versions interoperable during rolling deployment.
4. Backfill in bounded, idempotent, resumable batches with deterministic progress and explicit bad-row handling.
5. Apply captured deltas without allowing stale snapshot data to overwrite newer writes.
6. Reconcile availability, counts, identities, values, and domain invariants against a declared watermark.
7. Cut over only when freshness and discrepancy gates pass; monitor and retain a reversal or forward-repair path.
8. Contract old fields, code paths, capture infrastructure, and temporary permissions only after an observation window.

Decision Rules

• Prefer direct transactional SQL for small same-database transformations that fit the lock and deployment budget.
• Prefer an offline bulk importer for heterogeneous stores or transformations that need application code.
• Prefer lazy/on-read migration only when incomplete migration is acceptable indefinitely and concurrent misses use atomic upsert.
• Combine a bounded snapshot with Outbox, CDC, or event capture when writes must continue during a long migration.
• Use expand-and-contract for online schema changes: add compatible shape, deploy tolerant readers/writers, populate, validate, switch authority, then remove legacy shape.
• Use forward repair rather than destructive rollback once new writes cannot be losslessly represented by the old model.

Safety Invariants

• Preserve a stable source identity and transformation version for every target row.
• Make reruns harmless through upserts, compare-and-set/version guards, or processed-range records.
• Advance checkpoints only after the batch transaction commits; never use offset pagination over a changing source.
• Order by an immutable key and use keyset/range batches. Multiple workers need disjoint ranges or durable claims.
• Bound reads, writes, concurrency, retries, memory, and lock time against live-traffic capacity.
• Quarantine malformed or constraint-violating rows with redacted diagnostics; never convert infrastructure failure into valid empty data.
• Parameterize queries and keep credentials out of commands, source files, logs, and process arguments.
• Record who approved cutover, the exact watermark, artifacts, checksums, code/schema versions, and repair decisions.