Articles
ETL Pipeline Interview Questions: Idempotency, Backfills, Schema Evolution, and Data Quality
Practice ETL pipeline interview questions with version-aware reruns, deletion handling, safe backfills, schema evolution, and checks before publishing data.
A pipeline finishes successfully, then a rerun restores an old value. A backfill passes every test, then overwrites a correction published by the live job. These failures make ETL pipeline interview questions more interesting than a diagram of extract, transform and load.
The useful starting point: define the output grain, source version and publication boundary. Idempotency should preserve the intended result when the same operation repeats; it should not prevent a legitimate correction or authorize an older job to replace newer data.
This guide uses official Airflow, Confluent and dbt documentation, checked on September 30, 2026. The datasets and repair scenarios are original interview exercises. No candidate reports establish an employer's interview format or question frequency.
Choose one failure point and say what a retry will read, change and publish.
What must you clarify before proposing an ETL pipeline?
Begin with the consumer and the row contract. Is the target one row per shipment, one row per shipment revision, or a daily aggregate? An append-only event table and a current-state table need different retry behavior. An ETL versus ELT choice changes where transformation runs; it does not remove the need to define correctness.
Ask whether the source exposes immutable files, stable event IDs, ordered change sequences or mutable snapshots. Clarify whether a delete is visible, how late a correction can arrive, and whether historical output must represent current corrected truth or what was known at the time.
For this original exercise, the target holds current shipment quantities by entity ID. Each entity has a monotonically increasing source version; deletion is an explicit operation. Source version is authoritative, while ingestion time merely records arrival. Daily reports use the event's business date, with a documented timezone.
That contract gives later answers something concrete to defend. If the source has no reliable version, acknowledge the limitation and negotiate another ordering rule. A processing timestamp cannot manufacture source authority.
What makes a rerun idempotent when data can change?
Official guidance: Airflow advises retryable tasks to avoid partial results, read and write specific partitions, and avoid changing computational inputs through “latest” reads or the current clock. It recommends upsert rather than duplicate-producing inserts for relevant reruns. Airflow best practices
Design inference: an upsert needs a key and a conflict policy. “Update whatever arrived last” can be repeatable for one batch yet wrong when batches arrive in another order. First distinguish identical delivery, a newer correction, an older revision, and a conflicting payload for the same version.
Here is the shipment exercise:
| Incoming record | Target action | State after application |
|---|---|---|
| A, version 1, quantity 10 | Create A | A = 10 |
| A, version 2, quantity 7 | Apply correction | A = 7 |
| A, version 1, quantity 10, delivered again | Ignore stale revision | A remains 7 |
| B, version 1, quantity 5 | Create B | B = 5 |
| B, version 2, delete | Retain versioned tombstone | B excluded from active output |
| B, version 1, quantity 5, delivered again | Ignore stale revision | B remains deleted |
| C, version 1, quantity 3 | Create C | Active total = 10 |
The repeated A record must not reset its quantity to 10. Removing B's row and all version evidence would let its older record resurrect it. Keep enough deletion/version state for the supported replay horizon, or use another authoritative source of deletion history.
If two records share an entity ID and source version but disagree on content, quarantine or fail that conflict. Silently choosing the latest ingestion timestamp conceals an inconsistent source contract. Deduplication is a decision about identity and validity, not simply a DISTINCT clause.
How do late events and deletes affect historical output?
Suppose C arrives today but belongs to an earlier business date. A pipeline selecting only today's event date misses it; one selecting newly ingested changes can discover it. Discovery time and the partitions requiring repair are separate decisions.
Track changed entities and affected dates. A correction that moves an entity from Monday to Tuesday may require removing its contribution from Monday and adding it to Tuesday. Recomputing Tuesday alone leaves Monday inflated. A delete likewise needs the previous contribution or enough retained history to rebuild the affected aggregate.
A fixed lookback window is a practical policy when supported by source lateness and business tolerance. It is not proof that older corrections never happen. Define an exception path for changes beyond the window, and expose what historical data remains provisional.
The local fixture for this article tests stale revisions after corrections and deletion, identical reruns, and equal-version conflicting content. It finishes with A = 7, C = 3 and a retained B tombstone. This verifies the stated mutable-state policy, not a running orchestrator or warehouse.
How can a valid backfill still corrupt live data?
A backfill can be internally correct for the snapshot it read and still be unsafe to publish. Consider this original publication race:
- The backfill reads published generation 17 and builds candidate output.
- A live correction publishes generation 18.
- The older backfill finishes and tries to replace the current dataset.
If step three blindly overwrites the target, generation 18's correction disappears. Independent staging avoids exposing half a dataset, but it does not prevent this lost update.
One design records the expected generation and compares it at publication:
def publish(current, expected_generation, candidate):
if current["generation"] != expected_generation:
raise RuntimeError("stale publication base")
return {"generation": expected_generation + 1,
"dataset": candidate}
This function is a local reasoning model. In production, the comparison and pointer update must be atomic, using a transactional catalog or an equivalent conditional operation. A separate read followed by an unconditional write recreates the race.
The stale generation-17 candidate is rejected. Rebuild or reconcile against generation 18, validate again, and publish generation 19 if its expected base still matches. The generation tracks published dataset identity; it is not a shipment's source version.
Another valid policy serializes writers or performs a version-aware merge. Explain how it preserves live corrections. An intentional historical replacement may require a different contract, but that choice should be explicit and agreed with consumers. Atomic partition replacement also does not guarantee that a report spanning several partitions sees one coherent generation.
How would you plan the backfill without overwhelming production?
Official fact: current Airflow backfill documentation distinguishes reprocessing policies for existing runs, exposes a dry run of considered dates, and provides a backfill concurrency limit applied independently of the DAG's active-run setting. A dry run is not a guarantee that every listed run will be created later. Airflow backfill controls
Preparation reasoning: inventory affected intervals, source versions, code versions and downstream dependencies before choosing the job count. Estimate bytes scanned and write contention as well as task duration. Limit source extraction separately if its service is the bottleneck; a warehouse compute limit does not protect an upstream API.
Try a small representative interval containing a correction or deletion. Compare staged output against the agreed reference, then expand in bounded groups. Define stop conditions such as live freshness exceeding its allowance, rising source errors, or a reconciliation mismatch. Preserve progress so stopping does not force a full restart.
Also explain dependencies. Rebuilding a daily table may require refreshing weekly summaries, cached dashboards and exported datasets. Processing newest intervals first can help freshness, but a cumulative balance or history-dependent model may require older state first. Schedule order follows the dependency contract.
Keep technical rollback separate from business approval. Restoring an earlier dataset pointer is useful only if consumers can tolerate the restored content and any downstream effects are addressed.
Which schema changes are readable but unsafe?
Official fact: Confluent distinguishes backward compatibility, where a new schema can read data from the previous schema, from backward-transitive compatibility across earlier versions. Compatibility rules depend on the serialization format and field definitions. An Avro reader default can supply a field absent from older encoded data. Schema evolution and compatibility
Interview inference: a successful schema check does not establish unchanged meaning. A duration field can remain an integer while its unit changes from seconds to milliseconds. A status field can remain a string while “completed” starts including cancelled shipments. Both can invalidate historical transformations without causing a decoding error.
Explain three contracts: encoding compatibility, target types and business semantics. Preserve old raw input with its schema identity where policy permits. Test the new reader against representative older payloads, including versions needed for the proposed backfill. A check against only the immediately preceding schema is insufficient evidence for the entire retained history.
For a semantic change, version the transformation or introduce an explicit new field and migration path. Decide how old records are interpreted and whether missing values mean unknown, unavailable or a valid default. Filling unknown quantities with zero can create a clean-looking but misleading aggregate.
Which data-quality checks should block publication?
Start from a failure you need to detect. Uniqueness at the target grain, valid source versions, allowed operations and required identifiers protect the shipment contract. Reconciliation checks protect what downstream users see.
In this synthetic counterexample, both source and target have four rows and total quantity 100. A transformation incorrectly moves 10 units from one warehouse segment to another. Global counts and totals still match. Grouped totals or a key-level comparison reveal the error.
Define the reference carefully: identical cutoff, business-date boundaries, deletion policy and treatment of pending records. Then compare counts, distinct keys, segment totals, null rates, rejected rows and representative changed entities. A mismatch without shared definitions can create noise rather than evidence.
Official fact: dbt data-test configuration can distinguish errors and warnings through severity, error_if and warn_if; warnings can also be promoted to errors. dbt severity configuration
Release-policy inference: choose failure actions intentionally. Conflicting entity versions might block publication; a modest change in an optional attribute's null rate might warn. Each rule needs an owner, threshold rationale and response. A warehouse table materialized before a failing test may already be visible, so an external publication gate or staged promotion can be necessary. A green orchestrator run does not prove every consumer-facing result was validated.
How do you explain a wrong result when every task succeeded?
Work backward from one incorrect entity or metric. Identify the published generation and cutoff, inspect its transformed rows, then compare the exact source revisions used. This narrows investigation faster than listing every log system.
Ask whether the input changed between retries, an old writer published late, a delete was lost, a join multiplied rows, a timezone moved a date boundary, or a schema changed units. Keep evidence tied to the implicated boundary.
In an interview, finish with prevention: the invariant added, the failure injected, and the observation proving the repair. Describe what you personally implemented in a real project; use hypothetical language for proposed work. Do not invent production metrics to make the story impressive.
Practice ETL pipeline interview questions
These verified questions provide role-level practice, including records labeled with specific companies. They are not predictions of your exact interview.
| Question | Follow-up to practice |
|---|---|
| Data Pipeline Reliability, Backfills, and Spark Optimization | Preserve live output while repairing history |
| Explain ETL schema changes and ensure integrity | Separate readable payloads from correct business meaning |
| Reason About Duplicate Data and Scaling in Spark | Define duplicates, corrections and safe reruns before scaling |
| Walk Through an ETL Project | Explain personal ownership and failure evidence |
| Design and Debug a Point-in-Time Backtest Data Pipeline | Distinguish corrected history from information available at the time |
Return to the pipeline reliability exercise. Explain the correction, deletion and stale-backfill cases without tool names first. Then choose the mechanisms that enforce those promises.