ETL vs ELT: Differences, Examples and When to Use Each

ETL and ELT use the same three steps (extract, transform, load) in a different order. In ETL the data is cleaned and reshaped before it reaches the warehouse. In ELT the raw data is loaded first and transformed inside the warehouse, using the warehouse's own compute. The order sounds like a detail, but it changes where your logic lives, what you can reprocess, what you pay for, and who can maintain the pipeline.

ETL: transform, then load

ETL is the classic data warehouse pattern. A dedicated tool (Informatica, SSIS, Pentaho Data Integration, Talend) extracts data, applies business rules on its own server, and loads only the finished result into dimension and fact tables. It made sense when warehouse storage and compute were expensive and fixed: you did not want raw, messy data taking up space, and you did not want heavy transformations competing with report queries.

ETL is still the right choice in a few situations: when sensitive fields must be masked or removed before they reach shared storage, when the target is not a powerful analytical database (an operational system, for example), or when an organisation already has a large, well-run ETL estate. If you use Pentaho, knowpentaho.com has step-by-step ETL tutorials.

ELT: load, then transform

Cloud warehouses changed the economics. Storage became cheap, compute became elastic and billed by use, and SQL engines became fast enough to transform billions of rows in place. So teams started to load raw data as-is and do the transformation in SQL inside the warehouse, usually managed with dbt.

The biggest practical benefit is reprocessing: because the raw data is kept, a bug in a business rule can be fixed and the history rebuilt without going back to the source systems. Transformations written in SQL are also readable by analysts, not only by ETL specialists.

The difference in code

Here is the same small job done both ways, using pandas as the "ETL tool" and DuckDB as the warehouse. The raw orders have messy customer names, a non-numeric amount and a missing customer.

import duckdb, pandas as pd

raw = pd.DataFrame({
    "order_id":   [1, 2, 3, 4, 5],
    "customer":   [" alice ", "BOB", "carol", "bob", None],
    "amount_usd": ["120.50", "80", "n/a", "45.25", "60"],
    "order_ts":   ["2026-09-01 10:02", "2026-09-01 11:40", "2026-09-02 09:15",
                   "2026-09-02 17:30", "2026-09-03 08:05"],
})

# ---- ETL: transform in the pipeline, then load only the clean result ----
etl = raw.dropna(subset=["customer"]).copy()
etl["customer"] = etl["customer"].str.strip().str.lower()
etl["amount_usd"] = pd.to_numeric(etl["amount_usd"], errors="coerce")
etl = etl.dropna(subset=["amount_usd"])
etl["order_date"] = pd.to_datetime(etl["order_ts"]).dt.date
con = duckdb.connect()
con.execute("CREATE TABLE sales_etl AS SELECT customer, order_date, amount_usd FROM etl")

And the ELT version: load the raw table untouched, then express the cleaning as a SQL view.

# ---- ELT: load raw as-is, transform inside the warehouse with SQL ----
con.execute("CREATE TABLE raw_orders AS SELECT * FROM raw")
con.execute("""
CREATE VIEW sales_elt AS
SELECT lower(trim(customer))                AS customer,
       CAST(order_ts AS TIMESTAMP)::DATE     AS order_date,
       TRY_CAST(amount_usd AS DOUBLE)        AS amount_usd
FROM raw_orders
WHERE customer IS NOT NULL
  AND TRY_CAST(amount_usd AS DOUBLE) IS NOT NULL
""")
q = "SELECT customer, SUM(amount_usd) AS revenue FROM {} GROUP BY 1 ORDER BY 1"
print(con.sql(q.format("sales_etl")).df())
print(con.sql(q.format("sales_elt")).df())
print("raw rows kept in warehouse:", con.sql("SELECT count(*) FROM raw_orders").fetchone()[0])

  customer  revenue
0    alice   120.50
1      bob   125.25
  customer  revenue
0    alice   120.50
1      bob   125.25
raw rows kept in warehouse: 5

Both approaches give the same answer. The difference is what remains afterwards. The ETL warehouse contains only the cleaned rows; if you later decide that "n/a" amounts should be treated as zero, you have to re-extract from the source. The ELT warehouse still has all five raw rows, so you change one line of SQL and rebuild. (Run it yourself with pip install duckdb pandas.)

Side-by-side comparison

	ETL	ELT
Where transformation runs	ETL server or tool	Inside the warehouse / lakehouse
What gets stored	Clean, modelled data only	Raw data plus modelled data
Reprocessing history	Re-extract from sources	Re-run SQL on stored raw data
Typical tools	Informatica, SSIS, Pentaho PDI, Talend	Fivetran/Airbyte + dbt, Snowflake, BigQuery, Databricks
Skills needed	ETL tool specialists	SQL, version control, testing
Cost profile	Licence and server for the ETL tool	Warehouse compute for transformations
Sensitive data	Can be masked before loading	Lands raw; needs access controls and masking in the warehouse
Best fit	Strict pre-load privacy rules, non-analytical targets, existing ETL estates	Cloud warehouses, frequent changes to business rules, analyst-owned models

When to choose which

• Choose ELT for a new analytics platform on a cloud warehouse or lakehouse. It is the default for good reasons: simpler pipelines, full history, SQL that more people can read.
• Choose ETL when data must be filtered or de-identified before it lands anywhere shared, for example patient identifiers in healthcare, or when loading into a system that cannot do heavy transformation itself.
• Combine them in practice. Most real platforms do light transformation in flight (type casting, masking a few columns) and the heavy modelling in the warehouse. Some people call this ETLT.

Common ELT mistakes

• Treating the raw layer as a dumping ground. Keep it append-only and documented, with the load time on every row.
• Forgetting cost. Transformations now run on warehouse compute. Incremental models (processing only new rows) matter as data grows.
• No tests. Moving logic into SQL does not make it correct. Add uniqueness, not-null and accepted-value tests to every model.
• Raw personal data visible to everyone. Restrict the raw schema and expose only masked, modelled tables to analysts.

Summary

ETL transforms before loading and keeps the warehouse lean; ELT loads first and uses the warehouse to transform, keeping raw history for reprocessing. With cloud platforms, ELT is the sensible default, and ETL remains the right tool when data must be cleaned or protected before it lands. For how this fits into the full stack, see the modern data engineering architecture guide; for scheduling these pipelines, see Apache Airflow for data engineers.