Data migration fails in silent corruption, ordering bugs, and unclear cutover. Treat it as ETL with production risk: explicit mapping, checkpoints, and reconciliation against sources of truth.

When to Offer This Workflow

Trigger conditions:

• Moving data between databases, regions, or tenants
• Large backfills after schema changes
• Zero or minimal downtime requirements

Initial offer:

Use seven stages: (1) scope & invariants, (2) source/target mapping, (3) batching & idempotency, (4) validation rules, (5) execution strategy (big bang vs phased), (6) cutover & rollback, (7) reconciliation & sign-off). Confirm volume, downtime budget, and compliance (PII, audit).

Stage 1: Scope & Invariants

Goal: Define what moves, what must never diverge, and ordering dependencies (foreign keys, references).

Questions

• Cutover moment: read-only window vs dual-write?
• Immutable identifiers: preserve primary keys or remap with mapping tables?
• Deletes: soft-delete vs hard-delete semantics in target

Exit condition: Written invariants (e.g., “every migrated row has legacy_id for traceability”).

Stage 2: Source/Target Mapping

Goal: Field-level mapping document; transforms (timezone, encoding, rounding); defaults for nulls.

Practices

• Surrogate keys generated deterministically or via mapping table
• Document one-way vs bi-directional sync if any

Stage 3: Batching & Idempotency

Goal: Jobs restartable; same input yields same output (idempotent writes or upsert keys).

Practices

• Checkpoint by primary key or updated_at watermark
• Throttle to protect source and target DB

Stage 4: Validation Rules

Goal: Row counts, checksums, sample joins, business invariants (sums, balances).

Practices

• Shadow compare: run parallel queries on old vs new for critical aggregates

Exit condition: Validation checklist signed before cutover.

Stage 5: Execution Strategy

Goal: Phased by tenant/region vs single window—risk vs complexity trade-off.

Patterns

• Dual-write then backfill then flip reads
• Blue/green tables with rename swap

Stage 6: Cutover & Rollback

Goal: Runbook: who flips DNS/config, order of steps, rollback triggers (error rate, failed checks).

Practices

• Feature flags for read path to new store
• Keep rollback script tested in staging

Stage 7: Reconciliation & Sign-off

Goal: Post-cutover 24–72h monitoring; reconciliation job scheduled; support playbook for edge cases.

Final Review Checklist

• [ ] Invariants and mapping documented
• [ ] Idempotent batches with checkpoints
• [ ] Validation and shadow checks passed
• [ ] Cutover/rollback runbook tested
• [ ] Reconciliation after go-live

Tips for Effective Guidance

• Never assume “batch job finished” = correct—prove with checks.
• Clock skew and timezone bugs are classic—call them out in transforms.
• Pair with db-migrate for schema timing vs data movement.

Handling Deviations

• Small one-off SQL: still document mapping and run counts before/after.