Backfilling Data Without Breaking Production

backfilling data is the operation where you reload weeks, months, or years of history into a table that a live pipeline is still actively writing to — and it is the single routine data-engineering task most likely to trigger a production incident, because the naive version ("just re-run the DAG for all of history") saturates the warehouse, races the hourly job, and silently double-counts rows in the exact window where correctness matters most. A schema change adds a column that needs computing for old rows; a transform bug shipped three months ago and every historical partition is now wrong; a new downstream consumer wants two years of context on day one. Each of these is a historical reload, and each one asks you to pour a firehose of old data into the same pipes that are carrying today's traffic — without corrupting current data and without the catchup job knocking the live pipeline over.

This guide is the senior-DE walkthrough you wished existed the first time an on-call handoff said "we need to reprocess Q1, but the pipeline can't go down." It covers the four things that separate a safe backfill from an outage: making the reload idempotent backfill so a retry never double-writes, scoping the work to partition backfill units that are individually atomic and resumable, throttling the throughput so the backfill never wins the resource fight against live traffic, and reconciling the seam where the backfill window overlaps the live window so one row ends up with one truth — plus the reprocessing orchestration in Airflow that batches history into independent intervals and the dual-write cutover pattern for zero-downtime schema migrations. 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. Examples are PostgreSQL and Airflow, but the mental model carries to Spark, BigQuery, Snowflake, and every batch engine.

When you want hands-on reps immediately after reading, drill the ETL practice library →, rehearse the batch mechanics on the data-processing practice library →, and sharpen the upsert and transaction skills on the database practice library →.

---

On this page

• Why backfills break production
• Idempotent, partition-scoped backfills
• Throttling and resource isolation
• Reconciling backfill vs the live pipeline
• Orchestrating backfills — Airflow catchup and batching
• Cheat sheet — backfill recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why backfills break production

Three failure classes and four axes — the backfill is a concurrency problem wearing an ETL costume

The one-sentence invariant: a backfill is not "the normal pipeline run again for old dates" — it is a large, unbounded, concurrent second writer competing with your live pipeline for the same compute, the same tables, and the same time-windows, so it fails in three distinct ways (resource contention, correctness clobbering, and ordering races) that you must design against explicitly before a single historical partition is touched. The reason "re-run the DAG for all of history" pages you at 3 AM is that the normal pipeline was tuned for one interval's worth of data arriving once, while the backfill hands it thousands of intervals arriving as fast as the scheduler allows, all while the hourly job keeps writing to the same destination. The volume changes the physics: an operation that is safe at 1× concurrency and O(one day) rows becomes an incident at 100× concurrency and O(three years) rows.

The three failure classes.

• Resource contention. The backfill and the live pipeline share a warehouse, a database, a Kafka cluster, or a Spark pool. The backfill's throughput is bounded only by how much it can grab, so it grabs everything — connection-pool slots, warehouse credits, disk IO, lock queues — and the live pipeline's p99 latency blows out. The OLTP app that shares the source database starts timing out. This is the most common backfill outage and the easiest to prevent with throttling and isolation.
• Correctness clobbering. The backfill writes to rows the live pipeline also owns. A plain INSERT double-counts; a plain DELETE + INSERT on a partition the live job is mid-write to loses live rows; a non-deterministic transform produces different values on the backfill run than the original run, so the "fixed" history disagrees with the un-fixed present. This is the failure that is invisible until an analyst notices revenue doesn't tie out.
• Ordering races. The backfill for 2024-06 and the live pipeline for 2026-09 are fine — disjoint windows. The danger is the seam: the recent window that both the backfill (catching up to now) and the live pipeline (writing now) touch. Whoever writes last wins, and if that ordering is undefined, the row that survives is a coin flip. Every senior backfill design draws an explicit boundary at the seam.

The four axes that decide whether a backfill is safe.

• Idempotency. Can you run the backfill twice — because it will fail partway and retry — and get exactly the same table? If the answer is "no", you do not have a backfill, you have a foot-gun. Idempotency is achieved by upsert-by-key (MERGE / INSERT ... ON CONFLICT) or full-partition replace (INSERT OVERWRITE / delete-insert scoped to a partition), never by blind INSERT.
• Isolation. Does the backfill run on separate resources (a separate warehouse, a dedicated connection pool, an off-peak window, a throttled lane) so it cannot starve the live pipeline? A backfill that shares an unbounded resource pool with production is one busy afternoon away from an outage.
• Throughput budget. How many rows/second, partitions/hour, or concurrent tasks is the backfill allowed to consume? This is a number you choose and enforce (rate limiter, max_active_runs, pool slots), not a number you discover after the incident.
• Reconciliation. After the backfill finishes, how do you prove the target matches the source and that the seam with the live pipeline is consistent? Row-count parity plus a checksum, at partition granularity, is the acceptance test. A backfill without a reconciliation step is a backfill you can't sign off.

The 2026 reality — backfills are routine, so the runbook must be too.

• Schema evolution drives most backfills. Adding a NOT NULL column, a derived metric, or a new dimension means every historical row needs recomputation. This is the single most common trigger and almost always a partition backfill over a date range.
• Bug fixes drive the scariest backfills. A transform shipped wrong; three months of a fact table are corrupt; you must reprocess the affected window while the same buggy-then-fixed pipeline keeps running. Correctness at the seam is everything.
• New consumers drive the largest backfills. A new warehouse, a new ML feature store, a new search index wants full history on day one. These are the multi-terabyte, multi-day reloads where throttling and orchestration matter most.
• The naive approach is the anti-pattern. airflow dags backfill -s 2023-01-01 -e 2026-09-01 my_dag with default settings will launch as many concurrent runs as the scheduler allows, against the same pools the live DAG uses, with no rate limit and no reconciliation. It is the number-one cause of self-inflicted data-platform outages.

What interviewers listen for.

• Do you say "is it idempotent?" before writing any code? — required answer.
• Do you scope to partitions and treat one partition as one atomic, retryable unit? — senior signal.
• Do you name isolation and throttling ("separate pool, rate limit, off-peak") unprompted? — senior signal.
• Do you make reconciliation a first-class step, not an afterthought? — senior signal.
• Do you describe a backfill as "a second concurrent writer" rather than "just running the job again"? — required answer.

Worked example — the backfill risk matrix

Detailed explanation. The most useful artifact for a backfill interview (and for a real backfill design doc) is a risk matrix that maps each failure class to its trigger, its symptom, and its mitigation. Every senior backfill kickoff meeting converges on this table; having it in your head turns a vague "let's be careful" into a concrete checklist. Walk through building it for a canonical case: a fact_orders table in Snowflake fed hourly by a live pipeline, where you must reload 18 months of history after adding a margin_cents column.

• Source. raw.orders — append-only event log in the warehouse, partitioned by day.
• Target. analytics.fact_orders — hourly-refreshed fact table the live pipeline owns.
• Change. New column margin_cents computed from total_cents - cost_cents; must be populated for all 18 months.
• Constraint. The live hourly pipeline cannot pause; analysts query fact_orders all day.

Question. Build the risk matrix for the fact_orders backfill and pick a mitigation for each failure class.

Input.

Failure class	Trigger	Symptom
Resource contention	backfill shares the live warehouse	live hourly job queues; dashboards slow
Correctness clobber	backfill re-inserts existing rows	margin_cents fixed but order counts double
Ordering race	backfill catches up into the live window	recent hours flip between backfilled and live values
Non-determinism	transform reads now() or a mutable dim	backfilled rows disagree with original rows

Code.

-- The UNSAFE backfill (what NOT to do): blind insert, no scope, shared warehouse
INSERT INTO analytics.fact_orders
SELECT
    o.order_id,
    o.customer_id,
    o.total_cents,
    o.total_cents - o.cost_cents AS margin_cents,   -- the new column
    o.order_ts
FROM raw.orders o
WHERE o.order_ts >= '2025-03-01';                   -- 18 months, all at once
-- Problems: (1) duplicates every existing row  (2) runs on the live warehouse
--           (3) no partition scope  (4) no way to retry without more duplicates

Step-by-step explanation.

