ETL vs ELT & Pipeline Design

Interviewers ask about ETL vs ELT and pipeline design not to see whether you know what the acronyms expand to, but to see whether you have actually operated a pipeline that broke — because every pipeline eventually breaks, and what happens next is where the engineering lives. A job dies halfway through a load. A scheduler retries it. An upstream table gets backfilled six months late. A source system's clock skews by four minutes. None of these are exotic failure modes; they are Tuesday. The candidates who separate themselves are not the ones who can define "extract, transform, load" — they are the ones who can explain, with a concrete example, why re-running a failed job must not double-count a single row, and what specific mechanism in their pipeline guarantees that.

This subject covers the two questions interviewers actually probe. First, architecture: why did the industry default flip from transforming data before loading it into the warehouse (ETL) to loading raw data first and transforming it inside the warehouse (ELT), and what changed to make that the right call? Second, and far more heavily weighted in a real interview, correctness under failure: idempotency, incremental loading, change data capture, backfills, and retry semantics — the mechanics of making a pipeline that runs on a schedule, fails sometimes, and is re-run by a human or a scheduler, produce the same correct result every time regardless of how many times any given piece of it executed. A pipeline that works when nothing goes wrong is not a finished pipeline; it is a demo. The rest of this subject is about the part that makes it production software.