Data Migration Strategies: Zero-Downtime Patterns, CDC, Dual Writes, and Proving Correctness

Data migration is an end-to-end plan to move not just bytes, but behavior: reads, writes, constraints, semantics, SLAs, and failure handling from a source system to a target system with controlled risk. In real production systems, copying data is rarely the hardest part; the hard part is maintaining consistency under live traffic while you change schema, storage engines, and application code—without breaking correctness or availability.

Online migrations dominate at companies operating at scale because downtime is expensive. Netflix has documented dual-write approaches in Cassandra client tooling for cross-cluster migrations, and Airbnb and Uber have published large-scale migration stories that emphasize correctness, operational control, and staged cutovers. In interviews (Meta, Google, Stripe, Uber, Netflix), the “migrate from X to Y with zero downtime” prompt is a common proxy for whether you can reason about distributed systems trade-offs: ordering, idempotency, observability, rollback, and bounded risk.

Migration types and the core building blocks

At a high level, you’ll choose between offline (big-bang) and online (near-zero downtime) migrations. Offline migrations stop writes (or stop the whole service), copy data, verify, cut over, and restart. They’re operationally simple, but the downtime window grows with data size and verification time—moving 5 TB at 500 MB/s is ~2.8 hours just to copy, before validation and cutover. Online migrations keep the system live, so you need additional machinery to handle concurrent writes and correctness.

Online migrations are built from a few canonical blocks:

• Backfill: batch-copy historical data into the target. Must be idempotent (safe to retry) and restartable (checkpoint progress).
• Dual write (shadow write): application writes to both source and target during the transition window.
• Change Data Capture (CDC): stream incremental changes from DB logs (e.g., MySQL binlog, Postgres WAL) to keep the target current after an initial snapshot. Debezium is a widely used open-source CDC platform.
• Snapshot: capture a point-in-time baseline. Debezium supports multiple snapshot modes and incremental snapshots to reduce locking/impact.
• Reconciliation/validation: prove the target matches the source via counts, checksums, domain invariants, and sampling.
• Cutover: shift reads and writes to the target, typically gradually, with rollback criteria.
• Decommission: remove old code paths and infrastructure after confidence is high.

The most important decision you make early is the consistency model during migration. Strong consistency end-to-end is possible but expensive (and often requires single-writer constraints or transactional outbox patterns). Most real migrations accept eventual consistency with bounded staleness, and compensate with strong reconciliation and a carefully managed cutover.

Strategy 1: Offline (big-bang) migration

Offline migration is the simplest to reason about: stop writes → copy → verify → switch → start. It’s a reasonable fit for internal tools, low-SLA workloads, or small datasets where you can tolerate minutes of downtime.

Pros

• Simple correctness model (no concurrent writes to reconcile).
• Minimal new infrastructure.

Cons

• Downtime scales with data size and verification.
• Late surprises are expensive: you discover performance issues only at cutover.
• Rollback can be painful if clients have already been upgraded.

In interviews, a big-bang migration is acceptable only if you explicitly justify the SLA and show you understand why it’s not viable for most consumer-facing systems.

Strategy 2: Expand–Contract (recommended default)

For online migrations, the safest default pattern is Expand → Migrate → Contract:

1. Expand: add the new schema/tables/cluster while keeping the old system fully functional. Changes should be additive (new columns, new tables, new endpoints).
2. Migrate: backfill historical data and keep the target in sync (CDC or dual writes). Validate continuously.
3. Contract: once stable, remove old fields/tables/code paths and decommission infrastructure.

This pattern aligns with real “schema as code” practices and change management tooling (e.g., Liquibase versioned migrations and rollback plans). Airbnb’s internal schema change guidance emphasizes compatibility categories and additive-first changes for safe evolution.

The key idea is that you never deploy a change that requires every component to update simultaneously. Expand–contract gives you time to run old and new side-by-side and makes rollback feasible.

Strategy 3: Dual write + backfill + verify + cutover

A classic playbook for migrating a service datastore (e.g., MySQL → a new Postgres cluster, Cassandra cluster A → cluster B, DynamoDB table → new storage) is:

• Backfill historical records into the target.
• Enable dual writes so new writes go to both systems.
• Run continuous verification and repair.
• Cut over reads first (often gradually), then writes.

Architecture diagram (dual write + backfill)

flowchart LR
  Client[Clients] --> API[Service API]

  API -->|write| Source[(Source DB)]
  API -->|shadow write| Target[(Target DB)]

  Source --> Backfill[Backfill Job]
  Backfill --> Target

  API -->|read| ReadRouter{Read Router}
  ReadRouter -->|mostly| Source
  ReadRouter -->|canary %| Target

  Target --> Recon[Reconciliation Jobs]
  Source --> Recon
  Recon --> Dash[Dashboards/Alerts]

Runnable example: dual-write with fail-open vs fail-closed (Node.js)

Below is runnable JavaScript (Node 18+) that demonstrates the two most common policies:

• FAIL_CLOSED: if the target write fails, fail the request (better correctness, worse availability).
• FAIL_OPEN: tolerate target write failures and rely on replay/CDC/repair later (better availability, riskier drift).

// dual-write.js
// Run: node dual-write.js

class InMemoryDb {
  constructor(name) {
    this.name = name;
    this.map = new Map();
  }
  async upsertUser(user) {
    // simulate async IO
    await new Promise(r => setTimeout(r, 5));
    this.map.set(user.id, { ...user });
  }
  get(id) {
    return this.map.get(id);
  }
}

const WritePolicy = {
  FAIL_CLOSED: "FAIL_CLOSED",
  FAIL_OPEN: "FAIL_OPEN",
};

async function writeUser({ user, policy, sourceDb, targetDb, targetFailureRate = 0 }) {
  // source is authoritative early in the migration
  await sourceDb.upsertUser(user);

  try {
    if (Math.random() < targetFailureRate) throw new Error("simulated target outage");
    await targetDb.upsertUser(user);
  } catch (e) {
    console.error(`[metric] migration.target_write_failed=1 userId=${user.id} err=${e.message}`);
    if (policy === WritePolicy.FAIL_CLOSED) throw e;
  }
}

(async () => {
  const source = new InMemoryDb("source");
  const target = new InMemoryDb("target");

  for (let i = 1; i <= 5; i++) {
    try {
      await writeUser({
        user: { id: i, name: `user-${i}` },
        policy: WritePolicy.FAIL_OPEN,
        sourceDb: source,
        targetDb: target,
        targetFailureRate: 0.3,
      });
    } catch (e) {
      console.error("request failed", e.message);
    }
  }

  console.log("source has", source.get(1));
  console.log("target has", target.get(1));
})();

In production, fail-open is common early to protect availability, but it’s only safe if you also have a healing mechanism: retries with a DLQ, CDC replay, or periodic reconciliation-driven repair.

Trade-offs and failure modes

Dual writes are appealing because they don’t require database log access, but they create hard distributed-systems questions:

• Ordering: a write might reach the target before the source (or vice versa). If you rely on “last write wins,” you need a deterministic version (e.g., monotonic timestamp or sequence).
• Partial failure: what if source succeeds and target fails? Your policy determines correctness vs availability.
• Latency: synchronous dual writes add tail latency. If your p99 is 80 ms and the new target adds 40 ms, you may violate SLOs.
• Deletes: easy to forget. You must propagate tombstones and ensure backfill doesn’t resurrect deleted rows.

Netflix’s documentation around dual writes in migration contexts exists precisely because these edge cases show up constantly when moving critical traffic.

Strategy 4: CDC-based online migration (log-based replication)

CDC-based migrations usually look like: snapshot → stream changes → apply → validate → cut over. Instead of modifying application code for dual writes, you tail the source database’s replication log and apply changes to the target.

Airbnb open-sourced SpinalTap, a CDC service, and Debezium is a common choice in the broader ecosystem (Kafka Connect + source connectors). CDC tends to scale better at high write rates because you’re not doubling application write traffic; you’re replicating from a single log.

Architecture diagram (snapshot + CDC)

flowchart LR
  Source[(MySQL/Postgres Source)] -->|snapshot| Snap[Snapshot Loader]
  Snap --> Target[(Target DB)]

  Source -->|binlog/WAL| CDC[Debezium / SpinalTap]
  CDC --> Kafka[(Apache Kafka)]
  Kafka --> Applier[Target Applier Service]
  Applier --> Target

  Applier --> Lag[Consumer Lag Metrics]
  CDC --> Lag
  Lag --> Alerts[Alerts / Cutover Gates]

Runnable example: applying CDC events with idempotency (Python)

This script simulates consuming CDC events and applying them idempotently using a per-key version. In real deployments, the “version” might be a binlog position, WAL LSN, or Kafka offset.

# cdc_apply.py
# Run: python3 cdc_apply.py

from dataclasses import dataclass

@dataclass(frozen=True)
class Event:
    key: str
    op: str  # "UPSERT" or "DELETE"
    value: dict | None
    version: int

class TargetStore:
    def __init__(self):
        self.data = {}
        self.last_version = {}

    def apply(self, evt: Event):
        last = self.last_version.get(evt.key, -1)
        if evt.version <= last:
            return  # idempotent / ignore duplicates or replays

        if evt.op == "UPSERT":
            self.data[evt.key] = evt.value
        elif evt.op == "DELETE":
            self.data.pop(evt.key, None)
        else:
            raise ValueError("unknown op")

        self.last_version[evt.key] = evt.version

if __name__ == "__main__":
    store = TargetStore()
    events = [
        Event("u1", "UPSERT", {"name": "A"}, 10),
        Event("u1", "UPSERT", {"name": "A2"}, 11),
        Event("u1", "UPSERT", {"name": "A2"}, 11),  # duplicate
        Event("u1", "DELETE", None, 12),
        Event("u1", "UPSERT", {"name": "A3"}, 9),   # out-of-order older
    ]

    for e in events:
        store.apply(e)

    print(store.data)  # expected: {} (deleted)