1. The blind INSERT ... SELECT is the anti-pattern in one statement. Because fact_orders already contains rows for the 18-month window (the live pipeline has been writing them), the backfill adds a second copy of every row — the margin_cents column is now correct, but every count, sum, and average doubles. This is correctness clobbering by duplication.
2. Running it against the live warehouse is resource contention: an 18-month scan-and-insert is a large query that competes with the hourly job and every analyst dashboard for the same compute. The live pipeline's SLA is now hostage to the backfill's runtime.
3. There is no partition scope, so if the statement fails at row 400 million (network blip, timeout, spot-instance reclaim), you cannot retry — retrying re-inserts the 400 million rows that already landed. The whole thing is one non-resumable, non-idempotent unit.
4. If the transform were non-deterministic (say margin_cents depended on a dim_cost table that the live pipeline keeps updating), the backfilled value for an old order would reflect today's cost, not the cost at order time. The fix must read point-in-time inputs, not mutable current state.
5. The mitigations fall out of the matrix: isolate resources (separate warehouse / pool), scope to partitions (one day at a time), make it idempotent (replace the partition, don't append), and draw a seam so the backfill window and the live window don't fight — each is a section of this guide.

Output.

Failure class	Mitigation	Section
Resource contention	separate warehouse / pool + rate limit	§3 throttling
Correctness clobber	idempotent per-partition replace / upsert	§2 idempotency
Ordering race	watermark seam + last-writer-wins	§4 reconcile
Non-determinism	point-in-time inputs; deterministic transform	§2 idempotency

Rule of thumb. Never write a backfill as a single unscoped statement. Write the risk matrix first — contention, clobber, race, non-determinism — and confirm you have a named mitigation for each of the four before you touch production data.

Worked example — what interviewers actually probe

Detailed explanation. The senior backfill interview follows a predictable arc: an open-ended prompt ("we shipped a bug, three months of data are wrong, fix it") that the strongest candidates immediately reframe as a concurrency-and-idempotency problem, and the weakest treat as "run the job again." The interviewer then narrows with follow-ups that each probe one of the four axes. Walk through the grading rubric so you can pre-empt every follow-up.

• Ambiguous opener. "A transform bug corrupted three months of fact_orders. How do you fix it?" — invites you to name idempotency and scoping.
• Follow-up 1. "The live pipeline can't stop. How do you avoid a double-count?" — probes correctness clobbering.
• Follow-up 2. "The table also serves the OLTP dashboards. How do you avoid slowing them down?" — probes resource isolation.
• Follow-up 3. "How do you know the backfill worked?" — probes reconciliation.
• Follow-up 4. "Halfway through, it crashes. What happens?" — probes idempotency and resumability.

Question. Draft a five-point senior backfill answer that covers all four axes without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
First move	"re-run the DAG for those dates"	"make it idempotent and partition-scoped first"
Double-count	"delete then insert"	"replace per partition atomically, or MERGE by key"
Contention	"the warehouse can handle it"	"separate pool + rate limit + off-peak window"
Verification	"spot-check a few rows"	"row-count + checksum reconcile per partition"
Crash recovery	"start over"	"resume from the last completed partition checkpoint"

Code.

Senior backfill answer template (5 points)
==========================================

1 — Reframe: this is a concurrent second writer, not a re-run.
    "I'll treat the backfill as idempotent, partition-scoped units so a
     retry never double-writes."

2 — Idempotency + scope:
    "One day-partition = one atomic unit. I replace the partition
     (INSERT OVERWRITE / delete-insert in a txn) or MERGE by natural key.
     Re-running a partition yields the identical result."

3 — Isolation + throttle:
    "The backfill runs on a separate warehouse / pool with a rate limit
     (partitions/hour) and off-peak windowing, so the live hourly job and
     the dashboards never lose the resource fight."

4 — Seam + reconcile:
    "I draw a watermark boundary between the backfill window and the live
     window; the overlap is deduped last-writer-wins by a version column.
     Every partition is accepted only after row-count + checksum match
     the source."

5 — Resumability:
    "A checkpoint table records completed partitions. A crash resumes from
     the last completed one; nothing is reprocessed unnecessarily and
     nothing is skipped."

Step-by-step explanation.

1. Point 1 is the crucial reframe. Saying "this is a concurrent second writer" signals you understand why backfills are dangerous — it is a concurrency problem, and every downstream decision follows from that framing. Weak candidates never leave the "just run it again" mental model.
2. Point 2 pairs idempotency with partition scoping because they are the same idea at two levels: the unit must be atomic (partition) and the operation must be replayable (upsert / overwrite). Naming both, and giving the concrete SQL primitive, is the senior tell.
3. Point 3 addresses contention before the interviewer asks. "Separate pool + rate limit + off-peak" is the three-part isolation answer; say all three unprompted so the interviewer never gets to ask "what about the dashboards?"
4. Point 4 makes reconciliation and the seam first-class. The watermark boundary answers the ordering-race follow-up, and count+checksum answers the "how do you know it worked?" follow-up — two probes handled in one sentence.
5. Point 5 covers crash recovery with a checkpoint table. "Resume from the last completed partition" beats "start over" and shows you have actually operated a multi-day backfill, where crashes are certain, not hypothetical.

Output.

Grading criterion	Weak score	Senior score
Reframes as concurrency	rare	mandatory
Names idempotency + scope	occasional	mandatory
Names isolation + throttle	rare	senior signal
Names reconciliation	rare	senior signal
Names resumability	rare	senior signal

Rule of thumb. The senior backfill answer is a five-point monologue that covers idempotency, scope, isolation, reconciliation, and resumability before the follow-ups arrive. Rehearse it once; deploy it on every "we need to reload history" prompt.

Worked example — the "should I backfill in place or dual-write" decision

Detailed explanation. Before choosing how to throttle and orchestrate, the senior engineer chooses where the backfill writes: into the live table (in place) or into a shadow copy that is atomically swapped in at the end (dual-write / blue-green). The decision hinges on how tolerant the consumers are of transient inconsistency and how risky an in-place mistake would be. Walk the decision with three scenarios: a widely-queried fact table, an append-only event archive, and a NOT NULL column addition.

• Q1. Can consumers tolerate the target being partially backfilled (some partitions new-schema, some old) for hours or days? → yes = in place is fine; no = consider dual-write.
• Q2. Is the change destructive/irreversible if wrong (drop column, type change, NOT NULL)? → yes = dual-write to a shadow table and swap; no = in place with idempotent per-partition replace.
• Q3. Is the table small enough that a full shadow copy is cheap? → yes = dual-write is low-cost insurance; no = in place partition-by-partition to avoid doubling storage.
• Q4 (parallel). Do you need zero-downtime cutover with instant rollback? → yes = dual-write + atomic view/table swap; no = in-place is simpler.

Question. Walk the decision for the three scenarios and record the strategy each ends up with.

Input.

Scenario	Q1 (tolerate partial?)	Q2 (destructive?)	Q3 (small?)
Widely-queried fact table	no	no	no
Append-only event archive	yes	no	no
NOT NULL column addition	no	yes	yes

Code.

# Decision helper (illustrative)
def choose_backfill_strategy(tolerate_partial: bool,
                             destructive: bool,
                             small_table: bool) -> str:
    if destructive:
        return "dual-write to shadow table + atomic swap"
    if not tolerate_partial and small_table:
        return "dual-write to shadow table + atomic swap"
    return "in-place idempotent per-partition replace"


print(choose_backfill_strategy(False, False, False))
# -> in-place idempotent per-partition replace
print(choose_backfill_strategy(True, False, False))
# -> in-place idempotent per-partition replace
print(choose_backfill_strategy(False, True, True))
# -> dual-write to shadow table + atomic swap

Step-by-step explanation.

1. Scenario 1 — a widely-queried fact table, not destructive, too large to duplicate cheaply. It cannot tolerate a full-table shadow copy (storage doubling), so the answer is in-place with idempotent per-partition replace: each day-partition is swapped atomically, so a query either sees the fully-backfilled partition or the old one, never a torn state.
2. Scenario 2 — an append-only event archive that tolerates partial backfill (consumers filter by date and don't care that 2024 is reprocessed while 2025 waits). In-place per-partition is ideal and cheapest; there is no correctness pressure to hide the intermediate state.
3. Scenario 3 — adding a NOT NULL column is destructive-ish: you cannot add the constraint until every row has a value, and a botched in-place run could leave the table in a state that rejects live writes. Dual-write to a shadow table, backfill it fully, validate, then atomically swap (rename or view repoint) — with instant rollback by swapping back.
4. The Q4 parallel branch (zero-downtime cutover) always pushes toward dual-write, because an atomic table/view swap is the only way to flip all consumers at once with a single reversible operation. In-place backfills are eventually-consistent by construction.
5. If none of the destructive/partial/small conditions force dual-write, prefer in-place per-partition — it is simpler, cheaper, and resumable. Reserve dual-write for destructive schema changes and small tables where the shadow copy is cheap insurance.

Output.

Scenario	Strategy	Cutover
Widely-queried fact table	in-place per-partition replace	none (eventually consistent)
Append-only event archive	in-place per-partition replace	none
NOT NULL column addition	dual-write to shadow + swap	atomic rename / view repoint

Rule of thumb. Backfill in place with idempotent per-partition replace by default; reach for dual-write to a shadow table only when the change is destructive, the table is small, or you need a zero-downtime cutover with instant rollback. Decide this before you write any throttling or orchestration code.

Data engineering interview question on backfill risk

A senior interviewer often opens with: "You inherit a nightly pipeline that populates analytics.fact_orders from raw.orders. A transform bug has been producing wrong revenue_cents for the last 90 days, but the same pipeline (now fixed) keeps running every night. Walk me through how you'd reprocess the 90 corrupted days without taking the pipeline down, without double-counting, and without slowing the analyst dashboards that query this table all day."

Solution Using idempotent per-partition reprocessing with a checkpoint and a reconcile gate

# reprocess_fact_orders.py — safe 90-day in-place backfill
import hashlib
import psycopg2
from datetime import date, timedelta

BATCH_DAYS = 1                     # one day-partition = one atomic unit
START = date(2026, 6, 7)
END   = date(2026, 9, 4)          # exclusive; 90 partitions

def daterange(start: date, end: date):
    d = start
    while d < end:
        yield d
        d += timedelta(days=1)

def already_done(conn, part: date) -> bool:
    with conn.cursor() as cur:
        cur.execute(
            "SELECT 1 FROM backfill_checkpoint "
            "WHERE table_name='fact_orders' AND partition_date=%s AND status='done'",
            (part,))
        return cur.fetchone() is not None

def reprocess_partition(conn, part: date) -> None:
    if already_done(conn, part):
        return                     # resumable: skip completed partitions
    with conn:                     # one transaction per partition (atomic)
        with conn.cursor() as cur:
            # 1. Idempotent replace: delete the partition, re-insert corrected rows
            cur.execute(
                "DELETE FROM analytics.fact_orders WHERE order_date = %s", (part,))
            cur.execute("""
                INSERT INTO analytics.fact_orders
                    (order_id, customer_id, order_date, revenue_cents)
                SELECT order_id, customer_id, order_date,
                       total_cents - discount_cents AS revenue_cents  -- FIXED transform
                FROM   raw.orders
                WHERE  order_date = %s
            """, (part,))
            # 2. Reconcile gate: source vs target row count for this partition
            cur.execute(
                "SELECT count(*) FROM raw.orders WHERE order_date=%s", (part,))
            src = cur.fetchone()[0]
            cur.execute(
                "SELECT count(*) FROM analytics.fact_orders WHERE order_date=%s", (part,))
            tgt = cur.fetchone()[0]
            if src != tgt:
                raise ValueError(f"reconcile fail {part}: src={src} tgt={tgt}")
            # 3. Checkpoint inside the same txn -> exactly-once effect
            cur.execute("""
                INSERT INTO backfill_checkpoint(table_name, partition_date, status, rows)
                VALUES ('fact_orders', %s, 'done', %s)
                ON CONFLICT (table_name, partition_date)
                DO UPDATE SET status='done', rows=EXCLUDED.rows
            """, (part, tgt))

-- Checkpoint table: the resumability + audit backbone
CREATE TABLE IF NOT EXISTS backfill_checkpoint (
    table_name      TEXT        NOT NULL,
    partition_date  DATE        NOT NULL,
    status          TEXT        NOT NULL DEFAULT 'pending',
    rows            BIGINT,
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
    PRIMARY KEY (table_name, partition_date)
);

Step-by-step trace.

Step	Before (naive re-run)	After (safe reprocess)
Unit of work	90 days in one statement	one day-partition per transaction
Idempotency	none (blind insert)	delete-then-insert per partition
Double-count risk	high	zero (partition replaced, not appended)
Crash recovery	restart from scratch	resume from last checkpoint
Verification	manual spot-check	row-count reconcile gate per partition
Live-pipeline impact	competes at full speed	one small partition at a time (throttleable)

After deployment, each of the 90 corrupted partitions is deleted and re-inserted with the fixed revenue_cents transform inside its own transaction; a query against fact_orders sees either the old or the new partition atomically, never a torn half-state. A crash at partition 47 resumes at 47 on restart because 1–46 are marked done. The reconcile gate refuses to checkpoint any partition whose target count disagrees with the source.

Output:

Metric	Before	After
Double-counted rows	~all 90 days	0
Retryable	no	yes (per partition)
Torn-read window	whole run	none (atomic per partition)
Reconcile coverage	none	100% of partitions
Dashboard impact	full-speed contention	one throttleable partition at a time

Why this works — concept by concept:

• Partition as the atomic unit — one day-partition per transaction means every write is small, retryable, and atomic. A reader sees a partition either fully corrected or fully old; there is no half-written state to query.
• Delete-then-insert in one transaction — deleting the partition before re-inserting makes the operation idempotent: re-running produces the identical partition, so retries never double-count. The transaction boundary guarantees the delete and insert commit together.
• Reconcile gate before checkpoint — comparing source and target row counts before recording the partition as done turns a silent corruption into a loud failure. A partition is only accepted when it provably matches the source.
• Checkpoint in the same transaction — committing the checkpoint atomically with the data write gives an exactly-once effect: "data written" and "marked done" can never disagree, so resume-from-checkpoint is always correct.
• Cost — O(one partition) memory and lock footprint per step instead of O(90 days); 90 small transactions instead of one giant one. The added cost is a checkpoint row per partition (~free); the eliminated cost is the double-counted rows, the non-resumable failure, and the dashboard outage. Net O(rows) work spread over throttleable units instead of one contended blast.

ETL
Topic — etl
ETL problems on backfills and historical reloads

Practice →

Data Topic — data-processing Data-processing problems on idempotent batch writes

Practice →

---

2. Idempotent, partition-scoped backfills

idempotent backfill means run-twice-same-table — achieved by upsert-by-key or full-partition replace, never by blind insert

The mental model in one line: an idempotent backfill is one where re-running any unit of work produces the exact same target state, which you guarantee by making each partition backfill unit either an upsert-by-natural-key (MERGE / INSERT ... ON CONFLICT) or a full-partition replace (INSERT OVERWRITE / delete-then-insert scoped to one partition inside a transaction) — and by making the transform itself deterministic so the same input always yields the same output. Backfills fail partway; they get retried; the orchestrator relaunches them; a human re-triggers a range "just to be safe." If any of those replays changes the table, you have a correctness bug that scales with how many times you retry. Idempotency is not a nice-to-have — it is the property that makes a backfill operable.

The four idempotent write primitives.

• Upsert by natural key (MERGE / INSERT ... ON CONFLICT). The target has a primary/unique key; the backfill inserts new rows and updates existing ones by that key. Re-running upserts the same rows to the same values — idempotent by construction. Best when the backfill touches individual rows scattered across partitions, or when the live pipeline and backfill share keys.
• Full-partition replace (INSERT OVERWRITE / delete-then-insert). The unit is a whole partition; the backfill deletes the partition and re-inserts it (Spark/Hive INSERT OVERWRITE PARTITION, or DELETE WHERE part=X; INSERT ... in one transaction). Re-running replaces the same partition with the same rows — idempotent. Best when the backfill recomputes an entire partition (schema change, transform fix).
• Truncate-and-reload (whole table). The nuclear option: replace the entire table atomically (usually via dual-write + swap). Idempotent but not partition-scoped, so it forfeits resumability and throttleability. Reserve for small tables.
• Merge-into with a version guard. Upsert only when the incoming row's version/processing-time is newer than the stored one (WHEN MATCHED AND src.version > tgt.version THEN UPDATE). This is idempotent and safe against racing the live pipeline — the foundation of §4's reconciliation.

Partition scoping — why one partition is the right unit.

• Atomicity. A partition replace commits as one unit; a reader never sees a half-rebuilt partition. Scope any smaller (per-row) and you lose the atomic swap; any larger (whole table) and you lose resumability.
• Retryability. If partition 2024-06-14 fails, you retry only 2024-06-14. The blast radius of a failure is one partition, not the whole range.
• Parallelism with a cap. Independent partitions can run concurrently — but bounded, so the backfill's throughput stays within the throttle budget (§3). Partitions are the natural granularity for max_active_runs and pool slots.
• Reconciliation granularity. You verify (count + checksum) per partition, so a mismatch localizes to one partition instead of "somewhere in three years."

Determinism — the silent idempotency killer.

• No wall-clock inputs. A transform that reads now(), current_date, or a random seed produces different output on the backfill run than the original run. Use the partition's data-interval / logical date, never the physical run time.
• Point-in-time dimension joins. Joining a fact to a dimension that the live pipeline keeps mutating gives the backfill today's dimension value for an old fact. Use slowly-changing-dimension (SCD-2) effective-dated joins so an old fact gets the dimension value that was valid at fact time.
• Stable ordering and dedup. If the source has duplicates, dedup deterministically (ROW_NUMBER() OVER (PARTITION BY key ORDER BY event_ts, source_offset)) so the same row wins every run. Non-deterministic tie-breaks make the backfill non-idempotent even with a MERGE.
• Deterministic aggregations. SUM, COUNT are deterministic; ARRAY_AGG / STRING_AGG without an ORDER BY are not (order varies), which breaks checksum reconciliation. Always order inside order-sensitive aggregates.

Common interview probes on idempotent backfills.

• "How do you make a backfill idempotent?" — required answer: upsert-by-key or full-partition replace, never blind insert.
• "What's your atomic unit?" — one partition, replaced/committed atomically.
• "How do you avoid double-counting?" — replace the partition or MERGE by key; don't append.
• "What makes a transform non-idempotent?" — wall-clock inputs, mutable-dimension joins, non-deterministic dedup/aggregation.

Worked example — idempotent MERGE by natural key

Detailed explanation. The canonical row-level idempotent backfill: a fact_orders table keyed by order_id, backfilled with a corrected revenue_cents, using INSERT ... ON CONFLICT (Postgres) / MERGE (Snowflake/BigQuery). Re-running the exact same backfill upserts the same rows to the same values, so retries are free. Walk through the primitive.

• Key. order_id is the natural key and the target's primary key.
• Operation. Insert new orders; update existing ones' revenue_cents.
• Idempotency. Second run sets the same values; row count unchanged.

Question. Write an idempotent upsert that backfills revenue_cents for a date range and prove a second run is a no-op on row count.

Input.

Column	Role
order_id	natural key (PK)
customer_id	dimension
order_date	partition column
revenue_cents	value being backfilled (corrected)

Code.

-- Postgres: idempotent upsert by natural key (order_id)
INSERT INTO analytics.fact_orders
    (order_id, customer_id, order_date, revenue_cents)
SELECT
    o.order_id,
    o.customer_id,
    o.order_date,
    o.total_cents - o.discount_cents AS revenue_cents   -- corrected transform
FROM raw.orders o
WHERE o.order_date >= %(start)s
  AND o.order_date <  %(end)s
ON CONFLICT (order_id) DO UPDATE
    SET revenue_cents = EXCLUDED.revenue_cents,
        customer_id   = EXCLUDED.customer_id;
-- Re-running this statement updates the same rows to the same values.
-- No new rows appear on the second run; the table is unchanged.

-- Snowflake / BigQuery equivalent using MERGE
MERGE INTO analytics.fact_orders AS tgt
USING (
    SELECT order_id, customer_id, order_date,
           total_cents - discount_cents AS revenue_cents
    FROM   raw.orders
    WHERE  order_date >= :start AND order_date < :end
) AS src
ON tgt.order_id = src.order_id
WHEN MATCHED THEN UPDATE SET
    tgt.revenue_cents = src.revenue_cents,
    tgt.customer_id   = src.customer_id
WHEN NOT MATCHED THEN INSERT (order_id, customer_id, order_date, revenue_cents)
    VALUES (src.order_id, src.customer_id, src.order_date, src.revenue_cents);

Step-by-step explanation.

1. The ON CONFLICT (order_id) clause keys idempotency on the natural key: if the row already exists (the live pipeline inserted it), the backfill updates it instead of inserting a duplicate. This is the single most important line — it converts a double-counting INSERT into a safe upsert.
2. EXCLUDED.revenue_cents refers to the value the backfill would have inserted; DO UPDATE SET revenue_cents = EXCLUDED.revenue_cents overwrites the stored (corrupt) value with the corrected one. The update is scoped to exactly the columns the backfill owns.
3. The WHERE order_date >= start AND < end clause makes the statement partition-range-scoped, so the orchestrator can call it per day (or per week) as an independent unit rather than one 18-month blast.
4. The MERGE form is the portable equivalent for Snowflake/BigQuery/Delta: WHEN MATCHED handles updates, WHEN NOT MATCHED handles inserts. It is idempotent for the same reason — the match key deduplicates against existing rows.
5. Because the transform (total_cents - discount_cents) is a pure function of columns in the source row, it is deterministic: the same order_id yields the same revenue_cents on every run. Combined with the key-based upsert, the whole statement is idempotent — the acceptance test is that a second run changes zero row counts.

Output.

Run	New rows inserted	Rows updated	Total row count
1 (backfill)	0 (all exist)	1,240,110	8,455,201
2 (retry)	0	1,240,110	8,455,201
3 (retry)	0	1,240,110	8,455,201

Rule of thumb. When the backfill touches individual rows that may already exist, key idempotency on the natural key with INSERT ... ON CONFLICT DO UPDATE (Postgres) or MERGE (warehouse). Confirm idempotency by re-running and asserting the row count is unchanged — that assertion belongs in the reconcile gate.

Worked example — full-partition replace with INSERT OVERWRITE

Detailed explanation. When the backfill recomputes an entire partition (a schema change touching every row, or a transform fix), full-partition replace is cleaner than row-level upsert: delete the partition, re-insert it, atomically. In Spark/Hive/Delta this is INSERT OVERWRITE PARTITION; in a transactional DB it is DELETE WHERE part=X; INSERT ... in one transaction. Walk through both.

• Unit. One partition (order_date = '2024-06-14').
• Operation. Overwrite the whole partition with recomputed rows.
• Atomicity. Overwrite is atomic; readers see old-or-new, never torn.

Question. Replace a single day-partition idempotently in both Spark (dynamic partition overwrite) and Postgres (delete-insert in a transaction).

Input.

Parameter	Value
Partition column	order_date
Partition	2024-06-14
Engine A	Spark / Delta (INSERT OVERWRITE)
Engine B	Postgres (delete + insert txn)

Code.

# Spark / Delta — dynamic partition overwrite (idempotent per partition)
spark.conf.set("spark.sql.sources.partitionOverwriteMode", "dynamic")

(spark.read.table("raw.orders")
      .where("order_date = '2024-06-14'")
      .selectExpr(
          "order_id", "customer_id", "order_date",
          "total_cents - discount_cents AS revenue_cents")  # fixed transform
      .write
      .mode("overwrite")                 # only the touched partition is replaced
      .partitionBy("order_date")
      .insertInto("analytics.fact_orders"))
# Re-running replaces the SAME partition with the SAME rows -> idempotent.
# 'dynamic' mode overwrites only order_date=2024-06-14, not the whole table.

-- Postgres — delete + insert scoped to one partition, in one transaction
BEGIN;
    DELETE FROM analytics.fact_orders
    WHERE  order_date = DATE '2024-06-14';

    INSERT INTO analytics.fact_orders
        (order_id, customer_id, order_date, revenue_cents)
    SELECT order_id, customer_id, order_date,
           total_cents - discount_cents
    FROM   raw.orders
    WHERE  order_date = DATE '2024-06-14';
COMMIT;
-- The DELETE + INSERT commit atomically: a concurrent reader sees
-- either the old partition or the new one, never an empty/half state.

Step-by-step explanation.

1. In Spark, partitionOverwriteMode = dynamic is the critical setting: without it, mode("overwrite") replaces the entire table; with it, only the partitions present in the written DataFrame (here just 2024-06-14) are overwritten. This scopes the blast radius to one partition.
2. Re-running the Spark job reads the same source rows, applies the same deterministic transform, and overwrites the same partition — the result is byte-identical, so it is idempotent. A failed run leaves the old partition intact (overwrite is all-or-nothing at the partition level in Delta / with atomic commit protocols).
3. In Postgres, wrapping DELETE + INSERT in one BEGIN/COMMIT makes the replace atomic. Under MVCC, concurrent readers see the pre-transaction snapshot until commit, then the post-transaction snapshot — never the intermediate state where the partition is deleted but not yet re-inserted.
4. This pattern is preferable to row-level upsert when every row in the partition is recomputed: it avoids the overhead of matching each row against existing keys, and it naturally removes rows that should no longer exist (e.g. if the fix drops some rows), which a pure upsert would leave stranded.
5. For real partitioned Postgres tables, the even better form is to build the new partition as a separate table and ALTER TABLE ... ATTACH/DETACH PARTITION for an O(1) atomic swap — the same idea (atomic partition replace) with no delete cost. Choose delete-insert for simplicity, attach/detach for scale.

Output.

Engine	Mechanism	Atomic unit	Removes stale rows?
Spark/Delta	dynamic partition overwrite	partition	yes
Postgres delete-insert	txn-wrapped DELETE+INSERT	partition (via txn)	yes
Postgres attach/detach	build new + swap partition	partition (O(1) swap)	yes
Row-level upsert	MERGE / ON CONFLICT	row	no (stale keys linger)

Rule of thumb. When the backfill recomputes a whole partition, use full-partition replace (Spark INSERT OVERWRITE with dynamic mode, or a txn-wrapped delete-insert / attach-detach in Postgres) rather than row-level upsert. It is atomic, removes rows that should no longer exist, and is idempotent by construction.

Worked example — making the transform deterministic (point-in-time joins)

Detailed explanation. The subtlest backfill bug is a non-deterministic transform that passes every test on new data but produces wrong history. The usual culprit is joining a fact to a current dimension. When you backfill a 2024 order and join to dim_customer, you get the customer's 2026 tier, not their 2024 tier. Walk through fixing it with an SCD-2 effective-dated join.

• Bug. Backfill joins fact_orders to dim_customer on customer_id (current row only).
• Symptom. Backfilled 2024 rows show 2026 customer tiers; they disagree with what the original 2024 pipeline wrote.
• Fix. Join to the SCD-2 dimension on customer_id AND order_ts BETWEEN valid_from AND valid_to.

Question. Rewrite the backfill join so an old fact gets the dimension value that was valid at fact time, making the transform deterministic and idempotent.

Input.

Table	Shape
raw.orders	order_id, customer_id, order_ts, total_cents
dim_customer_scd2	customer_id, tier, valid_from, valid_to
Bug	join on customer_id only (current tier)
Fix	effective-dated join on order_ts

Code.

-- WRONG: joins to the current dimension row -> non-deterministic across time
INSERT INTO analytics.fact_orders (order_id, customer_id, order_date, tier, revenue_cents)
SELECT o.order_id, o.customer_id, o.order_ts::date, c.tier,
       o.total_cents - o.discount_cents
FROM   raw.orders o
JOIN   dim_customer c ON c.customer_id = o.customer_id   -- current tier only!
WHERE  o.order_ts::date = DATE '2024-06-14';

-- RIGHT: effective-dated SCD-2 join -> the tier valid AT order time
INSERT INTO analytics.fact_orders (order_id, customer_id, order_date, tier, revenue_cents)
SELECT o.order_id, o.customer_id, o.order_ts::date, c.tier,
       o.total_cents - o.discount_cents
FROM   raw.orders o
JOIN   dim_customer_scd2 c
       ON  c.customer_id = o.customer_id
       AND o.order_ts >= c.valid_from
       AND o.order_ts <  c.valid_to               -- point-in-time correctness
WHERE  o.order_ts::date = DATE '2024-06-14';

Step-by-step explanation.

1. The wrong version joins dim_customer on customer_id alone, which matches the current dimension row. For a 2024 order that is the 2026 tier — so the backfill overwrites correct 2024 history with wrong 2026-derived values. This is why "the numbers changed after the backfill" incidents happen.
2. The right version joins the SCD-2 table with order_ts >= valid_from AND order_ts < valid_to, selecting the dimension version that was in effect at order time. The [valid_from, valid_to) half-open interval avoids double-matching a row at the boundary instant.
3. This makes the transform a pure function of point-in-time inputs: the same order, joined to the same effective-dated dimension, yields the same tier on every run — the definition of a deterministic, idempotent transform.
4. The same discipline applies to any mutable input: currency rates (use the rate table effective-dated), pricing (price-at-time), org hierarchy (hierarchy-at-time). If the input can change, join it point-in-time or snapshot it, never read "current."
5. Determinism is a precondition for reconciliation: §4's checksum only works if re-running the transform yields identical bytes. A non-deterministic transform makes every checksum comparison flap, so fixing determinism is what makes verification possible at all.

Output.

Order (2024-06-14)	Wrong (current tier)	Right (tier @ order time)
order 501, cust 7	gold (2026)	silver (2024)
order 502, cust 9	platinum (2026)	gold (2024)
Re-run agreement	flaps over time	identical every run

Rule of thumb. A backfill transform must be a pure function of point-in-time inputs. Join every mutable dimension effective-dated (SCD-2 valid_from/valid_to), never to "current," and never read now()/current_date inside the transform. Determinism is what makes idempotency and checksum reconciliation possible.

Data engineering interview question on idempotent partition backfills

A senior interviewer might ask: "You need to backfill a new margin_cents column into an 18-month, 2-billion-row fact_orders table in Snowflake that a live hourly pipeline keeps writing to. Design the idempotent unit of work, the write primitive, the determinism guarantees, and prove that re-running any partition is a no-op."

Solution Using per-partition MERGE with a version guard and deterministic transform

-- 1. One partition = one MERGE, keyed on order_id, guarded by a version column.
--    Called once per day-partition by the orchestrator (§5).
MERGE INTO analytics.fact_orders AS tgt
USING (
    SELECT
        o.order_id,
        o.customer_id,
        o.order_date,
        o.total_cents - o.cost_cents          AS margin_cents,   -- deterministic
        o.updated_at                          AS src_version     -- monotonic per row
    FROM raw.orders o
    JOIN dim_cost_scd2 dc                      -- point-in-time cost, not current
      ON dc.sku = o.sku
     AND o.order_date >= dc.valid_from
     AND o.order_date <  dc.valid_to
    WHERE o.order_date = :partition_date       -- partition scope
) AS src
ON tgt.order_id = src.order_id
WHEN MATCHED AND src.src_version >= tgt.src_version THEN UPDATE SET
    tgt.margin_cents = src.margin_cents,
    tgt.src_version  = src.src_version
WHEN NOT MATCHED THEN INSERT
    (order_id, customer_id, order_date, margin_cents, src_version)
    VALUES (src.order_id, src.customer_id, src.order_date, src.margin_cents, src.src_version);

-- 2. Idempotency assertion (runs in the reconcile gate): re-run count delta = 0
SELECT
    (SELECT count(*) FROM raw.orders          WHERE order_date = :partition_date) AS src_rows,
    (SELECT count(*) FROM analytics.fact_orders WHERE order_date = :partition_date) AS tgt_rows;
-- Acceptance: src_rows = tgt_rows, and a second MERGE reports 0 rows inserted.

Step-by-step trace.

Step	Value	Reasoning
Atomic unit	one day-partition	retryable, throttleable, reconcilable
Write primitive	MERGE on order_id	upsert = idempotent; no double-count
Version guard	src_version >= tgt_version	never overwrites a newer live-pipeline row
Determinism	point-in-time cost join, pure arithmetic	same input -> same margin every run
Idempotency proof	second MERGE inserts 0 rows	run-twice-same-table
Scope	WHERE order_date = :partition	18 months = 540 independent units

After deployment, the orchestrator invokes the MERGE once per day-partition. Each MERGE upserts by order_id, so existing live-pipeline rows are updated (not duplicated) and only if the backfill's version is not older than the stored version — so a backfill can never clobber a fresher live write. Re-running any partition inserts zero new rows and rewrites the same margin_cents, so retries and human re-triggers are safe.

Output:

Metric	Value
Independent units	540 day-partitions
Rows inserted on re-run	0
Double-count risk	none (MERGE by key)
Clobber-newer-row risk	none (version guard)
Determinism	point-in-time join; pure transform

Why this works — concept by concept:

• MERGE by natural key — upsert on order_id deduplicates against existing rows, so the backfill updates the live pipeline's rows instead of inserting duplicates. This is what makes re-running a no-op on row count.
• Version guard — WHEN MATCHED AND src.src_version >= tgt.src_version prevents the backfill from overwriting a newer value the live pipeline wrote after the backfill read its source. It is the row-level form of last-writer-wins that §4 uses at the seam.
• Point-in-time dimension join — joining dim_cost_scd2 effective-dated gives each order the cost that was valid at order time, making the transform deterministic across runs and across calendar time.
• Partition scope — WHERE order_date = :partition turns an 18-month reload into 540 independent, retryable, throttleable units, each individually reconcilable.
• Cost — one MERGE per partition, O(partition rows) each, keyed by an indexed order_id; 540 units instead of one 2-billion-row statement. The added cost is a src_version column and a per-partition reconcile query; the eliminated cost is duplicate rows, clobbered live writes, and non-resumable failure. Net O(rows) spread over 540 idempotent units.

Data
Topic — data-processing
Data-processing problems on idempotent partition writes

Practice →

DB Topic — database Database problems on MERGE, upsert, and transactions

Practice →

---

3. Throttling and resource isolation

throttling means the backfill never wins the resource fight — rate limits, concurrency caps, isolated pools, and backpressure keep production's p99 intact

The mental model in one line: throttling a backfill is the discipline of capping its throughput (rows/second, partitions/hour, concurrent tasks) and running it on isolated resources (a separate warehouse, a dedicated connection pool, an off-peak window) so that no matter how much history it has to move, it cannot starve the live pipeline or the OLTP application that shares the source — and adding backpressure so the backfill automatically slows down when it detects that production is hurting. The backfill has no natural throughput ceiling; it will consume every connection, every warehouse credit, and every IO the platform offers unless you impose one. The whole game is to make the backfill polite: it uses the slack capacity, yields under load, and never turns a maintenance task into an incident.

The three isolation strategies — keep the backfill out of production's lane.

• Separate compute. A dedicated warehouse (Snowflake), a separate Spark pool / YARN queue, a read replica for the source reads, a distinct BigQuery reservation. The backfill's compute is physically separate, so its spikes cannot touch the live pipeline's compute. This is the strongest isolation and the first thing to ask for.
• Separate connection pool. When the backfill must share the same database (source or target), give it its own bounded connection pool (e.g. 4 connections) distinct from the app's pool. The database's lock and buffer contention still exists, but the backfill can never exhaust the app's connections.
• Time isolation (off-peak windows). Run the backfill only during low-traffic hours (nights, weekends) via a schedule window or a "run only if current QPS < threshold" gate. Weakest form alone, but combines well with the others.

Rate limiting — cap the throughput to a number you chose.

• Rows/second (token bucket). The backfill acquires N tokens before writing a batch; the bucket refills at the target rate. This smooths the throughput to a steady, predictable load rather than a spiky blast. The right primitive when writing to a shared OLTP database.
• Partitions/hour (orchestrator concurrency). Cap how many partition-units run concurrently (max_active_runs, pool slots) and how fast new ones launch. The right primitive when each partition is a heavy but self-contained job.
• Batch size + sleep. The simplest form: process chunk_size rows, sleep delay seconds, repeat. Crude but effective for a one-off script; the token bucket is the productionized version.
• Statement timeouts. Cap each backfill statement's runtime (SET statement_timeout) so a pathological query cannot hold locks or buffers indefinitely. A safety net under the rate limiter.

Backpressure — make the backfill yield automatically.

• Watch the right signal. Replica lag, live-pipeline queue depth, OLTP p99 latency, warehouse queue time, or Kafka consumer lag. Pick the metric that most directly reflects "production is hurting."
• Slow down or pause. When the signal crosses a threshold, reduce the rate (halve the token refill) or pause entirely until it recovers. This is a control loop, not a fixed rate — the backfill adapts to real conditions.
• Circuit breaker. A hard stop: if live-pipeline lag exceeds a red-line for M minutes, halt the backfill and alert. Prevents a slow degradation from becoming an outage while on-call sleeps.
• Chunk down under pressure. Beyond pausing, shrink batch size when latency rises so each unit holds locks for less time — reducing contention without stopping entirely.

Common interview probes on throttling.

• "How do you stop a backfill from overwhelming production?" — required answer: isolate resources + rate limit + backpressure.
• "What rate limit do you pick?" — a number you enforce (rows/sec or partitions/hour), tuned against a measured production headroom.
• "What signal drives backpressure?" — replica lag / live-pipeline lag / OLTP p99, with a threshold and a circuit breaker.
• "The backfill shares the source DB — now what?" — separate bounded pool + statement timeout + read from a replica.

Worked example — token-bucket rate limiter for batch writes

Detailed explanation. The canonical throttle for a backfill writing to a shared database: a token-bucket rate limiter that caps writes to a target rows/second. The backfill processes rows in batches, acquiring tokens before each batch; the bucket refills continuously at the target rate, smoothing the load. Build it.

• Target. 5,000 rows/sec sustained (measured to be safe headroom on the shared DB).
• Batch. 1,000 rows per write; acquire 1,000 tokens first.
• Refill. 5,000 tokens/sec, capped at a small burst.

Question. Implement a token-bucket limiter and drive a batched backfill through it at a capped rate.

Input.

Parameter	Value
Target rate	5,000 rows/sec
Batch size	1,000 rows
Bucket capacity (burst)	10,000 tokens
Refill rate	5,000 tokens/sec

Code.

import time
import threading

class TokenBucket:
    """Thread-safe token bucket: cap sustained throughput, allow small bursts."""
    def __init__(self, rate: float, capacity: float):
        self.rate = rate                 # tokens added per second
        self.capacity = capacity         # max burst
        self.tokens = capacity
        self.last = time.monotonic()
        self.lock = threading.Lock()

    def acquire(self, n: int) -> None:
        while True:
            with self.lock:
                now = time.monotonic()
                # refill proportional to elapsed time
                self.tokens = min(self.capacity, self.tokens + (now - self.last) * self.rate)
                self.last = now
                if self.tokens >= n:
                    self.tokens -= n
                    return
                deficit = n - self.tokens
            time.sleep(deficit / self.rate)   # wait just long enough to refill

def backfill_throttled(conn, partition_date, bucket: TokenBucket, batch=1000):
    cur = conn.cursor(name="bf")            # server-side cursor; constant memory
    cur.itersize = batch
    cur.execute("""
        SELECT order_id, customer_id, order_date, total_cents - discount_cents
        FROM   raw.orders WHERE order_date = %s ORDER BY order_id
    """, (partition_date,))
    buf = []
    for row in cur:
        buf.append(row)
        if len(buf) >= batch:
            bucket.acquire(len(buf))         # <-- throttle point
            upsert_batch(conn, buf)          # idempotent MERGE/ON CONFLICT
            buf.clear()
    if buf:
        bucket.acquire(len(buf))
        upsert_batch(conn, buf)

bucket = TokenBucket(rate=5000, capacity=10000)

Step-by-step explanation.

1. The token bucket holds up to capacity tokens and refills at rate tokens/sec. Before writing a batch of n rows, the backfill calls acquire(n), which blocks until n tokens are available. This caps the sustained write rate at rate rows/sec while permitting a small burst up to capacity.
2. acquire refills lazily: on each call it adds (now - last) * rate tokens (clamped to capacity), then either deducts n and returns, or sleeps exactly long enough for the deficit to refill. This gives smooth, precise throttling without a busy-wait.
3. The backfill reads via a server-side cursor (cursor(name=...)) with itersize = batch, so a billion-row partition streams in constant memory rather than loading into RAM — throttling throughput is pointless if the read OOMs the worker.
4. Each batch is written via an idempotent upsert_batch (MERGE / ON CONFLICT), so the throttle composes with §2's idempotency: a batch can be retried without double-writing, and the rate limit bounds the load.
5. The capacity (burst) is deliberately small (2× the target for one second) so the backfill cannot save up tokens during idle time and then dump a huge burst that spikes the DB. Sustained rate is the contract; the small burst just absorbs jitter.

Output.

Interval	Rows written	Effective rate	DB p99 impact
0–10 s	~50,000	~5,000/s	negligible
10–20 s	~50,000	~5,000/s	negligible
Uncapped (no bucket)	~380,000	~38,000/s	p99 3× worse

Rule of thumb. When a backfill shares a database with live traffic, throttle writes through a token bucket at a rate measured to leave headroom for production. Cap the burst small so the backfill stays smooth, and always read via a server-side cursor so the throttle isn't defeated by an OOM.

Worked example — isolated warehouse and connection pool

Detailed explanation. Rate limiting caps throughput; isolation guarantees the backfill's resources are physically distinct from production's. The two together are belt-and-braces. Walk through isolating a Snowflake backfill onto its own warehouse and a Postgres backfill onto its own bounded pool.

• Snowflake. A dedicated BACKFILL_WH (separate virtual warehouse) so backfill queries never queue behind or compete with the live ETL_WH.
• Postgres. A separate role + a bounded connection pool (4 conns) distinct from the app's pool; reads from a replica.
• Result. The backfill physically cannot exhaust production's compute or connections.

Question. Configure an isolated Snowflake warehouse and an isolated Postgres connection pool for the backfill.

Input.

Resource	Production	Backfill
Snowflake warehouse	ETL_WH (L)	BACKFILL_WH (S, auto-suspend)
Postgres pool	app_pool (40 conns)	backfill_pool (4 conns)
Source reads	primary	read replica

Code.

-- Snowflake: a dedicated, small, auto-suspending warehouse for the backfill
CREATE WAREHOUSE IF NOT EXISTS BACKFILL_WH
    WAREHOUSE_SIZE = 'SMALL'
    AUTO_SUSPEND   = 60          -- suspend after 60s idle (cost control)
    AUTO_RESUME    = TRUE
    INITIALLY_SUSPENDED = TRUE;

-- Backfill session pins itself to the isolated warehouse
USE WAREHOUSE BACKFILL_WH;
-- ... run the per-partition MERGE here ...
-- ETL_WH (the live pipeline) is never touched; no query queue contention.

# Postgres: a bounded, isolated pool that reads from a replica
from psycopg2.pool import ThreadedConnectionPool

# The APP uses its own 40-connection pool elsewhere; the backfill gets 4.
backfill_pool = ThreadedConnectionPool(
    minconn=1, maxconn=4,                     # <-- hard cap; cannot exhaust the DB
    host="db-replica.internal",               # <-- read from replica, not primary
    dbname="production", user="backfill_reader",
    options="-c statement_timeout=300000",    # 5-min statement cap (safety net)
)

def with_backfill_conn(fn):
    conn = backfill_pool.getconn()
    try:
        return fn(conn)
    finally:
        backfill_pool.putconn(conn)

Step-by-step explanation.

1. In Snowflake, BACKFILL_WH is a physically separate virtual warehouse: its compute is independent of ETL_WH, so a heavy backfill MERGE cannot make the live pipeline's queries queue. AUTO_SUSPEND = 60 keeps the cost bounded — the warehouse spins down when the backfill pauses between partitions.
2. Pinning the backfill session with USE WAREHOUSE BACKFILL_WH guarantees every backfill statement bills and runs on the isolated compute. The live pipeline continues on ETL_WH untouched — perfect compute isolation.
3. In Postgres, the backfill gets its own ThreadedConnectionPool capped at 4 connections. Even if every backfill connection is busy, the app's 40-connection pool is unaffected — the backfill cannot cause "too many connections" for the app. This is the connection-level isolation that a shared pool would forfeit.
4. Reading from db-replica.internal moves the backfill's read load off the primary entirely, so the OLTP write path on the primary is undisturbed. (Writes still go to the primary or the warehouse target; only the heavy scans hit the replica.)
5. statement_timeout = 300000 (5 min) is the per-statement safety net: a pathological backfill query is killed before it can hold locks or buffers long enough to hurt production. Isolation limits how much the backfill can grab; the timeout limits how long it can hold it.

Output.

Isolation dimension	Shared (risky)	Isolated (safe)
Compute	one warehouse	BACKFILL_WH separate
Connections	one pool	4-conn backfill pool
Read source	primary	replica
Runaway query	unbounded	5-min statement_timeout
Live p99 impact	high	negligible

Rule of thumb. Give the backfill its own compute (a separate warehouse / pool / queue), its own bounded connection pool, and a read replica for scans — then add a per-statement timeout as a safety net. Isolation and rate limiting are complementary: isolation caps how much the backfill can take, rate limiting caps how fast.

Worked example — adaptive backpressure on replica lag

Detailed explanation. A fixed rate limit is tuned for expected conditions; production load varies. Adaptive backpressure closes the loop: the backfill watches a health signal (here, read-replica lag) and slows down or pauses when the signal degrades, then speeds back up when it recovers. Build the control loop with a circuit breaker.

• Signal. Replica lag in seconds (pg_last_xact_replay_timestamp age), a proxy for "the DB is under strain."
• Control. Lag < 5 s → full rate; 5–30 s → halve rate; > 30 s → pause; > 120 s for 5 min → circuit-break and alert.
• Recovery. When lag drops back under 5 s, restore full rate.

Question. Add adaptive backpressure and a circuit breaker on top of the token-bucket backfill.

Input.

Lag	Action
< 5 s	full rate (5,000/s)
5–30 s	half rate (2,500/s)
> 30 s	pause (0/s)
> 120 s sustained 5 min	circuit-break + page

Code.

import time

def replica_lag_seconds(conn) -> float:
    with conn.cursor() as cur:
        cur.execute("""
            SELECT COALESCE(
                EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())), 0)
        """)
        return float(cur.fetchone()[0])

class Backpressure:
    def __init__(self, bucket, base_rate=5000):
        self.bucket = bucket
        self.base = base_rate
        self.red_since = None

    def adjust(self, conn) -> str:
        lag = replica_lag_seconds(conn)
        if lag > 120:
            if self.red_since is None:
                self.red_since = time.monotonic()
            if time.monotonic() - self.red_since > 300:   # 5 min sustained red
                raise RuntimeError(f"CIRCUIT BREAK: replica lag {lag:.0f}s > 120s for 5m")
        else:
            self.red_since = None

        if lag > 30:
            self.bucket.rate = 0                # pause
            time.sleep(10)
            return "paused"
        elif lag > 5:
            self.bucket.rate = self.base / 2    # throttle down
            return "half"
        else:
            self.bucket.rate = self.base        # full speed
            return "full"

# In the backfill loop: call bp.adjust(monitor_conn) between partitions/batches

Step-by-step explanation.

1. replica_lag_seconds reads how far the replica trails the primary. Growing lag is the earliest, most reliable proxy for "the primary is under write pressure" — often earlier than p99 latency alarms fire.
2. adjust is a control loop called between batches (or partitions). It maps the current lag to an action: full rate when healthy, half rate when lag creeps up, full pause when lag is high. Setting bucket.rate directly re-tunes the token bucket from the previous example — backpressure and rate limiting compose.
3. The circuit breaker tracks how long lag has been in the red zone (> 120 s). A momentary spike is tolerated (transient), but sustained red for 5 minutes raises RuntimeError, which halts the backfill and pages on-call. This prevents a slow degradation from silently becoming an outage overnight.
4. Recovery is automatic and hysteresis-free-enough: once lag drops back under 5 s, the rate is restored to base. The red_since timer resets whenever lag leaves the red zone, so only sustained red trips the breaker.
5. The signal is pluggable: replace replica_lag_seconds with live-pipeline queue depth, Kafka consumer lag, or warehouse queue time depending on what most directly reflects production pain. The control-loop shape is identical.

Output.

Time	Replica lag	Rate	State
t0	2 s	5,000/s	full
t1	12 s	2,500/s	half
t2	45 s	0/s	paused
t3	3 s	5,000/s	full (recovered)
t4	130 s for 5 min	—	circuit break + page

Rule of thumb. A fixed rate limit is the floor; adaptive backpressure is the ceiling. Watch replica lag (or live-pipeline lag), throttle down and pause as it degrades, and trip a circuit breaker on sustained red so the backfill halts itself before it becomes an incident.

Systems interview question on throttling a shared-database backfill

A senior interviewer might ask: "You must backfill a 2-billion-row derived column into a Postgres table that also serves your OLTP application's read/write path. The app's p99 must stay under 50 ms throughout. Design the throttling, the isolation, the backpressure signal, and the on-call kill-switch."

Solution Using an isolated pool, token-bucket rate limit, replica-lag backpressure, and a kill-switch

# safe_oltp_backfill.py — throttle + isolate + backpressure + kill-switch
import time
from psycopg2.pool import ThreadedConnectionPool

# 1. ISOLATION: bounded pool, replica reads, statement timeout
pool = ThreadedConnectionPool(
    1, 4, host="db-replica.internal", dbname="production",
    user="backfill_reader",
    options="-c statement_timeout=120000")           # 2-min cap

WRITE_DSN = "host=db-primary dbname=production user=backfill_writer"

# 2. RATE LIMIT: token bucket at 3,000 rows/s (measured safe headroom)
bucket = TokenBucket(rate=3000, capacity=6000)
bp     = Backpressure(bucket, base_rate=3000)

def kill_switch_engaged(conn) -> bool:
    with conn.cursor() as cur:                        # 3. KILL-SWITCH: a flag row
        cur.execute("SELECT paused FROM backfill_control WHERE job='fact_margin'")
        row = cur.fetchone()
        return bool(row and row[0])

def run_partition(part):
    read_conn = pool.getconn()
    try:
        while kill_switch_engaged(read_conn):        # on-call can pause instantly
            time.sleep(15)
        bp.adjust(read_conn)                          # backpressure on replica lag
        for batch in stream_batches(read_conn, part, size=1000):
            bucket.acquire(len(batch))                # throttle
            upsert_batch(WRITE_DSN, batch)           # idempotent MERGE (§2)
    finally:
        pool.putconn(read_conn)

-- Kill-switch control table: on-call flips one boolean to pause the whole backfill
CREATE TABLE IF NOT EXISTS backfill_control (
    job     TEXT PRIMARY KEY,
    paused  BOOLEAN NOT NULL DEFAULT FALSE
);
INSERT INTO backfill_control(job, paused) VALUES ('fact_margin', FALSE)
ON CONFLICT (job) DO NOTHING;

-- To pause instantly during an incident:
--   UPDATE backfill_control SET paused = TRUE WHERE job = 'fact_margin';

Step-by-step trace.

Layer	Mechanism	Protects
Isolation	4-conn pool + replica reads	app connection pool + primary read path
Rate limit	token bucket 3,000 rows/s	primary write path throughput
Statement cap	statement_timeout=120s	lock/buffer hold time
Backpressure	replica-lag control loop	auto-slow when DB strains
Circuit breaker	sustained-red halt + page	overnight silent degradation
Kill-switch	one-boolean control row	instant human pause during incidents

After deployment, the backfill reads through a 4-connection replica pool, writes to the primary at a capped 3,000 rows/sec, and continuously watches replica lag — halving its rate at 5 s, pausing at 30 s, and circuit-breaking on sustained red. On-call can pause the entire job instantly by flipping one boolean, no deploy required. The app's p99 stays under 50 ms throughout because the backfill physically cannot exhaust connections and never exceeds the measured-safe write rate.

Output:

Metric	Value
App p99 during backfill	< 50 ms (unchanged)
Backfill write rate	3,000 rows/s (capped)
Backfill connections	4 (isolated)
Auto-pause trigger	replica lag > 30 s
Human pause	one UPDATE, instant
Kill-to-halt latency	< 15 s (poll interval)

Why this works — concept by concept:

• Bounded isolated pool + replica reads — the backfill's 4-connection pool physically cannot exhaust the app's connections, and reading from the replica keeps heavy scans off the primary's read path.
• Token-bucket rate limit — caps the sustained write rate to the primary at a measured-safe 3,000 rows/s, so the OLTP write path always has headroom.
• Replica-lag backpressure + circuit breaker — a control loop that slows and pauses the backfill as the DB strains, and halts it on sustained red before degradation becomes an outage.
• Kill-switch control row — a single boolean on-call can flip to pause the entire backfill in under 15 seconds, with no code deploy — the fastest possible incident response.
• Cost — 4 connections, one small warehouse/replica of read load, one control-table poll per partition, and a modest total runtime (capped rate = longer wall-clock). The trade is deliberate: the backfill runs slower so production stays fast. O(rows) work at a bounded rate, with p99 protected by construction.

Data
Topic — data-processing
Data-processing problems on throttling and batching

Practice →

DB Topic — database Database problems on connection pools and locking

Practice →

---

4. Reconciling backfill vs the live pipeline

One row, one truth — draw a watermark seam, dedup the overlap last-writer-wins, and prove it with count plus checksum

The mental model in one line: reconciling a backfill against the live pipeline is the act of drawing an explicit watermark boundary between the window the backfill owns and the window the live pipeline owns, resolving the overlap where both wrote to a single truth via last-writer-wins (by a version or processing-time column), and then proving the target matches the source with a per-partition row-count-plus-checksum comparison — because a backfill you can't verify is a backfill you can't sign off. The seam — the recent window that the catching-up backfill and the still-running live pipeline both touch — is where every subtle backfill bug lives. Getting the seam right, and having a reconciliation test that would catch it if you didn't, is the difference between a backfill that ties out and one that quietly corrupts the freshest, most-scrutinized data.

The seam — where backfill meets live.

• The two windows. The backfill owns [history_start, seam); the live pipeline owns [seam, now). If the seam is a clean boundary and neither crosses it, there is no overlap and no conflict. The danger is only in the overlap.
• Why an overlap exists. The backfill "catches up to now" and the live pipeline "starts from now" rarely meet at a perfectly clean instant — the backfill may reprocess the last few hours the live pipeline already wrote, or both may claim the same partition during the cutover. That overlap must be resolved deterministically.
• The seam choice. Set the seam at a partition boundary the live pipeline has definitely finished (e.g. "backfill everything strictly before yesterday; the live pipeline owns yesterday onward"). A partition-aligned seam avoids row-level overlap entirely when possible.
• Freeze or overlap. Either freeze the seam partition (pause live writes to it briefly during cutover) or allow overlap and dedup last-writer-wins. Overlap + dedup is usually preferable because it needs no live-pipeline pause.

Last-writer-wins — resolving the overlap deterministically.

• The version column. Every row carries a monotonic version or processing-time (updated_at, _ingested_at, a Kafka offset, an LSN). On conflict, the higher version wins. This makes the resolution deterministic and order-independent.
• Backfill defers to live for recent rows. In the overlap, the live pipeline's row is usually the newer, authoritative one — so the backfill's MERGE uses WHEN MATCHED AND src.version >= tgt.version (from §2) to avoid clobbering a fresher live write.
• Live defers to backfill for corrections. If the backfill is a correction (fixing a bug), it must win even against a live row — so you bump the backfill's version above any live version, or mark corrected rows explicitly. Decide which side is authoritative per backfill, and encode it in the version comparison.
• No wall-clock ties. Two rows with identical version are a bug; break ties deterministically (source offset, row hash) so the winner is stable across reconciliation runs.

Reconciliation — proving the target matches the source.

• Row-count parity. Per partition, count(source) == count(target). The cheapest check; catches missing or duplicated rows immediately. Always the first gate.
• Checksum / hash. Per partition, a hash of the sorted rows (sum(hash(row)) or HASH_AGG) on both sides. Catches value corruption that row counts miss — a wrong revenue_cents that keeps the row count identical. Requires a deterministic transform (§2).
• Aggregate parity. Business-level totals (SUM(revenue), distinct customers) per partition, compared to an independent source of truth if one exists. Catches transform logic errors the checksum can't (because the checksum only proves target == recomputed-source, not that the logic is right).
• The seam audit. Explicitly diff the overlap window: for every key in the overlap, confirm exactly one row survived and it's the last-writer-wins winner. This is the check that catches double-counts at the seam.

Common interview probes on reconciliation.

• "How do you reconcile a backfill against the live pipeline?" — required answer: watermark seam + last-writer-wins + count/checksum per partition.
• "How do you avoid double-counting at the overlap?" — version-guarded MERGE; dedup the overlap by version.
• "Row counts match but values are wrong — now what?" — checksum, not just count; and aggregate parity for logic errors.
• "Which side wins in the overlap?" — decide per backfill (live wins for freshness, backfill wins for corrections) and encode it in the version comparison.

Worked example — the watermark seam and overlap dedup

Detailed explanation. The canonical seam design: the backfill owns everything strictly before a watermark; the live pipeline owns everything at or after it; a small overlap is deduped last-writer-wins by an updated_at version. Walk through defining the seam and resolving the overlap so exactly one row survives per key.

• Seam. 2026-09-04 00:00 — backfill owns < seam, live owns >= seam.
• Overlap. The backfill also reprocessed 2026-09-03 (which live had partially written).
• Resolution. Dedup by key keeping max(updated_at); live's fresher rows win.

Question. Write the seam-aware backfill that reprocesses history up to the seam and safely deduplicates the overlap window against live rows.

Input.

Window	Owner	Rule
< 2026-09-03	backfill	full replace
2026-09-03 (overlap)	both	last-writer-wins by updated_at
>= 2026-09-04	live pipeline	untouched

Code.

-- Overlap resolution: for the overlap day, keep the newest row per key.
-- The backfill inserts into a staging table; this MERGE resolves the seam.
MERGE INTO analytics.fact_orders AS tgt
USING (
    -- dedup within the incoming set first: one row per key, newest wins
    SELECT order_id, customer_id, order_date, revenue_cents, updated_at
    FROM (
        SELECT *,
               ROW_NUMBER() OVER (PARTITION BY order_id
                                  ORDER BY updated_at DESC) AS rn
        FROM staging.fact_orders_backfill
        WHERE order_date = DATE '2026-09-03'      -- the overlap partition
    ) d
    WHERE rn = 1
) AS src
ON tgt.order_id = src.order_id
WHEN MATCHED AND src.updated_at > tgt.updated_at THEN UPDATE SET   -- live wins if newer
    tgt.revenue_cents = src.revenue_cents,
    tgt.updated_at    = src.updated_at
WHEN NOT MATCHED THEN INSERT
    (order_id, customer_id, order_date, revenue_cents, updated_at)
    VALUES (src.order_id, src.customer_id, src.order_date, src.revenue_cents, src.updated_at);

-- Non-overlap history (strictly before the overlap) is a clean full replace:
BEGIN;
  DELETE FROM analytics.fact_orders WHERE order_date < DATE '2026-09-03';
  INSERT INTO analytics.fact_orders
  SELECT order_id, customer_id, order_date, revenue_cents, updated_at
  FROM   staging.fact_orders_backfill
  WHERE  order_date < DATE '2026-09-03';
COMMIT;

Step-by-step explanation.

1. History strictly before the overlap (order_date < 2026-09-03) has no live conflict — the live pipeline isn't writing to old partitions — so it is a clean, atomic full-partition replace (delete-insert in a transaction). Simple and safe because there is no seam there.
2. The overlap partition (2026-09-03) is where both wrote. The inner ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) first deduplicates the incoming backfill set so there is exactly one candidate row per key — guarding against duplicates in staging.
3. The MERGE ... WHEN MATCHED AND src.updated_at > tgt.updated_at clause is last-writer-wins: the backfill only overwrites the target row if its updated_at is strictly newer. Because live-pipeline rows in the overlap are typically newer, live wins — the backfill does not clobber fresh live data. This is the correct default when the backfill is a schema addition, not a correction.
4. WHEN NOT MATCHED THEN INSERT handles overlap keys the live pipeline hadn't yet written — the backfill fills them in. So the overlap ends with exactly one row per key: the newest of (live, backfill).
5. If instead the backfill were a correction that must win, you flip the guard to bump the backfill version above live (or use WHEN MATCHED THEN UPDATE unconditionally for the corrected columns). The seam design is the same; only the winner changes, and it is an explicit, documented choice per backfill.

Output.

order_id	live updated_at	backfill updated_at	Winner	Survives
900	09-03 10:00	09-03 08:00	live (newer)	live row
901	(none)	09-03 08:00	backfill (insert)	backfill row
902	09-03 09:00	09-03 11:30	backfill (newer)	backfill row

Rule of thumb. Split the backfill at a partition-aligned watermark seam: clean full-partition replace before the overlap, version-guarded last-writer-wins MERGE within the overlap. Decide explicitly whether live or backfill wins the overlap (freshness vs correction) and encode it in the updated_at comparison.

Worked example — count-plus-checksum reconciliation

Detailed explanation. Row-count parity proves no rows are missing or duplicated; it does not prove the values are right. A partition-level checksum catches value corruption a count would miss. Build a reconciliation that computes both, per partition, on source and target, and gates acceptance on both matching.

• Count. count(*) per partition, source vs target.
• Checksum. An order-independent hash aggregate over the business columns per partition.
• Gate. A partition is accepted only when count AND checksum match.

Question. Write a per-partition reconciliation that compares count and checksum between source and target and reports mismatches.

Input.

Check	Source expr	Target expr
count	count(*)	count(*)
checksum	hash-agg of columns	hash-agg of columns
partition	order_date	order_date

Code.

-- Order-independent checksum per partition (Postgres). md5 -> int8 summed;
-- SUM is commutative so row order does not matter.
WITH src AS (
    SELECT order_date,
           count(*) AS n,
           SUM(('x' || substr(md5(
                order_id::text || '|' ||
                coalesce(revenue_cents::text,'') || '|' ||
                coalesce(customer_id::text,'')), 1, 16))::bit(64)::bigint) AS chk
    FROM   raw.orders_recomputed          -- the deterministic recompute of source
    GROUP  BY order_date
),
tgt AS (
    SELECT order_date,
           count(*) AS n,
           SUM(('x' || substr(md5(
                order_id::text || '|' ||
                coalesce(revenue_cents::text,'') || '|' ||
                coalesce(customer_id::text,'')), 1, 16))::bit(64)::bigint) AS chk
    FROM   analytics.fact_orders
    GROUP  BY order_date
)
SELECT COALESCE(s.order_date, t.order_date) AS order_date,
       s.n   AS src_rows,  t.n   AS tgt_rows,
       s.chk AS src_chk,   t.chk AS tgt_chk,
       CASE
         WHEN s.n IS DISTINCT FROM t.n     THEN 'COUNT_MISMATCH'
         WHEN s.chk IS DISTINCT FROM t.chk THEN 'CHECKSUM_MISMATCH'
         ELSE 'OK'
       END AS status
FROM   src s
FULL OUTER JOIN tgt t USING (order_date)
WHERE  s.n IS DISTINCT FROM t.n OR s.chk IS DISTINCT FROM t.chk;   -- only bad partitions

# Reconcile gate wired into the orchestrator: fail the partition if not OK
def reconcile_partition(conn, part) -> None:
    with conn.cursor() as cur:
        cur.execute("SELECT status FROM reconcile_view WHERE order_date = %s", (part,))
        row = cur.fetchone()
        status = row[0] if row else 'OK'      # absent from mismatch view == OK
        if status != 'OK':
            raise ValueError(f"reconcile {part}: {status}")

Step-by-step explanation.

1. The count check (count(*) per order_date) is the first and cheapest gate. A mismatch means the backfill created or lost rows relative to the source — usually a scoping bug or a non-idempotent double-write. It is reported as COUNT_MISMATCH.
2. The checksum uses md5 of the concatenated business columns, truncated to 16 hex chars, cast to bigint, and SUMmed. SUM is commutative and associative, so the aggregate is order-independent — the source and target need not be sorted identically for the hashes to match.
3. Including the value columns (revenue_cents, customer_id) in the hash means a wrong value flips the checksum even when the row count is identical. This is the check that catches "the count ties out but the numbers are wrong" — the exact failure a count-only reconciliation misses.
4. The FULL OUTER JOIN ... USING (order_date) with IS DISTINCT FROM handles partitions present on only one side (a partition the backfill missed entirely, or an extra one) and NULL-safe comparison. The WHERE filters to only mismatched partitions, so an empty result set means "all good."
5. The gate function raises on any non-OK status, which the orchestrator catches to fail (and retry) that partition without checkpointing it. Reconciliation is thus not a separate manual step but a mandatory acceptance test inside the backfill loop — a partition that doesn't reconcile is never marked done.

Output.

order_date	src_rows	tgt_rows	status
2024-06-14	41,220	41,220	OK (not shown)
2024-06-15	40,980	81,960	COUNT_MISMATCH
2024-06-16	39,750	39,750	CHECKSUM_MISMATCH

Rule of thumb. Reconcile every partition on both count and checksum, and gate checkpointing on both. Count catches missing/duplicated rows; the order-independent checksum catches value corruption that counts miss. A backfill without a checksum gate can pass row-count parity while shipping wrong numbers.

Worked example — diagnosing and fixing the double-count seam bug

Detailed explanation. The most common backfill incident: the reconciliation shows a COUNT_MISMATCH where the target has exactly 2× the source rows in the overlap partition — the backfill and the live pipeline both inserted the same keys, and neither deduped. Walk through the diagnosis and the fix.

• Symptom. Overlap partition target count = 2× source count; SUM(revenue) doubled.
• Root cause. Backfill used blind INSERT (not MERGE) into a partition the live pipeline already populated.
• Fix. Re-run the overlap as a version-guarded MERGE after de-duplicating existing target rows.

Question. Diagnose the double-count and write the idempotent repair for the affected overlap partition.

Input.

Metric	Overlap partition
source rows	40,000
target rows	80,000 (2×!)
distinct order_id in target	40,000
duplicates per key	2

Code.

-- 1. Diagnose: confirm the double-count is exact duplication by key
SELECT order_id, count(*) AS copies
FROM   analytics.fact_orders
WHERE  order_date = DATE '2026-09-03'
GROUP  BY order_id
HAVING count(*) > 1
ORDER  BY copies DESC
LIMIT  5;
-- -> every order_id has exactly 2 copies: blind INSERT over live rows.

-- 2. Repair: collapse to one row per key (keep newest), atomically
BEGIN;
  -- keep the newest row per key, delete the rest
  DELETE FROM analytics.fact_orders a
  USING (
      SELECT ctid,
             ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn
      FROM   analytics.fact_orders
      WHERE  order_date = DATE '2026-09-03'
  ) d
  WHERE a.ctid = d.ctid
    AND a.order_date = DATE '2026-09-03'
    AND d.rn > 1;                      -- drop all but the newest per key
COMMIT;

-- 3. Verify the repair reconciles
SELECT count(*) AS tgt_rows,
       count(DISTINCT order_id) AS distinct_keys
FROM   analytics.fact_orders
WHERE  order_date = DATE '2026-09-03';
-- -> tgt_rows == distinct_keys == 40,000 (source parity restored)

Step-by-step explanation.

1. The diagnostic GROUP BY order_id HAVING count(*) > 1 confirms the shape of the corruption: every key has exactly 2 copies. That "exactly 2" signature is the fingerprint of a blind INSERT layered over rows the live pipeline (or a prior backfill run) already wrote.
2. The root cause is skipping idempotency (§2): the overlap was written with INSERT ... SELECT instead of a MERGE/upsert, so the backfill's rows were added to the live pipeline's rows rather than reconciled against them.
3. The repair uses ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) to rank duplicates per key and deletes all but rn = 1 (the newest). Using Postgres ctid (the physical row id) as the delete key targets exact duplicate rows even when every column except arrival is identical.
4. Wrapping the delete in a transaction makes the collapse atomic — a reader never sees a partially-deduplicated partition. After commit, tgt_rows == distinct_keys == source_rows, so count parity is restored.
5. The permanent fix is to re-run this partition through the version-guarded MERGE from the first worked example so future re-runs stay idempotent. The dedup repair fixes the symptom; switching the overlap write to a MERGE fixes the cause.

