Migrate live users from NoSQL to SQL without downtime

01The design, in the six-step framework

1. Requirements

Functional.

• A live product stores user data (profiles, settings, a list of items per user) in a document store; it must move to a relational database with a normalised schema.
• Users keep reading and writing throughout; no maintenance window, no visible data loss, no stale reads of their own writes.
• The migration must be reversible at any point until the old store is retired.
• Out of scope: why the move is happening (joins, transactions, tooling), and the schema design itself beyond what the migration needs.

Non-functional.

• Data: 200 million users, 2 TB of documents, 5,000 writes per second, 50,000 reads per second.
• Correctness: every record identical in both stores at cut-over, verified; writes during migration land in both stores.
• Added latency during migration under 10 ms at p99 on the write path.
• Time: backfill completes within a week at a rate that does not degrade production.

2. Core entities

• User record: user_id, document (old) and the row set it maps to (users, settings, items tables in the new schema).
• Migration state per user: user_id, phase (not started, backfilled, verified, cut over), last_version_seen, mismatches.
• Change event: user_id, version, operation, payload, source (app dual-write or change stream).
• Routing policy: percentage or cohort of users whose reads come from the new store; a global version number.
• Verification report: run id, records compared, mismatches by type, sample of diffs.

3. API

The product API does not change. The migration adds a data-access layer with a policy and an operator surface.

• Data-access layer: read(user_id) consults the routing policy and the per-user phase; write(user_id, change) writes to the old store, then publishes the change for the new store, and (in later phases) writes to the new store synchronously.
• POST /migration/backfill {rate, user_id range} → job; GET /migration/progress.
• POST /migration/verify {sample or full} → report.
• POST /migration/route {read_from_new_percent, cohorts} and POST /migration/rollback → policy version applied to every app server within 30 seconds.

4. High-level design

The phases:

1. Shadow writes. Every write still goes to the document store; its change stream feeds an applier that transforms documents into rows and upserts them into the new database, keyed by (user_id, version). Nothing reads the new store yet.
2. Backfill. A throttled job walks user ids, reads each document and writes its rows; because the applier is already running, a document that changes during backfill is overwritten by the newer version, never by an older one.
3. Verify. Compare both stores per user by a canonical hash; fix mapping bugs and rerun on the mismatches until the rate is zero.
4. Shadow reads, then real reads. The data-access layer reads from both and compares in the background (logging diffs), then routes reads for 1 percent, 10 percent, 50 percent of users to the new store, watching error rates and latency.
5. Cut writes. Writes go to the new store first (it is now the source of truth), with the reverse change stream feeding the old store for a safety period; then the reverse stream is stopped and the old store retired.

5. Deep dives

How do backfill and live changes interleave without the old data winning?

Every document carries a version (a monotonically increasing update counter or timestamp from the source). Both the backfill and the applier write with "upsert if incoming version is greater than stored version", which makes every write idempotent and order-insensitive: a backfill read taken at version 7 arriving after the applier wrote version 8 is rejected; the applier replaying version 8 twice is harmless. Deletes are written as tombstones with a version, so a delete is not undone by a late backfill. Without versions, the only safe order is "backfill a user, then start applying their changes", which requires per-user coordination and is where migrations usually lose data.

How do we know the two stores agree, and what do we do when they do not?

A verifier computes a canonical hash per user from each store (serialising the new rows back into the document shape, sorting keys, normalising types) and compares; it runs over everyone once, then continuously over a random 1 percent sample and over every user who was recently written. Mismatches fall into classes: mapping bugs (a nullable field, a date format), which are fixed in the transform and re-applied; in-flight writes (the applier lagging), which disappear on recheck after a minute; and genuine drift, which is a bug in dual-write and blocks cut-over. The report that goes to the decision meeting is the mismatch rate over the last 24 hours, by class, with zero genuine drift as the gate. Metrics to watch the whole time: applier lag, backfill rate, mismatch rate, and p99 latency of the write path.

How do staged reads avoid showing a user stale data?

During staged reads the new store lags the old by the applier delay, usually under a second but sometimes minutes during a backfill burst. A user who just wrote and then reads from the new store could see their old value. Two guards: route by user cohort, not by request, so a user is consistently on one store; and hold a per-user "written at" timestamp in the data-access layer's cache, and for a few seconds after a write, read that user from the old store regardless of policy (read-your-writes). This is a rollout of a data path, and the same dashboards as any rollout decide whether to continue: error rate, latency, and the shadow-read mismatch rate for the cohort.

What is different for an SRE interviewer?

They want the failure drills: what happens if the applier falls behind (alert on lag, throttle backfill, add applier partitions); if the new database runs out of IOPS during backfill (backfill rate is the knob, and it is set from the database's headroom, not from the deadline); if a schema change ships mid-migration (the transform is versioned and both stores accept the new field before the app sends it); and the rollback rehearsal before each phase, timed. Say the words "no step is taken that cannot be undone in under a minute until the retire step".

6. What a strong answer sounds like

• Lay out the five phases and say which store is the source of truth in each; that framing answers most follow-ups by itself.
• Put versions on every record and make every write a conditional upsert; explain that this is what lets backfill and live changes run at the same time.
• Define verification as a canonical hash per user with mismatch classes and a gate of zero genuine drift.
• Handle read-your-writes during staged reads explicitly.
• Expected follow-ups: documents that do not fit the relational schema (a JSON column as the escape hatch, with a plan); transactions that span users; what to do with the old store's unique features (TTL fields, secondary indexes).
• Common mistake: a one-shot export and import with a maintenance window, which the question has ruled out, or dual-writing from the application without a change stream, which drifts silently when one write fails.

02Where this question was reported

• LeetCode Discuss · Question took time to understand; candidate rated it outstanding (search snippet)
• PracHub · Hard.