Data Move
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.