Output.

Stage	target rows	distinct keys	status
Before repair	80,000	40,000	COUNT_MISMATCH (2×)
After dedup	40,000	40,000	OK
Root-cause fix	40,000	40,000	idempotent MERGE going forward

Rule of thumb. A target count that is an exact multiple of the source is the signature of a blind-insert double-count at the seam. Repair by collapsing to one row per key (newest wins) atomically, then switch the overlap write to a version-guarded MERGE so the double-count can never recur.

Data engineering interview question on reconciliation

A senior interviewer might ask: "Your backfill and your live pipeline both wrote to the last 48 hours of a fact table during the cutover. Some keys now have two rows, some have the wrong value, and finance says revenue is off by 3%. Walk me through how you'd draw the seam, resolve the overlap to a single truth, and prove to finance that the table is now correct."

Solution Using a versioned seam MERGE plus count/checksum/aggregate reconciliation

-- 1. Resolve the 48-hour overlap: one row per key, newest updated_at wins.
BEGIN;
  -- collapse existing duplicates in the overlap to the newest per key
  DELETE FROM analytics.fact_orders a
  USING (
      SELECT ctid, ROW_NUMBER() OVER (PARTITION BY order_id
                                      ORDER BY updated_at DESC) rn
      FROM   analytics.fact_orders
      WHERE  order_ts >= now() - INTERVAL '48 hours'
  ) d
  WHERE a.ctid = d.ctid AND d.rn > 1;

  -- upsert the authoritative recomputed rows, version-guarded
  MERGE INTO analytics.fact_orders tgt
  USING (
      SELECT order_id, customer_id, order_ts, order_ts::date AS order_date,
             total_cents - discount_cents AS revenue_cents, updated_at
      FROM   raw.orders
      WHERE  order_ts >= now() - INTERVAL '48 hours'
  ) src ON tgt.order_id = src.order_id
  WHEN MATCHED AND src.updated_at >= tgt.updated_at THEN UPDATE SET
      tgt.revenue_cents = src.revenue_cents, tgt.updated_at = src.updated_at
  WHEN NOT MATCHED THEN INSERT
      (order_id, customer_id, order_ts, order_date, revenue_cents, updated_at)
      VALUES (src.order_id, src.customer_id, src.order_ts, src.order_date,
              src.revenue_cents, src.updated_at);
