Data Engineering Interview: How Do You Safely Backfill Large-Scale Historical Data?

Prompt and Applicable Context

An order data pipeline reads 2 TB of immutable raw data from object storage each day and produces an ordersdaily table partitioned by businessdate. The team finds a tax transformation defect that affects 90 partitions. It must therefore recompute 180 TB within 5 days. The daily incremental pipeline cannot stop; its P95 freshness must remain at or below 45 minutes. Late refunds and order corrections may also update the same business keys as the backfill.

Design a backfill that is repeatable, resumable after a pause, auditable, and reversible. Cover how to pin the input and code versions, divide and schedule partitions, prevent historical work from taking production capacity, resolve overlap between the backfill and live increments, define publication-blocking validation, and restore the last trusted version after a failure.

Recent public data engineering interview material explicitly treats historical backfills, idempotent reruns, and preserving real-time processing as pipeline reliability questions. Public scenarios also ask candidates to handle terabyte-scale input, partition reprocessing, validation, and rollback. Official pipeline documentation exposes historical runs, reprocessing policies, and separate concurrency limits. The search intent is specific: a candidate needs an executable production plan that goes beyond the scheduling entry point of “rerun the last 90 days in Airflow.”

What the Interviewer Is Evaluating

The first signal is whether the candidate defines a reproducible data version. A strong answer pins the backfill interval, logical date, source snapshot or source version, transformation code, dependent dimension versions, and target schema. If two attempts read different input, or if the code depends on now(), random values, or mutable external lookups, “safe to rerun” has no testable meaning.

The second signal is separating orchestration success from data correctness. An orchestrator can create runs for historical logical dates and limit concurrency. It does not make business writes idempotent, and it does not resolve a write conflict between a live increment and a historical backfill. The candidate should choose partition replacement, a stable-key MERGE, or a versioned target and explain the preconditions.

The third signal is capacity and isolation. To finish 180 TB in 5 days, the minimum average raw read rate is:

180 TB / (5 × 24 h) = 1.5 TB/h ≈ 417 MB/s

That is a lower bound for continuous execution without retries. It excludes scan amplification, shuffle, target writes, validation, and failed recomputation. A good answer benchmarks one partition first, measures throughput and peak resources by stage, then sets a separate queue or compute pool, concurrency limits, and production priority. Live-pipeline freshness becomes a feedback signal that reduces the backfill rate.

The fourth signal is the publication and recovery boundary. Ninety successful tasks do not make a new version safe for consumers. Before publication, the system must prove partition completeness, key uniqueness, business invariants, source reconciliation, and differences consistent with the defect. Publication should be one controlled version cutover. Keep the old version through the observation period so rollback means changing a pointer, not recomputing 180 TB again.

Questions to Clarify Before Answering

• Is the raw input truly immutable? Obtain object versions, a snapshot ID, or a replayable log position. If

the source mutates data in place, create a referencable input version first.

• Which clock defines the 90-day interval? Align business_date, event time, ingestion time, and time zone.

Also define which partition owns a late refund.

• What are the target grain and stable key? Establish whether a row is an order, order item, or daily

aggregate and whether orderid, sourceversion, and a deterministic conflict order exist.

• Which mutable data does the transformation depend on? Exchange rates, tax rules, SCD dimensions, and

deletion records must be read as of the historical event time, not silently replaced with today's values.

• Which partitions can the live increment update? If it only writes the most recent 7 days, the backfill can

own older partitions. If any historical order can change, version ordering or delta catch-up is required.

• Which isolation and atomic publication features exist? A separate warehouse, resource pool, priority

queue, partition transaction, table clone, view cutover, or catalog pointer changes the design.

• Is 5 days a hard deadline or a target? Obtain the live P95 freshness target, source read quota, cost cap,

and any allowed short cutover window.

• Who signs off? Technical assertions, financial reconciliation, downstream sampling, and the observation

period each need an owner and blocking threshold.

30-Second Answer Framework

