Warehouse-to-Lakehouse Migration: Dual-Write, Backfill, Reconciliation & Rollback

A warehouse to lakehouse migration is the platform project every mature data team eventually signs up for — move a decade of tables off a proprietary warehouse (Snowflake, Redshift, BigQuery, Teradata) and onto an open lakehouse (Delta Lake, Apache Iceberg, Hudi) — and it is the one most often mistaken for a copy job. The tables are not the hard part; the choreography is. A live warehouse is serving hundreds of dashboards, feature pipelines, and finance reports while you migrate, and every one of those consumers assumes the numbers never move. You cannot take an outage, you cannot lose a late-arriving row, and you cannot cut anyone over onto data you have not proven equals the source. That is why a serious migration is not "export, load, repoint" but a four-move loop: dual-write into both systems during an overlap window, backfill the history the stream never saw, reconciliation that proves the two agree cycle after cycle, and a cutover that stays rollback-ready until the evidence is in.

This guide is the senior-data-engineering walkthrough for running that loop the way interviewers probe it: the zero-downtime write path that fans ingestion to both targets without ever endangering the authoritative warehouse, the watermark-boundary backfill that overlaps the seam and dedupes so nothing is lost or double-counted, the tiered data validation ladder that turns "it looked right" into a queryable gate, and the wave-based parallel run cutover with rollback triggers and a decommission gate so the warehouse is retired on evidence rather than on a date. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse the pipelines on the ETL practice library →, and stress-test the checks on the data-validation practice library →.

---

On this page

• Why the migration strategy decides everything downstream
• Dual-write — warehouse and lakehouse in parallel
• Backfill — loading history into the lakehouse
• Reconciliation — proving the two systems agree
• Cutover and rollback
• Cheat sheet — warehouse-to-lakehouse migration recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why the migration strategy decides everything downstream

A warehouse to lakehouse migration is a four-move loop, not a copy job — and the move you skip is the one that pages you at cutover

The one-sentence invariant: a warehouse to lakehouse migration is a four-move loop — dual-write into both systems, backfill the history the stream missed, reconcile the two continuously, then cut over wave by wave with rollback ready — and the move teams under-invest in is never "stand up the lakehouse" but "prove the lakehouse equals the warehouse," which is why reconciliation, not the table format, is where the migration succeeds or fails. Provisioning a Delta or Iceberg catalog is an afternoon. The decade of dashboards, feature pipelines, and finance reports that trust the warehouse's exact answers is what takes quarters — and every one of those consumers hard-codes an assumption about the source's behaviour that a naive copy silently breaks.

The four axes interviewers actually probe.

• Write-path safety. During the overlap, are you fanning each ingestion write to both systems, and is the lakehouse branch isolated so a lakehouse failure can never break the authoritative warehouse write? Interviewers open here because a dual-write that couples the two failure domains turns a migration into an outage of the system you were trying to keep alive.
• Historical completeness. The dual-write stream only captures data from the moment it starts. How do you load the history that predates it — and how do you overlap the backfill and the stream at the seam so no row is lost and no row is counted twice? A migration that "loaded the tables" but left a gap or a double-count on the watermark boundary is not done.
• Reconciliation rigor. How do you prove the lakehouse matches the warehouse? The weak answer is "we spot-checked a few dashboards." The senior answer is a tiered ladder — row counts, then aggregate control totals, then full row-hash checksums — run every cycle during the parallel run, with tolerances and a ledger that gates cutover.
• Cutover and rollback. Big-bang or wave by wave? Is the warehouse still authoritative until you are sure? The senior answer never flips everyone at midnight. It says "repoint readers by wave behind a flag, keep the warehouse authoritative and rollback-ready until N clean reconcile cycles, then decommission behind a gate."

The 2026 reality — the table format is a commodity; the choreography is the migration.

• Table formats converged. Delta Lake, Apache Iceberg, and Hudi all give you ACID commits, time travel, schema evolution, and MERGE. Which one you pick matters far less than how you move onto it. Interviewers who ask "Delta or Iceberg?" are usually testing whether you stay on the choreography instead of relitigating a settled question.
• Dual-write is the safe default. A one-shot bulk copy of a live warehouse is stale the moment it finishes. Fanning writes to both systems during an overlap window keeps the lakehouse current while you backfill history behind it — the two halves meet at a watermark.
• Reconciliation is the gate. No consumer cuts over on "it loaded." A reconcile harness compares the two systems in tiers, every cycle, and clean cycles accumulate toward a sign-off gate. This is the part no tool owns and the part senior interviews drill hardest.
• Rollback is not optional. The warehouse stays authoritative and rollback-ready until the gate passes. Migrations fail when the source is retired on a calendar date instead of on evidence.

What interviewers listen for.

• Do you name all four moves — dual-write, backfill, reconcile, cutover — and put reconciliation at the centre? — senior signal.
• Do you insist the lakehouse write is failure-isolated from the authoritative warehouse write? — required answer.
• Do you describe the watermark seam where backfill and stream overlap-and-dedupe? — senior signal.
• Do you treat tiered reconciliation — count, aggregate, hash — as the thing that gates cutover, not a one-time check? — required answer.
• Do you refuse a big-bang cutover and keep the warehouse rollback-ready to a decommission gate? — senior signal.

Worked example — the four-move migration map

Detailed explanation. The single most useful artifact for a migration interview is a move map that names each move, its exit criterion, and its failure mode. Every senior migration discussion converges on this map; having it in your head keeps you from conflating "the lakehouse has the data" with "the migration is validated." Walk through building the map for a hypothetical Snowflake ANALYTICS warehouse landing on a Delta Lake.

• The estate. ~900 Snowflake tables feeding 300 dashboards, 40 feature pipelines, and a monthly finance close.
• The target. A Delta Lake on S3 with a Unity/Glue catalog, warehouses replaced by Spark/Photon compute.
• The constraint. No consumer may see a wrong or missing number; the finance close migrates last and validates hardest.

Question. Lay out the four moves with an exit criterion and the failure mode each guards against.

Input.

Move	Primary output	Exit criterion	Failure mode if skipped
Dual-write	writes landing in both systems	overlap window live, isolated	lakehouse stale the moment copy ends
Backfill	history loaded up to watermark T	history present, seam overlapped	gap or double-count on the seam
Reconcile	count/aggregate/hash harness	N clean cycles per object	cutover on unverified data
Cutover	reader repoint + rollback + gate	consumers switched, source retired	big-bang outage, no rollback

Code.

Warehouse → Lakehouse — four-move loop (memorise this)
=====================================================

  ┌────────────┐   ┌──────────┐   ┌────────────┐   ┌──────────┐
  │ DUAL-WRITE │──▶│ BACKFILL │──▶│ RECONCILE  │──▶│ CUTOVER  │
  │ fan writes │   │ history  │   │ count/agg/ │   │ flag flip│
  │ to both,   │   │ up to T, │   │ hash every │   │ + rollback│
  │ isolated   │   │ overlap  │   │ cycle      │   │ + retire  │
  └────────────┘   └──────────┘   └────────────┘   └──────────┘
       │                │               │               │
   exit: overlap    exit: history   exit: N clean   exit: readers
   live, lakehouse  loaded, seam    reconcile       switched, source
   write isolated   deduped         cycles (gate)   decommissioned

  Warehouse stays AUTHORITATIVE and ROLLBACK-READY from move 1 to the gate.

Step-by-step explanation.

1. Dual-write exits only when each ingestion write lands in both systems and the lakehouse branch is isolated. The failure it guards is a stale lakehouse: a one-shot copy is out of date the instant it completes, so without a live parallel write the reconciliation can never converge.
2. Backfill exits when the history predating the stream is loaded and the seam — the watermark boundary between "backfilled" and "streamed" — is overlapped and deduped. The trap is a gap (rows between the copy cutoff and the stream start that neither captured) or a double-count (rows both captured).
3. Reconcile is the centre of gravity. Both systems run live; the harness compares them in tiers every cycle. Exit is not "it looked right once" but "N consecutive clean cycles" — the gate.
4. Cutover repoints readers wave by wave behind a flag, keeps the warehouse authoritative and rollback-ready, and retires it only behind a decommission gate. The whole loop keeps the warehouse authoritative from move 1 to that gate — the single rule that makes the migration reversible until it is proven.
5. The moves are a loop, not a line: reconciliation failures push you back to backfill (fix the gap) or dual-write (fix a divergence), and only sustained clean cycles advance you to cutover. Treating it as a one-pass pipeline is the mistake.

Output.

Looks done	The trap	Actually done when
Lakehouse provisioned	"we're migrated!"	nothing is validated yet
Tables loaded	"the data is there"	the numbers are equal, proven
Dual-write flowing	"it's in sync"	the seam is deduped and reconciled
Readers repointed	"cutover complete"	N clean cycles + rollback retired

Rule of thumb. Never call a migration "done" at data-load. Draw the four-move loop, put reconciliation at the centre, and keep the warehouse authoritative until a decommission gate. The move you are tempted to skip — reconciliation — is the move that pages you at cutover.

Worked example — what interviewers actually probe

Detailed explanation. The senior migration interview has a predictable arc: an ambiguous opener ("how would you move our Snowflake warehouse to a lakehouse?"), then progressive narrowing to test whether you know the moves and, crucially, whether you treat validation as the gate. Candidates who name dual-write, the watermark seam, and a rollback plan score highest; candidates who describe "export and load" score lowest. Walk through the grading rubric.

• Ambiguous opener. "How would you migrate our warehouse to Delta/Iceberg?" — invites the four-move loop.
• Follow-up 1. "How do you keep the lakehouse current while you migrate?" — probes dual-write.
• Follow-up 2. "How do you load the history?" — probes backfill and the seam.
• Follow-up 3. "How do you know the lakehouse is correct?" — probes tiered reconciliation.
• Follow-up 4. "How do you cut over 300 dashboards safely?" — probes wave cutover + rollback.

Question. Draft a five-minute senior migration answer that covers all four moves without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Keeping current	"we'd export nightly"	"dual-write to both, lakehouse branch isolated"
History	"just copy the tables"	"backfill to watermark T, overlap the stream, dedupe"
Validation	"spot-check dashboards"	"count → aggregate → row-hash, every cycle, ledger-gated"
Cutover	"flip it over a weekend"	"reader flag by wave, warehouse authoritative, rollback-ready"
Done	"data is loaded"	"N clean cycles + decommission gate"

Code.

Senior lakehouse-migration answer template (5 minutes)
======================================================

Minute 1 — name the four moves up front
  "Dual-write, backfill, reconcile, cut over. The hard move is
   reconciliation, not standing up Delta or Iceberg."

Minute 2 — dual-write
  "During the overlap I fan every ingestion write to both the warehouse
   and the lakehouse, keyed identically so retries converge, and I
   isolate the lakehouse branch so a lakehouse failure never blocks the
   authoritative warehouse write."

Minute 3 — backfill
  "The stream only sees data from when it started, so I bulk-load history
   up to a watermark T and stream from T, deliberately overlapping the
   seam and deduping on the primary key so nothing is lost or doubled."

Minute 4 — reconcile
  "Both systems run in parallel and I reconcile in tiers every cycle:
   row counts, then aggregate control totals, then full row-hash
   checksums. Clean cycles accumulate into a sign-off gate."

Minute 5 — cutover + rollback
  "I repoint readers wave by wave behind a feature flag. The warehouse
   stays authoritative and rollback-ready until N clean cycles and
   consumer sign-off. Only then do I freeze and decommission it. No
   big-bang."

Step-by-step explanation.

1. Minute 1 frames the whole answer around moves with reconciliation at the centre. Weak candidates dive into table-format features ("Iceberg has hidden partitioning…") before naming the program shape; naming the four moves signals you have run one.
2. Minute 2 states the isolation invariant. The tell that separates a real migration from a docs read is insisting the lakehouse write cannot break the warehouse — same-key idempotency plus an isolated failure domain.
3. Minute 3 names the seam. Saying "overlap and dedupe at watermark T" shows you know the one place a live migration silently loses or doubles rows; "just copy the tables" does not.
4. Minute 4 puts the tiered reconciliation at the heart of the parallel run. Naming three tiers — count, aggregate, hash — and "every cycle" shows you treat validation as continuous evidence, not a one-time check.
5. Minute 5 refuses the big-bang and keeps rollback alive to a decommission gate. Showing you keep the warehouse authoritative until proven is the single strongest senior signal in a migration interview.

Output.

Grading criterion	Weak score	Senior score
Names four moves in minute 1	rare	mandatory
Isolates the lakehouse write	rare	required
Names the watermark seam	occasional	senior signal
Tiered reconciliation as the gate	rare	senior signal
Wave cutover + rollback + gate	rare	senior signal

Rule of thumb. The senior migration answer is a five-minute monologue: four moves, reconciliation at the centre, an isolated dual-write, a deduped watermark seam, and a wave cutover with rollback to a decommission gate. Rehearse it once; deploy it every interview.

Worked example — the "which table migrates first" decision tree

Detailed explanation. Given a large estate, the senior architect runs a short decision tree to order the migration waves. Codifying it makes the plan defensible: any stakeholder can hand you a table and you can place it. Walk the tree with three canonical datasets — a low-risk marketing event table, a heavily consumed conformed dimension, and the finance close fact.

• Q1. Does anything downstream depend on this table's outputs? → many dependents = migrate early behind a bridge; none = pilot candidate.
• Q2. How correctness-sensitive is it? → low = early; high (finance) = last, hardest validation.
• Q3. How write-hot is it? → hot = the dual-write path is exercised early here; cold = trivial.
• Q4. Does history matter, or is it append-only recent? → deep history = big backfill budget; recent-only = light.

Question. Walk the tree for the three datasets and record the wave each lands in.

Input.

Dataset	Q1 dependents?	Q2 critical?	Q3 write-hot?	Q4 deep history?
Marketing events	no	low	hot	shallow
Conformed dim_customer	many	high	warm	deep
Finance close fact	some	high	warm	deep

Code.

# Wave-ordering helper (illustrative)
def place_wave(has_dependents: bool,
               business_critical: bool,
               write_hot: bool,
               deep_history: bool) -> str:
    """Return the migration wave for a table."""
    if has_dependents and business_critical:
        return "Wave 0 - migrate first behind a compatibility bridge"
    if not has_dependents and not business_critical:
        return "Wave 1 (pilot) - low risk, exercises the machinery"
    if business_critical and deep_history:
        return "Wave 3 (last) - hardest validation, biggest backfill"
    return "Wave 2 - standard risk"


print(place_wave(False, False, True,  False))
# -> Wave 1 (pilot) - low risk, exercises the machinery

print(place_wave(True,  True,  False, True))
# -> Wave 0 - migrate first behind a compatibility bridge

print(place_wave(True,  True,  False, True))   # finance held back by risk
# -> Wave 0 ... but risk-adjusted to Wave 3 (see step 3)

Step-by-step explanation.

1. The marketing event table has no dependents and low criticality — the ideal pilot. Its real value is exercising the machinery (dual-write fan-out, backfill, reconcile, flag flip) on something that cannot hurt the business if a cycle fails; being write-hot means it stress-tests the dual-write path early.
2. Conformed dim_customer is a shared dependency: many facts join to it. It must migrate first (Wave 0) behind a compatibility bridge so both systems read consistent keys during the overlap — otherwise joins split across the two platforms.
3. The finance close fact is correctness-critical with deep history. The topological rule would place it early (facts feed the close), but risk overrides dependency order: it is held to the last wave with the biggest backfill and validation budget, because a wrong number in the close is the failure the whole program exists to prevent.
4. The tree is deliberately shallow — four questions — so it is whiteboard-able. An interviewer can hand you any dataset and you place it in under a minute, which is exactly the fluency the wave-planning question tests.
5. The ordering is dependency-first, risk-last: shared dimensions go first behind a bridge, a low-risk table pilots the machinery, and the correctness-critical, deep-history table goes last. That framing is the senior signal.

Output.

Dataset	Wave	Why
dim_customer	Wave 0	shared dependency; bridge during overlap
Marketing events	Wave 1 (pilot)	low risk; exercises dual-write + reconcile
(standard tables)	Wave 2	normal risk
Finance close fact	Wave 3 (last)	critical + deep history; hardest validation

Rule of thumb. Order waves dependency-first and risk-last: shared dimensions go first behind a bridge, a low-risk write-hot table pilots the machinery, and the correctness-critical, deep-history table goes last with the biggest backfill and validation budget. Never pilot on the finance close.

Senior interview question on migration strategy

A senior interviewer often opens with: "You inherit a Snowflake warehouse feeding 300 dashboards and 40 feature pipelines, and leadership wants to be on an open lakehouse in two quarters with no downtime. Walk me through how you'd sequence the migration, where the risk actually lives, and how you'd prove — not assert — that the lakehouse returns the same numbers before anyone cuts over."

Solution Using a four-move loop anchored on continuous reconciliation and a decommission gate

Program plan - Warehouse -> Lakehouse (2 quarters)
==================================================

Q1  Move 1 Dual-write (all waves) + Move 2 Backfill (Wave 0/1)
    - Tee ingestion writes to warehouse + lakehouse, lakehouse isolated
    - Bulk-load history to watermark T for conformed dims + pilot
    - Overlap the seam; dedupe on primary key

Q2  Move 3 Reconcile (all waves) + Move 4 Cutover (as each gate passes)
    - Reconcile harness online: count -> aggregate -> row-hash, every cycle
    - Accumulate clean cycles per table into the ledger
    - Repoint readers wave by wave behind a flag as each hits its gate
    - Warehouse stays authoritative + rollback-ready until decommission gate

-- The gate, expressed as data: a table cannot cut over until it has
-- accumulated N consecutive clean reconciliation cycles.
CREATE TABLE migration.reconcile_ledger (
    object_name     STRING   NOT NULL,
    cycle_ts        TIMESTAMP NOT NULL,
    count_match     BOOLEAN  NOT NULL,
    aggregate_match BOOLEAN  NOT NULL,
    hash_match      BOOLEAN  NOT NULL
);

-- Cutover-eligibility view: 5 consecutive clean cycles in the last 7 days
CREATE OR REPLACE VIEW migration.cutover_eligible AS
SELECT object_name
FROM (
    SELECT object_name,
           count(*) AS clean_cycles
    FROM   migration.reconcile_ledger
    WHERE  count_match AND aggregate_match AND hash_match
      AND  cycle_ts > current_timestamp() - INTERVAL 7 DAYS
    GROUP  BY object_name
)
WHERE clean_cycles >= 5;   -- the gate

Step-by-step trace.

Phase	Moves active	Exit evidence
Q1 early	dual-write (all)	each write lands in both; lakehouse isolated
Q1 late	backfill (W0/W1)	history to T loaded; seam deduped
Q2 early	reconcile (all)	ledger filling; clean cycles accruing
Q2 late	cutover (per wave)	each wave hits >= 5 clean cycles -> flag flips
gate	rollback retired per wave	warehouse frozen only after decommission gate

After the program runs, no wave cuts over until migration.cutover_eligible lists it — five consecutive clean count/aggregate/hash cycles. Finance (Wave 3) accumulates the longest clean streak because its correctness bar is highest. The warehouse stays authoritative and rollback-ready for every wave until that wave's decommission gate passes; only then is the source schema frozen and retired.

Output:

Cutover risk	Big-bang (rejected)	Four-move loop (chosen)
Correctness evidence	"it looked right"	5 clean count/agg/hash cycles per object
Rollback	none after flip	warehouse authoritative until gate
Blast radius of a bug	all 300 dashboards	one wave's consumers
Downtime	migration freeze window	none — dual-write keeps both live
Decommission trigger	date on a slide	evidence-based gate

Why this works — concept by concept:

• Four-move sequencing — dual-write → backfill → reconcile → cutover makes the program legible and gives each move an exit criterion. The risk is front-loaded into keeping both systems live and centred on validation, not on the table-format choice.
• Isolated dual-write — fanning writes to both systems with an isolated lakehouse branch keeps the warehouse authoritative and the lakehouse current at the same time, which is the only way reconciliation can ever converge on a moving target.
• Reconcile ledger as the gate — cutover eligibility is data, not a judgment call: a table cuts over only after N consecutive clean count/aggregate/hash cycles. The gate is queryable and auditable.
• Warehouse authoritative until decommission — keeping the source rollback-ready until the gate passes makes every wave reversible. Migrations fail when the warehouse is retired on a calendar date instead of on evidence.
• Cost — the parallel-run window doubles storage and write compute for the overlap (both systems live) and the reconcile harness costs engineering time — but it buys evidence-based, zero-downtime cutover with O(1)-per-wave blast radius instead of O(all) big-bang risk. The extra spend is a few weeks of overlap; the avoided cost is a finance-close incident and a full rollback.

ETL
Topic — etl
ETL problems on migration and incremental pipelines

Practice →

Design Topic — design Design problems on platform migration programs

Practice →

---

2. Dual-write — warehouse and lakehouse in parallel

dual-write fans one ingestion write to both systems — same key, idempotent, and isolated so the lakehouse can never break the warehouse

The mental model in one line: dual-write is the move where, during the migration overlap window, every ingestion write is fanned to both the authoritative warehouse and the new lakehouse using the same idempotent key, with the lakehouse branch running in an isolated failure domain so a lakehouse outage degrades only the migration — never the production warehouse — which is what makes the migration zero-downtime and keeps the two systems close enough that reconciliation can converge. Every senior data engineer who has run a live migration has learned the hard way that a coupled dual-write turns a lakehouse hiccup into a warehouse outage.

The four axes for dual-write.

• Where the fan-out lives. Three options: in the application/producer, in the ingestion/ETL job, or by tee-ing an existing CDC/Kafka stream. Streaming tee is the cleanest for warehouses already fed by CDC; a job-level fan-out is simplest when a nightly Spark job is the single writer. Fanning out in application code is the most invasive and the most error-prone.
• Idempotency. Both sides must converge under retries. The write is a MERGE (upsert) keyed on the business primary key plus a version/sequence, not a blind INSERT — so a replayed batch produces the same final state on both systems rather than duplicating rows on one.
• Failure isolation. The lakehouse write must be decoupled from the warehouse write's success. The warehouse commit is the source of truth during the overlap; the lakehouse write is best-effort-plus-retry (async queue, separate task, dead-letter). A lakehouse failure raises an alert and backfills later — it does not fail the pipeline.
• Ordering and late data. Both systems must apply updates in a consistent order. A version column (_ingested_at, _seq, or a monotonic CDC LSN) lets the MERGE reject stale overwrites, so out-of-order retries and late-arriving updates land identically on both sides.

Where the write happens — three patterns.

• Producer-level. The application publishes to two sinks. Maximum coupling, maximum blast radius; avoid unless the producer already has an outbox.
• Job-level. A single ETL job that already writes the warehouse gains a second write to the lakehouse. Easiest to reason about; the fan-out is one function with one shared batch and one shared key.
• Stream-tee. A Kafka/CDC topic already feeds the warehouse; add a second consumer group that writes the lakehouse. Fully decoupled by construction — the lakehouse consumer can lag or fail without touching the warehouse sink.

Idempotency — the same key on both sides.

• Primary key. The natural business key (order_id, customer_id) is the MERGE key on both systems. Never rely on an auto-increment surrogate that differs across the two.
• Version guard. A monotonic _seq or _ingested_at column lets WHEN MATCHED AND source._seq > target._seq THEN UPDATE reject stale writes. This is what makes replay safe.
• Deterministic transforms. Any transform applied before the write must be pure — same input, same output — or the two systems diverge on a retry even with the same key.

Failure isolation — the invariant that protects production.

• Warehouse first, lakehouse best-effort. During the overlap the warehouse commit gates pipeline success; the lakehouse write is enqueued and retried out of band.
• Dead-letter, don't fail. A lakehouse write that exhausts retries lands in a dead-letter table; a reconcile/backfill job repairs it. The pipeline stays green.
• Separate compute. Run the lakehouse writer on separate workers/queues so lakehouse backpressure cannot starve the warehouse write.

Common interview probes on dual-write.

• "How do you keep the lakehouse current during migration?" — dual-write with an idempotent, isolated lakehouse branch.
• "What if the lakehouse write fails?" — dead-letter + retry; the warehouse write is unaffected; a backfill repairs the gap.
• "How do retries not double-count?" — MERGE on the business key with a version guard, not blind INSERT.
• "Where do you put the fan-out?" — tee the existing CDC stream if there is one; otherwise a job-level fan-out; never invasive producer changes.

Worked example — tee-ing a Spark write to both systems

Detailed explanation. The canonical job-level dual-write: a nightly Spark job that already upserts the warehouse gains a second, idempotent upsert into the lakehouse from the same transformed DataFrame, with the lakehouse write wrapped so its failure cannot fail the job. Build it.

• Source. One transformed orders DataFrame per batch.
• Warehouse write. Existing MERGE into Snowflake — authoritative, gates success.
• Lakehouse write. New MERGE into a Delta table — same key, try/except isolated.

Question. Write the Spark job that upserts both systems from one DataFrame with the lakehouse branch isolated.

Input.

Component	Value
Batch source	transformed orders DataFrame
Merge key	order_id
Version guard	_ingested_at
Warehouse	Snowflake (authoritative)
Lakehouse	Delta Lake table lake.orders

Code.

# Nightly dual-write job — one DataFrame, two idempotent upserts
from delta.tables import DeltaTable

def dual_write_orders(spark, batch_df):
    """Warehouse write is authoritative; lakehouse write is isolated."""

    # 1. Warehouse upsert (authoritative) — failure fails the job (as today)
    (batch_df.write
        .format("snowflake")
        .options(**SNOWFLAKE_OPTS)
        .option("dbtable", "ANALYTICS.ORDERS")
        .mode("append")               # a MERGE proc runs on the SF side
        .save())

    # 2. Lakehouse upsert (best-effort) — isolated so it can never fail the job
    try:
        target = DeltaTable.forName(spark, "lake.orders")
        (target.alias("t")
            .merge(batch_df.alias("s"), "t.order_id = s.order_id")
            .whenMatchedUpdateAll(condition="s._ingested_at > t._ingested_at")
            .whenNotMatchedInsertAll()
            .execute())
    except Exception as e:            # noqa: BLE001 — deliberate broad catch
        # Do NOT re-raise: the warehouse write already succeeded.
        dead_letter(batch_df, reason=str(e))
        alert("lakehouse dual-write failed; dead-lettered batch", error=e)