COMMIT;

-- 2. Prove it: count + checksum + business aggregate, per day, source vs target
SELECT s.order_date,
       s.n  AS src_rows, t.n  AS tgt_rows,
       s.rev AS src_rev, t.rev AS tgt_rev,
       (s.n = t.n AND s.rev = t.rev) AS ties_out
FROM (
    SELECT order_ts::date AS order_date, count(*) n,
           SUM(total_cents - discount_cents) rev
    FROM raw.orders WHERE order_ts >= now() - INTERVAL '48 hours'
    GROUP BY 1
) s
JOIN (
    SELECT order_date, count(*) n, SUM(revenue_cents) rev
    FROM analytics.fact_orders WHERE order_ts >= now() - INTERVAL '48 hours'
    GROUP BY 1
) t USING (order_date)
ORDER BY s.order_date;

Step-by-step trace.

Step	Action	Result
Dedup overlap	keep newest per key (ctid + ROW_NUMBER)	one row per order_id
Version-guarded MERGE	upsert recomputed rows, updated_at wins	authoritative values, no clobber
Count reconcile	src rows vs tgt rows per day	parity restored
Aggregate reconcile	SUM(revenue) src vs tgt per day	finance ties out
Atomicity	all in one transaction	no torn seam state
Idempotency	MERGE by key	safe to re-run