“I would pin the 90 business dates, input snapshot, and code version, then write each logical-date partition to isolated staging with a manifest. Processing 180 TB in 5 days needs at least about 417 MB/s, so I would benchmark first and throttle when live P95 approaches 45 minutes. After backfilling W0, I would catch up through W1 and cut over only after 90/90 partitions, reconciliation, and business checks pass. The old version remains available for rollback.”

Step-by-Step Deep Dive

Step 1: Define the backfill as an immutable run specification

Create a backfill_id and record the following:

| Field | Example | Purpose | |---|---|---| | Range | [2026-04-01, 2026-06-29], 90 partitions | Prevent boundaries from drifting during execution | | Input | rawsnapshot=s1042, watermark W0 | Make every attempt read the same facts | | Logic | codesha=abc123, taxrules=v17 | Pin transformation and dependency versions | | Output | ordersdailybf20260718 | Isolate the candidate result from the trusted version | | Resources | Backfill pool, maximum concurrency, read/write quotas | Protect the production SLO | | Gates | Unique key, amount delta, completeness, approver | Make “done” a decidable state |

Pass business_date into every partition run. Do not substitute wall-clock time for logical time inside the transformation. When historical exchange rates or SCD dimensions are dependencies, perform an as-of join at event time. A change to the input, code, or any dependency version creates a new backfill_id; do not mix two result versions inside one run.

Generate a plan without writing the production target. List all 90 partitions, dependency order, estimated input bytes, proposed concurrency, and destination paths. Detect gaps, duplicate dates, partitions outside source retention, and downstream side effects. Disable non-data effects such as email, billing, and external API calls, or redirect them to audit mode, so historical replay cannot trigger real business actions again.

Step 2: Use a partition manifest for pause, resume, and audit

Treat each business_date as a bounded work unit. The manifest should record at least:

backfill_id, business_date, input_snapshot, code_sha,
state, attempt, input_rows, output_rows, output_checksum,
staging_location, published_version, started_at, completed_at

A useful state model is PENDING → RUNNING → VALIDATED → PUBLISHED, with failures entering FAILED. Claim a unit through a conditional update or lease so that a partition has only one active owner. An expired lease can be reclaimed. Retry only the failed partition and write another isolated staging attempt for that partition; never append blindly to the final table.

If a date partition is fully closed, the simplest idempotent write is to build the complete partition and then replace that partition transactionally. If an order can be corrected across dates, use a stable business key and source version in a MERGE. Define an explicit winner, such as sourceupdatedat followed by a monotonic source_sequence for ties. A MERGE prevents an old historical record from replacing a new correction only when the primary key, version, and deletion semantics are all reliable.

Step 3: Derive concurrency from measurements instead of guessing a thread count

The 180 TB/5-day requirement gives a raw-read lower bound of about 417 MB/s. Run a canary on 1 representative partition and measure bytes, duration, CPU, memory, warehouse queue time, and temporary space for reading, decompression, shuffle, transformation, writing, and validation. If one partition delivers r MB/s of effective throughput, the theoretical concurrency lower bound is approximately ceil(417/r). Source quotas, shuffle peaks, target commit capacity, and cost limits further constrain the real value.

Give the backfill a separate compute pool or queue at lower priority than the daily increment. Limit scheduler concurrency, source reads, target writes, and total cost together; limiting only one is usually insufficient. A controller watches live-pipeline P95 freshness, warehouse queueing, and source throttling. When freshness approaches 45 minutes, it stops claiming new partitions or lowers concurrency. It increases gradually only after the live pipeline returns to a safe range. Do not hard-kill already committing partitions and leave partial output. A cancellation signal should prevent new claims and let active work finish or safely roll back staging.

After the canary passes, scale in batches: for example, first 1 partition, then 3, then the measured safe concurrency. Observe a complete live incremental cycle at each level. If capacity cannot meet the deadline, change the deadline, temporary capacity, or scope early. Do not hide an estimation error by sacrificing live freshness.

Step 4: Define write ownership between historical and live pipelines