Step-by-step explanation.

1. The warehouse write runs first and keeps its existing semantics — if it fails, the job fails, exactly as before the migration. The warehouse is authoritative during the overlap, so nothing about its behaviour changes.
2. The lakehouse write is a Delta MERGE on the same order_id key. whenMatchedUpdateAll(condition="s._ingested_at > t._ingested_at") is the version guard: a replayed or out-of-order batch only overwrites when it is strictly newer, so retries converge instead of flapping.
3. whenNotMatchedInsertAll() inserts genuinely new rows. Combined with the matched-update guard, the MERGE is fully idempotent: running the same batch twice yields the same final Delta state.
4. The try/except is the isolation boundary. A Delta commit conflict, an S3 throttle, or a catalog outage is caught, the batch is dead-lettered, and an alert fires — but the exception is not re-raised, so the job stays green and the authoritative warehouse write is never rolled back.
5. The dead-letter table is the repair hook: a separate reconcile/backfill job drains it into the lakehouse later, so an isolated failure becomes a bounded lag, not lost data.

Output.

Scenario	Warehouse	Lakehouse	Job status
Both succeed	committed	merged	green
Retry same batch	idempotent	idempotent (version guard)	green
Lakehouse S3 throttle	committed	dead-lettered + retried	green
Warehouse fails	fails	not attempted	red (as today)

Rule of thumb. Fan the dual-write out of one transformed DataFrame with one business key, make the lakehouse side a version-guarded MERGE, and wrap it so its failure dead-letters instead of failing the job. The warehouse stays authoritative; the lakehouse catches up.

Worked example — the idempotent MERGE key that survives retries

Detailed explanation. A dual-write that uses a blind INSERT on the lakehouse side double-counts on every retry — and retries are guaranteed in any real pipeline (task restarts, speculative execution, at-least-once streams). The fix is a MERGE keyed on the business PK with a version column that rejects stale updates. Walk through why blind insert breaks and how the guarded merge fixes it.

• The bug. A retried micro-batch re-inserts rows already present on the lakehouse; warehouse (upsert) stays correct, lakehouse (insert) inflates counts.
• The fix. MERGE ... WHEN MATCHED AND source._seq > target._seq THEN UPDATE ... WHEN NOT MATCHED THEN INSERT.
• The guard. _seq is a monotonic per-key sequence (CDC LSN, or _ingested_at at micro-batch granularity).

Question. Show the divergence a blind insert causes and the guarded MERGE that removes it.

Input.

Event	Blind INSERT (lakehouse)	Guarded MERGE (lakehouse)
First delivery of order 42 (_seq=10)	insert row	insert row
Retry of the same batch	insert duplicate	matched, _seq not greater — skip
Later update order 42 (_seq=12)	insert third row	matched, _seq greater — update
Stale replay (_seq=11)	insert fourth row	matched, _seq not greater — skip

Code.

-- Idempotent lakehouse upsert (Delta / Iceberg SQL) — the version guard
MERGE INTO lake.orders AS t
USING batch_orders    AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s._seq > t._seq THEN
    UPDATE SET *                       -- newer version wins
WHEN MATCHED AND s._seq <= t._seq THEN
    -- stale or duplicate delivery: do nothing (idempotent)
    UPDATE SET t._seq = t._seq         -- no-op keeps the row untouched
WHEN NOT MATCHED THEN
    INSERT *;                          -- genuinely new key

# How _seq is assigned so it is monotonic per key
from pyspark.sql import functions as F

def add_seq(df, cdc_lsn_col=None):
    """Prefer the CDC log position; fall back to ingestion time."""
    if cdc_lsn_col:
        return df.withColumn("_seq", F.col(cdc_lsn_col).cast("long"))
    # Fallback: microsecond ingestion timestamp (monotonic enough per key
    # when the same key is not updated twice within the same microsecond)
    return df.withColumn("_seq", (F.col("_ingested_at").cast("double") * 1e6).cast("long"))

Step-by-step explanation.

1. The blind INSERT is correct exactly once. The warehouse tolerates retries because its write is already an upsert; the lakehouse, if it only inserts, gains a duplicate row on every redelivery. Because streams and Spark tasks are at-least-once, this is not an edge case — it is the normal case.
2. The MERGE keyed on order_id collapses all deliveries of the same key onto one row. The s._seq > t._seq condition is the version guard: only a strictly-newer version updates the row, so a duplicate (_seq equal) or a stale replay (_seq lower) is a no-op.
3. _seq must be monotonic per key. A CDC log sequence number (LSN/GTID) is ideal. When the source is a stream without an LSN, a microsecond-resolution _ingested_at is a workable proxy as long as the same key is not updated twice inside one microsecond.
4. The stale-replay case (_seq=11 arriving after _seq=12) is the subtle one: without the guard it both duplicates and reverts the row to older values. The guard makes late/out-of-order delivery safe — the newest version always wins regardless of arrival order.
5. Because the guarded MERGE is a pure function of the key's highest-seq version, the lakehouse converges to the same state as the warehouse no matter how many times a batch is replayed. That convergence is the precondition for reconciliation to ever pass.

Output.

Delivery order	Blind INSERT rows for order 42	Guarded MERGE state for order 42
10, retry 10	2 rows (wrong)	1 row @ _seq 10
10, 12	2 rows (wrong)	1 row @ _seq 12
10, 12, stale 11	3 rows (wrong)	1 row @ _seq 12 (correct)

Rule of thumb. Never blind-INSERT on the lakehouse side of a dual-write. Upsert with a MERGE on the business key guarded by a monotonic _seq, so retries, duplicates, and out-of-order deliveries all converge to the newest version — the only way the two systems stay reconcilable.

Worked example — failure-isolated async lakehouse writer

Detailed explanation. For high-throughput warehouses fed by a stream, the cleanest dual-write is a separate consumer group that writes the lakehouse independently of the warehouse sink. The two share the source topic but nothing else — the lakehouse consumer can lag, crash, or be paused without touching the warehouse. Walk through the tee.

• Source. A Kafka/CDC topic that already feeds the warehouse sink.
• Warehouse sink. Existing consumer group A — unchanged, authoritative.
• Lakehouse sink. New consumer group B — independent offsets, independent failure.

Question. Design the stream-tee so the lakehouse write is fully isolated and independently resumable.

Input.

Component	Value
Source topic	cdc.orders
Warehouse consumer	group wh-sink (unchanged)
Lakehouse consumer	group lake-sink (new)
Isolation	independent offsets + independent compute
Resume	lake-sink resumes from its own committed offset

Code.

# Lakehouse consumer — its own group, its own offsets, its own failure domain
def run_lake_sink(spark):
    stream = (spark.readStream
        .format("kafka")
        .option("kafka.bootstrap.servers", BROKERS)
        .option("subscribe", "cdc.orders")
        .option("startingOffsets", "earliest")   # bounded by backfill watermark
        .option("kafka.group.id", "lake-sink")    # independent of wh-sink
        .load())

    parsed = parse_debezium(stream)               # -> order_id, _seq, cols...

    def upsert_batch(batch_df, batch_id):
        target = DeltaTable.forName(spark, "lake.orders")
        (target.alias("t")
            .merge(batch_df.alias("s"), "t.order_id = s.order_id")
            .whenMatchedUpdateAll(condition="s._seq > t._seq")
            .whenNotMatchedInsertAll()
            .execute())

    (parsed.writeStream
        .foreachBatch(upsert_batch)
        .option("checkpointLocation", "s3://lake/_checkpoints/orders")  # own checkpoint
        .trigger(processingTime="1 minute")
        .start())

Step-by-step explanation.

1. The lakehouse consumer subscribes to the same cdc.orders topic as the warehouse sink but under a different group.id (lake-sink). Kafka tracks its offsets independently, so its progress and its failures are entirely its own.
2. foreachBatch runs the idempotent guarded MERGE per micro-batch. Because the merge is version-guarded, Spark's at-least-once foreachBatch semantics (a batch can re-run after a failure) do not double-write — the retry converges.
3. The Delta write uses its own checkpointLocation. If the lakehouse consumer crashes, it resumes from its own committed offset + checkpoint; the warehouse sink never noticed, because it commits its own offsets in its own group.
4. Isolation is structural, not defensive: there is no shared transaction, no shared compute, and no shared offset store between the two sinks. A lakehouse S3 outage stalls only lake-sink; when it recovers it drains the backlog and reconciliation re-converges.
5. The overlap with backfill is handled by startingOffsets: the stream starts at the watermark the backfill loaded up to (next section), so the seam is deliberately overlapped and the guarded MERGE dedupes it — no gap, no double-count.

Output.

Failure	wh-sink (warehouse)	lake-sink (lakehouse)
Lakehouse S3 outage	unaffected	stalls, resumes from checkpoint
Lakehouse consumer crash	unaffected	resumes from own offset
Warehouse sink lag	independent	unaffected
Redelivered offset	idempotent	idempotent (version guard)

Rule of thumb. When the warehouse is already stream-fed, dual-write by adding a second consumer group with its own offsets, checkpoint, and compute. Isolation-by-construction beats try/except: the lakehouse sink can fail, lag, or be paused with zero effect on the authoritative warehouse.

Senior interview question on dual-write

A senior interviewer might ask: "Your warehouse is fed by a Debezium → Kafka stream and a nightly Spark aggregation job. You want to start dual-writing to a Delta lakehouse for the migration. Design the dual-write so retries never double-count, the lakehouse write can never break the warehouse, and a lakehouse outage self-heals. Cover the fan-out point, the idempotency key, the failure isolation, and how the seam with the backfill is handled."

Solution Using an isolated stream-tee with a version-guarded MERGE and dead-letter self-heal

# 1. Stream-tee: independent lakehouse consumer group
def lake_sink(spark):
    stream = (spark.readStream.format("kafka")
        .option("kafka.bootstrap.servers", BROKERS)
        .option("subscribe", "cdc.orders")
        .option("kafka.group.id", "lake-sink")            # isolated group
        .option("startingOffsets", BACKFILL_WATERMARK)    # overlap the seam
        .load())

    def upsert(batch_df, _id):
        try:
            t = DeltaTable.forName(spark, "lake.orders")
            (t.alias("t")
              .merge(batch_df.alias("s"), "t.order_id = s.order_id")
              .whenMatchedUpdateAll(condition="s._seq > t._seq")   # version guard
              .whenNotMatchedInsertAll()
              .execute())
        except Exception as e:                             # isolation boundary
            dead_letter("lake.orders", batch_df, str(e))
            # swallow: warehouse sink (separate group) is unaffected

    (parse_debezium(stream).writeStream
        .foreachBatch(upsert)
        .option("checkpointLocation", "s3://lake/_ckpt/orders")
        .start())

-- 2. Dead-letter drain (self-heal) — runs every 15 min, idempotent
MERGE INTO lake.orders AS t
USING (SELECT * FROM lake.orders_deadletter WHERE resolved = false) AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s._seq > t._seq THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

UPDATE lake.orders_deadletter SET resolved = true WHERE resolved = false;

# 3. The nightly aggregation job dual-writes the same way, from one DataFrame
def dual_write_daily_agg(spark, agg_df):
    write_warehouse(agg_df)              # authoritative; fails the job if it fails
    try:
        merge_delta("lake.daily_agg", agg_df, key="grain_key", seq="_ingested_at")
    except Exception as e:
        dead_letter("lake.daily_agg", agg_df, str(e))   # never re-raise

Step-by-step trace.

Concern	Answer	Reasoning
Fan-out point	stream-tee (new consumer group) + job-level for the batch agg	isolation by construction
Idempotency	MERGE on order_id guarded by _seq	retries + out-of-order converge
Failure isolation	separate group/checkpoint; try/except → dead-letter	lakehouse never fails the warehouse
Self-heal	dead-letter drain every 15 min	isolated failure becomes bounded lag
Seam with backfill	startingOffsets = BACKFILL_WATERMARK	overlap + guarded merge dedupes

After deployment, the warehouse sink is byte-for-byte unchanged; the lakehouse sink runs as an independent consumer group that tails the same topic from the backfill watermark. Retries and out-of-order events converge via the version guard; an S3 or catalog outage dead-letters the affected batches and a 15-minute drain repairs them; the warehouse never observes any of it.

Output:

Metric	Value
Warehouse write path change	none (authoritative)
Lakehouse write semantics	idempotent version-guarded MERGE
Double-count on retry	0 (guard rejects stale/equal _seq)
Warehouse impact of lakehouse outage	none (isolated group + swallow)
Self-heal lag after outage	<= 15 min (dead-letter drain)
Seam handling	overlap from watermark + dedupe

Why this works — concept by concept:

