System design interview

Backfill Strategy for Historical Data

MediumLocked55 min read

Reprocess 2 years after a bug fix using atomic swaps, incremental date ranges, without disrupting live consumers.

system-designbackfilldata-opslakehouse

Start here

The problem in plain words

You find and fix a bug in how discounts are calculated. The fix protects future data, but two years of already-built tables are wrong. To correct them you must rebuild two years of history.

The risky part is not the computing. It is that people are using those tables right now. If you overwrite a partition while a dashboard is reading it, the dashboard can see half old numbers and half new ones in the same chart. That is called a torn read, and it is worse than either version alone.

A backfill is a controlled way of publishing corrected history. You build the new version privately, check it, and swap it in at once, while the live pipeline carries on with today's data.

Words you will meet

Backfill
Recomputing historical data, usually after a bug fix or a logic change.
Blast radius
Which tables and which dates are affected by the bug.
Shadow table
A private copy where the corrected data is built, invisible to readers until it is ready.
Torn read
A reader seeing a mix of old and new data because a rewrite was half done.
Atomic swap
Replacing the old data with the new in one step, so readers see one version or the other, never a mixture.
Cutover date
The date from which the live pipeline owns the data. Before it, the backfill owns it.
Validation
Checks that compare the rebuilt data against a trusted calculation before it is published.
Dependent table
A table built from another one, such as a daily revenue mart built from order items. It must be rebuilt too.
Rebuilding history while people are reading it
overwritereadtornRebuild joboverwrites partitionsGold table2 years of historyDashboardreading right now
Press Play to watch it move

Each arrow is one thing happening. Play it step by step, or read the list below.

  1. 1.The rebuild job starts overwriting partitions, one after another, directly in the live table.
  2. 2.At the same moment a dashboard queries the table, and some partitions are old while others are corrected.
  3. 3.The chart shows a mixture of before and after the fix, and nobody can tell which numbers to trust.

The numbers, as things you can picture

  • About 2 years of partitionsHundreds of daily chunks of data to rebuild and check.
  • Cannot lock the table for daysThe business keeps running, so reads and the live pipeline must continue throughout.

What the interviewer wants

Plan a safe rebuild of two years of tables that runs beside production, publishes without torn reads, avoids clashing with the live pipeline, and is validated before cutover.

The interview setup

Design a backfill to reprocess 2 years of history after a bug fix. Discuss atomic swap, incremental backfill by date, and avoiding disruption to live consumers.

Backfill is a publication problem. Wrong numbers already escaped. You must rebuild without torn reads and without fighting today's writer.

Why in-place overwrite is dangerous

BI tools query while you rewrite. Readers can see half-old half-new files. Or writers for today collide with historical rebuilds on the same partition.

Shadow then swap

Write corrected data to a shadow location or shadow table. Validate. Atomically replace partition or swap table metadata.

Incremental by date

Loop date partitions. Parallelize carefully. Isolate live ownership: live pipeline owns date >= cutover; backfill owns history.

Validation

Compare row counts, aggregates, and sampled keys against expectations before publish. Backfill without validation is how you ship a second bug faster.

The expanded problem

  • Rebuild affected tables for ~2 years
  • Publish atomically per partition or via table swap
  • Keep incremental live pipeline running
  • Validate before cutover
  • Bound blast radius and estimate cost/time
For date in history:
  transform --> shadow partition
validate samples / aggregates
atomic replace partition (or swap table)
live pipeline continues for today+

What good looks like

  • Bug impact scoping first
  • Shadow writes
  • Date loop with isolation from live
  • Validation gates
  • Consumer communication

Clarifying questions

  • Which downstreams read which partitions?
  • Bug in transform logic or source data?
  • Can consumers pause or read dual versions?
  • How far does the dependency graph extend (marts on marts)?

Out of scope

  • Fixing the original business logic bug itself in detail
  • Rebuilding the entire company warehouse unrelated tables

Clarifying questions strategy

Ask what changes grain, SLA, retention, or cost. State assumptions when answers are vague.

Scope control

Protect the critical path. Park optional marts after the SLA landing succeeds.

Interview framing details

You are expected to teach while you design. Start from a concrete failure, define the terms, then put the algorithm or layout on the board. Keep the scope tight: one grain, one SLA, one publication method.

Ask only the clarifying questions that would change your diagram. Write assumptions when the interviewer asks you to decide. Call out out-of-scope work so you do not burn the clock on tooling trivia.

A strong close restates the critical path, the failure mode you fear most, and what you would ship in week one.