ETL vs ELT: Differences and When to Use Each

ETL and ELT use the same three letters in a different order, and the order is the whole difference. In ETL, extract, transform, load, data is reshaped by a separate engine before it reaches the destination. In ELT, extract, load, transform, raw data is loaded first and transformed inside the destination. Microsoft's architecture guide puts it plainly: ELT differs from ETL solely in where the transformation takes place (Azure Architecture Center: ETL). That one choice changes what you store, what you can reprocess, and which tools you need.

Both, side by side on real data

The clearest way to see the difference is to build the same result both ways. The source is January 2024 New York yellow taxi trips (NYC TLC: trip record data); the goal is trips and fares per day, excluding negative fares. The warehouse is DuckDB, which can query and load Parquet files directly in SQL (DuckDB docs: Parquet):

import urllib.request
from pathlib import Path
import duckdb
import pandas as pd

source = "yellow_tripdata_2024-01.parquet"
if not Path(source).exists():
    urllib.request.urlretrieve(f"https://d37ci6vzurychx.cloudfront.net/trip-data/{source}", source)
JANUARY = "tpep_pickup_datetime >= '2024-01-01' AND tpep_pickup_datetime < '2024-02-01'"

# ETL: transform in Python, then load only the result
raw = pd.read_parquet(source, columns=["tpep_pickup_datetime", "fare_amount"])
kept = raw[raw["tpep_pickup_datetime"].between("2024-01-01", "2024-02-01", inclusive="left") & (raw["fare_amount"] >= 0)]
etl_daily = kept.groupby(kept["tpep_pickup_datetime"].dt.date).agg(trips=("fare_amount", "size"), fares=("fare_amount", "sum"))
etl = duckdb.connect("etl.duckdb")
etl.execute("CREATE OR REPLACE TABLE daily AS SELECT * FROM etl_daily")
print("ETL warehouse rows stored:", etl.sql("SELECT COUNT(*) FROM daily").fetchone()[0])

# ELT: load the raw data unchanged, transform inside the warehouse with SQL
elt = duckdb.connect("elt.duckdb")
elt.execute(f"CREATE OR REPLACE TABLE raw_trips AS SELECT * FROM '{source}'")
print("ELT warehouse rows stored:", elt.sql("SELECT COUNT(*) FROM raw_trips").fetchone()[0])

def daily_view(min_fare_rule):
    elt.execute(f"""CREATE OR REPLACE VIEW daily AS
                    SELECT CAST(tpep_pickup_datetime AS DATE) AS pickup_date, COUNT(*) AS trips, SUM(fare_amount) AS fares
                    FROM raw_trips WHERE {JANUARY} AND {min_fare_rule} GROUP BY 1""")
    return elt.sql("SELECT COUNT(*), SUM(trips), ROUND(SUM(fares), 2) FROM daily").fetchone()

print("ELT, rule 'fare >= 0':", daily_view("fare_amount >= 0"))
print("ETL, rule 'fare >= 0':", (len(etl_daily), int(etl_daily["trips"].sum()), round(float(etl_daily["fares"].sum()), 2)))
print("ELT, new rule 'fare > 0', no re-extract:", daily_view("fare_amount > 0"))

ETL warehouse rows stored: 31
ELT warehouse rows stored: 2964624
ELT, rule 'fare >= 0': (31, 2927159, 54596173.4)
ETL, rule 'fare >= 0': (31, 2927159, 54596173.4)
ELT, new rule 'fare > 0', no re-extract: (31, 2926266, 54596173.4)

What the run shows

The answers match. Both produce 31 days, 2,927,159 trips and the same fare total. ETL and ELT are two routes to the same result, not two different results.

What is stored differs enormously. The ETL warehouse holds 31 rows: exactly what reports need, and nothing else. The ELT warehouse holds all 2,964,624 raw rows plus a view defining the transformation. ELT costs more storage and relies on the warehouse's compute to transform.

Changing a rule is where ELT pays off. Suppose the business decides zero-fare trips should be excluded too. The ELT pipeline redefines its view against raw data already loaded, and 893 zero-fare trips drop out; nothing is extracted again. The ETL pipeline must go back to the source, which may be slow, expensive, rate-limited or, for data that the source system has since overwritten, gone. Keeping raw data is the main reason modern platforms lean towards ELT.

Why ELT became practical

ETL was the norm when warehouses were expensive, fixed-size appliances: storage and compute were scarce, so only cleaned, summarised data was worth loading, and transformation happened on separate servers. Two changes shifted the balance. Cloud object storage made keeping raw data cheap, and cloud warehouses and lakehouses separated storage from compute, so transformation capacity can be scaled up for a heavy job and released afterwards. Microsoft's guide notes this directly: with ELT, scaling the target data store also scales the pipeline. Once loading everything became affordable, keeping the raw history and transforming it in place became the sensible default.

Layers in an ELT platform

Loading raw data does not mean querying raw data. ELT platforms usually organise data in layers:

• Raw: data exactly as extracted, append-only, kept so anything can be rebuilt.
• Cleaned: types fixed, duplicates removed, rejection rules applied, one row per real entity or event.
• Modelled: fact and dimension tables or aggregates shaped for reporting.

Each layer is built from the one below by SQL transformations that live in version control and are tested like code. Many teams call these layers bronze, silver and gold; medallion architecture describes the pattern in detail. Because every layer can be regenerated from raw, a bug found in a transformation months later is fixed by correcting the SQL and rebuilding, rather than by patching tables by hand.

When to choose each

Microsoft's guidance sums up the trade-off well:

Two practical additions. Sensitive data is a reason to transform before loading: masking or dropping personal fields in flight means raw personal data never lands in the warehouse. And most real platforms mix both: light ETL during ingestion, such as type fixes and masking, then ELT inside the warehouse, often with a tool such as dbt managing the SQL; the dbt tutorial covers that layer.

Variants worth knowing

• Reverse ETL moves modelled data from the warehouse back into operational tools such as a CRM or marketing platform, so teams can act on it.
• Streaming pipelines transform data continuously as events arrive, instead of in scheduled batches, using a message broker and a stream processor.

The design principles that apply to both

Whichever order you choose, the same engineering decides whether a pipeline is trustworthy:

• Rerunnable steps: a retry must not duplicate data; see idempotency in data pipelines.
• Loading only what changed: see incremental data loads, and for databases, change data capture.
• Late and out-of-order data: see handling late-arriving data.
• Explicit data quality rules, counted and logged, as in building your first data pipeline.

ETL versus ELT concepts, extraction, transformation pipelines and loading strategies are part of the data processing and ETL module of the Data Engineering course.

Sources

• Extract, transform, load (ETL) (Azure Architecture Center, Microsoft Learn) — Microsoft Learn
• Reading and writing Parquet files (DuckDB documentation) — DuckDB
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission