ETL Orchestration, Error Handling & Restartability

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

A single ETL script that works perfectly in isolation is not the same thing as a production-grade pipeline, and this topic is where interviewers separate candidates who've built one-off loads from those who've operated a warehouse's nightly batch process under real failure conditions over months and years.

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

Orchestration is about sequencing and dependency management across dozens or hundreds of individual load steps: dimension tables generally must load before the fact tables that reference them (to avoid broken surrogate key lookups), and independent load streams can run in parallel to shrink the overall batch window, while dependent steps must wait for their prerequisites — tools like ODI's Load Plans, Oracle Scheduler, or general-purpose orchestrators define this dependency graph explicitly rather than relying on a single long linear script where one slow step blocks everything downstream unnecessarily.

OrchestrationLoad Plans

Error handling needs to distinguish between different failure severities and respond appropriately: a single bad row (caught by the data-quality/reject-table pattern discussed earlier) generally shouldn't halt the entire batch — it gets logged and quarantined while the rest of the load continues; but a genuinely broken step (source system unreachable, a critical transformation throwing an unexpected error) should halt dependent downstream steps to avoid loading incomplete or nonsensical data forward, while independent parallel streams can often continue unaffected.

Error handling

Restartability is the property that lets a failed job be safely resumed or re-run from a checkpoint rather than restarting the entire multi-hour batch from scratch — this depends on idempotent load logic (the MERGE/upsert pattern discussed in incremental loading) and on tracking job-level checkpoints/state (which steps completed successfully, which watermarks were actually committed) so a restart knows precisely where to pick back up.

Restartabilityidempotentjob-level checkpoints/state

A senior-level point worth raising unprompted: end-to-end monitoring and alerting — a batch that silently fails at 2 AM with nobody notified until a business user complains the next morning that a dashboard looks stale is an operational failure independent of the ETL logic's correctness, so mature pipelines integrate job status into a monitoring/alerting system (email, PagerDuty-style paging, or a dedicated operations dashboard) as a first-class requirement, not an afterthought.

end-to-end monitoring and alerting