skip to content

In a migration where the application writes to both an old and a new representation of the same data, how do you keep the two in agreement, and how do you decide it is finally safe to stop writing the old one and remove it?

level: principalimportance: should knowfreq 34%

answer

  1. Both writes in one transaction, or outbox / CDC across stores
  2. One shared transform implementation, not two
  3. Continuous divergence metric, alert at non-zero
  4. Recent-writes check catches the missed writer fast
  5. Contract gates: zero divergence + past rollback horizon + all consumers off

basics

~20 s

Write both inside one transaction, inventory every writer, and run a continuous comparison that counts disagreements. Contract only after reads have run on the new path with zero divergence for longer than your rollback and slowest-consumer window.

solid answer

~1 min

**Keeping them in agreement** rests on three things. Both writes go in **one transaction**, so a crash cannot leave half a row; if the new representation lives elsewhere (another table, another store) and a shared transaction is impossible, the write becomes an outbox or change-stream problem and eventual reconciliation is mandatory, not optional. The **transform must be one implementation**, shared by the application and the backfill, or the two will drift on edge cases. And every writer must be covered — batch jobs, admin tools, imports, other services, ad-hoc SQL. The inventory is always optimistic, so I verify empirically. **Verification** is a continuously running comparison that counts rows where the representations disagree, sampled by key range so it is affordable, plus a spot check on freshly written rows to catch a missed path immediately rather than at the next full sweep. Divergence is an alert, not a report. **The contract decision** is a risk call, not a date. My gates: zero divergence sustained; reads served from the new path long enough to cover the rollback horizon; every consumer confirmed off the old field, including analytics, exports and downstream services; and a rehearsed way back. Then drop it — and actually drop it, because a permanent fork is its own liability.

code

sql · 4 lines
sql
SELECT count(*) AS mismatches
  FROM person
 WHERE id BETWEEN :lo AND :hi
   AND full_name IS DISTINCT FROM concat_ws(' ', first_name, last_name);

go deeper

for a junior

Understand that during the migration both representations must be written and that the old one cannot be removed while anything still reads it.

for a middle

Explain dual write inside a single transaction, the shared transform, and that a comparison job should confirm agreement before the drop.

for a senior

Own the verification design — sweep plus recent-writes check as an alerting metric — and the concrete gates for the drop, including rollback horizon and consumer inventory.

for a principal

Decide whether the apparatus is warranted at all, handle the cross-store case with an outbox or CDC plus reconciliation, and treat an unfinished contract phase as an accumulating liability with an owner and a date.

