Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 36 ยท Architecture and Lifecycle

ETL vs ELT

Learn the difference between ETL and ELT, why cloud warehouses made ELT popular, and how to build both patterns with Python and SQL in your browser.

Two three-letter words dominate data engineering talk: ETL and ELT. Both describe moving data from sources into an analytical store. The letters stand for Extract, Transform and Load, and the only difference is the order of the last two steps. That tiny change in order has big consequences for cost, flexibility and who does the work.

The three steps

  • Extract: read data from a source (a database, an API, files).
  • Transform: clean, reshape, join and aggregate it.
  • Load: write it into the destination.

In ETL, transformation happens before loading, in a separate processing layer. Only the transformed result reaches the warehouse. In ELT, raw data is loaded first and transformed inside the destination using its own engine, usually SQL.

flowchart LR
    subgraph ETL
    A1["Source"] --> B1["Transform<br/>(separate server)"] --> C1["Warehouse"]
    end
    subgraph ELT
    A2["Source"] --> C2["Warehouse<br/>(raw)"] --> D2["Transform<br/>(SQL in the warehouse)"]
    end

Where the T runs is the whole difference.

Why ETL came first

When warehouses were expensive, tightly sized servers, you did not want to waste their capacity on messy raw data or heavy transformation. So a separate ETL server did the work and loaded only polished tables. Tools like Informatica and SSIS followed this model.

Why ELT took over

Cloud warehouses and lakehouses changed the economics:

  • Storage is cheap, so keeping raw data is affordable.
  • Compute is elastic and fast, so transforming inside the warehouse is practical.
  • SQL is universal, so analysts can write transformations themselves.
  • Raw data stays available, so you can reprocess when requirements change.

ETL strengths

Filter early Sensitive data never lands.
Smaller loads Only final data is stored.
Any language Python, Java, Scala.
1 / 2

Neither is "right". Many teams use both: ETL-style pre-processing for privacy or format fixes, then ELT for modeling.

ETL in Python

Here the Python code does the transformation, and the database only receives the finished table.

Python ETL: transform before loading

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Only customer_revenue exists in the warehouse; the raw orders are gone. If someone later asks for revenue by order date, you must re-extract.

ELT in SQL

Now load the raw data untouched, and let SQL do the work inside the destination.

Python ELT: load raw, transform with SQL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Because raw data was kept, the new question "revenue by day" needed only one more view. That flexibility is the main reason ELT is popular. Tools such as dbt industrialize this style: they manage a graph of SQL models running inside the warehouse.

Where privacy breaks the ELT rule

Loading raw data means loading everything, including personal data you may not be allowed to keep. A common compromise: apply minimal ETL-style steps before loading, such as dropping or hashing sensitive columns, then do the rest as ELT.

Python Hash an email before loading

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The same person always maps to the same token, so joins and counts still work, but the warehouse never stores the address. In real systems use a secret managed key, not a hard-coded salt. Governance is covered in Governance and Cost.

Choosing between them

Question Leans toward
Sensitive fields that must not land? ETL (at least a masking step)
Cheap elastic warehouse, SQL-skilled team? ELT
Complex logic hard to express in SQL (ML, parsing)? ETL-style code step
Need to reprocess history often? ELT with raw layer
Strict latency, tiny warehouse? ETL

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the only difference between ETL and ELT?

    ETL transforms before loading; ELT loads raw data and transforms inside the destination.
  2. 2. Why did cloud warehouses make ELT popular?

    When storage and compute are cheap and scalable, keeping raw data and transforming in place is attractive.
  3. 3. A benefit of keeping raw data in the warehouse is:

    Raw data lets you add new views and models later without going back to the source.
  4. 4. When might you still do a transformation step before loading?

    Privacy rules often require removing or hashing personal data before it lands in shared storage.

Summary

  • ETL transforms before loading; ELT loads raw data and transforms in the destination.
  • Cheap cloud storage and compute made ELT, with SQL models, the common default.
  • ETL remains useful for masking sensitive data and for logic that is awkward in SQL.
  • Many real platforms mix both.

Next: Pipeline Architecture Patterns