First recompute the historical interval from source snapshot or log watermark W0 into a new version. If the live pipeline corrects only the most recent 7 days, let the backfill exclusively own the older 83 days. Keep the latest 7 under live ownership, then recompute or merge that overlap window into the new version at the end.

If live corrections can touch any historical order, use a snapshot-plus-delta-catch-up flow:

1. Record W0; the backfill reads only deterministic input no later than W0.
2. The live pipeline continues serving the old version, while changes after W0 remain in a replayable log.
3. After all 90 historical partitions validate, record W1 and apply changes in (W0, W1] to the new version

with the same version rule.

1. Once lag is within the cutover budget, briefly freeze the publication pointer or acquire a write fence and

apply the final delta.

1. Atomically move consumers to the new version, then make it the live pipeline's write target.

This design requires a complete change log, stable ordering, and a genuinely atomic cutover primitive. If the platform has no atomic view or catalog change, use an explicit maintenance window or transactional partition replacement with backups of old partitions. Document the visible intermediate state; do not promise a seamless cutover verbally.

Step 5: Validate in layers and put hard gates before publication

Run three layers of checks after each staging partition finishes:

• Structure and completeness: compatible schema, required fields present, unique business keys, correct

date bounds, and no missing or duplicate partition among the 90.

• Source reconciliation: compare input and output row counts, distinct orders, pre-tax amount, tax, and net

amount by date, region, currency, and state. For aggregates, retain a record-level difference that can be investigated.

• Business rules and differences: amount conservation, refunds within refundable amount, and valid state

transitions. New-versus-old differences should concentrate on orders affected by the defect; unaffected slices should not move without an explanation.

Equal row counts are weak evidence: a bad join can add and omit rows at the same time. Also compute bucketed checksums by stable key, sample known defects, boundary dates, late corrections, and deletions, and have the finance or data-product owner confirm the direction of the correction. Version the validation queries and retain thresholds, actual values, and results.

The global publication gate should require at least: 90/90 VALIDATED in the manifest; no active or failed partition; consistent input snapshot, code, and dependency versions; catch-up through W1; all hard assertions passing; the daily pipeline maintaining 45-minute P95 freshness during the backfill; and owner approval. Any failure keeps the old version visible.

Step 6: Cut over once, observe continuously, and roll back quickly

Save the old read pointer and output version before publication. The cutover changes only a stable view or catalog pointer; it does not move 180 TB during the cutover. Immediately run a consumer-query suite against critical dashboards and downstream jobs, and check query latency and the most recent incremental write. Continue comparing important metrics between old and new versions during the observation period.

If key uniqueness, amount reconciliation, live freshness, or consumer queries fail, stop publication to the new version and move the pointer back. Keep the failed version, manifest, and validation evidence for investigation; do not edit it in place and present it as the same version. After fixing the input or code, create a new run specification. Reuse partitions that are proven trustworthy and version-compatible; recompute partitions whose version differs.

The monitoring view should show processed and remaining TB, partition count by state, throughput, estimated completion time, retries and validation failures, source throttling, compute-pool queueing, target commit latency, and live-pipeline P50/P95 freshness. Alerts should include backfill_id, partition, code version, failed gate, and owner so an operator can decide whether to reduce concurrency, retry a partition, or stop the entire publication.

High-Quality Sample Answer

“I would first freeze this backfill as a versioned run: 90 business_date values, raw snapshot and watermark W0, code SHA, tax-dimension version, target-table version, and acceptance thresholds. Each day is one task that writes isolated staging output. A manifest records PENDING/RUNNING/VALIDATED/PUBLISHED and every attempt. Transformations use logical time only. Closed partitions are completely built and replaced; orders that can receive late corrections use a stable primary key and source version in a MERGE, so an old version cannot overwrite a newer one.”

“Processing 180 TB in 5 days needs at least about 417 MB/s of average raw reads, before retries and validation. I would benchmark one representative partition, measure end-to-end throughput and every stage's peak, and then increase concurrency in steps. The backfill gets an isolated low-priority resource pool with limits on source reads, target writes, and scheduler concurrency. The controller protects live P95 freshness: it stops claiming new partitions as lag approaches 45 minutes and ramps back up slowly after recovery.”