CDC is not “free correctness,” though. You still need:

• Correct snapshot boundary (start streaming from the exact point the snapshot represents).
• DDL/schema changes handling (column additions, type changes).
• Ordering guarantees per key (Kafka partitioning by primary key is common).
• Operational visibility into lag (seconds/minutes behind) so you can gate cutover.

Validation, reconciliation, and proving correctness

Successful migrations treat correctness as a feature with explicit acceptance criteria. “We copied the table” isn’t evidence. You need invariants and reconciliation jobs that run continuously.

Practical invariants that work well:

• Row counts per partition (e.g., per day, per tenant, per shard).
• Checksums per partition (e.g., CRC32 of stable serialization).
• Domain invariants: foreign key coverage, sums of ledger entries, monotonic counters, “no negative balances,” etc.
• Dual-run comparisons: shadow reads executed against target and diffed against source.

Uber’s ledger migrations highlight why domain invariants matter: when you’re migrating financial/ledger-like data (Uber’s LedgerStore migration involved more than a trillion entries), correctness isn’t “close enough.” You need provable properties and repeatable validation.

Cutover and rollback: treat it like a launch

Cutover is where migrations fail because teams treat it like a config flip. In reality, it’s a launch with risk controls:

• Read cutover first is common: route 1% of reads to the target, compare results (shadow reads), then gradually ramp to 100%.
• Write cutover depends on your strategy: if you’ve been dual-writing, you can switch the authoritative write to target after you’ve proven the target is caught up and stable.
• Rollback window: keep the old system warm for hours/days (depending on business risk) and define explicit rollback triggers (error rate, p99 latency regression, reconciliation deltas).

A concrete example of gating criteria might look like:

• CDC lag < 5 seconds for 24 hours.
• Reconciliation: checksum match for 99.99% of partitions; remaining 0.01% explained and repaired.
• Target p99 read latency within +10 ms of source under production load.
• Error budget impact < 0.1% over 7 days.

This is also where observability matters: instrument the migration pipeline with OpenTelemetry traces and metrics (write failures, apply failures, lag, reconciliation mismatches) so you can debug quickly under pressure.

Common pitfalls (and how to avoid them)

Most migration outages come from a small set of repeat offenders:

1. Non-idempotent backfills: retries create duplicates or overwrite newer data. Fix with upserts keyed by primary key and version checks.
2. Ignoring deletes: backfill copies only live rows; CDC doesn’t propagate tombstones; target accumulates zombie records.
3. Unbounded dual-write window: “temporary” dual writes become permanent; drift accumulates; engineers stop trusting either system.
4. Wrong event partitioning: CDC events aren’t ordered per primary key; last-write-wins breaks; you get time-travel bugs.
5. Schema divergence: a new field is added on one side only; default values differ; your diffing logic lies.
6. Cutover without performance parity: target passes staging but fails real query patterns (missing indexes, different query planner, hot partitions).
7. Bi-directional writes without a single-writer rule: split-brain data is extremely hard to repair.

A good interview answer explicitly calls out at least a few of these and ties them to mitigations: idempotency keys, ordering constraints, reconciliation, and rollback.

Interview framing: how to present a migration plan

In system design interviews, a strong migration answer is structured and evidence-driven:

• Phased plan: Expand → Migrate (backfill + CDC/dual write) → Validate → Cutover → Contract.
• Correctness plan: invariants, reconciliation cadence, sampling strategy, dual-run comparisons.
• Operational plan: throttling, lag monitoring, dashboards, on-call/runbooks, rehearsals.
• Failure mode thinking: partial failures, retries, DLQ, replay strategy, ordering guarantees.

A typical prompt—“Migrate user profiles from MySQL to a new datastore with zero downtime”—is really asking whether you can control risk while changing multiple dimensions at once: schema, storage, and code.

Summary: actionable takeaways

Data migration is a distributed systems exercise disguised as ETL. The safest migrations are the ones you can measure, throttle, validate, and roll back.

Actionable checklist:

• Prefer Expand–Contract; keep changes additive until you’re ready to contract.
• Make backfills idempotent and checkpointed; assume jobs will restart.
• Pick one: dual writes (app-level control) or CDC (log-based replication). Know the trade-offs.
• Define explicit invariants and run reconciliation continuously; treat “correctness” as a deliverable.
• Gate cutover on evidence (lag, checksums, latency) and keep a time-bounded rollback window.
• Instrument everything (errors, lag, diffs) and prepare runbooks—migrations fail operationally more often than technically.

Primary references worth reading deeply include Debezium’s documentation (snapshot modes and CDC semantics), Airbnb’s SpinalTap and key-value store migration writeups, Netflix’s dual-write notes, and Uber’s migration posts (Docstore and LedgerStore) for what “correctness at scale” looks like in practice.