After running the repair, each overlap key has exactly one row carrying the newest authoritative value; the count reconciliation shows source-target parity per day; and the business-aggregate reconciliation shows SUM(revenue) matching the source to the cent, which is the artifact finance signs off. Because the resolution is a version-guarded MERGE, re-running it is idempotent — a nervous re-trigger changes nothing.

Output:

order_date	src_rows	tgt_rows	src_rev	tgt_rev	ties_out
2026-09-03	40,000	40,000	5,102,330	5,102,330	true
2026-09-04	41,110	41,110	5,290,880	5,290,880	true

Why this works — concept by concept:

• Overlap dedup by ctid + ROW_NUMBER — collapses existing duplicates to one row per key (newest wins) atomically, removing the double-count that inflated revenue.
• Version-guarded MERGE — upserts the authoritative recomputed rows keyed on order_id, only overwriting when updated_at is not older, so the correction wins without clobbering a fresher live write and re-running is a no-op.
• Count + aggregate reconciliation — count parity proves no missing/duplicate rows; SUM(revenue) parity is the business-level proof finance accepts. Both computed per day so a mismatch localizes to one partition.
• Single transaction — dedup and MERGE commit together, so no reader (or reconciliation) ever sees a half-resolved seam.
• Cost — O(overlap rows) for the dedup and MERGE (48 hours, not all history), plus two aggregate scans for proof. The eliminated cost is the 3% revenue error and the finance escalation. O(seam) work to make one row equal one truth.

