Skip to content
ADevGuide Logo ADevGuide
Go back

Backend Interview Scenario: Zero-Downtime Database Migration

By Pratik Bhuite | 10 min read

Hub: Java / Interview Fundamentals

Series: Backend Interview Mastery

Last verified: Sep 19, 2026

Part 73 of 88 in the Backend Interview Mastery

Key Takeaways

On this page
Reading Comfort:

Backend interview scenario: Zero-Downtime Database Migration

This scenario asks you to plan an expand-migrate-contract database change with dual writes, backfill, verification, and rollback. Treat it as an incident and design prompt: state the invariant, expose the failure boundary, and choose a mitigation that can be rolled back. Continue from the Backend Interview Mastery series and the system design interview cheatsheet.

Table of Contents

Open Table of Contents

1. Interview Question

“An on-call engineer reports that we must plan an expand-migrate-contract database change with dual writes, backfill, verification, and rollback. How would you make the system correct under concurrency, retries, and partial failure?”

2. Clarifying Questions

Ask what the user-visible invariant is, what is authoritative, the peak rate, acceptable latency, retry policy, ordering requirement, failure scope, and recovery objective. Assume Schema registry, old/new columns, backfill workers, verifier, feature flag.

3. Naive Design

Start with one synchronous service and a direct call to the dependency. It is easy to deploy, but it couples request latency to every downstream operation and leaves no durable record for work that timed out. A process-local cache or lock also fails when requests land on another replica.

4. Why It Fails

The naive path violates correctness when two requests observe the same state, a response is lost after a commit, or a worker dies mid-operation. It also scales the wrong resource: retries and unbounded concurrency amplify load exactly when the dependency is unhealthy.

5. Better Design

Persist an intent or event before performing the side effect, attach an idempotency key, and make state transitions monotonic. Use a bounded queue for asynchronous work, a lease for worker ownership, and a dead-letter path for exhausted attempts. Return a pending status when the outcome is uncertain instead of guessing.

6. Architecture

The core components are 1 TB table; continuous writes; no maintenance window. Keep the request path thin and route work through durable state and a queue.

flowchart TD
    A[Old and New App] --> B[Compatibility Layer]
    B --> C[Old Schema]
    B --> D[New Schema]
    C --> E[Dual-Write or CDC]
    E --> D
    D --> F[Backfill and Validation]
    F --> G[Flagged Cutover]
    G -. rollback .-> B
  1. Expand the schema with backward-compatible fields while old code still runs.
  2. Write through an outbox or CDC path and compare old/new values during shadow reads.
  3. Backfill in resumable chunks with validation and a pause threshold.
  4. Cut over reads and writes behind a flag only after parity is proven.
  5. Keep the compatibility layer until rollback is no longer required.

7. Data and Race Analysis

Use a state table with id, owner or tenant, idempotency key, status, version, lease expiry, attempt count, and timestamps. Add a unique index for the key and a compare-and-set on version. If two workers race, only the worker holding the current lease and version may commit. Keep the system of record authoritative; caches and projections may be stale.

8. Failure Handling

A timeout becomes UNKNOWN or PENDING until the system can query or reconcile the dependency. Retry only transient, idempotent operations with exponential backoff and jitter. Expired leases make work claimable, while a DLQ preserves payload, error, correlation ID, and attempt history. Recovery starts with the oldest pending item, not the newest alert.

9. Scaling and Capacity

Assume a 1 TB table with continuous writes and no maintenance window; size dual-write overhead, backfill rate, and validation lag. A 10,000-row/second backfill needs checkpoints and pause thresholds so production writes retain headroom.

10. Trade-offs

Synchronous completion gives immediate feedback but couples availability and latency. Asynchronous completion absorbs bursts and enables replay but requires a status API and eventual-consistency language. Strong serialization protects the invariant but can reduce throughput; partitioning improves scale while narrowing the ordering guarantee.

11. Observability

Track request rate, error rate, P50/P95/P99, queue age, oldest pending work, lease expiry, retry amplification, state-store saturation, and invariant violations. Correlate logs, traces, and events with one ID. Alert on trend and age, not only instantaneous error percentage.

12. Interview Follow-Ups

Scenario-Specific Follow-Ups

1. What does expand-and-contract mean?

Expand adds nullable columns or new tables while old code still works; migrate data; then switch reads and writes; contract removes old fields only after every client is upgraded.

2. How do you keep dual writes consistent?

Prefer one authoritative write plus CDC or an outbox, and compare old/new values during shadow reads. If dual writes are unavoidable, record failures for replay instead of hiding them.

3. When can you roll back?

Before destructive cleanup, keep the compatibility layer and old schema readable. A rollback after dropping columns is a restore exercise, not a normal deployment rollback.

1. How do you make retries safe?

Store a scoped idempotency key and the first result under a unique constraint. Pass that key to downstream providers and return the stored result on duplicate requests. A retry after an uncertain timeout therefore converges on one business outcome.

2. What if the queue is unavailable?

Commit the intent only when an outbox row is written in the same transaction. A relay can publish later. If the state store itself is unavailable, reject new work rather than acknowledging an unrecoverable request.

3. How do you handle a hot key?

Coalesce concurrent requests, shard or replicate the hot key when semantics allow, and rate-limit the caller. Do not simply increase concurrency against one serialized row. Measure per-key skew separately from fleet averages.

4. How do you recover after a region or dependency outage?

Pause nonessential work, cap retries, fail over only where the data contract permits, and reconcile after recovery. Replay a bounded range with the original IDs. Document which user operations return pending or retryable states.

5. What would you test before launch?

Run concurrent invariant tests, crash workers after each state transition, inject timeouts and duplicate events, and load-test the hottest partition. Verify dashboards, rollback, DLQ replay, and operator permissions. Test recovery time as a first-class SLO.

Read backpressure, timeouts, and observability.

References

  1. Google SRE: Handling overload
  2. AWS: Timeouts, retries and backoff
  3. OpenTelemetry documentation

YouTube Videos

  1. “Backend Distributed Systems Interview Scenario” - Hello Interview [https://www.youtube.com/watch?v=Ibf7wKf8MqU]

Share this post on:

Next in Series

Continue through the [object Object] with the next recommended article.

Related Posts

Keep Learning with New Posts

Subscribe through RSS and follow the project to get new series updates.

Was this guide helpful?

Share detailed feedback

Previous Post
Backend Interview Scenario: Sudden Memory Increase
Next Post
Backend Interview Scenario: Zero-Downtime Deployment