## What the dual-write window really is During expand-contract, there is a period where two representations of the same fact both exist and both must be correct: the old one because rollback and stragglers depend on it, the new one because it is about to become authoritative. That period is where migrations go wrong, and the failures are silent — nothing errors, the data merely stops agreeing. ## Mechanisms for agreement, strongest first **Same transaction, same statement.** If both representations are columns of the same row, writing them in one statement makes divergence structurally impossible for that path. This is why in-table expand-contract is dramatically easier than migrating across tables or systems. **Database-enforced derivation.** Where the new value is a pure function of the old, a generated/computed column or a trigger removes the question entirely: no application path can miss it. The costs are real — extra work on every write, logic living in the schema, and triggers being easy to overlook in review — but for a mechanical transform this is the most reliable option and should be considered before hand-written dual write. **Application dual write in one transaction.** The common case. Correct as long as every write path is covered and the transform is shared code, not two implementations that agree today. **Cross-store dual write.** If the target is a different database or service, there is no shared transaction. Now you need an outbox written transactionally with the business change and drained asynchronously, or change-data-capture from the source's log. Direct dual write to two systems — write A, then write B — is not safe: a crash or a timeout between them leaves permanent divergence with no record that it happened. Whichever mechanism, reconciliation stops being a check and becomes a required component. ## Verification you can act on The purpose is to detect what the inventory missed, so it must run continuously and alert, not run once and reassure. - **Sampled full comparison.** A job that walks key ranges counting mismatches, cycling through the table over hours or days. Report the mismatch count as a metric with an alert threshold of zero. - **Recent-writes comparison.** Rows changed in the last few minutes, compared immediately. This is the sensitive detector: a missed write path shows up within minutes rather than after a full sweep. - **Read-path shadow comparison.** When reads switch over, optionally compute both and compare on live traffic, logging differences while still returning the old value. Expensive, but it validates the transform against real data distributions rather than a sample. When a mismatch appears, the response is to find the path, fix it, re-backfill the affected range, and reset the clock — not to overwrite the difference and move on. ## The contract decision Dropping the old representation is the only irreversible step, so it deserves explicit gates rather than a ticket that says "cleanup". 1. **Divergence is zero and has been for a defined period**, across both the sweep and the recent-writes check. 2. **Reads have been on the new path** long enough that reverting to the old read path is no longer a scenario anyone would choose. In practice this means past the rollback horizon: the point beyond which you would fix forward instead of rolling back. 3. **Every consumer is accounted for.** Not just the service: reporting queries, BI models, data exports, downstream services with their own copy, notebooks, and anything reading a replica. This is where migrations stall for months, and the honest answer is that you need an inventory plus evidence — query logs, column-level access statistics, or a deliberate period where the old column is populated with a canary value in a non-production environment to see what breaks. 4. **The removal itself is safe to execute.** Dropping a column is usually a metadata operation, but it still needs a lock, so it gets the same short-timeout-and-retry treatment as any DDL. 5. **There is a way back for the data, not just the code.** After the drop, the old representation is gone; if it cannot be recomputed from the new one, ensure a backup or an archived copy exists before dropping. ## Deciding when the ceremony is too expensive A principal-level answer also names the alternative. If the table is small, the traffic modest, or a maintenance window genuinely available, a single locked migration is cheaper than six deploys, a doubled write path and a reconciliation job. The dual-write apparatus is justified by the cost of an outage and the size of the table, and choosing it reflexively for a configuration table is waste. Conversely, for cross-system migrations the apparatus is *insufficient* on its own and needs the outbox or CDC machinery above. ## The failure mode to name explicitly The most common bad outcome is not corruption — it is a migration that never finishes. Reads move to the new representation, the drop is deferred "until things calm down", and the schema keeps both forever: two sources of truth, dual-write code nobody dares delete, and every new engineer asking which field is real. Treat the contract phase as scheduled work with an owner and a date, and treat an aging dual-write window as a defect on the backlog, because the risk it carries grows rather than decays.

  • The new representation lives in a different database, so there is no shared transaction. How does that change the dual-write design?
    Direct dual write becomes unsafe: a failure between the two writes leaves permanent divergence that nothing records. The standard replacements are a transactional outbox — the business change and an event row committed together, then drained asynchronously to the second store — or change-data-capture reading the source's replication log. Both give at-least-once delivery, so the consumer must be idempotent, and a periodic reconciliation job stops being optional because ordering and retries will eventually produce a discrepancy.
  • Analytics and downstream teams still read the old column. How do you get to the drop without waiting forever?
    Make the dependency visible and time-boxed: publish the removal date, use column access statistics or query logs to identify actual readers rather than guessing, and offer the replacement mapping rather than asking each team to figure it out. A useful forcing step is to break it safely first — deprecate in a non-production environment, or stop backfilling the old column so it visibly goes stale, so silent consumers surface as complaints instead of as an incident on drop day. What you must not do is leave it indefinitely, because the fork's cost compounds.
  • How do you decide the ceremony is not worth it and a short locked migration is better?
    Weigh table size and write volume against the cost of the outage window the simple approach needs. A few million rows on a system with a real maintenance window is a single throttled statement, not six deploys, a reconciliation job and a doubled write path. The expand-contract apparatus earns its cost when the table is large, the traffic continuous, and a minute of unavailability is materially expensive.

It is the same discipline as replacing the wiring in an occupied building: run the new circuit beside the old one, energise both, prove every outlet works on the new one, and only then cut the old cable — because once it is cut, finding out something was still on it is an outage, not a test.

saying these in an interview costs you the question

  • Writing to the two representations in separate transactions and assuming they stay consistent
  • Implementing the transform twice — once in the backfill and once in the application
  • Verifying once at the start of the dual-write window instead of continuously
  • Dropping the old column as soon as reads switch, before the rollback horizon has passed
  • Counting only the main service as a consumer and forgetting analytics, exports and replicas
  • Leaving the contract phase permanently undone so both representations live on forever

context

resolving links…