A New Column on a Billion Rows

A hard Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.

Domain

Pipeline Design

Difficulty

hard

Seniority

L6

Problem

Our orders table holds a billion rows and takes 50K writes a minute, and both analytics replicas and the change-data-capture feed that loads our warehouse read from it. Design how we add a new column and fill it for every existing row within a week, knowing the next app release starts setting the column on every write.

Add and backfill a new column to a billion-row production table with zero downtime.

Business requirements

• The orders table is taking 50K writes a minute and the application's latency budget can't slip during this migration.
• Analytics replicas and the warehouse change feed depend on this database; backfill can't drive replication so far behind that those consumers break.
• Once the application starts writing the new column, the backfill may only fill rows where the column is still empty, never overwrite a value the application set.
• If the migration is abandoned, we have to be able to remove the column cleanly within a week, and that removal has to be rehearsed before the backfill starts.

Worked solution and explanation

The trap: it's the default value

This is a zero-downtime schema evolution problem wearing a "product wants a column" costume. The real skill is to split the shape change from the value change, so that neither one ever hits the write path at full weight. Anyone can type ALTER TABLE orders ADD COLUMN. The trap is the default. On many engines, ADD COLUMN ... DEFAULT x rewrites all billion rows under a lock while 50K writes a minute queue behind it. A full-speed backfill afterwards then floods replication for everything that reads this table. Get either one wrong and on-call kills your migration on day one.

The statement only says that availability can't slip. Before drawing anything, a senior turns that sentence into four questions. What replicates from this table? Will the app write the new column while the backfill runs? How far behind can replicas fall? How fast must we be able to back out? Here the answers are: analytics replicas and a warehouse CDC feed, yes, an agreed lag budget, and within a week. The design answers all four.

Six moves, in order

Step 1: Add the column as NULL, with no default

The schema_change_plan ships a nullable column. On modern engines that is a catalog edit, not a table rewrite, so the latency budget never notices it. Every value arrives later, through a path you control.

Step 2: Turn on the dual write

Next, the app starts setting new_column on every insert and update, through the dual_write_layer. From that moment the set of rows that need backfilling only shrinks. It also means that two writers now touch the same column, and the next two moves exist because of that.

Step 3: Backfill in key ranges, paced by lag

The orchestrator hands batched_backfill one primary-key range at a time. After each chunk it records the range's end in backfill_checkpoint and reads replication_lag_check. Over budget means pause; recovered means resume from the checkpoint. A crash means restart from the checkpoint, not from row one.

Step 4: Write only where new_column IS NULL

Every chunk passes through fill_if_empty_guard, which runs UPDATE ... WHERE id BETWEEN :lo AND :hi AND new_column IS NULL. If the app got to a row first, the backfill skips it. That one predicate is both the overwrite protection and the idempotency.

Step 5: Validate before you cut over

The cutover_validator checks three things: the count of rows where new_column IS NULL has reached zero, a sample of app-written values is unchanged, and the replicas have caught up. Only after that do you add NOT NULL or point readers at the column.

Step 6: Rehearse the rollback first

The rollback_plan drops the column (a metadata change) and clears the checkpoint state. rollback_rehearsal runs it on a staging copy before the backfill starts, so the runtime is a measured number rather than a hope.

The reference design

Upsert backfill

UPDATE orders SET new_column = :derived across the chunk, or ON CONFLICT DO UPDATE. A row the app set ten seconds ago gets replaced by the backfill's stale derivation. Reruns converge, so it looks idempotent, but every rerun converges on the wrong value.

Fill-only-if-empty backfill

The same UPDATE, plus AND new_column IS NULL. The app's value always wins, rerunning a finished chunk is a no-op, and the guard costs nothing extra because it filters rows the key range already fetched.

Common follow-up questions

• Replication lag breaches the budget mid-backfill. What happens, and where does the job resume? (Tests whether pausing and resuming from `backfill_checkpoint` are properties of the design, not an operator's judgment call.)
• Half the table is backfilled when product cancels the column. What does rollback do, and how long does it take? (Looks for the rehearsed path: drop the column, clear the checkpoint, let replicas catch up, and quote the measured runtime.)
• The derived value depends on a row the app is updating concurrently. Is `new_column IS NULL` still enough? (Probes for a read-and-write race inside one chunk, and for computing the value in the same `UPDATE` statement.)