🧱 Foundations · Chapter 3

ETL vs ELT

Same three letters, different order — and the order decides what you can recover from.

Study this lesson free in the app →

The basics

ETL vs ELT is about the order of the work: Extract → Transform → Load cleans data before it lands; Extract → Load → Transform lands it raw and cleans it inside the warehouse.

The problem it solves: your transformation logic will have bugs, and the business will ask new questions. If only the cleaned version was kept, you can't redo history. Keeping raw input lets you replay any day with corrected logic.

The mental model: two cooks. The ETL cook peels vegetables at the market and throws the peels away — tidy, but when the recipe changes the market is closed. The ELT cook brings the whole crate home and chops as needed.

Today ELT is the default because storage is cheap, warehouse compute is elastic, and tools like dbt made SQL transformations testable code. Data flows through layers: raw bronze, cleaned silver, business-ready gold.

Explained

ETL: transform, then load. The warehouse only ever sees the cleaned version.

ELT: load raw first, transform inside the warehouse. The raw version is still there tomorrow.

That second sentence is the entire modern shift.

Two cooks, one kitchen

Cook A (ETL) chops the vegetables outside, brings in only what the recipe needs, and throws the peels away at the market. The kitchen stays tidy. But when the chef changes the recipe on Thursday, Cook A has to go back to the market — and the market has closed.

Cook B (ELT) brings the whole crate into the kitchen, stores it, and chops as needed. More storage, but any new recipe can be cooked from what is already there — including recipes nobody thought of in January.

Why the industry moved to ELT

Three things changed:

1. Storage got cheap. Keeping raw data costs less than the meetings about whether to keep it.
2. Warehouse compute got elastic. Snowflake/BigQuery/Databricks can transform faster than a hand-rolled Python box.
3. Transformations became code. dbt turned SQL models into version-controlled, tested, documented, dependency-ordered software.

So the pattern became: land raw → model in layers → serve. Usually named bronze → silver → gold, or staging → intermediate → marts.

The layer cake

BRONZE / raw      exactly as it arrived, append-only, never edited
   │              (replayable source of truth)
SILVER / clean    typed, deduped, validated, conformed keys
   │              (one row per real-world thing)
GOLD / marts      business logic, aggregates, metrics
                  (what dashboards and ML read)

Rule: every layer may only read from the layer above it. Break that and lineage becomes spaghetti.

When ETL is still the right answer

ELT is the default, not a law. Choose ETL-style pre-processing when:

• PII must be masked before it lands — legal, not architectural
• The payload is huge and mostly useless — filter at the edge to save transfer
• The target cannot transform — loading straight into a serving store
• Streaming — enrichment happens in flight, in Flink or Spark Streaming, before the sink

In interviews say "ELT by default, ETL where compliance or payload size forces the transform earlier." That is a trade-off answer, not a fashion answer.

ETL throws away the peels at the market. ELT brings the crate home.

If you cannot re-run yesterday's logic on yesterday's data, you chose ETL — and you may not have meant to.

Gotchas

• Transforming during ingestion 'just a little' — parsing a timestamp, dropping a column. Now raw isn't raw, and you cannot reproduce a bug
• Skipping the silver layer — dashboards read staging directly, business logic gets copy-pasted into six places
• No idempotent re-runs — re-running a model must produce the same result, not duplicate rows

Say this

"I land raw immutably so any transform can be replayed after a logic fix. Modelling happens in the warehouse as version-controlled SQL with tests, in staging → intermediate → mart layers. The exception is PII, which I mask before landing, and high-volume payloads I filter at the edge."

Then the follow-up will be "how do you handle a schema change in the source?" — and that answer is data contracts.

Words to know

In two minutes

• ETL transforms before loading; ELT loads raw first and transforms inside the warehouse.
• ELT keeps the raw input, so a logic bug can be fixed and replayed over all of history.
• The shift happened because storage got cheap, warehouse compute got elastic, and dbt made SQL testable code.
• Model in layers — bronze (raw), silver (clean, deduped), gold (business metrics) — and each layer reads only from the one above.
• ETL is still right for PII masking before landing, filtering huge low-value payloads, streaming enrichment, or targets that can't transform.
• ELT isn't automatically cheap: re-scanning raw tables every hour is expensive, so build incremental models.
• Say: 'ELT by default, ETL where compliance or payload size forces the transform earlier.'

Test yourself

There are 8 questions, 3 puzzles and 4 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.