Data Migration Specialist

Plan and execute large-scale data migrations with zero-downtime strategies, comprehensive validation, and reliable rollback plans.

Activation Triggers

Activate on: "data migration", "database migration", "zero-downtime migration", "dual-write", "backfill", "cutover", "data validation", "schema migration", "platform migration", "cloud migration"

NOT for: Streaming schema evolution → schema-evolution-manager | API backward compatibility → api-versioning-backward-compatibility | Warehouse optimization → data-warehouse-optimizer

Quick Start

1. Audit source data — profile row counts, data types, nulls, constraints, and edge cases
2. Choose strategy — big bang (downtime), dual-write (zero-downtime), CDC-based (continuous sync)
3. Build validation framework — row counts, checksums, spot checks, and business rule assertions
4. Run rehearsal — execute full migration on staging with production-scale data
5. Plan rollback — define rollback triggers, procedure, and maximum acceptable rollback window

Core Capabilities

Domain	Technologies
Strategies	Dual-write, CDC-based, big bang, strangler fig
CDC Tools	Debezium, AWS DMS, GCP Datastream, pglogical
Validation	Great Expectations, custom checksums, row-count reconciliation
Schema	Flyway, Liquibase, Prisma Migrate, Alembic
Orchestration	Airflow, Temporal, custom migration scripts

Architecture Patterns

Zero-Downtime Migration (Dual-Write)

Phase 1: Dual-Write (days/weeks)
──────────────────────────────────
App writes → Old DB (primary)
         → New DB (shadow, async)
Reads from: Old DB only

Phase 2: Shadow Read Validation
──────────────────────────────────
App writes → Old DB + New DB
Reads from: Old DB (primary)
            New DB (shadow, compare results)

Phase 3: Cutover
──────────────────────────────────
App writes → New DB (primary)
         → Old DB (shadow, for rollback)
Reads from: New DB

Phase 4: Cleanup
──────────────────────────────────
App writes → New DB only
Remove old DB writes
Decommission old DB (after rollback window expires)

Validation Framework

class MigrationValidator:
    """Run after each migration phase to verify data integrity."""

    def validate_row_counts(self):
        """Source and target row counts must match within tolerance."""
        for table in self.tables:
            source = self.source_db.count(table)
            target = self.target_db.count(table)
            tolerance = 0.001  # 0.1% tolerance for in-flight writes
            assert abs(source - target) / source < tolerance, \
                f"{table}: source={source}, target={target}"

    def validate_checksums(self):
        """Hash comparison on sampled rows."""
        for table in self.tables:
            sample_ids = self.source_db.sample_ids(table, n=10000)
            for batch in chunked(sample_ids, 1000):
                source_hash = self.source_db.hash_rows(table, batch)
                target_hash = self.target_db.hash_rows(table, batch)
                assert source_hash == target_hash, \
                    f"{table}: checksum mismatch in batch"

    def validate_business_rules(self):
        """Domain-specific invariants."""
        # Example: total revenue must match
        source_rev = self.source_db.query("SELECT SUM(amount) FROM orders")
        target_rev = self.target_db.query("SELECT SUM(amount) FROM orders")
        assert source_rev == target_rev, "Revenue mismatch!"

    def validate_constraints(self):
        """All FKs, unique constraints, and NOT NULLs hold on target."""
        violations = self.target_db.check_constraints()
        assert len(violations) == 0, f"Constraint violations: {violations}"

Rollback Decision Tree

Migration issue detected?
  │
  ├─ Data loss or corruption? → IMMEDIATE ROLLBACK
  │   Switch reads/writes back to old DB
  │   Replay writes from new DB → old DB (if needed)
  │
  ├─ Performance regression? → EVALUATE
  │   ├─ < 2x slower → optimize, do not rollback
  │   └─ > 2x slower → rollback, investigate
  │
  └─ Minor data discrepancy? → FIX FORWARD
      Run reconciliation job to sync
      Do NOT rollback for fixable issues

Rollback window: keep old DB live for 7-14 days post-cutover

Anti-Patterns

1. No rehearsal — always run the full migration on staging with production-scale data before the real cutover
2. Validation after cutover only — validate continuously during dual-write phase, not just at the end
3. No rollback plan — every migration needs a documented rollback procedure with defined triggers
4. Big bang for large datasets — migrating 1TB+ in one shot risks extended downtime; use CDC or phased approach
5. Ignoring edge cases — NULLs, empty strings, Unicode, timezone differences, and precision loss cause subtle data corruption

Quality Checklist

• Source data profiled: row counts, types, nulls, edge cases documented
• Migration strategy chosen with justification (dual-write, CDC, big bang)
• Validation framework covers: row counts, checksums, business rules, constraints
• Full rehearsal completed on staging with production-scale data
• Rollback procedure documented with trigger criteria
• Rollback window defined (keep old system live 7-14 days)
• Dual-write conflict resolution strategy defined
• Performance benchmarked: target system meets SLA before cutover
• Communication plan: stakeholders notified of timeline and risks
• Post-migration monitoring: alerts for data discrepancies for 30 days