ETL vs ELT: Designing Data Pipelines That Don't Break Nightly
Key takeaway
ELT — load raw data first, transform inside the warehouse — is the better default when your destination has cheap compute, because reprocessing doesn't require re-extracting from source systems. Whichever you choose, make every run idempotent and incremental, or you will be rerunning full loads at 3am.
Every reporting stack eventually becomes a set of pipelines that move data from operational systems to somewhere it can be analysed. How you sequence the transform step has consequences you live with daily.
The two shapes
- ETL — extract, transform, then load the finished result. Transformation happens in a separate processing layer, and only modelled data lands in the destination.
- ELT — extract, load raw, then transform inside the destination using its own compute. Raw data is retained, so transformations can be rewritten and replayed without touching source systems.
Why ELT usually wins now
Modern analytical databases have enough compute to do the transformation work, and separating storage from compute makes retaining raw data cheap. That changes the economics: when a business rule turns out to be wrong, ELT lets you rebuild history from the raw layer, while ETL means going back to operational systems that may no longer hold the data. ETL still earns its place when data must be filtered or masked before it lands — regulated fields you're not permitted to store raw, for instance.
The design details that decide reliability
- Idempotency — running the same load twice must not duplicate rows. Use a natural key and a merge, not a blind insert.
- Incremental loads with a high-water mark, so a daily run doesn't re-pull five years of history.
- Late-arriving data — decide how far back each run reprocesses, because source systems routinely backdate records.
- Schema change handling: a new column upstream should not fail the pipeline, and a removed one should alert rather than silently null out a report.
- A clear layering convention — raw, cleaned, modelled — so nobody builds a dashboard directly on an untested extract.
Operating it
- Alert on freshness and row counts, not just on job failure; a job that succeeds having loaded nothing is the dangerous case.
- Make every run restartable from the point of failure rather than from the beginning.
- Keep transformation logic in version control and review it like application code — it encodes business definitions.
- Document which downstream report depends on which model, or every change becomes a guessing game.