DB
Topic — database
Database problems on dedup, window functions, and reconciliation

Practice →

ETL Topic — etl ETL problems on data reconciliation and validation

Practice →

---

5. Orchestrating backfills — Airflow catchup and batching

catchup batches history into independent, resumable, rate-limited intervals — run it as a separate DAG with a pool and a reconcile task, never as an unbounded blast

The mental model in one line: orchestrating a backfill means turning "reload three years" into a stream of independent, resumable, rate-limited per-interval task runs — which Airflow does natively via catchup=True over a date range, bounded by max_active_runs and a dedicated pool so concurrency stays within the throttle budget, isolated as a separate backfill DAG so it never perturbs the live schedule, and finished with a reconcile task so a partition is only "done" once it's verified. The orchestrator is where §2's partition units, §3's throttle budget, and §4's reconciliation come together: each historical interval is one DAG run, the pool caps how many run at once, the tasks are idempotent so retries are safe, and a checkpoint makes the whole thing resumable across days.

Airflow catchup — the native backfill mechanism.

• catchup=True. When a DAG's start_date is in the past, Airflow schedules one run per interval from start_date to now — that is a backfill. Each run gets its own data_interval_start/data_interval_end, which the task uses to scope its partition. This is the idiomatic way to express "run this for every historical interval."
• data_interval params. Each run's task reads its own interval ({{ data_interval_start }}), so the same DAG code processes one partition per run — deterministic and partition-scoped by construction, provided the task uses the interval, not now().
• The backfill CLI. airflow dags backfill -s <start> -e <end> <dag_id> triggers runs for an explicit range on demand, independent of the schedule — the tool for a targeted historical reload without changing the DAG's start_date.
• depends_on_past / wait_for_downstream. Force strictly sequential intervals when order matters (e.g. cumulative aggregates). Leave off when partitions are independent so they parallelize (bounded by the pool).

