The interview question. Customer T42 has outgrown its current database. Move its data to a new database while reads and writes continue. You can take a consistent snapshot and consume change data capture from the source. Explain the copy and catch-up phases, write ownership transition, stale routers, validation, and the point after which rollback becomes difficult.
Assume one existing source is authoritative for T42, its committed changes can be read from an ordered, durable log with a defined position, and both databases support the necessary local transactions. Briefly pausing T42's writes during the handoff is allowed. Other customers continue. The databases do not share one atomic transaction.
Think first
Move tenant T42 from database A to B with live traffic. You have snapshot at log position L0 and CDC, but some routers cache ownership for ten minutes. Sketch the state machine, identify a safe Lcut, and walk through an old client writing after the target is enabled. What can you safely roll back after B acknowledges its first write?
Take a few minutes to form an approach, then compare it with the worked explanation.
Reveal the worked explanation
“Caught up” is a moving target
Imagine the destination reports zero seconds of replication lag. You flip a feature flag that tells application servers to send writes there. Some servers get the flag immediately. Others have a cached routing table from several minutes ago. One source transaction is still in flight.
What prevents that old request from committing on the source while a new request commits on the target? Nothing in the phrase “zero lag” answers that question. Replication lag describes pending changes, not unique write authority.
The invariant I want to protect is precise: every accepted write for T42 belongs to one ordered authority history, and no acknowledged write is lost during ownership transfer. We can temporarily return a retryable response rather than violate it.
A tenant migration takes a consistent snapshot, replays CDC, briefly fences source writes at a cutover position, and enables the target only after it catches up and ownership changes
Build a destination that is not yet writable
Start with a transactionally consistent snapshot at a known source log position, call it L0. Copy the tenant's records and the metadata needed to interpret them. Start replaying changes afterL0 into the destination. A random dump plus “consume recent messages” can miss changes at the join.
The target stays non-authoritative during this phase. Applying the same row version twice should either be harmless or detected. A replayed delete must not resurrect a row because an earlier snapshot copy arrives afterward. Choose an ordering or version-checked apply protocol that resolves this, not best-effort arrival timing.
Validate along the way. Compare row counts only as a coarse signal. Sample records, check per-range checksums or semantic invariants, and verify that the destination has processed all log positions expected for its snapshot. Large tables can contain equal counts and different values.
At this stage source traffic remains live. If a worker fails, the copy restarts from a checkpoint and CDC continues from its known position. If the destination proves faulty before the cutover, discard or repair its incomplete state. The source has not relinquished write ownership.
Make the handoff a controlled state transition
Here is one safe protocol for the stated system, expressed as a design rather than vendor commands.
First, put T42 into MOVING in authoritative routing metadata. New operations still target the source, but the source knows a handoff is being prepared. When the target is almost caught up, stop accepting new T42 writes on the source and drain all in-flight source transactions. Record the last committed tenant-specific source log boundary Lcut after those transactions are resolved.
Next, make the target apply all T42 changes through Lcut and verify that boundary. Only then publish a new ownership generation, for example generation=42, and enable T42 writes on the target. The old source must continue rejecting writes for that tenant after the cutover. If a client still carries generation 41, it receives a redirect or refresh instruction. A stale client cannot choose the old owner as an alternative authority.
The freeze and generation change must themselves be enforced by a mechanism that cannot accidentally authorize both owners. A robust implementation might use a linearizable directory plus source-side fencing that survives retries and leader failover. A DNS update or delayed pub/sub notification is not such a mechanism. The new owner should verify it holds the current generation, not merely trust the caller's header.
This pauses T42 writes briefly. Old-source reads during copying require an explicit staleness contract. After cutover, a read-your-writes client must use the target. Only T42 is fenced.
Vitess's current VReplication workflows are useful production references. Its cutover procedure can pause source writes, let targets catch up, and change traffic routing. That is evidence of a real migration mechanism, not proof that the toy protocol above is exactly how every Vitess deployment behaves. Vitess MoveTables
Rollback has a boundary
Before target writes are enabled, rollback is generally simple: abandon the candidate and keep serving from source. After the target acknowledges new writes, flipping routing back to the source could discard data the source never received. The old database is now stale even if it still holds a complete pre-cutover snapshot.
To reverse later, replicate target changes back to a validated source, fence target writers, verify the reverse boundary, and perform another ownership transition. Or declare forward-only recovery and repair the target. Which path exists must be decided before the first production cutover. Vitess offers optional reverse replication as part of its traffic switching workflow, illustrating why a working reverse path is a separate capability. Vitess reverse replication
For an interview, I would test this design by crashing the source or target before and after Lcut, delaying the routing update, and letting an old client retry. The assertion is not “the flags eventually look right.” It is that there is never a committed write acknowledged by neither surviving authority, nor two independent accepted histories for the same tenant.
The move worked. But during a real node failure, the system may try to run dozens of such transfers simultaneously. Can the cluster survive the repair traffic it just created?
Go deeper
Go deeper: the tricky boundaries in live migration
The boundary must be a source commit position, not an application wall-clock timestamp. Large or long-running transactions may commit out of order relative to their start time. CDC must preserve the necessary transactional order for T42 and understand log retention long enough for catch-up. A replication slot retaining source logs can itself exhaust disk if the destination stays behind.
A global shard routing directory can become an availability dependency. Clients can cache a versioned map for ordinary reads, but storage must reject stale writers. During a directory outage, an existing owner may continue within its documented lease or epoch protocol. New ownership transitions should stop if the system cannot establish unique authority.
Cutover validation should include schema versions, tenant access control, secondary indexes, and derived data that live outside the primary records. Moving rows but losing a uniqueness claim or authorization policy is still a failed migration.
A continuously available write API can queue or forward operations through a single authoritative path during a handoff, if its latency contract permits it. Concurrent writes to two databases instead require a separately proven coordination and recovery protocol; zero visible downtime does not by itself require distributed transactions. Our problem explicitly permits a bounded write pause, so use that simplification rather than casually asserting atomic dual writes across two independent databases.
Check your reasoning
Tie the consistent snapshot to a durable CDC boundary and apply changes in the required order.
Fence and drain source tenant writes, capture the final commit position, and require target catch-up.
Enforce new ownership with epochs or equivalent authority at the destination and rejection by the source.
Distinguish read routing and stale clients from two competing accepted write owners.
State the post-cutover rollback limitation and need for reverse replication or forward repair.
Common mistake: Interpreting zero replication lag or updated DNS as proof that the old source can no longer accept writes.
Read the answer explanation
No, first establish an exclusive authority transition and cutover position. — Without fenced ownership, source and target can both commit writes after the lag measurement.
Apply it elsewhere
The same change moves a customer between two search-indexing fleets, where reads tolerate seconds of staleness but updates must not be lost. Which cutover constraints can you relax and which remain?
Compare your approach
Read freshness may allow an overlapping read transition, but accepted writes still require a recoverable update log and a defined owner for each write.
One more constraint
A shard move must support truly uninterrupted writes across two independent databases. What protocol is required to remove the short cutover pause?
Quick revision
A snapshot plus CDC builds a current candidate, but zero replication lag does not grant write authority. Capture a consistent snapshot at L0, apply the source log, validate the target, then briefly fence tenant writes on source and drain in-flight commits. Record Lcut, replay through it, and only then enable the target under a new authoritative routing generation. Old routers must be rejected or redirected. Rollback is easy before target writes, but after new acknowledgments requires reverse replication and another fenced handoff.
