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.

Gates

Do not cut over on row-count equality alone. Require:

• source and target dependencies are healthy;
• migration lag is within the agreed threshold at the watermark;
• missing, extra, duplicate, and changed identities are measured separately;
• value checksums or full comparisons pass for critical fields;
• target constraints and domain invariants pass;
• quarantined rows are resolved or explicitly accepted;
• rollback/forward-repair and operator runbooks have been rehearsed.

Related Skills

• Use infrastructure-design for Outbox, Inbox, CDC, broker delivery, transactions, locking, and capacity mechanics.
• Use tdd-best-practices and its references/migration-testing.md for failure, restart, race, reconciliation, and cutover verification.
• Use refactoring-best-practices only for introducing code seams and compatibility paths without changing existing behavior.
• Use ddd-best-practices to decide whether transformed data changes domain meaning or bounded-context ownership.

Reference

Read references/operational-migrations.md for snapshot/delta handoff, batching, reconciliation, observability, cutover, repair, and course caveats.