• Stream-tee isolation — a separate consumer group with its own offsets, checkpoint, and compute means the lakehouse write shares only the source topic with the warehouse. There is no transaction, offset store, or worker in common, so a lakehouse failure is structurally incapable of touching the warehouse.
• Version-guarded MERGE — WHEN MATCHED AND s._seq > t._seq makes the upsert a pure function of the highest-seq version per key. Retries, duplicates, and out-of-order deliveries all converge, which is the precondition for reconciliation.
• Dead-letter self-heal — catching the lakehouse exception and dead-lettering (instead of re-raising) converts an isolated failure into a bounded lag that a periodic idempotent drain repairs. The pipeline stays green.
• Watermark-aligned start — starting the stream at the backfill watermark deliberately overlaps the seam; the guarded MERGE dedupes the overlap so history and live data meet without a gap or a double-count.
• Cost — one extra consumer group's compute and one dead-letter table per stream, plus a second write per batch job. In exchange the warehouse write path is untouched and the lakehouse stays continuously current — O(1) extra work per event, with the warehouse's failure domain fully preserved. Compared to an invasive producer-level dual-write, this is dramatically lower blast radius.

ETL
Topic — etl
ETL problems on idempotent upserts and dual-write

Practice →

Data Processing Topic — data-processing Data-processing problems on streaming writes

Practice →

---

3. Backfill — loading history into the lakehouse

backfill loads the history the stream never saw — up to a watermark T, overlapping the seam so nothing is lost or double-counted

The mental model in one line: backfill is the move where you bulk-copy every historical partition that predates the dual-write stream into the lakehouse up to a watermark T, start the stream from a point at or before T, and deliberately overlap the seam so the idempotent MERGE dedupes it — which is the only way to fill years of history without a gap (rows neither the copy nor the stream captured) or a double-count (rows both captured), while throttling the copy so it never starves the live warehouse. Every senior data engineer has been burned once by a backfill that either missed the seam or ran unthrottled and knocked over production.

The four axes for backfill.