Concurrency control — cap how much runs at once.

• max_active_runs. Caps concurrent DAG runs (i.e. concurrent intervals). Setting it to, say, 3 means at most three partitions backfill simultaneously — the orchestrator-level throttle that keeps the backfill within budget.
• Pools. A named pool with N slots caps concurrent tasks across all runs sharing it. Put the backfill's DB-writing tasks in a backfill_pool with few slots so they can never exhaust the shared resource — the orchestrator mirror of §3's connection-pool isolation.
• priority_weight. Lower the backfill tasks' priority so that when the backfill pool and live pool share workers, the live pipeline's tasks are scheduled first. The backfill uses slack, not priority.
• max_active_tasks / pool_slots. Fine-grained caps per task; a heavy per-partition task can claim multiple pool slots to reduce its own concurrency further.

Separate backfill DAG — isolate it from the live schedule.

• Why separate. Running the backfill inside the live DAG (by widening its date range) risks pausing/perturbing the production schedule and mixes two very different concurrency profiles. A distinct *_backfill DAG keeps the live DAG's schedule, SLAs, and alerting untouched.
• Shared task code. The backfill DAG imports the same transform functions as the live DAG (one source of truth for the logic) but wraps them with backfill-specific config: the backfill pool, lower priority, catchup=True, a reconcile task, and the throttle.
• Its own SLA and alerting. The backfill DAG's failures page a different channel (or none overnight) so a slow backfill partition doesn't trip the live pipeline's SLA alarms.

Resumability and the reconcile task.

• Checkpoint per interval. Each run records its partition as done (the §1 checkpoint table). A re-triggered range skips already-done partitions, so resuming a multi-day backfill is free and idempotent.
• Reconcile as the last task. Every DAG run ends with a reconcile task (§4) that gates success on count+checksum. A run is only success if its partition verified — so "the DAG is green" means "the data is proven correct."
• Retries with backoff. Task-level retries + retry_exponential_backoff handle transient failures (throttle pauses, replica lag) without human intervention, because the tasks are idempotent.

Common interview probes on orchestration.

• "What is Airflow catchup?" — required answer: one run per historical interval from start_date to now; the native backfill mechanism.
• "How do you cap backfill concurrency?" — max_active_runs + a dedicated pool + lower priority_weight.
• "Why a separate backfill DAG?" — isolate the live schedule/SLA; share task code, not config.
• "How do you make it resumable?" — per-partition checkpoint; re-triggered ranges skip done partitions.

Worked example — a separate backfill DAG with catchup, pool, and reconcile

Detailed explanation. The canonical backfill DAG: catchup=True over a historical range, max_active_runs and a dedicated pool capping concurrency, the same transform as the live DAG, a checkpoint skip, and a reconcile task gating success. Build it.

• Range. start_date = 2026-06-07, backfill to 2026-09-04 (daily).
• Concurrency. max_active_runs=3, pool='backfill_pool' (4 slots), low priority.
• Tasks. skip-if-done → transform partition → reconcile.

Question. Write the backfill DAG with catchup, pool-bounded concurrency, checkpoint skip, and a reconcile gate.

Input.

Setting	Value
catchup	True
schedule	@daily
max_active_runs	3
pool	backfill_pool (4 slots)
priority_weight	1 (low)

Code.

# dags/fact_orders_backfill.py — SEPARATE from the live DAG
from airflow.decorators import dag, task
from datetime import datetime

@dag(
    dag_id="fact_orders_backfill",
    schedule="@daily",
    start_date=datetime(2026, 6, 7),     # in the past -> catchup backfills the range
    catchup=True,                        # one run per historical day
    max_active_runs=3,                   # at most 3 partitions concurrently
    default_args={
        "pool": "backfill_pool",         # bounded slots -> §3 isolation
        "priority_weight": 1,            # yield to the live DAG
        "retries": 3,
        "retry_exponential_backoff": True,
    },
    tags=["backfill"],
)
def fact_orders_backfill():

    @task.short_circuit                  # skip the rest if already done (resumable)
    def not_yet_done(data_interval_start=None) -> bool:
        part = data_interval_start.date()
        return not checkpoint_is_done("fact_orders", part)

    @task
    def reprocess(data_interval_start=None):
        part = data_interval_start.date()
        throttled_partition_merge(part)   # §2 idempotent MERGE + §3 throttle

    @task
    def reconcile(data_interval_start=None):
        part = data_interval_start.date()
        reconcile_partition_or_raise(part)   # §4 count + checksum gate
        mark_checkpoint_done("fact_orders", part)

    gate = not_yet_done()
    gate >> reprocess() >> reconcile()

fact_orders_backfill()

# Create the isolated pool once (4 slots caps concurrent backfill DB tasks)
airflow pools set backfill_pool 4 "bounded slots for backfill isolation"

Step-by-step explanation.

1. catchup=True with a past start_date makes Airflow schedule one run per day from 2026-06-07 to now — that is the backfill. Each run's data_interval_start is the partition date, so the same task code processes exactly one day per run, partition-scoped by construction.
2. max_active_runs=3 caps concurrent runs, and pool='backfill_pool' (4 slots) caps concurrent DB-writing tasks. Together they bound the backfill's throughput at the orchestrator level — the §3 throttle budget, expressed in Airflow. priority_weight=1 makes the backfill yield worker slots to the higher-priority live DAG.
3. The @task.short_circuit not_yet_done gate checks the checkpoint table and skips the rest of the run if the partition is already done. This makes the whole backfill resumable and idempotent at the orchestration level: re-triggering the range reprocesses nothing already completed.
4. reprocess runs the §2 idempotent per-partition MERGE wrapped in the §3 throttle, and reconcile runs the §4 count+checksum gate, marking the checkpoint done only if reconciliation passes. So a run turns green only when its partition is verified correct — "DAG success" means "data proven."
5. This is a separate DAG (fact_orders_backfill) from the live fact_orders DAG. It imports the same throttled_partition_merge transform (one source of truth for the logic) but layers backfill-only config — pool, low priority, catchup, reconcile — so the live DAG's schedule, SLAs, and alerts are untouched.

Output.

Concern	Mechanism	Effect
History → intervals	catchup + @daily	one run per day-partition
Concurrency cap	max_active_runs=3 + pool(4)	bounded throughput
Yield to live	priority_weight=1	live tasks scheduled first
Resumability	short_circuit on checkpoint	re-runs skip done partitions
Verified success	reconcile gate before checkpoint	green == correct

Rule of thumb. Express a backfill as a separate DAG with catchup=True, cap concurrency with max_active_runs + a dedicated pool + low priority_weight, short-circuit on a checkpoint for resumability, and end every run with a reconcile task that gates the checkpoint. Share the transform code with the live DAG; never share its config.

Worked example — the airflow dags backfill CLI for a targeted range

Detailed explanation. Sometimes you don't want to change a DAG's start_date; you want to reprocess a specific past range on demand. The airflow dags backfill CLI triggers runs for an explicit [-s, -e] window, honoring pools and max_active_runs. Walk through using it safely.

• Scenario. Reprocess only 2026-07-01 .. 2026-07-14 after a bug fix, without touching the DAG definition.
• Safety. The DAG already has the backfill pool and reconcile task; the CLI just triggers the range.
• Idempotency. Because tasks checkpoint and reconcile, re-running the range is safe.

Question. Trigger a targeted 2-week reprocess via the CLI with concurrency and reset controls.

Input.

Flag	Value
-s (start)	2026-07-01
-e (end)	2026-07-14
--reset-dagruns	yes (re-run existing)
--max-active-runs	3

Code.

# Targeted range reprocess — honors the DAG's pool, priority, and reconcile task
airflow dags backfill \
    --start-date 2026-07-01 \
    --end-date   2026-07-14 \
    --reset-dagruns \                 # clear prior run state so tasks re-execute
    --rerun-failed-tasks \            # retry only failed tasks on subsequent invocations
    --max-active-runs 3 \             # cap concurrency (matches the pool budget)
    fact_orders_backfill

# Because the tasks are idempotent + checkpointed + reconciled:
#   - already-done partitions short-circuit (no reprocess)
#   - --reset-dagruns forces a clean re-run when you DO want to reprocess
#   - each run still ends on the reconcile gate

# Optional: clear the checkpoint for a range so short_circuit lets it reprocess
def clear_checkpoints(conn, table, start, end):
    with conn, conn.cursor() as cur:
        cur.execute(
            "DELETE FROM backfill_checkpoint "
            "WHERE table_name=%s AND partition_date >= %s AND partition_date < %s",
            (table, start, end))
    # Now airflow dags backfill will actually reprocess these partitions.

Step-by-step explanation.

1. airflow dags backfill -s ... -e ... triggers DAG runs for exactly the [start, end) intervals, independent of the DAG's schedule and start_date. It is the on-demand tool for a targeted historical reload — no DAG edit, no start_date change.
2. The CLI respects the DAG's max_active_runs and pools, and you can further cap with --max-active-runs 3, so the targeted reprocess stays within the same throttle budget as a scheduled catchup. Isolation and rate limiting are not bypassed by the CLI.
3. --reset-dagruns clears prior run state so the tasks actually re-execute; without it, existing successful runs are left alone. This is the flag you use when you want to reprocess a range that previously succeeded (e.g. a newly-discovered bug).
4. But the short_circuit checkpoint gate still applies: if a partition is marked done in the checkpoint table, it skips even under --reset-dagruns. So to force reprocessing you also clear the checkpoint for the range (the clear_checkpoints helper) — belt-and-braces so you never accidentally reprocess, and never accidentally skip.
5. Every triggered run still ends on the reconcile task, so even a hand-triggered CLI backfill is verified before its partition is re-marked done. The CLI is a trigger; the DAG's safety machinery (throttle, checkpoint, reconcile) is unchanged.

Output.

Flag combination	Behavior
plain backfill	skips done partitions (checkpoint)
--reset-dagruns	re-runs runs, but checkpoint still short-circuits
--reset-dagruns + clear_checkpoints	fully reprocesses the range
--rerun-failed-tasks	retries only failed tasks

Rule of thumb. Use airflow dags backfill -s -e for targeted on-demand reloads without editing the DAG; it honors pools and max_active_runs so throttling still applies. To actually reprocess already-done partitions, combine --reset-dagruns with clearing the checkpoint for the range — the checkpoint is the source of truth for "done."

Worked example — batching intervals for a 3-year daily backfill

Detailed explanation. One DAG run per day for three years is ~1,095 runs — a lot of scheduler overhead. Batching groups several days into one run (or processes them in one task) to reduce overhead while keeping each batch idempotent and reconcilable. Walk through weekly batching with per-day reconciliation inside each batch.

• Batch. One DAG run per week; the task loops the 7 days inside.
• Idempotency. Each day within the batch is still a per-partition MERGE.
• Reconcile. Each day reconciled individually; the batch fails if any day fails.

Question. Convert the daily backfill DAG to weekly batches that process and reconcile each day inside the batch, preserving per-partition idempotency.

Input.

Parameter	Value
Batch size	7 days (weekly)
Runs for 3 years	~156 (vs ~1,095 daily)
Per-day idempotency	preserved (MERGE per day)
Per-day reconcile	preserved (gate per day)

Code.

# Weekly-batched backfill: fewer runs, same per-partition idempotency
from airflow.decorators import dag, task
from datetime import datetime, timedelta

@dag(dag_id="fact_orders_backfill_weekly",
     schedule="@weekly",
     start_date=datetime(2023, 9, 4),          # 3 years back
     catchup=True,
     max_active_runs=2,                          # 2 weeks at a time
     default_args={"pool": "backfill_pool", "priority_weight": 1, "retries": 3})
def weekly_backfill():

    @task
    def process_week(data_interval_start=None, data_interval_end=None):
        day = data_interval_start.date()
        end = data_interval_end.date()
        while day < end:                         # loop the 7 days in the batch
            if not checkpoint_is_done("fact_orders", day):
                throttled_partition_merge(day)   # §2 idempotent per-day MERGE
                reconcile_partition_or_raise(day)  # §4 gate per day
                mark_checkpoint_done("fact_orders", day)
            day += timedelta(days=1)

    process_week()

weekly_backfill()

Step-by-step explanation.

1. Batching to @weekly cuts the number of DAG runs from ~1,095 (daily over 3 years) to ~156, sharply reducing scheduler and metadata overhead — meaningful at multi-year scale where per-run overhead dominates.
2. Crucially, batching does not coarsen the atomic unit: inside process_week, each of the 7 days is still processed by an independent per-partition MERGE and reconciled individually. The batch is a scheduling grouping, not a transactional one — so idempotency and reconciliation granularity are preserved.
3. The per-day checkpoint_is_done check inside the loop keeps resumability at day granularity: if the batch crashes on day 4 of 7, the retry skips days 1–3 (done) and resumes at day 4. You get batching's efficiency without losing fine-grained resume.
4. max_active_runs=2 caps concurrency at two weeks at a time; combined with the pool, the throughput budget is still enforced even though each run does more work. Batch size and concurrency cap are tuned together against the throttle budget.
5. If any day's reconcile raises, the whole batch task fails and retries — but because completed days are checkpointed, the retry only redoes the failing day forward. Batching thus trades a little blast-radius coarseness (a batch fails as a unit) for a lot of scheduler efficiency, while per-day checkpointing keeps the actual re-work minimal.

Output.

Strategy	DAG runs (3 yr)	Atomic unit	Resume granularity
Daily	~1,095	day	day
Weekly batch	~156	day (in-loop)	day (checkpoint)
Monthly batch	~36	day (in-loop)	day (checkpoint)
One big run	1	day (in-loop)	day (checkpoint)

Rule of thumb. Batch multiple partitions into one DAG run to cut scheduler overhead on multi-year backfills, but keep the per-partition MERGE, reconcile, and checkpoint inside the batch loop so the atomic unit, verification granularity, and resume granularity stay at one partition. Tune batch size and max_active_runs together against the throttle budget.

Data engineering interview question on orchestrating a backfill

