Skip to content

ETL, ELT and EtLT

Three orderings of the same steps, extract, transform and load, that differ in where data is reshaped: before it reaches the analytical store, inside it, or lightly before loading and fully inside.

Before reading

Data lake, warehouse and lakehouse

Why it matters

Where transformation happens decides what the analytical store contains, who can change the logic, how easy it is to fix a mistake, and whether sensitive data ever lands in the platform. It is one of the first design choices in any pipeline.

How it works

Every pipeline extracts data from a source, transforms it into a useful shape, and loads it into a target. The letters say in which order.

ETL: extract, transform, load. Data is reshaped in a separate processing step, often a dedicated tool or server, and only the finished result is loaded. This was the norm when analytical storage and compute were expensive, so only clean, compact data was worth keeping. It needs its own processing infrastructure next to the store, sized for the transformation load. Classic ETL also usually discards the raw extract after transforming. If the logic was wrong, or a new question needs a field that was dropped, the data must be extracted again, if the source still has it.

ELT: extract, load, transform. Data is loaded close to its original form, then transformed inside the analytical store using its own SQL engine. Cheap storage and scalable compute made this practical. Keeping the raw copy means mistakes can be fixed by re-running transformations, and new questions can use fields nobody planned for. Transformations run as SQL in the store, so anyone who knows SQL can read and change them, and no separate processing infrastructure is needed; the store's compute does the work and is billed for it. The drawback is that everything extracted lands in the platform, including data that should not be there.

EtLT: extract, light transform, load, transform. A small, mechanical transformation happens on the way in, then the real modelling happens inside the store as in ELT. The light step does things that must happen before data lands, or that are needed for every later use: masking or dropping sensitive fields, converting formats, adding load metadata such as the source and the ingestion time, splitting a stream into tables. It never contains business rules.

flowchart TB
  subgraph ETL
    direction LR
    S1[Source] --> X1[Transform<br/>outside the store] --> L1[(Store:<br/>finished data only)]
  end
  subgraph ELT
    direction LR
    S2[Source] --> L2[(Store: raw)] --> X2[Transform<br/>inside the store] --> M2[(Modelled)]
  end
  subgraph EtLT
    direction LR
    S3[Source] --> t3[light transform:<br/>mask, convert, tag] --> L3[(Store: raw, safe)] --> X3[Transform<br/>inside the store] --> M3[(Modelled)]
  end
  ETL ~~~ ELT ~~~ EtLT

Most modern platforms use ELT or EtLT. The choice between them is most often decided by privacy: if raw data contains fields that must never be stored in the platform, the small t is where they are removed.

Key terms

  • Extract: reading data out of a source system.
  • Load: writing data into the target store.
  • Transform: cleaning, combining and reshaping data for use.
  • Raw layer: the copy of source data kept as delivered, the basis for re-running transformations.
  • Load metadata: columns added during loading, such as source system and ingestion time.

Example

The shop's payment service sends events that include full card numbers.

{"payment_id": "p-77", "order_id": "o-1001", "amount": 49.90,
 "card_number": "4111111111111111", "method": "card", "paid_at": "2026-10-28T09:14:03Z"}

With EtLT, the light step on the way in replaces the card number and adds load metadata, and the event lands in shop.raw_payments like this:

{"payment_id": "p-77", "order_id": "o-1001", "amount": 49.90,
 "card_last4": "1111", "card_token": "tok_9f3a", "method": "card", "paid_at": "2026-10-28T09:14:03Z",
 "_source": "payments-service", "_ingested_at": "2026-10-28T09:15:00Z"}

Modelling happens later, inside the store, when shop.clean_payments is built from shop.raw_payments.

The other two orderings handle the same event differently: - ETL: a processing job drops the card number, joins payments to orders and loads only daily totals. A later question about refunds per payment method cannot be answered, because the events were not kept. - ELT: the event is loaded as it is, card number included. Every later question can be answered, but the platform now stores card numbers and falls under the rules for card data.

Pitfalls

  • Business logic in the light step. Once it calculates totals or applies rules, it is ETL again, with logic hidden in ingestion code.
  • Loading raw data with no plan for sensitive fields. Removing them afterwards is far harder than not storing them.
  • Assuming ELT is free. Transforming inside the store uses the store's compute, which is usually what you pay for.

Learn more

  • Joe Reis and Matt Housley, Fundamentals of Data Engineering (O'Reilly, 2022), chapter 8 (queries, modeling and transformation).
  • dbt Labs, Extract, load, transform: https://www.getdbt.com/blog/extract-load-transform

Processing patterns: batch, micro-batch and streaming · Medallion layers: bronze, silver and gold · Ingestion patterns · dbt · PII and data classification