Why it matters
Extraction is the slowest and least controllable part of a pipeline, so teams want to do it once. Loading raw data first preserves the ability to change a transformation without going back to the source, which is the common case in analytics. ETL made sense when warehouse compute was scarce and storage expensive; the trade has inverted, but the governance obligations of storing raw data have not disappeared.
Mental model
ETL is cooking before delivery; ELT is stocking the pantry and cooking to order. The metaphor breaks because a pantry can be inspected and re-cooked from, while raw data may contain personal information that should never have been stored, so access control matters as much as the recipe.
How it works
ETL · Step 1 of 5
Extract once
The source is read once per run. Because the transform runs before load, that same hop happens again for every rule change, and the source may not offer the history the rule needs.
Active layers
- Source systems
- Extract
Hover a layer to read what it holds.
Next: Transform outside
- Transform
- before load
- Warehouse holds
- modelled rows
- Rule change
- re-extract
Figure. Two pipelines behind a switch, each a complete stack with its own steps. ETL transforms on a separate engine and loads only modelled rows, so no raw layer exists in the warehouse. ELT lands raw append-only and transforms with the warehouse's compute, so a changed rule is a SQL rerun. Tools, schedulers, where redaction happens, and per-table detail are omitted.
What to notice. The switch is a placement decision, not a maturity ladder. ETL is the right shape when the target cannot hold raw rows or a field must be redacted before it leaves a boundary; ELT is the right shape when the warehouse is cheaper than a second engine and raw storage is allowed. Each arrangement shows the failure it prevents: ETL has no raw copy to replay a changed rule against, while ELT keeps rows that the warehouse's access control must then cover.
Text version
A switch labelled Pipeline selects ETL or ELT, each a complete vertical stack with its own steps. ETL: source systems are extracted, transformed in a group outside the warehouse that holds clean and conform then business rules, loaded, and written to a warehouse holding only modelled rows, which BI and models query. ELT: source systems are extracted, raw rows are loaded append-only into a retained raw-layer group, then a transform group inside the warehouse runs staging models and modelled tables, which BI and models query. In ETL a changed rule has no raw copy to replay; in ELT the same change is a SQL rerun from raw.
- Check whether the target can store raw data at acceptable cost and whether policy allows it to land unredacted.
- Land source data append-only with load metadata, so a reload is a new partition rather than an overwrite.
- Transform in versioned SQL models with tests, so each step is reviewable and rerunnable.
- Keep a staging layer that mirrors source shape and a modelled layer that encodes business definitions.
- Redact or tokenise sensitive fields at ingestion, before raw data reaches shared storage.
- Measure transformation cost per model, because ELT makes warehouse spend a function of how often models run.
Trade-offs
| Choice | Good for | Cost | Fails when |
|---|---|---|---|
| ETL with an external engine | Transform before data crosses a boundary | A separate compute system to operate | The warehouse is cheaper and already governed |
| ELT in the warehouse | Rerunnable SQL and one system | Raw storage and compute cost | Raw data contains regulated fields |
| Transform on read | No duplicated storage | Repeated compute and inconsistent definitions | Many consumers query the same source directly |
| Streaming ELT | Low latency raw landing | Small files and continuous cost | Analysts need daily aggregates only |
Good for
Transform before data crosses a boundary
Cost
A separate compute system to operate
Fails when
The warehouse is cheaper and already governed
- The warehouse is cheaper and already governed
- Raw data contains regulated fields
- Many consumers query the same source directly
- Analysts need daily aggregates only
Figure. Each choice with what it buys, what it costs, and the case where it loses.
What to notice. The fourth column is the decision: if the losing case describes your situation, the choice is wrong.
Text version
Four trade-offs: ETL with an external engine is good for Transform before data crosses a boundary, costs A separate compute system to operate, and fails when The warehouse is cheaper and already governed. ELT in the warehouse is good for Rerunnable SQL and one system, costs Raw storage and compute cost, and fails when Raw data contains regulated fields. Transform on read is good for No duplicated storage, costs Repeated compute and inconsistent definitions, and fails when Many consumers query the same source directly. Streaming ELT is good for Low latency raw landing, costs Small files and continuous cost, and fails when Analysts need daily aggregates only
Failure modes
- Unrecoverable transform. A business rule was wrong and the raw data was never kept. Debug: check whether the landing zone retains source records for the affected period.
- Raw data exposure. Personal fields land in a shared schema and every analyst can read them. Debug: audit grants on raw schemas and scan column names against the classification list.
- Model sprawl. Hundreds of models rerun hourly and compute cost climbs. Debug: rank models by run frequency multiplied by bytes scanned.
In production
Watch warehouse spend per model and per team, not just in total. Keep raw retention deliberate and access-controlled, and rehearse rebuilding a modelled table from raw after a logic change.
Common misconceptions
People think: ELT is strictly more modern than ETL.
Actually: It is a placement decision. If data cannot leave a boundary unredacted or the target cannot hold raw rows, transform first.
People think: Keeping raw data makes the pipeline reproducible.
Actually: Only if the load is immutable and the transformation code is versioned; overwritten raw tables reproduce nothing.
Interview angle
Answer with placement and retention: where transformation runs, what raw data is kept, who can read it. Then name the failure each choice prevents. Weak answers describe ELT as newer and skip the governance cost of storing raw records.
Sources
- dbt documentation — SQL transformations as versioned, tested models.
- PostgreSQL documentation, COPY — bulk loading, which is the load step in practice.
- Kleppmann, Designing Data-Intensive Applications — batch pipelines and the cost of reprocessing.