“For concurrent correctness, I build the new version from snapshot W0 while the live pipeline continues to serve the old version. After the historical partitions finish, I record W1, replay (W0, W1] corrections into the new version, then use a short fence for final catch-up and atomically move the pointer. Publication requires 90/90 partitions, unique keys, source amount reconciliation, business invariants, explainable old/new differences, and healthy production freshness. I retain the old version through observation. A failed hard gate or consumer query moves the pointer back and preserves the failed version for investigation.”

Common Mistakes

• Set scheduler concurrency to the maximum → The source, shuffle, target commits, or daily jobs become the

actual bottleneck → **Derive concurrency from a one-partition benchmark, throughput lower bound, and the production SLO, then throttle dynamically.**

• Treat task retry as idempotency → The orchestrator only executes the task again; blind appends still

duplicate data → **Use deterministic input and logical time, then replace a partition or merge by stable key and version.**

• Let historical and live jobs write the same partition → A late-finishing old computation can overwrite a

new correction → Assign partition ownership or use W0/W1 delta catch-up and explicit version ordering.

• Publish after 90 tasks succeed → Success states do not prove complete records, correct amounts, or sensible

differences → Gate on partition, source, business, difference, and consumer checks.

• Overwrite the online table before validation → A failed validation leaves another large recomputation as

the recovery path → Write a versioned output, validate it, cut over once, and retain the old pointer.

• Compare only total row counts → Duplicates and omissions can cancel each other → **Also compare unique

keys, bucketed checksums, amounts, state distributions, and record differences.**

• Call now() in a historical transformation → Two attempts produce different partition semantics →

Pass logical date, input snapshot, and dependency versions as run parameters.

• Rerun all 180 TB after a failure → This expands cost and risk and discards validated progress → **Resume

failed units from the partition manifest; recompute affected partitions only when a version changes.**

Follow-Up Questions and Responses

Follow-up 1: How can the backfill be repeatable if the source has no immutable snapshot?

Prefer a versioned historical export created before the run, or record a database snapshot position, CDC log position, and object version IDs. If the only source is a mutable table, record read time, watermark, and per-partition checksum in the manifest and continuously capture changes during the run for later catch-up. When the original input cannot be reconstructed, disclose the reproducibility gap; do not claim two attempts must be identical.

Follow-up 2: A corrected SCD dimension feeds the fact table. Which should be backfilled first?

Build the affected lineage subgraph and process it in topological order. Create the new dimension version first, then join the fact table to that version as of event time, and finally rebuild aggregates and marts. Every layer shares one backfill version identifier; do not publish an old dimension with a new fact table. Validate dimension business keys, non-overlapping effective intervals, fact foreign-key match rate, and critical aggregates.

Follow-up 3: How do you publish if the platform has no atomic table swap?

Prefer writing an independent version table and routing consumers through a stable view. If the view definition can be replaced atomically, switch only the view. If even that is unavailable, use transactional small-batch replacement with old-partition backups or an explicit maintenance window that pauses related reads and writes for cutover and acceptance. State the visible intermediate states and rollback time; do not describe a multi-step overwrite as atomic.

Follow-up 4: Old and new versions have equal row counts and total amount. What else should be checked?

Compute bucketed checksums by stable business key and inspect record differences. Compare distributions by region, currency, order state, tax band, and boundary date. Confirm known bad examples changed and unaffected examples did not. Check primary keys, referential integrity, state transitions, and refund ceilings. Equal totals can hide one overcount that cancels one omission.

Follow-up 5: Should the run start with the oldest or newest date?

It depends on dependencies and business value. If a later partition depends on state from an earlier one, run forward. If partitions are independent and recent reports have higher urgency, reverse order can help. In both cases, the publication gate still covers the complete interval unless the product owner explicitly approves phased publication with a separate version, consumer scope, and rollback boundary for every phase.