Articles
Data Pipeline Backfill Interview Questions: Dependencies, Cutovers, and Reconciliation
Practice data pipeline backfill interview questions with dependency windows, replay-safe writes, live cutovers, reconciliation examples, and rollback checks.
You fix three days of historical orders, rerun the failed tasks, and watch every job turn green. The seven-day revenue dashboard is still wrong for the following week. The backfill repaired input partitions without repairing all the outputs that depended on them.
Strong data pipeline backfill interview questions test whether you can release corrected history while preserving live data. A backfill is a versioned data release: define its dependency closure, rebuild from identifiable inputs, reconcile at a precise cutoff, and switch consumers only after the new generation is ready.
Evidence boundary: Official Airflow, dbt, Apache Beam, and BigQuery documentation was checked September 21, 2026. Platform facts are labeled below. The order-data scenario, cutover protocol, and acceptance criteria are original design exercises; no candidate reports are used. Local Python checks validate only the dependency arithmetic and reconciliation counterexample, not a running scheduler or warehouse.
What must you establish before launching a backfill?
Original interview scenario: A corrected tax rule affects orders dated January 10 through January 12. The pipeline produces daily order facts, daily revenue, a trailing seven-day revenue metric, and a dashboard export. New orders continue arriving during the repair.
Ask what “corrected” means before choosing a scheduler command. Is the requirement to restate those dates using today's rule, or reproduce what the business knew then? Those outputs may both be useful, but they are not interchangeable.
Define the affected entity grain and time convention. Here, an order is identified by tenant and order ID, and reporting dates use UTC. The input may include corrections whose arrival date differs from the original order date. An ingestion-date filter alone could exclude precisely the records you intend to repair.
Record the reason, input interval, transformation version, source snapshot or cutoff, output generation, and reconciliation owner. A saved manifest gives each retry the same inputs and intended output. Without it, “retry yesterday's backfill” can silently mean new code, new reference data, and a different population.
How do dependencies expand the repair window?
For the original scenario, define a trailing metric for date d as the sum from d minus six days through d, inclusive. A correction to January 10 contributes to outputs dated January 10 through January 16. Corrections through January 12 extend the affected output range through January 18.
The three repaired input days therefore affect nine rolling-output days. A local Python set calculation confirmed that dependency expansion. To compute the earliest affected output, the rebuild also needs unchanged daily inputs from January 4 onward; the input context extends beyond the corrected dates.
| Artifact | Repair requirement in this scenario |
|---|---|
| Order facts | Recompute the affected orders and their reporting dates. |
| Daily revenue | Rebuild January 10–12 from corrected facts. |
| Seven-day revenue | Recompute January 10–18 using each full seven-day window. |
| Dashboard export | Refresh every changed output consumed by the export. |
This is a dependency question, not simply a date-range question. A cumulative lifetime metric can propagate a change much farther than a seven-day window. A changed customer dimension may affect facts across many dates. A model that uses the previous day's output may require chronological rebuilding.
In an interview, draw both the dataset graph and the time dependency. A topological ordering of tables alone does not tell you which historical partitions must run first.
What does the scheduler guarantee—and what remains your job?
Official fact: Airflow backfill creates runs over past logical dates for a scheduled DAG. Its reprocessing policy controls which existing run states qualify. Backfill concurrency has its own max_active_runs setting, and runs can be requested in reverse order. These controls do not establish that the underlying data writes are safe to repeat. Airflow backfill
Official fact: For scheduled DAGs, Airflow associates a run with a data interval; its logical date denotes the interval's start rather than the wall-clock execution time. Manual trigger behavior must be checked against the timetable and trigger path. DAG runs and data intervals
Interview application: A task run today for January 10 must read January 10's intended inputs, not whatever now() happens to select. Inspect the rendered interval parameters before launching the full range.
Reverse execution can make recent independent partitions available sooner. It is inappropriate when January 11 consumes January 10's rebuilt state. Explain that dependency before using the option.
Separate scheduler status from release status. A successful task means its declared work completed according to its code. It does not independently prove that all business keys exist, that the input snapshot was complete, or that a downstream export refreshed.
How do you make historical work repeatable?
Official fact: Airflow's best-practice guidance recommends repeatable task behavior, avoiding duplicate-producing inserts on reruns, and reading a specific partition rather than changing “latest” data. Airflow best practices
Original design: Write corrected results into generation orders_v2 rather than directly mixing them into the served orders_v1. Identify each work unit by dataset, reporting interval, transformation version, and source cutoff. Retrying the same unit should reproduce or safely replace that unit's result.
A checkpoint should mean the output is durably committed, not merely that the task started or uploaded a temporary file. Commit the unit first, then record its completed state through a recovery-safe protocol. If the acknowledgment is lost, rerunning must not double the result.
For partition replacement, confirm that the partition contains the entire repair unit. For keyed merging, confirm uniqueness and prevent an older correction from replacing a newer version. A generic MERGE statement is not a proof of either property.
Pin reference data too. Rebuilding last year's revenue with today's exchange-rate table can produce a deterministic computation of the wrong requirement. Preserve the intended rate version, product classification, and timezone rule, or explicitly define the restatement policy that allows them to change.
Can a schema update substitute for a historical rebuild?
Official fact: dbt's incremental-model documentation distinguishes schema-change handling from historical value population. Adding a column through schema handling does not automatically backfill its values into historical rows; a full refresh or deliberate historical update may be needed. Configuring incremental models
Interview inference: Treat “the new column exists” and “the historical values are correct” as separate acceptance conditions. A nullable tax_category added yesterday can appear in every table schema while remaining absent from old orders.
Before promising a full refresh, verify source retention. If raw events were deleted after 30 days, rerunning a 12-month model cannot recreate those inputs. Options include restoring an archive, reconstructing from another authoritative system, or narrowing the repair with an explicit limitation.
Also check historical code compatibility. A new parser may assume fields that older records never contained. Test the oldest affected partition and a schema-transition boundary before consuming the entire rebuild budget.
How do you protect live traffic while rebuilding history?
Official fact: Airflow pools constrain concurrent tasks using shared capacity, and tasks can consume different numbers of pool slots. Backfill run limits and resource-pool limits control different dimensions. Airflow pools
Original capacity example: Suppose a source has an agreed extraction budget of 100 requests per second, and live ingestion needs up to 70. Reserving the remaining 30 for historical work is a starting policy, not a measured guarantee. It ignores variability until you instrument it.
Throttle against the actual shared bottleneck: source API quota, warehouse slots, database I/O, or downstream write capacity. More scheduler workers do not create more source capacity.
Define a live-data freshness threshold that pauses the backfill when exceeded. Record whether the pause leaves work units restartable. Prefer bounded units so a failed six-hour job does not force six hours of work to repeat.
Keep production and rebuild writers from racing on the same output ownership. If both update an order, decide which source version wins. If ownership is by partition, explain how a late correction crosses that partition boundary. “They run in separate queues” addresses scheduling, not conflicting data writes.
Where exactly is the historical-to-live handoff?
An original cutover protocol can use two explicit source positions. H is the consistent baseline position used to build the new generation. K is the later position through which catch-up and validation are complete.
- Capture a reproducible baseline at H and retain changes needed after H.
- Build the affected history into the new generation while the old generation continues serving readers.
- Apply retained changes after H through K, including corrections to historical orders.
- Briefly coordinate writers, reconcile both generations against the intended source state at K, and publish the new generation.
- Resume the new writer strictly after K, with replay handling at the boundary.
If the source has partitioned offsets, H and K may be position vectors rather than single numbers. An API timestamp becomes an equivalent boundary only if the source contract supports a complete, reproducible cut at that timestamp.
Official fact: Apache Beam describes watermarks as estimates of event-time completeness and provides handling for late data. A watermark is not a universal promise that historical business data can never change. Beam watermarks and late data
Design consequence: Keep a correction policy after cutover. A backfill cannot eliminate future late events; it can establish which versions were included at release and how later ones will be processed.
What does reconciliation need beyond row counts?
Consider this original counterexample. The expected dataset contains A = 100 and B = 200. The rebuilt dataset contains A = 150 and C = 150. Both have two rows and total 300, but the rebuild is wrong.
A local Python check confirmed three distinct discrepancies: B is missing, C is extra, and A has a changed value. Count and sum equality alone would accept all three errors.
Use several complementary checks. Compare missing and extra business keys; test uniqueness at the intended grain; compare important fields for matching keys; then reconcile aggregates by date, tenant, currency, and status. Define null and rounding semantics before computing differences.
When comparing against a moving source, stabilize the comparison at a common snapshot or cutoff. Otherwise, valid new writes can look like backfill errors. Sampling helps investigate mismatches, but a sample should not be described as full-population proof.
Persist mismatch examples with the generation and input positions. Separate expected changes caused by the repaired rule from unexplained changes. Reconciliation should verify the new specification, not force the repaired dataset to equal an old dataset known to be wrong.
How do you cut over and roll back safely?
Official fact: BigQuery supports multi-statement transactions for supported operations, allowing related mutations to commit or roll back together. That capability has statement and concurrency constraints; it is not a blanket guarantee that arbitrary table or view replacement operations form one atomic release. BigQuery transactions
Design recommendation: Choose the consumer-switch mechanism supported by the actual platform: a controlled view change, catalog pointer, versioned table reference, or a transaction over supported mutations. Verify its visibility semantics rather than labeling any rename “atomic.”
Keep the old generation available during an observation window. Record which readers switched and which cached exports still reference old data. Watch freshness, error rates, and the specific metrics changed by the fix.
Rollback is more than pointing readers backward. If the old generation stopped receiving updates at K, it becomes stale afterward. To roll back safely, either keep it caught up during the observation window or retain enough changes to catch it up before restoring reads. Avoid claiming reversible publication when the required source history has already expired.
The release is complete when the new generation is reconciled, consumers use it, freshness remains acceptable, and ownership of future corrections is clear.
Practice the backfill explanation
| Question | Backfill angle |
|---|---|
| Data Pipeline Reliability, Backfills, and Spark Optimization | Connect historical repair with reliable execution. |
| Design Work Orchestration for Machine-Learning Data Pipelines | Expand both dataset and time dependencies. |
| Choose Between Batch and Streaming for a Data Pipeline | Define the historical-to-live handoff. |
| Design Transaction Data Quality Checks | Find errors hidden by matching totals. |
| Design batch and streaming ETL architecture | Assign ownership during replay and cutover. |
Rehearse the January repair from scope to rollback, naming one observable gate at each step. Continue with Data Engineer interview practice when you can explain why a successful rerun is only part of a successful release.