Stop 04 · SQL & Data Engineering BasicsTopics · 4/9

ETL vs ELT

In short

Should you clean data before storing it, or store everything raw and clean it later? That one choice shapes how most modern data teams are set up.

Two orderings of extract/transform/load — ETL transforms before loading, ELT loads raw data first and transforms inside the warehouse — a distinction that shapes modern data stack design.

The purple blob stands by an open fridge with chopped vegetables on one shelf and whole ones on another.

Think of it like this

Chop first or chop later?

Some homes chop all the vegetables on Sunday and store them ready to cook. Others put whole vegetables in the fridge and chop only what tonight's dish needs. The first is ETL: transform, then store. The second is ELT: store raw, transform when you know what you need.

Watch it happen

Two orders for the same three letters

ETL

Clean before loading
Only tidy data gets stored
Changing logic means re-extracting

ELT

Load raw, clean in the warehouse
Raw history kept for later
Transforms written in SQL

Cheap, powerful cloud warehouses made ELT the common default. ETL still fits when data must be masked or filtered before storing.

The big ideas

What you need to know

Same three steps, different order

Extract, Transform, Load. The question is only whether transform happens before or after the data lands in storage.

ELT keeps the raw data

If you find a bug in your cleaning, you can rebuild clean tables from the raw ones without fetching again.

ETL can protect sensitive data

Masking phone numbers or dropping personal fields before storage is easier when you transform first.

ELT transforms are often SQL

Tools like dbt manage SQL models that build clean tables from raw ones inside the warehouse.

See it in code

Walk through the code

sql

1-- ELT: raw_orders was loaded as-is. Now transform inside the warehouse.2CREATE OR REPLACE TABLE clean_orders AS3SELECT4    CAST(order_id AS INTEGER)              AS order_id,5    LOWER(TRIM(city))                      AS city,6    CAST(amount AS NUMERIC(10, 2))         AS amount,7    CAST(created_at AS TIMESTAMP)          AS created_at8FROM raw_orders9WHERE amount IS NOT NULL10  AND CAST(amount AS NUMERIC(10, 2)) >= 0;

1/3Raw data is already in the warehouse. This builds a new clean table from it.

The raw table stays untouched. If the cleaning rules change next month, you rerun this query instead of pulling everything again. CREATE OR REPLACE works in BigQuery, Snowflake and DuckDB.

Save yourself the pain

Common mistakes

Oops

Throwing away raw data once it's cleaned.

Do this instead

Keep raw data when you can. It's your safety net when cleaning logic turns out to be wrong.

Oops

Loading raw personal data everywhere because 'it's ELT'.

Do this instead

Mask or drop sensitive fields early when privacy rules require it, whichever pattern you use.

Your mission

Try ELT with DuckDB

~20 min

Quick check

Your team found a bug in how cities were cleaned six months ago. Which setup makes fixing history easiest?

Go deeper

1Learn the practical trade-offs between ETL and ELT for different team sizes.
2Identify which pattern a tool you've used (e.g. dbt) follows.
3Sketch both patterns for the same dataset to see the difference concretely.