System Design · medium
A nightly pipeline fails halfway through. How do you design it so re-running is safe?
Asked in Data Engineer interviews, in the System Design round.
Short answer
Make each step idempotent and partition-scoped: a run owns a date partition and fully replaces it, so re-running converges instead of accumulating. Checkpoint at partition boundaries rather than per row, recovery granularity should match the unit you can safely recompute. Side effects that cannot be repeated (incrementing a counter, sending an alert, charging something) either move outside the retryable path or get keyed and deduplicated.
How to answer it
The property you want
Idempotency: running the job once and running it five times leave the system in the same state. It is a design property, not something added afterwards by cleaning up duplicates.
How to get it
Idempotent writes MERGE on a stable business key, or
delete-then-insert scoped to the partition, in one
transaction
Stable keys derived from the source data, never generated at load
time. A surrogate key made at runtime differs on every
run and defeats the whole thing
Partition scoping rebuild one day, not the table. An unscoped delete is a
very fast way to lose everything
Atomicity one transaction, or write to a staging table and swap
it in, so a half-finished run is never visibleCheckpointing and task granularity
Break the job into tasks with explicit dependencies, as an orchestrator like Airflow or Dagster does, so a failure resumes from the failed task rather than from the beginning. Small tasks make retries cheap; enormous single-task jobs make every failure a full rerun.
Retries need backoff and a cap. Infinite retries on a genuinely broken input turn one failure into an outage of everything behind it.
Prove it rather than assert it
run the job twice on the same input
assert row count unchanged
assert a checksum of the output unchangedThat test is worth more than any amount of care, because idempotency is easy to believe you have and hard to actually have.
Key points
- Idempotency is designed in, through a stable business key and scoped writes.
- Scope the delete to the partition, always.
- One transaction, or a staging table and an atomic swap.
- Split into tasks so a rerun resumes rather than restarts.
- Test it by running twice and comparing, rather than trusting it.
FAQ
- What about side effects like emails? Those are not idempotent by nature. Guard them with a sent-log keyed on the same business key.
- Does this apply to streaming? Yes, and more so. At-least-once delivery guarantees duplicates, so consumers must be idempotent.
- What if the source data changed between runs? Then a rerun legitimately produces different output. Snapshot the input if reproducibility matters.
The 30-second answer
I would design the job to be idempotent, so running it five times leaves the same state as running it once, rather than cleaning up duplicates afterwards.
Concretely that is either a MERGE on a stable business key, or a delete then insert scoped to that day's partition inside a single transaction. The key has to come from the source data, because a surrogate key generated at load time differs on every run and defeats the whole thing.
I would also break the job into tasks with dependencies in an orchestrator so a failure resumes from the failed step rather than the start, and put retries behind backoff and a cap. And I would test it rather than assume it: run twice, and assert the row count and a checksum have not moved.
In the interview
- What they are checking: the word idempotent, partition-scoped replacement, and the treatment of side effects.
- Common mistake: adding "retry 3 times" to the job and calling it resilient. Retrying a non-idempotent step three times makes three copies.