ETL, ELT and Reverse ETL
Level 2 · Pipelines
What a data pipeline is, and the three patterns it can follow. ETL cleans data before the warehouse. ELT cleans it inside. Reverse ETL sends it back out.
Course contentsLesson 4 of 11
Level 3 · Kinds of data
Level 4 · Models and workloads
After this lesson you can
- Say what a data pipeline does
- Tell ETL from ELT by where the cleaning runs
- Explain what reverse ETL sends back, and to where
12 mincovers 4 glossary entries
Worth reading first: Data Sources and Data Silos
How ETL, ELT and reverse ETL move data
A data pipeline is a set of steps that moves data from a source to a place where it is used. Most pipelines follow one of three patterns. ETL cleans data before it reaches the warehouse. ELT cleans it after it lands. Reverse ETL copies finished data from the warehouse back into business tools.
All three use the same three verbs. Extract reads data out of a source. Transform cleans and reshapes it. Load writes it into the target. The patterns differ only in the order of the verbs and in which way the data flows. That order decides where the work runs and what is kept.
ETL is the older pattern, from the days when warehouse space was dear. Data is cleaned on a separate server first, so only finished rows reach the warehouse. Private fields can be stripped out before they ever land. The cost is that the raw data is gone, so a bug in the cleaning means pulling from the source again.
ELT loads the raw data first and cleans it inside the warehouse, using SQL. It became the usual pattern in the 2010s, when cloud warehouses made storage cheap and fast. Because the raw copy stays, fixing a bug just means running the SQL again. The risk is that raw and private data now sits in the warehouse.
Reverse ETL runs the other way. It reads finished tables in the warehouse and writes them into tools people use every day. A sales tool gets each customer's total spend. A support tool learns which customers pay the most. The warehouse stops being a place people visit and starts feeding the tools they already have open.
Each pattern has its own way to fail. In ETL, a cleaning rule drops rows nobody meant to drop. Nobody notices for weeks. In ELT, raw tables pile up, and nobody knows which ones are safe to use. In reverse ETL, two tools write the same field and undo each other every night.
The three patterns side by side
| Pattern | Order | Where cleaning runs | Where data ends up |
|---|---|---|---|
| ETL | Extract, transform, load | On a server before the warehouse | The warehouse, clean rows only |
| ELT | Extract, load, transform | Inside the warehouse, in SQL | The warehouse, raw and clean |
| Reverse ETL | Extract from the warehouse, load | Already done in the warehouse | Business tools, such as a sales tool |
Which one to pick
- 1
Pick it when private fields must never land in the warehouse, such as health records.
- 2
Pick it for a new team on a cloud warehouse. The raw copy saves you when a rule turns out wrong.
- 3
Pick it when people keep pulling numbers out of the warehouse by hand. They paste them into their own tools.
Worked example
An app team fixes a bad week
The words for this lesson4
Check yourself
Why did most teams move from ETL to ELT?
Finished this lesson?
Marking it complete also marks its 4 glossary entries learned, which updates your roadmap and your saved page. 0 are already marked.