Hard Database Engineering interview problem
Zero-Downtime Column Split Loses Rows
Written and reviewed by Sahil Srivastav
This problem is available as the same repository challenge in Python, Java, Node.js, and C++. Open the full problem and choose a language
What this interview round tests
A live migration splits full_name into first_name and last_name while user creation continues. The backfill reports completion, but old rows were skipped and new writes still rely exclusively on the legacy column.
This is a hard production-debugging exercise because the algorithm must remain bounded and restartable while the dataset changes underneath it, without locking the whole table or pausing writes. It tests whether you can move from operational evidence to a narrow invariant, then make a repair that survives concurrency, retries, failures, or workload growth instead of merely passing one happy-path example.
The scenario
The expand phase has added nullable target columns and a bounded backfill records progress. During the compatibility window, application writes and the backfill must cooperate until the contract phase can prove no incomplete row remains.
The starter’s progress model assumes a static table and its writer does not dual-write the expanded schema. Concurrent inserts can land outside the backfill’s scan assumptions, and completion reflects cursor position rather than remaining work.
What you’ll practice
- Applying the expand-migrate-contract sequence
- Dual-writing compatible old and new columns
- Designing bounded, restartable backfill batches
- Using a partial index to find remaining work
- Defining completion from data state rather than cursor exhaustion
How to approach it
Separate responsibility for rows created after expansion from responsibility for legacy rows. Make the live writer produce migration-complete rows, then ensure each bounded backfill query finds actual remaining work.
Challenge completion with inserts before, during, and after a batch. A cursor reaching a high-water mark is not enough; the contract gate must be tied to the absence of rows that still need migration.
The starter repository ships with a failing test suite and a bundled verify.sh. Reviewed reference solutions are part of Gronex Pro — this page stays spoiler-free on purpose.
Try it in a real repository
LeetCode teaches algorithms. Gronex teaches backend coding rounds with real repositories, failing tests, service logic, and production-style constraints. Read the diagnostic brief on this page, then open the challenge workspace when you are ready to investigate the repository.
FAQ
What should I inspect first in Zero-Downtime Column Split Loses Rows?
Begin with backfill progress, rows still matching the partial index, inserts during migration, batch boundaries, and completion checks. The supplied evidence narrows the failure mode before you touch the implementation, which is the same evidence-first habit expected during a production incident or senior backend interview.
What makes this a hard database engineering problem?
It is hard because the algorithm must remain bounded and restartable while the dataset changes underneath it, without locking the whole table or pausing writes. The test suite checks the underlying invariant and adverse execution paths, so a local patch or a larger resource limit will not satisfy the challenge.
Which languages can I use for this repository challenge?
The same scenario is implemented in Python, Java, Node.js, and C++. Each language directory includes its own source, evidence or database setup, tests, and verify.sh entry point while preserving the same production invariant.