What is ELT (Extract, Load, Transform)?

How it works

In ELT the loader does as little as possible: it copies each source record into a raw (often called bronze) table, frequently as JSON with load metadata such as a batch id and load time. All cleaning, typing, deduplication and modelling then run inside the target engine as SQL, in layers: staging models that type and clean the raw data, intermediate models, and marts shaped for the questions analysts ask.

ELT became the common choice once cloud warehouses and lakehouses separated cheap storage from scalable compute. The benefits:

• Raw data is preserved, so you can rerun or change transformations without touching the source again.
• Transformations are plain SQL that analysts can read, review and test.
• The warehouse’s own engine does the heavy work in parallel.

dbt is built around this pattern: it compiles SQL models, runs them in dependency order inside the warehouse and adds tests and documentation. When something must happen before data lands, such as pseudonymising personal data, that step is ETL, and the rest of the pipeline can still be ELT.

Example

The loader stores records as JSON without changing them. A new field (coupon) lands without any loader change, and a staging view types the fields inside the database (PostgreSQL 16).

CREATE TABLE raw_orders (payload jsonb, loaded_at timestamptz DEFAULT now());
INSERT INTO raw_orders (payload) VALUES
  ('{"id": 1, "amount": "40.00", "status": "PAID"}'),
  ('{"id": 2, "amount": "25.50", "status": "cancelled"}'),
  ('{"id": 3, "amount": "10.00", "status": "paid", "coupon": "WELCOME"}');

CREATE VIEW stg_orders AS
SELECT (payload->>'id')::int            AS order_id,
       (payload->>'amount')::numeric     AS amount,
       lower(payload->>'status')         AS status,
       payload->>'coupon'                AS coupon
FROM raw_orders;

SELECT status, count(*) AS orders, sum(amount) AS revenue
FROM stg_orders GROUP BY status ORDER BY status;

  status   | orders | revenue
-----------+--------+---------
 cancelled |      1 |   25.50
 paid      |      2 |   50.00

If the business later wants coupon orders reported separately, you change the model and every historical day is recomputed from raw_orders.

Common mistakes

• Letting the raw layer become the reporting layer, so dashboards read untyped JSON directly.
• Loading personal data raw when policy forbids it; that part has to be transformed before the load.
• Rebuilding every model in full on every run long after tables have grown; switch large ones to incremental models.
• No retention or access rules on raw tables, which often hold the most sensitive copy of the data.