ETL Orchestration, Error Handling & Restartability

Explain how production ETL pipelines are orchestrated with dependency management, error handling, and restart/recovery capability.

A load that works in isolation is not a pipeline. Orchestration, failure handling and restart are what make it operable.

It's like an assembly line with several parallel sub-lines feeding into a final assembly stage: a jammed part on one sub-line gets pulled aside for inspection without stopping every other sub-line, the final assembly stage correctly waits only for the sub-lines it actually depends on, and if the whole line loses power, it resumes from the last completed station rather than starting the entire day's production run over from scratch.

Key Concepts

Dependencies decide the sequence.

    dimensions first, then facts
      -- a fact load needs the surrogate keys the dimension
         load created

Load a fact before its dimension and every lookup fails, producing unknown-member rows for data that was perfectly good.

Independent streams run in parallel, which is where the window is won. Customer and product dimensions have no relationship, so they load together.

    stage 1:  all dimensions, in parallel
    stage 2:  facts, in parallel
    stage 3:  aggregates
    stage 4:  cache purge and seed

Restartability is the property that matters at 3 a.m.

    restartable   rerun the failed step; it produces the same result
    not           rerun and it double-loads, or the state is
                  inconsistent

Making a step idempotent — a merge on the business key rather than an insert, a delete-then-insert for the affected period — turns a failure into "run it again" rather than an investigation.

Checkpointing for long steps. A fact load processing a hundred million rows should commit and record its position periodically, so a failure at 90% does not repeat the first 90%.

Error handling should distinguish severity.

    a row fails validation        -> reject it, continue
    a source is unavailable       -> retry with backoff
    a reconciliation check fails  -> stop; do not publish

The third is the one people omit. A load that completed with totals that do not match the source should not make the data visible, because a wrong dashboard is worse than a late one.

Publishing separately from loading is how that is enforced: load into the warehouse, verify, and only then make it available — by switching a partition, updating a view, or setting a flag the reports respect.

Monitoring the pipeline, not the steps. A step that succeeded while processing zero rows is a success by its own measure and a failure by every other. Alert on expected against actual volume, on elapsed time against normal, and on the reject count — not only on exit status.

And the thing that gets forgotten. A pipeline that finishes at 05:00 instead of 03:00 for three weeks is heading for a failure on the day it overruns the business day. Trend the duration, not just the outcome.

What the interviewer is probing.1. "What makes a pipeline restartable?" Probing: the engineering. Stalls: "A retry button." Moves up: idempotent steps with recorded state, so rerunning from the failure point neither duplicates nor skips work.

2. "How do you make a load idempotent?" Probing: the technique. Stalls: "Delete first." Moves up: a batch identifier with delete-then-insert for that batch, or a merge on the business key — so the second run produces the same result.

3. "What should the orchestration layer own?" Probing: the separation. Stalls: "Everything." Moves up: dependencies, scheduling, retries and alerting — the individual jobs should not each implement their own.

4. "What do you alert on beyond failure?" Probing: the depth. Stalls: "Nothing." Moves up: a job that succeeded with zero rows, or took far longer than usual — both are failures that no error handler catches.