• The watermark boundary. Pick a watermark T (a timestamp or a CDC position). Backfill everything <= T; stream everything >= T' where T' <= T. The overlap [T', T] is intentional — it is where the two halves meet, and it must be deduped by the idempotent key, not assumed away.
• Resumability. A multi-terabyte backfill will be interrupted (spot reclaims, throttles, deploys). It must checkpoint per-partition so a restart skips completed partitions rather than re-copying from zero. Idempotent MERGE per partition makes a re-run of an in-flight partition safe.
• Throttling. The backfill reads the source warehouse (or its external stage), which is still serving production. Cap concurrency and read rate so the copy never contends with live queries. Prefer reading from an unloaded stage/export over hammering the live warehouse.
• Ordering vs the stream. Backfilled rows carry their original _seq; live rows carry theirs. Because the seam is deduped by _seq, a live update that supersedes a backfilled row wins even if the backfill lands later — the version guard, not wall-clock arrival, decides.

The watermark seam — the one place migrations lose or double rows.

• Gap failure. Backfill copies < T_copy; stream starts at T_stream > T_copy. Rows in (T_copy, T_stream) are captured by neither — silent data loss. Prevent by ensuring T_stream <= T_copy (stream starts before the copy cutoff).
• Double-count failure. Without an idempotent key, rows in the overlap [T_stream, T_copy] are written by both — inflated counts. Prevent by upserting on the business key so the overlap dedupes.
• The correct recipe. Choose the stream start first (e.g. the earliest retained CDC offset), copy history through a T at or after that offset, and let the guarded MERGE reconcile the overlap.

Resumable, partitioned backfill.

• Partition unit. Backfill by natural partition (day, month, or key range). Each partition is an independent, idempotent unit of work.
• Progress ledger. A backfill_progress(partition, status, rows, checksum) table records each partition's completion so a restart resumes.
• Parallelism with a cap. Run K partitions concurrently, bounded so the source warehouse read stays under its budget.

Throttling — don't starve production.

• Read from a stage, not the live warehouse. Export/unload once to S3/ADLS, then backfill from files — this decouples the copy from live query compute entirely.
• Rate-limit if reading live. If you must read the warehouse, cap warehouse size / concurrency and run in off-peak windows.
• Backpressure. Watch source-warehouse queue depth; pause the backfill if live latency degrades.

Common interview probes on backfill.

• "How do backfill and stream not double-count?" — overlap the seam and dedupe on the idempotent key.
• "How do you avoid a gap at the seam?" — start the stream before the backfill cutoff.
• "How do you resume a failed 10 TB backfill?" — per-partition progress ledger + idempotent partition merge.
• "How do you not knock over production?" — backfill from an unloaded stage, throttle, off-peak.

Worked example — the watermark boundary and overlap

Detailed explanation. The canonical seam design: choose the stream start as the earliest retained CDC offset T_stream, backfill all history through T_copy >= T_stream, and let the idempotent MERGE dedupe the overlap [T_stream, T_copy]. Walk through why the ordering of the two cutoffs is what prevents both failure modes.

• Stream start. T_stream = earliest retained Kafka offset (say, 7 days of retention → 7 days ago).
• Copy cutoff. T_copy = now (or any point >= T_stream).
• Overlap. [T_stream, T_copy] is written by both; deduped by MERGE on order_id + _seq.

Question. Show how choosing T_stream <= T_copy plus an idempotent overlap prevents both the gap and the double-count.

Input.

Quantity	Value	Role
T_stream	7 days ago (earliest offset)	stream starts here
T_copy	now	backfill copies through here
Overlap	last 7 days	written twice, deduped
Dedupe key	order_id + _seq	resolves the overlap

Code.

# 1. Choose cutoffs so the stream starts BEFORE the copy ends (no gap)
T_stream = earliest_retained_offset("cdc.orders")   # e.g. 7 days ago
T_copy   = now()                                     # >= T_stream  (overlap!)
assert T_stream <= T_copy, "stream must start at or before copy cutoff"

# 2. Backfill everything up to T_copy from an UNLOADED stage (not live WH)
backfill_df = spark.read.parquet("s3://export/orders/")   # one-time unload
backfill_df = backfill_df.filter(F.col("event_ts") <= F.lit(T_copy))

# 3. Idempotent partition merge into the lakehouse
def merge_partition(part_df):
    t = DeltaTable.forName(spark, "lake.orders")
    (t.alias("t")
        .merge(part_df.alias("s"), "t.order_id = s.order_id")
        .whenMatchedUpdateAll(condition="s._seq > t._seq")   # overlap dedupes here
        .whenNotMatchedInsertAll()
        .execute())

# 4. The stream (section 2) starts at T_stream, so [T_stream, T_copy] is
#    written by BOTH — and the version-guarded merge keeps exactly one row.

Step-by-step explanation.

1. The ordering T_stream <= T_copy is the whole game. If the stream started after the copy ended, rows committed in the gap (T_copy, T_stream) would be seen by neither — silent loss. Starting the stream at or before the copy cutoff guarantees the two ranges touch or overlap.
2. Reading the backfill from a one-time unloaded export (s3://export/orders/) rather than the live warehouse decouples the heavy historical read from production query compute — the copy cannot contend with live dashboards.
3. The overlap [T_stream, T_copy] is written twice: once by the backfill and once by the stream. This is intentional, not a bug — it is the insurance against a gap. The cost of the overlap is paid back by the dedupe.
4. The version-guarded MERGE on order_id + _seq resolves the overlap deterministically: whichever write carries the higher _seq wins, and equal/lower _seq is a no-op. So a row in the overlap ends up present exactly once at its newest version, regardless of whether backfill or stream landed it first.
5. Because backfilled rows carry their original _seq, a live update that supersedes a historical row wins even if the backfill physically writes later — arrival order is irrelevant; only _seq decides. This is what lets backfill and stream run concurrently without coordination.

Output.

Row event_ts	Captured by	Rows after MERGE	Correct?
30 days ago	backfill only	1	yes
5 days ago (overlap)	backfill + stream	1 (newest _seq)	yes
5 days ago, later update	stream	1 (updated)	yes
1 hour ago	stream only	1	yes

Rule of thumb. Choose the stream start before the backfill cutoff, treat the overlap as deliberate insurance, and let an idempotent MERGE on the business key + _seq dedupe it. A gap comes from starting the stream too late; a double-count comes from a non-idempotent write — the seam recipe defeats both.

Worked example — partition-parallel resumable backfill

Detailed explanation. A multi-terabyte backfill must survive interruption and run in parallel without re-copying completed work. The pattern is a backfill_progress ledger plus per-partition idempotent merges, driven K-at-a-time. Build it for a orders table partitioned by day.

• Partition unit. One day of orders.
• Ledger. backfill_progress(partition_day, status, rows, checksum).
• Driver. Submit pending partitions K-at-a-time; mark done on success.

Question. Write the resumable partition driver and the ledger it consults.

Input.

Component	Value
Partition	event_day
Ledger	backfill_progress
Concurrency	K = 8 partitions
Idempotency	per-partition guarded MERGE

Code.

-- Progress ledger — one row per partition
CREATE TABLE migration.backfill_progress (
    partition_day  DATE     NOT NULL,
    status         STRING   NOT NULL,   -- 'pending','running','done','failed'
    rows           BIGINT,
    checksum       STRING,
    updated_at     TIMESTAMP DEFAULT current_timestamp()
);

# Resumable partition-parallel backfill driver
from concurrent.futures import ThreadPoolExecutor

def backfill_partition(spark, day):
    # Idempotent: re-running an interrupted partition is safe (guarded MERGE)
    part = (spark.read.parquet("s3://export/orders/")
                 .filter(F.col("event_day") == F.lit(day)))
    merge_partition(part)                          # guarded MERGE from prior example
    rows  = part.count()
    chk   = partition_checksum(spark, "lake.orders", day)   # for reconcile
    mark(day, status="done", rows=rows, checksum=chk)

def run_backfill(spark, all_days, K=8):
    pending = [d for d in all_days if status_of(d) != "done"]   # resume: skip done
    with ThreadPoolExecutor(max_workers=K) as pool:             # throttle to K
        for day in pending:
            mark(day, status="running")
            pool.submit(_guarded, spark, day)                   # _guarded marks failed on error

Step-by-step explanation.

1. The backfill_progress ledger is the durable resume point. On restart, run_backfill filters to partitions whose status is not done, so completed days are skipped and only the remaining work runs — a 10 TB backfill interrupted at 80% resumes at 80%, not zero.
2. Each partition is an independent idempotent unit. backfill_partition runs the same version-guarded MERGE as the seam example, so a partition that was running when the job died can be safely re-run — the merge converges rather than duplicating.
3. ThreadPoolExecutor(max_workers=K) is the throttle. K bounds how many partitions read the source concurrently, keeping the export/stage read (and any residual live-warehouse contention) under budget. Lowering K during business hours is the backpressure lever.
4. Each partition records its rows and a checksum in the ledger. Those are not just progress markers — the checksum feeds reconciliation (next section), so backfill and validation share one artifact per partition.
5. A partition that raises is marked failed (not silently skipped), so the driver can retry it or surface it for investigation. The ledger is thus both the resume mechanism and the audit trail of what has and has not been loaded.

Output.

Restart scenario	Partitions re-copied	Behaviour
Clean run	all once	done
Crash at 80%	remaining 20% only	resumes from ledger
Partition failed	just that partition	retried, not whole table
Re-run a done day	0	skipped (idempotent anyway)

Rule of thumb. Backfill by partition, record each partition's status + row count + checksum in a ledger, and drive K-at-a-time with idempotent per-partition merges. Resumability comes from the ledger; safety comes from the guarded merge; throttling comes from K — and the per-partition checksum you compute here is the same one reconciliation consumes.

Worked example — deduping on the seam under late-arriving history

Detailed explanation. A subtle backfill failure: the source warehouse itself receives late-arriving corrections to historical rows during the migration (a finance restatement backdated to last quarter). The backfill snapshot may predate the correction; the stream may or may not carry it. Walk through why the version guard plus a re-backfill of touched partitions keeps the lakehouse correct.

• The hazard. A row for event_day = 2025-03-01 is corrected today; the backfill export was taken yesterday and missed it.
• The catch. If the correction is a CDC event, the stream carries it and the guarded MERGE applies it (newer _seq). If it is an out-of-band batch fix with no CDC, the stream misses it.
• The fix. Track which historical partitions were touched after the snapshot and re-backfill exactly those.

Question. Design the mechanism that keeps historically-corrected rows consistent between warehouse and lakehouse.

Input.

Correction path	Carries _seq?	Lakehouse sees it via
CDC update event	yes	stream + guarded MERGE
Out-of-band batch fix	often no	re-backfill of touched partition
No change	—	untouched

Code.

-- 1. Find historical partitions modified after the backfill snapshot time
--    (warehouse audit column or information_schema last_altered)
SELECT DISTINCT event_day
FROM   analytics.orders
WHERE  _updated_at > (SELECT snapshot_ts FROM migration.backfill_meta)
  AND  event_day  <  (SELECT snapshot_day FROM migration.backfill_meta);
-- -> the "dirty" historical partitions that need a re-backfill

# 2. Re-backfill only the dirty partitions (idempotent, cheap)
dirty_days = query_dirty_partitions()
for day in dirty_days:
    mark(day, status="pending")     # reset in the ledger
run_backfill(spark, dirty_days, K=4)  # guarded MERGE overwrites with newer _seq

Step-by-step explanation.

1. Late-arriving corrections to historical rows are the case a naive one-shot backfill misses: the export was a point-in-time snapshot, and anything corrected after that snapshot but dated before it is invisible to both the snapshot and a forward-only stream position.
2. When the correction flows as a CDC event, there is nothing special to do — it carries a newer _seq, the stream delivers it, and the version-guarded MERGE overwrites the stale historical row. The seam machinery already handles it.
3. When the correction is an out-of-band batch fix with no CDC trail, the stream never sees it. The defense is to detect which historical partitions changed after the snapshot — via a warehouse audit column (_updated_at) or catalog last_altered — and re-backfill exactly those.
4. The re-backfill is cheap and safe because backfill is partitioned and idempotent: resetting the dirty partitions to pending and re-running loads only those days, and the guarded MERGE overwrites the stale rows with the corrected ones (which carry a newer _seq from the warehouse side).
5. This closes the last gap between the two systems for history: forward changes ride the stream, backward corrections ride a targeted re-backfill, and reconciliation (next section) is what proves both mechanisms actually caught everything.

Output.

Historical change	Detected by	Repaired by
CDC update	newer _seq in stream	guarded MERGE
Batch restatement	_updated_at > snapshot_ts	targeted re-backfill
Reconcile mismatch	row-hash diff	re-backfill that partition
No change	—	nothing

Rule of thumb. Assume history is not frozen during a migration. Ride forward corrections on the stream via the _seq guard, catch out-of-band backdated fixes by re-backfilling only the partitions the source marks as changed after the snapshot, and let reconciliation flag anything both missed. A migration that assumes the past is immutable ships a silent drift.

Senior interview question on backfill

A senior interviewer might ask: "You're backfilling 8 TB of five-year orders history into a Delta lakehouse while a dual-write stream keeps the last seven days current. Design the backfill so it resumes after interruption, never starves the live warehouse, seams with the stream without a gap or double-count, and stays correct when finance backdates a correction to a partition you already loaded."

Solution Using a staged, partitioned, resumable backfill with a deduped watermark seam and dirty-partition repair

# 1. Unload once to a stage so the heavy read never touches live compute
#    (run off-peak; warehouse EXPORT / UNLOAD to S3 as Parquet)
unload_warehouse("analytics.orders", "s3://export/orders/", fmt="parquet")

# 2. Cutoffs: stream starts BEFORE copy ends -> overlap, no gap
T_stream = earliest_retained_offset("cdc.orders")   # 7 days ago
T_copy   = snapshot_ts()                             # now; >= T_stream
record_backfill_meta(snapshot_ts=T_copy)

# 3. Resumable, throttled, partitioned backfill (guarded MERGE per day)
all_days = date_range("2020-01-01", T_copy)
run_backfill(spark, all_days, K=8)                  # skips 'done', idempotent

-- 4. Seam dedupe is implicit in the per-partition guarded MERGE:
MERGE INTO lake.orders t USING backfill_part s
ON t.order_id = s.order_id
WHEN MATCHED AND s._seq > t._seq THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

-- 5. Dirty-partition repair for out-of-band historical corrections
--    (scheduled daily during the parallel run)
INSERT OVERWRITE migration.dirty_partitions
SELECT DISTINCT event_day
FROM   analytics.orders
WHERE  _updated_at > (SELECT snapshot_ts FROM migration.backfill_meta)
  AND  event_day  <  date(current_timestamp());

# 6. Repair loop: re-backfill only the dirty partitions, idempotently
for day in read("migration.dirty_partitions"):
    mark(day, "pending")
run_backfill(spark, read("migration.dirty_partitions"), K=4)

Step-by-step trace.

Concern	Mechanism	Result
Live-warehouse contention	unload to stage; K-throttled read	production untouched
Interruption at 80%	ledger skips done partitions	resumes at 80%
Seam gap	T_stream <= T_copy	ranges overlap, no gap
Seam double-count	guarded MERGE on key + _seq	overlap deduped
Backdated correction	dirty-partition detect + re-backfill	history stays correct

After the run, five years of history sit in the lakehouse loaded from a stage (no live-warehouse load), the last seven days are covered by both the tail of the backfill and the stream (deduped at the seam), an interrupted run resumed from its ledger, and a nightly dirty-partition repair catches any finance restatement backdated into an already-loaded partition. Reconciliation then proves it.

Output:

Metric	Value
History loaded	~8 TB, 5 years, from stage
Live-warehouse load from backfill	~0 (staged read)
Resume granularity	per partition (day)
Seam correctness	no gap, no double-count
Backdated-correction handling	dirty-partition re-backfill
Idempotent re-run	yes (guarded MERGE)

Why this works — concept by concept:

• Staged unload — exporting once to S3 and backfilling from Parquet decouples the multi-terabyte historical read from live warehouse compute, so the copy cannot contend with production dashboards.
• Watermark seam — choosing T_stream <= T_copy makes the backfill and stream ranges overlap, eliminating the gap; the version-guarded MERGE dedupes the overlap, eliminating the double-count.
• Ledger-driven resumability — the backfill_progress ledger lets a restart skip completed partitions and retry only failed ones, turning an 8 TB copy from all-or-nothing into partition-granular progress.
• Dirty-partition repair — detecting historical partitions the source changed after the snapshot and re-backfilling exactly those keeps the lakehouse correct even though history is not frozen during the migration.
• Cost — one full staged copy (O(history)) plus a small daily repair (O(dirty partitions)) and a bounded overlap. Compared to a one-shot copy, the extra cost is the ledger, the repair loop, and the deduped overlap — and the payoff is a resumable, production-safe backfill that reconciliation can actually certify. Net O(history) once, then O(changed) per day.

SQL
Topic — sql
SQL backfill, watermark, and dedupe problems

Practice →

ETL Topic — etl ETL problems on partitioned resumable loads

Practice →

---

4. Reconciliation — proving the two systems agree

reconciliation climbs a ladder — row count, then aggregate control totals, then row-hash checksum — every cycle, feeding a ledger that gates cutover

The mental model in one line: reconciliation is the move that turns "the lakehouse looks right" into a queryable gate by comparing the warehouse and lakehouse in tiers of increasing rigor — cheap row counts first, then aggregate control totals, then full row-hash checksums — run every cycle during the parallel run, with tolerances and drift buckets, so that clean cycles accumulate in a reconcile ledger that mechanically decides when a table is safe to cut over. This is the move no tool owns, the move that separates a migration that ships from one that gets rolled back, and the single thing senior interviews drill hardest under the banner of data validation.

The three-tier ladder — cheap first, proof last.

• Tier 1 — row count. COUNT(*) per partition on both systems. Cheapest, catches gross gaps and double-counts. Necessary but not sufficient: two tables can have equal counts and different rows.
• Tier 2 — aggregate control totals. SUM, MIN, MAX, COUNT(DISTINCT) on business-critical columns (SUM(total_cents), COUNT(DISTINCT customer_id)). Catches value-level drift that counts miss — a wrong amount, a dropped column. Cheap and high-signal.
• Tier 3 — row-hash checksum. Hash each row's columns, aggregate the hashes per partition, compare. The proof: if the partition hash matches, every row matches. Most expensive; run on partitions that pass tiers 1 and 2, or on a sampled/rolling basis at scale.

Tolerances and drift buckets.

• Exact vs tolerant. Counts and hashes must match exactly. Floating-point aggregates may need a tolerance (abs(a-b) < epsilon) because warehouse and lakehouse may round differently — decide per column, and prefer integer/decimal control totals to avoid the question.
• Drift buckets. When a partition mismatches, classify it: stale (lakehouse behind — will self-heal), gap (missing rows — re-backfill), value drift (transform difference — a real bug). The bucket drives the response.
• Freshness alignment. Reconcile as-of a watermark both systems have reached, so a mismatch is a real difference and not just the lakehouse being a few minutes behind.

Continuous, not one-shot.

• Every cycle. Reconciliation runs on every load cycle during the parallel run, not once before cutover. A single clean run proves nothing; a streak proves stability.
• Rolling coverage. At petabyte scale, tier 3 hashes a rolling subset of partitions each cycle so every partition is covered over a window, while tiers 1–2 cover everything every cycle.
• The ledger. Each cycle appends (object, cycle_ts, count_match, aggregate_match, hash_match) to a ledger. Cutover eligibility is a query over that ledger, not a human's judgment.

Common interview probes on reconciliation.

• "How do you prove the lakehouse equals the warehouse?" — tiered ladder: count → aggregate → row-hash, every cycle.
• "Why not just compare row counts?" — equal counts can hide swapped or wrong-valued rows; hashes are the proof.
• "How do you reconcile a petabyte table?" — tiers 1–2 fully every cycle; tier 3 rolling/sampled coverage.
• "When is a mismatch OK?" — only the stale bucket (lakehouse behind, will self-heal); gaps and value drift are bugs.

Worked example — the three-tier reconcile query

Detailed explanation. The canonical reconcile: run counts, control totals, and a row-hash per partition on both systems, aligned to a shared watermark, and record the three booleans per partition. Build the queries and the comparison.

• Alignment. Reconcile partitions <= watermark_both (a point both systems have reached).
• Tiers. count; SUM(total_cents) + COUNT(DISTINCT customer_id); partition row-hash.
• Record. three booleans per partition into the ledger.

Question. Write the per-partition three-tier reconcile for orders across warehouse and lakehouse.

Input.

Tier	Metric	Cost	Catches
1	COUNT(*)	cheap	gaps, double-counts
2	SUM(total_cents), COUNT(DISTINCT customer_id)	cheap	value drift
3	XOR/SUM of per-row hashes	expensive	any row difference

Code.

-- Run the SAME query shape on both systems, per partition, as-of a watermark.
-- Tier 1 + Tier 2 + Tier 3 in one pass per side.
SELECT
    event_day,
    COUNT(*)                                        AS row_count,        -- tier 1
    SUM(total_cents)                                AS sum_total_cents,  -- tier 2
    COUNT(DISTINCT customer_id)                     AS distinct_custs,   -- tier 2
    -- tier 3: order-independent aggregate of per-row hashes
    SUM(CRC32(CONCAT_WS('|',
        CAST(order_id     AS STRING),
        CAST(customer_id  AS STRING),
        CAST(total_cents  AS STRING),
        COALESCE(status, ''),
        CAST(_seq         AS STRING))))             AS row_hash_agg
FROM   orders                       -- analytics.orders  OR  lake.orders
WHERE  event_day <= DATE '2026-08-17'               -- shared watermark
GROUP  BY event_day;

# Compare the two result sets and append booleans to the ledger
def reconcile(wh_rows, lake_rows):
    wh   = {r.event_day: r for r in wh_rows}
    lake = {r.event_day: r for r in lake_rows}
    for day in wh.keys() | lake.keys():
        a, b = wh.get(day), lake.get(day)
        count_ok = a and b and a.row_count == b.row_count
        agg_ok   = a and b and a.sum_total_cents == b.sum_total_cents \
                            and a.distinct_custs == b.distinct_custs
        hash_ok  = a and b and a.row_hash_agg == b.row_hash_agg
        ledger_append("orders", day, count_ok, agg_ok, hash_ok,
                      bucket=classify(a, b, count_ok, agg_ok, hash_ok))

Step-by-step explanation.

1. The identical query shape runs on both systems so any difference is in the data, not the query. Grouping by event_day makes the comparison per-partition, which localizes a mismatch to a day you can re-backfill instead of a whole-table red flag.
2. Tier 1 (COUNT(*)) is the cheap gate: a count mismatch means a gap or a double-count and you can stop there for that partition. But equal counts do not imply equal data, so counts alone are never sufficient.
3. Tier 2 sums a monetary column and counts distinct customers — integer/decimal control totals chosen deliberately to avoid floating-point tolerance questions. These catch value drift a count cannot: a wrong total_cents or a dropped customer_id changes the totals while the count stays equal.
4. Tier 3 is the proof. Each row is hashed over its columns (including _seq so a stale version is a different hash), and the per-row hashes are combined with an order-independent aggregate (SUM of CRC32), so the two systems can store rows in any physical order and still match. Equal partition hash ⇒ every row matches.
5. The comparison records three booleans and a drift bucket per partition into the ledger. The bucket (stale / gap / value-drift) turns a raw mismatch into an actionable classification, and the booleans are what the cutover gate later queries.

Output.

event_day	count_ok	agg_ok	hash_ok	bucket
2026-08-15	true	true	true	clean
2026-08-16	true	true	false	value-drift (bug)
2026-08-17	false	false	false	stale (lakehouse behind)

Rule of thumb. Reconcile in tiers cheapest-first, align both sides to a shared watermark, hash rows with an order-independent aggregate that includes the version column, and classify every mismatch into a bucket. Counts find gross errors fast; control totals find value drift cheaply; the row-hash is the only tier that proves equality.

Worked example — order-independent row-hash checksum at scale

Detailed explanation. The row-hash tier is the proof, but it must be order-independent (the two systems store rows differently) and cheap enough to run continuously. The pattern: a strong per-row hash combined with a commutative aggregate, computed per partition, with rolling coverage so a petabyte table is fully hashed over a window. Walk through the design.

• Per-row hash. A strong hash over the canonical column tuple (SHA2/XXHASH64), including _seq.
• Commutative aggregate. BIT_XOR or SUM of the row hashes — independent of row order.
• Rolling coverage. Hash M partitions per cycle so all N are covered every N/M cycles; tiers 1–2 still cover all N every cycle.

Question. Design a partition checksum that is order-independent and a rolling schedule that covers a huge table continuously.

Input.

Property	Choice	Why
Per-row hash	`XXHASH64(concat_ws('	', cols, _seq))`
Combine	BIT_XOR(hash) per partition	order-independent
Coverage	rolling M of N partitions/cycle	bounds tier-3 cost
Sensitivity	includes every column + _seq	any change flips the hash

Code.

-- Order-independent partition checksum (identical on both systems)
SELECT event_day,
       BIT_XOR(XXHASH64(CONCAT_WS('|',
           CAST(order_id    AS STRING),
           CAST(customer_id AS STRING),
           CAST(total_cents AS STRING),
           COALESCE(status, ''),
           CAST(_seq        AS STRING)))) AS partition_checksum
FROM   orders                              -- run on warehouse AND lakehouse
WHERE  event_day IN (:rolling_partition_batch)   -- M partitions this cycle
GROUP  BY event_day;

# Rolling coverage scheduler — every partition hashed within a window
def rolling_batch(all_partitions, cycle_idx, M):
    """Return M partitions for this cycle so all N are covered every N/M cycles."""
    N = len(all_partitions)
    start = (cycle_idx * M) % N
    return [all_partitions[(start + i) % N] for i in range(M)]

# Each cycle: tiers 1-2 over ALL partitions (cheap) + tier 3 over M (rolling)
def cycle(cycle_idx):
    reconcile_counts_and_aggregates(ALL_PARTITIONS)          # full, cheap
    batch = rolling_batch(ALL_PARTITIONS, cycle_idx, M=48)   # e.g. 48 days/cycle
    reconcile_row_hash(batch)                                # rolling, expensive

Step-by-step explanation.

1. The per-row hash covers every column plus _seq, so any single-field difference — a wrong amount, a stale version, a dropped status — produces a different row hash. This is what makes the tier a proof rather than a heuristic.
2. BIT_XOR (or SUM) as the combiner is commutative and associative, so the partition checksum is independent of the order in which rows are stored or scanned. The warehouse's clustered order and the lakehouse's file order both yield the same partition checksum when the rows are identical.
3. Running tier 3 over all partitions every cycle is too expensive at petabyte scale, so a rolling schedule hashes M partitions per cycle. Over N/M cycles every partition is hashed, giving full coverage on a bounded per-cycle budget.
4. Tiers 1 and 2 remain full every cycle because they are cheap — so gross gaps and value drift are still caught immediately everywhere; only the expensive full-proof hash is amortized. A partition that fails tier 1 or 2 is hashed immediately regardless of the rolling schedule.
5. The rolling window turns "prove a petabyte matches" from an impossible per-cycle job into a continuous guarantee: at any time, every partition has been proven equal within the last N/M cycles, and any drift shows up in tiers 1–2 within one cycle.

Output.

Table size	Tier 1–2 per cycle	Tier 3 per cycle	Full-hash coverage window
small	all	all	1 cycle
large (N=1825 days)	all	48 days	~38 cycles
huge (sampled)	all	48 + all failing	rolling + on-demand

Rule of thumb. Make the row-hash order-independent with a commutative aggregate over a strong per-row hash that includes the version column, run tiers 1–2 fully every cycle, and roll tier 3 across partitions so a huge table is fully proven within a bounded window. Never make equality depend on physical row order.

Worked example — the reconcile ledger and cutover gate

Detailed explanation. Reconciliation's output is a ledger; cutover eligibility is a query over it. This removes human judgment from the go/no-go and makes the gate auditable. Build the ledger, the eligibility view, and the drift-bucket dashboard.

• Ledger. One row per (object, cycle) with the three booleans and a bucket.
• Gate. N consecutive clean cycles in a recent window ⇒ eligible.
• Dashboard. Bucket counts per object so a mismatch is triaged, not just seen.

Question. Write the ledger schema, the cutover-eligible gate, and the drift triage query.

Input.

Artifact	Purpose
reconcile_ledger	append-only per-cycle record
cutover_eligible	N clean cycles ⇒ eligible
drift triage	classify open mismatches

Code.

-- 1. The ledger (append-only)
CREATE TABLE migration.reconcile_ledger (
    object_name     STRING    NOT NULL,
    cycle_ts        TIMESTAMP NOT NULL,
    count_match     BOOLEAN   NOT NULL,
    aggregate_match BOOLEAN   NOT NULL,
    hash_match      BOOLEAN   NOT NULL,
    drift_bucket    STRING             -- 'clean','stale','gap','value-drift'
);

-- 2. The gate: >= 5 consecutive clean cycles in the last 7 days
CREATE OR REPLACE VIEW migration.cutover_eligible AS
WITH recent AS (
    SELECT object_name, cycle_ts,
           count_match AND aggregate_match AND hash_match AS clean
    FROM   migration.reconcile_ledger
    WHERE  cycle_ts > current_timestamp() - INTERVAL 7 DAYS
)
SELECT object_name
FROM   recent
GROUP  BY object_name
HAVING MIN(CAST(clean AS INT)) = 1        -- every recent cycle clean
   AND COUNT(*) >= 5;                      -- at least 5 cycles

-- 3. Drift triage: what is blocking the not-yet-eligible objects?
SELECT object_name, drift_bucket, COUNT(*) AS cycles
FROM   migration.reconcile_ledger
WHERE  cycle_ts > current_timestamp() - INTERVAL 2 DAYS
  AND  NOT (count_match AND aggregate_match AND hash_match)
GROUP  BY object_name, drift_bucket
ORDER  BY cycles DESC;

Step-by-step explanation.

1. The ledger is append-only: every cycle writes one row per object with the three tier booleans and the drift bucket. It is the durable, auditable record of the entire parallel run — you can always show why a table was or was not eligible on any date.
2. The cutover_eligible view encodes the gate as data. MIN(CAST(clean AS INT)) = 1 requires every recent cycle to be clean (a single dirty cycle in the window disqualifies), and COUNT(*) >= 5 requires at least five cycles of evidence — a streak, not a lucky single pass.
3. Because eligibility is a query, cutover is not a meeting decision — the flag flip (next section) reads this view. That removes optimism and politics from the go/no-go and makes it reproducible and reviewable.
4. The drift-triage query turns open mismatches into an action queue grouped by bucket: stale cycles will self-heal and can be ignored, gap points at a re-backfill, and value-drift points at a transform bug that must be fixed before the streak can even start. This is how a team works the migration down to zero blockers.
5. The window (7 days) and threshold (5 cycles) are per-risk tunables: the finance close might require 14 days and 10 clean cycles, a low-risk mart 3. The same ledger and view serve all objects; only the constants change.

Output.

object	recent cycles	all clean?	eligible?
dim_customer	6	yes	yes
orders	5	yes	yes
finance_fact	4	yes	no (needs 5)
clickstream	6	no (value-drift)	no

Rule of thumb. Make cutover eligibility a query over an append-only reconcile ledger — N consecutive clean cycles in a recent window — and triage open mismatches by drift bucket. The gate becomes auditable and optimism-proof: a table cuts over because the data says it is safe, not because someone felt ready.

Senior interview question on reconciliation

A senior interviewer might ask: "You're running a parallel warehouse/lakehouse for a 5-year, 2-billion-row orders table plus a finance fact. Design the reconciliation that proves they agree well enough to cut over — the tiers, how you handle the fact that the lakehouse is a few minutes behind, how you make it affordable at that scale, and how the go/no-go decision is made without anyone eyeballing dashboards."

Solution Using a watermark-aligned tiered ladder with rolling row-hash coverage and a ledger-driven gate

-- 1. Align both sides to a shared watermark so 'behind' != 'wrong'
--    Reconcile only partitions both systems have fully loaded.
SET reconcile_watermark = (
    SELECT LEAST( (SELECT max_loaded_day FROM warehouse_progress),
                  (SELECT max_loaded_day FROM lake_progress) ));

-- 2. Tiers 1+2 over ALL partitions <= watermark, every cycle (cheap)
SELECT event_day, COUNT(*) AS c,
       SUM(total_cents) AS s, COUNT(DISTINCT customer_id) AS d
FROM   orders WHERE event_day <= :reconcile_watermark GROUP BY event_day;
-- (run identically on analytics.orders and lake.orders; diff in the harness)

# 3. Tier 3 rolling row-hash: M partitions/cycle + any tier-1/2 failures now
def reconcile_cycle(cycle_idx):
    wm = reconcile_watermark()
    parts = partitions_upto(wm)

    diffs12 = compare_counts_aggs(parts)               # full, cheap
    failing = [p for p, ok in diffs12.items() if not ok]

    rolling = rolling_batch(parts, cycle_idx, M=48)    # amortized proof
    hashed  = set(rolling) | set(failing)              # prove failures immediately
    diffs3  = compare_row_hash(hashed)

    for p in parts:
        ledger_append("orders", now(),
            count_match=diffs12[p].count_ok,
            aggregate_match=diffs12[p].agg_ok,
            hash_match=diffs3.get(p, PRIOR_CLEAN),      # carry last proof if not re-hashed
            bucket=classify(diffs12[p], diffs3.get(p)))

-- 4. The gate the cutover reads (finance uses a stricter window)
SELECT object_name FROM migration.cutover_eligible;                 -- 5 clean / 7d
SELECT object_name FROM migration.cutover_eligible_strict;          -- 10 clean / 14d

Step-by-step trace.

Concern	Mechanism	Result
Lakehouse behind	reconcile as-of LEAST(watermarks)	"behind" never reads as "wrong"
Affordability at 2B rows	tiers 1–2 full; tier 3 rolling M/cycle	bounded per-cycle cost
Immediate error surfacing	hash failing partitions now, not on schedule	drift caught in one cycle
Row order differs	BIT_XOR commutative checksum	order-independent equality
Go/no-go	cutover_eligible view	auditable, no eyeballing

After the harness runs, every partition up to the shared watermark is count/aggregate-checked every cycle and row-hash-proven within a rolling window; the finance fact is held to a stricter 10-clean/14-day gate; a partition that drifts is hashed immediately and bucketed; and the cutover decision is a SELECT against cutover_eligible, not a judgment call.

Output:

Metric	orders	finance_fact
Tier 1–2 coverage	full every cycle	full every cycle
Tier 3 coverage	rolling 48/cycle + failures	rolling + failures
Gate	5 clean / 7 days	10 clean / 14 days
Watermark alignment	LEAST(wh, lake)	LEAST(wh, lake)
Decision	cutover_eligible query	cutover_eligible_strict

Why this works — concept by concept:

• Watermark alignment — reconciling only up to LEAST(warehouse, lakehouse) loaded points means a mismatch reflects a real data difference, not the lakehouse trailing by a few minutes. Without it, every cycle is a false alarm.
• Tiered ladder — cheap counts and control totals run fully every cycle and catch gross and value drift immediately; the expensive row-hash is the proof and is amortized. Each tier does the job the tier below cannot at a cost the tier above cannot afford.
• Order-independent rolling hash — a commutative BIT_XOR over strong per-row hashes proves equality regardless of physical order, and rolling M partitions per cycle makes full proof affordable on a 2-billion-row table within a bounded window.
• Ledger-driven gate — cutover eligibility is a query (N clean cycles), stricter for finance, so the go/no-go is auditable and optimism-proof rather than a dashboard glance.
• Cost — tiers 1–2 are O(rows) cheap aggregates every cycle; tier 3 is O(rows/(N/M)) per cycle amortized. Compared to full row-hashing everything every cycle (O(rows) of the expensive tier), the rolling schedule cuts continuous cost by the coverage factor while still proving every partition within the window — and buys an evidence-based, reviewable cutover decision.

Data Validation
Topic — data-validation
Data-validation and reconciliation problems

Practice →

SQL Topic — sql SQL checksum, aggregate, and diff problems

Practice →

---

5. Cutover and rollback

cutover flips readers behind a feature flag wave by wave — keeping the warehouse authoritative and rollback-ready until a decommission gate

The mental model in one line: cutover is the move where consumers are repointed from the warehouse to the lakehouse behind a feature flag, wave by wave, only for objects the reconcile ledger marks eligible — while the warehouse stays authoritative and rollback-ready so a single flag flip reverts a wave instantly — and the warehouse is decommissioned only behind a gate (N clean post-cutover cycles + consumer sign-off), which is what makes the whole migration zero-downtime and reversible until it is proven. Every senior data engineer knows the migration is not the risky part — the cutover is — and the flag-plus-gate discipline is what keeps the risk bounded to one wave.

The four axes for cutover.

• Reader repoint mechanism. A feature flag / config-driven data source per consumer (or per wave), so flipping the source is a config change, not a deploy. The flag reads the reconcile gate: only eligible objects can be flipped.
• Wave granularity. Cut over by wave (a set of related consumers/objects), never big-bang. Blast radius is bounded to one wave; a problem pages one team, not all 300 dashboards.
• Rollback path. The warehouse stays authoritative and the dual-write keeps it current after cutover, so a rollback is a flag flip back — instant, lossless, no data replay. Rollback stays available until the decommission gate.
• Decommission gate. The warehouse is frozen and retired only after N clean post-cutover reconcile cycles and explicit consumer sign-off. Decommission is the last, irreversible step — gated on evidence.

The feature-flag reader switch.

• Per-consumer source flag. Each BI model / job resolves its source from config: orders_source = warehouse | lakehouse. Flip = config change, effective next query/run.
• Gate-guarded. The flag flip is allowed only if the object is in cutover_eligible. You cannot flip a table that has not passed the reconcile gate.
• Canary. Optionally route a small % of read traffic to the lakehouse first, compare results live, then flip the rest of the wave.

Rollback — instant because the warehouse never went stale.

• Dual-write stays on. After a wave cuts over, the dual-write keeps writing the warehouse, so it remains a live, current fallback — not a stale snapshot.
• Flag flip back. Rollback = set the wave's source back to warehouse. No data movement, no replay; the warehouse has every row.
• Rollback triggers. Automated: a post-cutover reconcile failure, a data-freshness SLO breach, or a consumer error-rate spike auto-reverts the flag and pages.

The decommission gate — the last irreversible step.

• Post-cutover clean cycles. Reconcile continues after cutover (warehouse still dual-written); N clean cycles prove the lakehouse holds up under real read traffic.
• Consumer sign-off. Each wave's owners confirm their dashboards/jobs are correct on the lakehouse.
• Teardown order. Only then: stop the dual-write to the warehouse, freeze it read-only for a grace period, then drop. Never drop before the grace period — it is the last rollback insurance.

Common interview probes on cutover.

• "How do you cut over without downtime?" — feature-flag reader repoint; both systems already live via dual-write.
• "How do you roll back?" — flip the flag back; the dual-write kept the warehouse current, so it is instant and lossless.
• "What triggers a rollback?" — post-cutover reconcile failure, freshness SLO breach, or consumer error spike.
• "When do you decommission?" — after N clean post-cutover cycles + sign-off; freeze read-only before dropping.

Worked example — the feature-flag reader switch

Detailed explanation. The cutover mechanism is a per-object source flag that every consumer resolves at query/run time, gated on the reconcile ledger. Build the flag store, the gate-guarded flip, and the consumer-side resolution.

• Flag store. reader_source(object, source, wave) — source ∈ {warehouse, lakehouse}.
• Gate. Flip to lakehouse only if the object is cutover_eligible.
• Consumer. Resolves its table from the flag at run time.

Question. Write the gate-guarded flip and the consumer-side source resolution.

Input.

Component	Value
Flag store	migration.reader_source
Gate	migration.cutover_eligible
Flip unit	wave (set of objects)
Consumer	resolves source at run time

Code.

-- 1. Reader-source flag, one row per migrated object
CREATE TABLE migration.reader_source (
    object_name STRING NOT NULL,
    source      STRING NOT NULL DEFAULT 'warehouse',  -- 'warehouse' | 'lakehouse'
    wave        INT,
    updated_at  TIMESTAMP DEFAULT current_timestamp()
);

-- 2. Gate-guarded flip: only eligible objects may point at the lakehouse
MERGE INTO migration.reader_source AS t
USING (
    SELECT object_name FROM migration.cutover_eligible
    WHERE  object_name IN (SELECT object_name FROM migration.reader_source WHERE wave = :wave)
) AS s
ON t.object_name = s.object_name
WHEN MATCHED THEN UPDATE SET source = 'lakehouse', updated_at = current_timestamp();
-- Objects in the wave that are NOT eligible stay on 'warehouse' (safe default).

# 3. Consumer resolves its physical table from the flag at run time
def resolve_table(object_name: str) -> str:
    src = query_scalar(
        "SELECT source FROM migration.reader_source WHERE object_name = %s",
        object_name) or "warehouse"                    # safe default
    return f"lake.{object_name}" if src == "lakehouse" else f"analytics.{object_name}"

# BI models / jobs call resolve_table() instead of hard-coding the source
orders_tbl = resolve_table("orders")   # -> 'lake.orders' once flipped

Step-by-step explanation.

1. The reader_source flag defaults every object to warehouse, so the safe state is "read the authoritative system." Cutover is the deliberate act of flipping specific objects to lakehouse; nothing moves onto the lakehouse by accident.
2. The flip is gate-guarded: the MERGE only updates objects that appear in cutover_eligible. An object in the wave that has not passed the reconcile gate stays on warehouse — you cannot cut over unproven data even if you try to flip its wave.
3. The flip is a config change, not a deploy. It takes effect the next time a consumer resolves its source, so there is no downtime, no redeploy, and no query interruption — the essence of zero-downtime cutover.
4. Consumers call resolve_table() instead of hard-coding analytics.orders or lake.orders. This indirection is the one code change consumers make; after it, cutover and rollback are pure config flips they never redeploy for.
5. The or "warehouse" fallback means any lookup failure resolves to the authoritative system — the flag store failing open to the safe side, never accidentally to the unproven lakehouse.

Output.

object	eligible?	flag after wave flip	consumer reads
orders	yes	lakehouse	lake.orders
dim_customer	yes	lakehouse	lake.dim_customer
finance_fact	no	warehouse (unchanged)	analytics.finance_fact
(flag lookup fails)	—	—	analytics.* (safe default)

Rule of thumb. Cut over with a per-object reader-source flag that defaults to the warehouse, gate the flip on the reconcile ledger so only proven objects flip, and have consumers resolve their source at run time. Cutover and rollback become config flips — no deploy, no downtime, and unproven data physically cannot be served.

Worked example — rollback trigger and runbook

Detailed explanation. Rollback must be fast and boring: because the dual-write kept the warehouse current after cutover, reverting is a flag flip with no data replay. Automate the triggers so a bad cutover self-reverts before a human wakes up. Build the trigger detector and the revert.

• Triggers. Post-cutover reconcile failure, freshness SLO breach, or consumer error-rate spike.
• Action. Flip the wave's flags back to warehouse; page; freeze further flips.
• Guarantee. No data loss — the warehouse was dual-written throughout.

Question. Write the automated rollback trigger and the revert it performs.

Input.

Trigger	Threshold	Action
Post-cutover reconcile fail	any hash mismatch on a flipped object	revert wave
Freshness SLO breach	lakehouse lag > 15 min for 10 min	revert wave
Consumer error spike	error rate > 2× baseline	revert wave

Code.

# Automated rollback watcher — runs every few minutes over flipped waves
def rollback_watcher():
    for wave in flipped_waves():
        reasons = []
        if reconcile_failed_since_cutover(wave):
            reasons.append("post-cutover reconcile mismatch")
        if lakehouse_lag(wave) > timedelta(minutes=15):
            reasons.append("freshness SLO breach")
        if consumer_error_rate(wave) > 2 * baseline(wave):
            reasons.append("consumer error spike")

        if reasons:
            revert_wave(wave, reasons)

def revert_wave(wave, reasons):
    # Flag flip back — no data replay; warehouse was dual-written throughout
    execute("""
        UPDATE migration.reader_source
        SET    source = 'warehouse', updated_at = current_timestamp()
        WHERE  wave = %s
    """, wave)
    freeze_further_flips(wave)                 # no re-flip until root-caused
    page_oncall(f"ROLLBACK wave {wave}: {', '.join(reasons)}")

Step-by-step explanation.

1. The watcher runs continuously over flipped waves only — objects still on the warehouse cannot regress, so there is nothing to watch there. It checks the three independent failure signals every few minutes.
2. Each trigger is a distinct failure mode: a reconcile mismatch means the lakehouse data diverged, a freshness breach means the lakehouse fell behind, and a consumer error spike means something broke for readers even if the data looks fine. Any one is sufficient to revert.
3. revert_wave is a pure flag flip back to warehouse. There is no data replay, no catch-up, no downtime — because the dual-write kept the warehouse current after cutover, it holds every row and is instantly serviceable. This is why rollback is boring.
4. After reverting, freeze_further_flips prevents an automated or manual re-flip until a human root-causes the trigger. This stops a flapping cutover from oscillating readers back and forth.
5. The page carries the reasons, so on-call starts with the failure classification (data / freshness / consumer) already in hand. Rollback is automatic and fast; diagnosis is human and unhurried, exactly the right split.

Output.

Trigger fired	Revert action	Data loss	Consumer impact
Reconcile mismatch	flag → warehouse	none	reads authoritative again
Freshness breach	flag → warehouse	none	fresher data restored
Error spike	flag → warehouse	none	errors clear
No trigger	none	—	stays on lakehouse

Rule of thumb. Keep the dual-write running after cutover so the warehouse stays a live fallback, and automate rollback as a flag flip on any of three triggers — reconcile mismatch, freshness breach, or consumer error spike. Rollback should be instant, lossless, and boring; the interesting work is the unhurried root-cause afterward.

Worked example — the decommission gate and teardown order

Detailed explanation. Decommissioning the warehouse is the only irreversible step, so it is gated hardest and ordered carefully. Walk through the gate (post-cutover clean cycles + sign-off) and the teardown order that preserves rollback until the last safe moment.

• Gate. N clean post-cutover reconcile cycles + explicit consumer sign-off per wave.
• Teardown order. Stop dual-write → freeze warehouse read-only for a grace period → drop.
• Insurance. The read-only grace period is the final rollback window.

Question. Write the decommission gate check and the ordered teardown.

Input.

Step	Precondition	Reversible?
Stop dual-write	N clean post-cutover cycles + sign-off	yes (restart it)
Freeze read-only	dual-write stopped, grace begins	yes (unfreeze)
Drop warehouse	grace period elapsed, no rollback used	no

Code.

-- 1. Decommission gate: post-cutover clean cycles AND sign-off, per object
CREATE OR REPLACE VIEW migration.decommission_eligible AS
SELECT r.object_name
FROM (
    SELECT object_name, COUNT(*) AS post_clean
    FROM   migration.reconcile_ledger l
    JOIN   migration.reader_source    s USING (object_name)
    WHERE  s.source = 'lakehouse'                 -- only after cutover
      AND  l.cycle_ts > s.updated_at              -- cycles AFTER the flip
      AND  l.count_match AND l.aggregate_match AND l.hash_match
    GROUP  BY object_name
    HAVING COUNT(*) >= 10                          -- 10 clean post-cutover cycles
) r
JOIN migration.consumer_signoff g ON g.object_name = r.object_name
WHERE g.signed_off = true;                         -- explicit human sign-off

# 2. Ordered teardown — each step gated, rollback preserved until the last
def decommission(object_name):
    assert is_decommission_eligible(object_name), "gate not passed"

    stop_dual_write(object_name)                   # step 1 (reversible: restart)
    log(f"{object_name}: dual-write stopped")

    freeze_read_only(object_name, grace_days=14)   # step 2 (reversible: unfreeze)
    log(f"{object_name}: warehouse frozen read-only, 14-day grace")

    # step 3 runs only after the grace period with no rollback invoked
    schedule_after(days=14, guard=lambda: not rollback_used_since(object_name),
                   action=lambda: drop_warehouse_object(object_name))  # irreversible

Step-by-step explanation.

1. The decommission gate is stricter than the cutover gate: it counts clean cycles that occurred after the flag flip (l.cycle_ts > s.updated_at), proving the lakehouse holds up under real read traffic, and it requires an explicit human signed_off = true. Evidence and a person.
2. Teardown is ordered so rollback survives as long as possible. Stopping the dual-write is first but reversible — you can restart it and the warehouse catches up — so it is not yet a point of no return.
3. Freezing the warehouse read-only for a grace period is the final rollback insurance: the data is still there, still correct as of the freeze, and a wave can be reverted to it for the whole grace window. Nothing is dropped yet.
4. The drop is the only irreversible step and it runs only after the grace period elapses and a guard confirms no rollback was invoked in the interim. If anyone rolled back during grace, the drop is cancelled and the migration returns to investigation.
5. This ordering means the migration is reversible right up until the last scheduled action — the warehouse is retired on accumulated post-cutover evidence plus sign-off plus an unused grace period, never on a date. That is the discipline that keeps a migration from becoming an unrecoverable incident.

Output.

Teardown step	State	Rollback available?
Gate passed	lakehouse authoritative, warehouse dual-written	yes (flag flip)
Dual-write stopped	warehouse current as of stop	yes (restart dual-write)
Frozen read-only (grace)	warehouse immutable, intact	yes (unfreeze + flip)
Dropped (post-grace)	warehouse gone	no

Rule of thumb. Gate decommission on clean post-cutover cycles plus explicit sign-off, and tear down in reverse-risk order: stop dual-write, freeze read-only for a grace period, then drop — with the drop guarded on "no rollback used during grace." The warehouse is retired on evidence and a grace window, never on a calendar date.

Senior interview question on cutover and rollback

A senior interviewer might ask: "The lakehouse has passed reconciliation for a wave of 50 dashboards. Walk me through the cutover: how readers move without downtime, how you'd roll a wave back in minutes if a dashboard breaks, what automatically triggers that rollback, and how and when you finally decommission the warehouse so the whole thing stays reversible until the last possible moment."

Solution Using a gate-guarded feature-flag cutover with automated rollback and an ordered decommission gate

-- 1. Flip the wave's readers — gated on the reconcile ledger, zero-downtime
MERGE INTO migration.reader_source t
USING (SELECT object_name FROM migration.cutover_eligible) s
ON t.object_name = s.object_name AND t.wave = :wave
WHEN MATCHED THEN UPDATE SET source = 'lakehouse', updated_at = current_timestamp();
-- Consumers resolve_table() at run time -> no deploy, no downtime.
-- Dual-write STAYS ON to the warehouse after the flip (rollback fuel).

# 2. Automated rollback watcher — instant, lossless flag flip on any trigger
def watch_and_maybe_rollback(wave):
    reasons = []
    if reconcile_failed_since_cutover(wave):        reasons.append("reconcile")
    if lakehouse_lag(wave) > MINUTES_15:            reasons.append("freshness")
    if consumer_error_rate(wave) > 2 * baseline(wave): reasons.append("errors")
    if reasons:
        set_wave_source(wave, "warehouse")          # revert: no data replay
        freeze_further_flips(wave)
        page_oncall(f"rollback wave {wave}: {reasons}")

-- 3. Decommission only after 10 clean POST-cutover cycles + sign-off
SELECT object_name FROM migration.decommission_eligible;
-- teardown order (reversible until the last step):
--   stop_dual_write  -> freeze_read_only(grace=14d) -> drop (guarded)

Step-by-step trace.

Stage	Action	Reversible?
Flip	gate-guarded flag → lakehouse, run-time resolve	yes (flip back)
Post-cutover	dual-write stays on; reconcile continues	yes
Trigger fires	auto-revert flag → warehouse; page	n/a (this is the revert)
Gate	10 clean post-cutover cycles + sign-off	—
Teardown	stop dual-write → freeze RO 14d → drop	reversible until drop

After the wave cuts over, the 50 dashboards read lake.* with no redeploy and no downtime; the dual-write keeps the warehouse current so any of three triggers auto-reverts the wave in seconds with zero data loss; reconciliation keeps running post-cutover; and only after 10 clean post-cutover cycles plus consumer sign-off does the ordered teardown begin — stop dual-write, freeze read-only for 14 days, then drop under a guard.

Output:

Property	Value
Cutover downtime	none (config flip, run-time resolve)
Rollback time	seconds (flag flip, no replay)
Rollback data loss	none (warehouse dual-written post-cutover)
Rollback triggers	reconcile / freshness / error-rate
Decommission gate	10 clean post-cutover cycles + sign-off
Point of no return	the guarded drop after 14-day grace

Why this works — concept by concept:

• Gate-guarded flag flip — repointing readers is a config change gated on cutover_eligible, so cutover is zero-downtime and unproven objects physically cannot be served. No deploy, no query interruption.
• Dual-write survives cutover — keeping the warehouse dual-written after the flip means it stays a live, current fallback, which is precisely what makes rollback an instant, lossless flag flip instead of a data-replay project.
• Automated triggers — reconcile mismatch, freshness breach, and consumer error spike each auto-revert the wave and page, so a bad cutover self-heals in seconds and diagnosis happens afterward, unhurried.
• Ordered decommission gate — post-cutover clean cycles plus sign-off, then stop-dual-write → freeze-read-only → guarded-drop, keeps the migration reversible until the very last scheduled action. The warehouse is retired on evidence and an unused grace window, never on a date.
• Cost — the parallel run and post-cutover dual-write extend the double-storage/double-write window through the grace period, and the flag indirection is one code change per consumer. In exchange every wave is zero-downtime, revertible in seconds, and irreversible only after a guarded grace period — O(1)-per-wave blast radius versus O(all) for a big-bang, and no unrecoverable state until the final guarded drop.

Design
Topic — design
Design problems on zero-downtime cutover and rollback

Practice →

Data Transformation
Topic — data-transformation
Data-transformation problems on migration pipelines

Practice →

---

Cheat sheet — warehouse-to-lakehouse migration recipes

• The four-move loop. Dual-write into both systems, backfill history up to a watermark, reconcile continuously, cut over wave by wave with rollback ready. The warehouse stays authoritative and rollback-ready from move 1 to the decommission gate. The table format (Delta / Iceberg / Hudi) is a commodity; the choreography is the migration.
• Dual-write idempotent MERGE template. Fan one transformed batch to both systems on the same business key. Lakehouse side: MERGE INTO lake.t USING batch s ON t.pk = s.pk WHEN MATCHED AND s._seq > t._seq THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *. Warehouse write is authoritative and gates job success; lakehouse write is try/except → dead-letter, never re-raised. _seq is a monotonic per-key version (CDC LSN, else microsecond _ingested_at).
• Stream-tee isolation. When the warehouse is already stream-fed, dual-write via a separate consumer group with its own offsets, checkpoint, and compute. A lakehouse outage stalls only that group; a 15-minute dead-letter drain self-heals; the warehouse sink never notices.
• Backfill watermark-boundary recipe. Choose the stream start T_stream (earliest retained offset) before or equal to the copy cutoff T_copy so the ranges overlap — no gap. Backfill from an unloaded stage (S3 Parquet), not the live warehouse, so the heavy read never contends. The overlap [T_stream, T_copy] is deduped by the guarded MERGE — no double-count.
• Resumable partitioned backfill. Backfill by partition, record backfill_progress(partition, status, rows, checksum), drive K-at-a-time (K throttles source read), and use an idempotent per-partition MERGE. Restart skips done partitions and retries only failed. The per-partition checksum feeds reconciliation. Re-backfill only "dirty" partitions the source changed after the snapshot to catch backdated corrections.
• Three-tier reconcile SQL. Tier 1 COUNT(*); tier 2 SUM(amount) + COUNT(DISTINCT key); tier 3 order-independent row-hash BIT_XOR(XXHASH64(concat_ws('|', cols, _seq))) per partition. Same query shape both sides, aligned to LEAST(warehouse_watermark, lake_watermark) so "behind" never reads as "wrong". Prefer integer/decimal control totals to dodge floating-point tolerance.
• Rolling row-hash coverage. Tiers 1–2 full every cycle (cheap); tier 3 rolls M partitions per cycle plus any tier-1/2 failures immediately, so a huge table is fully proven within N/M cycles on a bounded per-cycle budget. Equal partition checksum ⇒ every row matches.
• Reconcile ledger + cutover gate. Append (object, cycle_ts, count_match, aggregate_match, hash_match, drift_bucket) per cycle. cutover_eligible = every recent cycle clean AND COUNT(*) >= N in the window (finance stricter: 10 clean / 14 days). Triage mismatches by bucket: stale self-heals, gap → re-backfill, value-drift → transform bug.
• Feature-flag reader switch. reader_source(object, source, wave) defaulting to warehouse; consumers resolve_table() at run time. Flip to lakehouse gated on cutover_eligible — unproven objects physically cannot be served. Flip is config, not deploy → zero-downtime.
• Rollback trigger + teardown order. Keep the dual-write ON after cutover so the warehouse stays a live fallback. Auto-revert (flag → warehouse, no replay) on: post-cutover reconcile mismatch, lakehouse lag > 15 min, or consumer error rate > 2× baseline. Decommission only after N clean post-cutover cycles + sign-off; teardown order: stop dual-write → freeze read-only (14-day grace) → guarded drop. Reversible until the drop.
• Migration axis decision matrix. Write-path: dual-write, lakehouse isolated. History: backfill to watermark, overlap + dedupe. Proof: count → aggregate → row-hash, ledger-gated. Cutover: flag flip per wave, rollback-ready, decommission behind a gate. Print this on a sticky note; use it in every interview.

Frequently asked questions

What is a warehouse-to-lakehouse migration in one sentence?

A warehouse to lakehouse migration is the program of moving an established data warehouse (Snowflake, Redshift, BigQuery, Teradata) onto an open lakehouse table format (Delta Lake, Apache Iceberg, Hudi) without downtime and without changing the numbers any consumer sees — which in practice is a four-move loop rather than a copy: dual-write into both systems during an overlap window, backfill the history the stream never saw up to a watermark, reconciliation that proves the two agree cycle after cycle, and a cutover that repoints readers wave by wave while staying rollback-ready until a decommission gate. The table format is a commodity; the choreography — keeping both systems live, proving equivalence, and cutting over reversibly — is the actual migration and the part senior interviews probe.

Why dual-write instead of a one-shot copy?

Because a one-shot copy of a live warehouse is stale the instant it finishes — new writes keep landing in the warehouse while your copy runs, so the lakehouse is already behind before you can validate it. dual-write fans every ingestion write to both systems during the overlap window, keeping the lakehouse continuously current while the backfill loads history behind it, so the two halves meet at a watermark and reconciliation has a stationary target to converge on. The non-negotiable rule is isolation: the lakehouse write runs in a separate failure domain (a separate consumer group, or a try/except that dead-letters) so a lakehouse outage can never break or block the authoritative warehouse write — during the migration the warehouse is still the source of truth, and dual-write must never put it at risk.

How do backfill and dual-write avoid double-counting on the seam?

The seam is the watermark boundary where the backfilled history meets the live stream, and it is the one place a live migration silently loses or doubles rows. Two rules defeat both failures: choose the stream start T_stream at or before the backfill copy cutoff T_copy so the two ranges overlap (this prevents a gap — rows committed between the cutoffs that neither captured), and write the lakehouse with an idempotent MERGE keyed on the business primary key and guarded by a monotonic _seq version (this dedupes the overlap — a row written by both backfill and stream collapses to one row at its newest version). The overlap is deliberate insurance, not a mistake: it guarantees no gap, and the version-guarded upsert guarantees no double-count, so history and live data meet cleanly with the newest version of every key winning regardless of arrival order.

How do you prove the lakehouse matches the warehouse?

With a tiered reconciliation ladder run every cycle during the parallel run, not a one-time spot-check. Tier 1 is a per-partition COUNT(*) on both systems — cheap, catches gross gaps and double-counts. Tier 2 is aggregate control totals (SUM(amount), COUNT(DISTINCT key)) — cheap and catches value-level drift a count misses. Tier 3 is an order-independent row-hash checksum (BIT_XOR of a strong per-row hash that includes every column plus the version), which is the actual proof: if the partition checksum matches, every row matches. You align both sides to a shared watermark so "the lakehouse is a few minutes behind" never reads as "wrong," run tiers 1–2 fully every cycle and tier 3 on a rolling subset so it stays affordable at scale, and append the three booleans to a reconcile ledger. Cutover eligibility is then a query over that ledger — N consecutive clean cycles — so the go/no-go is auditable and optimism-proof rather than someone eyeballing a dashboard.

What makes a lakehouse cutover zero-downtime?

The combination of dual-write plus a feature-flag reader switch. Because dual-write has kept both systems live and current, cutover is not a data-movement event at all — it is a cutover config flip: each consumer resolves its source (orders_source = warehouse | lakehouse) from a flag at query/run time, and flipping the flag repoints readers with no redeploy and no query interruption. The flip is gated on the reconcile ledger, so only objects that passed validation can be served, and it happens wave by wave so blast radius is bounded to one wave rather than all consumers. There is never a freeze window because you are not copying data at cutover time — the lakehouse was already current via dual-write, and the flag simply chooses which of two live systems each reader talks to. That is the zero-downtime parallel run model.

When is it safe to decommission the warehouse?

Only behind a decommission gate — never on a calendar date. The gate requires N clean post-cutover reconcile cycles (proving the lakehouse holds up under real read traffic after readers moved, not just during the parallel run) plus explicit consumer sign-off per wave. Even then, teardown is ordered to preserve rollback until the last safe moment: stop the dual-write to the warehouse (reversible — you can restart it), freeze the warehouse read-only for a grace period of one to two weeks (reversible — the data is intact and a wave can still be reverted to it), and only after the grace period elapses with no rollback invoked do you drop it (irreversible). Throughout cutover and the grace period the dual-write keeps the warehouse current so rollback stays an instant, lossless flag flip. The warehouse is retired on accumulated evidence, sign-off, and an unused grace window — the discipline that keeps a migration from ever becoming an unrecoverable incident.

Practice on PipeCode

• Drill the SQL practice library → for the watermark, dedupe, checksum, and diff queries that reconciliation and backfill live on.
• Rehearse on the ETL practice library → for idempotent upserts, dual-write fan-out, and partitioned resumable loads.
• Prove the numbers on the data-validation practice library → for the tiered count/aggregate/row-hash reconciliation that gates cutover.
• Stack the design fundamentals against PipeCode's broader 450+ data-engineering catalogue to anchor the four-move loop against real graded inputs.

Lock in migration muscle memory

Docs explain table formats. PipeCode drills explain the decision — when the lakehouse write must be isolated from the warehouse, when the watermark seam silently drops rows, when a row-hash is the only proof that counts, when to roll a wave back and when to finally decommission. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice data-validation problems →