Two Sources of Truth

A medium 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

medium

Seniority

mid

Asked in

Slalom

Problem

A retail company is moving its on-prem orders database to a cloud warehouse, and analytics teams cannot lose access to current data during the months-long cutover. Design a pipeline that does the one-time bulk load of the existing 2 TB, keeps the warehouse within minutes of the live source as new orders land, and proves the two systems agree before anyone flips reporting over.

Business requirements

• We have 2 TB of history sitting in the old database, and orders keep coming in while we migrate. We need both moved over.
• Analysts run dashboards all day. The warehouse can't be hours behind the live orders table during the migration.
• Before we point reporting at the new warehouse, we have to be sure it matches the old system. A silent gap would be a disaster.

Worked solution and explanation

Why this problem exists in real interviews

Migration reads like 'copy the data once,' and that is the trap. The source is a moving target: 5 million rows change a day while a 2 TB dump runs for hours, so the snapshot is stale the moment it finishes. The real skill being probed is whether you split the load into a bulk backfill plus a change-capture stream, stitch them at a known log position, and refuse to cut over until the two systems are proven equal. Get the stitch wrong and you silently lose or double every order written during the load window, and nobody notices until finance reconciles the quarter.

The whiteboard answer is a nightly full reload of the orders table into the warehouse until the day you flip reporting. It works in the demo and falls apart at 2 TB: the reload takes longer than the night, dashboards trail by a full day, and on cutover day there is no evidence the warehouse actually matches the source. The fix is two paths sized for two jobs, reconciled before anyone trusts them.

Walk the requirements

Step 1: Bulk-load the history once, off a recorded log position

A batch job reads the 2 TB into a staging area in the lake, notes the write-ahead-log position the snapshot was consistent as-of, then a load step moves the staged history into the warehouse. Streaming 2 TB row by row through change capture would take days and hammer the source; the bulk path exists precisely because the backfill is a different job from the steady state. The recorded position is what makes the next step safe.

Step 2: Stream every change after the seam, straight into the warehouse

Change capture reads the source's write-ahead log starting from the snapshot position and applies inserts and updates directly to the warehouse as upserts on the order id. Because it starts exactly where the snapshot ended, nothing between 'dump finished' and 'stream started' falls through a hole, and a duplicated event just overwrites a row with identical data. This path is what keeps the warehouse within minutes of live without a nightly reload.

Step 3: Prove parity before cutover

A reconciliation job compares source and warehouse row counts and per-day order totals as-of a frozen log position. Comparing against the live source is a moving target that never reads zero, so you freeze a position, let the stream catch up to it, then diff. Cutover is gated on that diff being empty or fully explained. Skip this and you are betting the migration on faith.

The shape that fits

# 1) Bulk load: snapshot the 2 TB at a RECORDED log position.
#    That position is the seam between backfill and stream.
seam_lsn = source.current_wal_lsn()            # remember where the snapshot is consistent as-of
snapshot = source.export_snapshot(at=seam_lsn)  # consistent read, does not block writers
staging_lake.write_parquet(snapshot)            # land history in the lake
warehouse.bulk_load(staging_lake.path)          # one-time load of the backfill

# 2) Incremental: start CDC from the SAME position so no row
#    falls in a gap and none is replayed before the seam.
for change in source.stream_wal(start=seam_lsn):
    # idempotent upsert keyed on the order id: a redelivered
    # event overwrites a row with identical data, never duplicates.
    warehouse.upsert(
        table='orders',
        key='order_id',
        row=change.after,
        delete=(change.op == 'delete'),
    )

# 3) Parity: freeze a position, let the stream reach it, then diff.
#    Comparing against the live source never converges.
def cutover_is_safe(check_lsn):
    wait_until(lambda: warehouse.applied_lsn() >= check_lsn)
    src = source.aggregate_as_of(check_lsn)   # row counts + per-day order totals
    wh = warehouse.aggregate()
    diff = reconcile(src, wh)                 # per-table, per-day
    return diff.is_empty()                    # gate cutover on an empty (or explained) diff

Nightly full reload

Re-extracts the whole table every night. Simple to write, but at 2 TB the job outruns the night, dashboards trail a full day, and cutover ships with no proof of correctness.

Bulk plus change capture

Backfills once, then streams deltas. More moving parts and a log-position seam to manage, but the warehouse stays minutes-fresh and reconciliation gives a hard go/no-go signal.

Common follow-up questions

• The source schema changes mid-migration: a column is added to orders. What does your change-capture path do, and where does it break? (Tests whether the candidate has a schema-evolution story for CDC and the downstream warehouse table rather than a static mapping.)
• Parity passes overall but one day's order total is off by a few cents in the warehouse. How do you decide whether to cut over? (Tests triage of a non-empty diff: classify it as rounding, a transform bug, or a real loss, and gate cutover on explanation rather than a raw zero.)