Skip to content
AI Engineer Guide.

concept · data · intermediate · concept 6 of 17

ETL vs ELT

0 of 17 complete

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

Where the transform runs: before the load or inside the warehouseInteractive
Pipeline
signal

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
Step 1/5 · Extract once

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.

  1. Check whether the target can store raw data at acceptable cost and whether policy allows it to land unredacted.
  2. Land source data append-only with load metadata, so a reload is a new partition rather than an overwrite.
  3. Transform in versioned SQL models with tests, so each step is reviewable and rerunnable.
  4. Keep a staging layer that mirrors source shape and a modelled layer that encodes business definitions.
  5. Redact or tokenise sensitive fields at ingestion, before raw data reaches shared storage.
  6. Measure transformation cost per model, because ELT makes warehouse spend a function of how often models run.

Trade-offs

ChoiceGood forCostFails when
ETL with an external engineTransform before data crosses a boundaryA separate compute system to operateThe warehouse is cheaper and already governed
ELT in the warehouseRerunnable SQL and one systemRaw storage and compute costRaw data contains regulated fields
Transform on readNo duplicated storageRepeated compute and inconsistent definitionsMany consumers query the same source directly
Streaming ELTLow latency raw landingSmall files and continuous costAnalysts need daily aggregates only
Trade-off mapInteractive

Good for

Transform before data crosses a boundary

Cost

A separate compute system to operate

Fails when

The warehouse is cheaper and already governed

  1. The warehouse is cheaper and already governed
  2. Raw data contains regulated fields
  3. Many consumers query the same source directly
  4. 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