A senior interviewer might ask: "Design an Airflow backfill for three years of daily partitions of a fact table that a live @hourly DAG keeps writing to. Cover the catchup configuration, concurrency and pool isolation from the live schedule, resumability across a multi-day run, the reconcile gate, and how you'd trigger a targeted re-run of one bad month later."

Solution Using a separate catchup DAG with pool isolation, checkpointing, reconcile, and CLI re-runs

# dags/fact_orders_backfill.py — the full production shape
from airflow.decorators import dag, task
from datetime import datetime, timedelta

@dag(
    dag_id="fact_orders_backfill",
    schedule="@daily",
    start_date=datetime(2023, 9, 4),          # 3 years back -> catchup range
    catchup=True,
    max_active_runs=3,                         # concurrency cap (throttle budget)
    dagrun_timeout=timedelta(hours=6),
    default_args={
        "pool": "backfill_pool",              # 4 slots -> isolated from live pool
        "priority_weight": 1,                 # yield to the live @hourly DAG
        "retries": 3,
        "retry_exponential_backoff": True,
        "retry_delay": timedelta(minutes=5),
    },
    tags=["backfill", "isolated"],
)
def fact_orders_backfill():

    @task.short_circuit
    def not_done(data_interval_start=None):    # resumability
        return not checkpoint_is_done("fact_orders", data_interval_start.date())

    @task
    def backfill(data_interval_start=None):    # §2 idempotent + §3 throttle
        throttled_partition_merge(data_interval_start.date())

    @task
    def reconcile(data_interval_start=None):   # §4 count + checksum gate
        part = data_interval_start.date()
        reconcile_partition_or_raise(part)
        mark_checkpoint_done("fact_orders", part)

    not_done() >> backfill() >> reconcile()

fact_orders_backfill()

# One-time: isolate concurrency with a bounded pool
airflow pools set backfill_pool 4 "backfill isolation"

# Later: reprocess one bad month (2024-02) on demand, fully
python -c "import ops; ops.clear_checkpoints('fact_orders','2024-02-01','2024-03-01')"
airflow dags backfill -s 2024-02-01 -e 2024-03-01 --reset-dagruns \
    --max-active-runs 3 fact_orders_backfill

Step-by-step trace.

Concern	Answer	Reasoning
History → runs	catchup=True, @daily, 3-yr start_date	one run per day-partition
Concurrency	max_active_runs=3 + backfill_pool(4)	bounded throughput, isolated
Live isolation	separate DAG + priority_weight=1	live @hourly schedule untouched
Resumability	short_circuit on checkpoint	multi-day run resumes cleanly
Verified done	reconcile gate before checkpoint	green == correct
Targeted re-run	backfill CLI + clear_checkpoints	reprocess one month on demand

After deployment, the separate backfill DAG schedules one run per day across three years, at most three concurrently, all in a 4-slot pool at low priority — so the live @hourly DAG never loses a worker or a connection to the backfill. Each run short-circuits if its partition is already checkpointed, runs the idempotent throttled MERGE, and only marks the partition done after the reconcile gate passes. Months later, reprocessing 2024-02 is a two-command operation: clear the month's checkpoints and trigger the CLI over the range.

Output:

Metric	Value
DAG runs	~1,095 (one per day, 3 yr)
Max concurrent partitions	3
Pool slots (isolated)	4
Live @hourly impact	none (separate DAG, low priority)
Resume after crash	from last checkpoint
Targeted re-run	airflow dags backfill -s -e + clear checkpoints

Why this works — concept by concept:

• catchup=True over a past start_date — the native Airflow backfill: one run per historical interval, each scoped to its own data_interval, so the same DAG code processes exactly one partition per run.
• max_active_runs + bounded pool + low priority — caps concurrent partitions and DB tasks and makes the backfill yield to the live DAG, so the throttle budget and resource isolation of §3 are enforced by the orchestrator.
• short_circuit on a checkpoint — makes a multi-day, crash-prone backfill resumable and idempotent at the orchestration level: re-triggered ranges reprocess nothing already done.
• reconcile task before checkpoint — a partition is marked done only after count+checksum pass, so "the DAG is green" is equivalent to "the data is proven correct" — reconciliation is a first-class gate, not an afterthought.
• separate DAG + backfill CLI — isolating the backfill in its own DAG protects the live schedule/SLA, while airflow dags backfill -s -e (plus checkpoint clearing) makes targeted re-runs a two-command operation months later.
• Cost — ~1,095 lightweight runs, 3-way concurrency, a 4-slot pool, one reconcile scan per partition. The added cost is scheduler metadata and a checkpoint row per partition; the eliminated cost is the live-pipeline outage, the non-resumable multi-day failure, and the unverified backfill. O(partitions) orchestration over an idempotent, throttled, verified unit.

ETL
Topic — etl
ETL problems on Airflow scheduling and catchup

Practice →

Data
Topic — data-processing
Data-processing problems on batching and resumable jobs

Practice →

---

Cheat sheet — backfill recipes

• The one-line rule. A backfill is a concurrent second writer, not a re-run — design for idempotency, isolation, throughput budget, and reconciliation before touching production. If you can't run it twice safely, you don't have a backfill.
• Idempotent write primitives. Row-level: INSERT ... ON CONFLICT (key) DO UPDATE (Postgres) / MERGE (warehouse). Partition-level: INSERT OVERWRITE PARTITION with spark.sql.sources.partitionOverwriteMode=dynamic, or txn-wrapped DELETE WHERE part=X; INSERT ..., or ATTACH/DETACH PARTITION for an O(1) atomic swap. Never blind INSERT.
• Partition = atomic unit. One partition per transaction/overwrite: atomic (readers see old-or-new), retryable (failure blast radius = one partition), parallelizable-with-a-cap, and reconcilable at partition granularity. Scope smaller and lose atomicity; scope larger and lose resumability.
• Determinism guarantees. No now()/current_date/random in the transform — use the partition's data-interval. Join mutable dimensions point-in-time (SCD-2 order_ts >= valid_from AND < valid_to), not "current." Dedup deterministically (ROW_NUMBER() OVER (PARTITION BY key ORDER BY event_ts, offset)). Order inside ARRAY_AGG/STRING_AGG.
• Throttle template (token bucket). Cap sustained writes at a measured-safe rate (rows/sec); acquire N tokens per batch; small burst capacity; refill at target rate. Read via a server-side cursor so throttling isn't defeated by an OOM. Add SET statement_timeout as a safety net.
• Isolation template. Separate compute (Snowflake BACKFILL_WH, Spark queue, BigQuery reservation); bounded connection pool (e.g. 4 conns) distinct from the app's; read from a replica; off-peak windowing. Isolation caps how much; rate limiting caps how fast.
• Backpressure + kill-switch. Watch replica lag / live-pipeline lag / OLTP p99; halve rate at warning, pause at high, circuit-break on sustained red and page. Add a one-boolean backfill_control row on-call can flip to pause instantly without a deploy.
• Watermark seam + last-writer-wins. Backfill owns < seam, live owns >= seam; partition-align the seam. Overlap resolved by version-guarded MERGE (WHEN MATCHED AND src.version >= tgt.version). Decide per backfill who wins: live for freshness (schema add), backfill for corrections (bump version).
• Reconciliation gate. Per partition: row-count parity (missing/duplicate rows) + order-independent checksum (SUM(('x'||substr(md5(cols),1,16))::bit(64)::bigint)) for value corruption + business-aggregate parity (SUM(revenue)) for logic errors. Gate the checkpoint on all three; a partition isn't done until it reconciles.
• Double-count fingerprint. Target count = exact multiple of source = blind-insert double-count at the seam. Repair: collapse to newest row per key (ROW_NUMBER + ctid delete) atomically; then switch the overlap write to a version-guarded MERGE so it can't recur.
• Orchestration template. Separate *_backfill DAG; catchup=True over a past start_date; max_active_runs + dedicated pool + low priority_weight; @task.short_circuit on a checkpoint for resumability; a reconcile task that gates the checkpoint. Share transform code with the live DAG, never its config. Targeted re-run: airflow dags backfill -s -e --reset-dagruns + clear the range's checkpoints.
• In-place vs dual-write. Default: in-place idempotent per-partition replace (eventually consistent, cheap, resumable). Dual-write to a shadow table + atomic swap when the change is destructive (NOT NULL, type change), the table is small, or you need zero-downtime cutover with instant rollback.
• Batching for scale. For multi-year daily backfills, batch several partitions into one DAG run to cut scheduler overhead — but keep the per-partition MERGE, reconcile, and checkpoint inside the batch loop so the atomic unit, verification, and resume granularity stay at one partition.
• Rollback plan. Always have one: for in-place, keep the pre-backfill partition (snapshot/time-travel/backup) so you can restore; for dual-write, swap back to the original table. Never run a backfill whose only rollback is "another backfill."

Frequently asked questions

What does "backfilling data" mean and why is it risky?

backfilling data is loading or recomputing historical records into a dataset after the fact — populating a new column across old rows, fixing a transform bug across months of history, or seeding a new downstream consumer with full history. It is risky because the target is usually a table a live pipeline is still writing to, so the backfill becomes a second concurrent writer competing for the same compute, tables, and time-windows. The three failure classes are resource contention (the backfill starves live traffic), correctness clobbering (double-counting or overwriting fresh rows), and ordering races at the seam where the backfill window overlaps the live window. The naive "re-run the DAG for all of history" triggers all three at once, which is why backfills are a leading cause of self-inflicted data-platform incidents.

How do I make a backfill idempotent?

An idempotent backfill produces the identical target state no matter how many times you re-run it. Achieve it two ways: upsert by natural key (INSERT ... ON CONFLICT (key) DO UPDATE in Postgres, MERGE in Snowflake/BigQuery/Delta) so existing rows are updated rather than duplicated, or full-partition replace (Spark INSERT OVERWRITE PARTITION with partitionOverwriteMode=dynamic, or a transaction-wrapped DELETE WHERE partition=X; INSERT ...) so each partition is atomically swapped. Never use a blind INSERT into a table that may already hold the rows — that double-counts on every retry. Idempotency also requires a deterministic transform: no now()/random inputs, point-in-time (SCD-2) dimension joins instead of "current," and deterministic dedup/ordering. The acceptance test is that re-running a partition changes zero row counts and the same checksum.

How do I stop a backfill from overwhelming production?

Combine three controls. First, isolate resources: run the backfill on separate compute (a dedicated Snowflake warehouse, Spark queue, or BigQuery reservation), a bounded connection pool distinct from the app's, and read from a replica — so the backfill physically cannot exhaust production's compute or connections. Second, rate-limit throughput: a token bucket capping rows/second (for shared-DB writes) or max_active_runs + a bounded pool capping concurrent partitions (for orchestrated jobs), tuned to a measured-safe headroom. Third, add adaptive backpressure: watch replica lag or live-pipeline lag, slow down and pause as it degrades, trip a circuit breaker on sustained red, and expose a one-boolean kill-switch on-call can flip to pause instantly. Isolation caps how much the backfill can take; rate limiting caps how fast; backpressure makes it yield automatically.

How do I reconcile a backfill against the live pipeline?

Draw an explicit watermark seam: the backfill owns everything before it, the live pipeline owns everything at or after it, partition-aligned so overlap is minimized. Resolve the unavoidable overlap deterministically with last-writer-wins on a version or processing-time column — a version-guarded MERGE (WHEN MATCHED AND src.version >= tgt.version) so the backfill never clobbers a fresher live write (for schema-add backfills) or always wins (for corrections, by bumping its version). Then prove correctness per partition with three checks: row-count parity (catches missing/duplicate rows), an order-independent checksum over the value columns (catches value corruption a count misses), and a business-aggregate like SUM(revenue) (catches transform-logic errors). Gate the partition's "done" checkpoint on all three — a partition that doesn't reconcile is never accepted.

What is Airflow catchup and when should I use it?

Airflow catchup=True schedules one DAG run per interval from the DAG's start_date up to now — so a DAG with a past start_date automatically backfills the whole range, one partition per run, each scoped to its own data_interval. Use it as the native way to express a historical reload, but always pair it with concurrency control: max_active_runs to cap concurrent intervals, a dedicated pool to bound concurrent DB tasks, and a low priority_weight so the backfill yields to the live schedule. Run the backfill as a separate DAG (sharing transform code with the live DAG but not its config) so the live pipeline's schedule and SLAs stay untouched, and end every run with a reconcile task that gates a checkpoint for resumability. For on-demand targeted ranges without editing the DAG, use airflow dags backfill -s <start> -e <end> instead.

Should I backfill in place or dual-write to a shadow table?

Default to in-place with idempotent per-partition replace: it is cheap, resumable, and each partition swaps atomically so readers see old-or-new but never a torn state — the right choice for large tables and non-destructive changes. Reach for dual-write (build a full shadow copy, backfill it, validate, then atomically swap via rename or view repoint) when the change is destructive or irreversible (adding a NOT NULL column, a type change, dropping a column), when the table is small enough that a shadow copy is cheap insurance, or when you need a zero-downtime cutover with instant rollback (swap back to the original in one operation). Dual-write also cleanly solves the dual-write problem during migrations — writing to both old and new schemas transiently — but costs double storage and a cutover step. Choose in-place for routine reloads; choose dual-write for schema surgery.

Practice on PipeCode

• Drill the ETL practice library → for the backfill, historical-reload, incremental-load, and Airflow catchup problems senior interviewers love.
• Rehearse on the data-processing practice library → for the idempotent batch-write, throttling, batching, and resumable-job patterns.
• Sharpen the upsert and transaction skills on the database practice library → for the MERGE, ON CONFLICT, dedup, window-function, and reconciliation scenarios.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the four-axis backfill playbook — idempotency, isolation, throughput, reconciliation — against real graded inputs.

Lock in backfill muscle memory

Docs explain the primitives. PipeCode drills explain the decision — when a blind insert double-counts at the seam, when a backfill starves the live pipeline, when a non-deterministic join corrupts history, when catchup needs a pool and a reconcile gate. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice ETL problems →
Practice database problems →