Skip to content
BENIBIX Learn

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

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

PatternOrderWhere cleaning runsWhere data ends up
ETLExtract, transform, loadOn a server before the warehouseThe warehouse, clean rows only
ELTExtract, load, transformInside the warehouse, in SQLThe warehouse, raw and clean
Reverse ETLExtract from the warehouse, loadAlready done in the warehouseBusiness tools, such as a sales tool

Which one to pick

  1. 1

    ETL

    Pick it when private fields must never land in the warehouse, such as health records.

  2. 2

    ELT

    Pick it for a new team on a cloud warehouse. The raw copy saves you when a rule turns out wrong.

  3. 3

    Reverse ETL

    Pick it when people keep pulling numbers out of the warehouse by hand. They paste them into their own tools.

The same verbs in three arrangements. The order decides where the cleaning runs, and the direction decides who gets the data.

Worked example

An app team fixes a bad week

Jack's app loads 2 million raw events into its warehouse each day with ELT. A bug in the cleaning SQL counts every sign-up twice for a week.

The raw events are still there, so he fixes the SQL and runs it again in 15 minutes. With ETL, he would have had to pull a week of data from the app again.

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.