ETL and ELT, drawn

Quick reference

• Tier 1 · Sources: OLTP database (OLTP) · SaaS APIs (API) · Files and logs (FILE)
• Tier 2 · Pipeline: Extract (EX) · Transform (TX) · Load (LD)
• Tier 3 · Warehouse: Warehouse (WH) · Data marts (MART) · BI and dashboards (BI)

Request flow: OLTP database → Extract → Transform → Load → Warehouse → Data marts → BI and dashboards; SaaS APIs → Extract; Files and logs → Extract

Explained

Sources Three kinds of source feed the pipeline: an OLTP database that holds the orders, SaaS APIs that hold billing and the CRM, and files and logs that arrive from everywhere else. Extract reads each one in its own way, and that is already the hardest part.

Pipeline Extract pulls the rows into a staging area. Transform cleans, joins and aggregates them, and load writes the result into the warehouse. ETL runs the transform before the load, so the heavy work happens on a separate engine and only clean rows land in the warehouse.

ELT keeps the same boxes and moves the letter: extract loads the raw rows into the warehouse, and the transform runs there, in SQL, next to the data. The warehouse, the marts and the BI layer then read from those tables.

Warehouse The warehouse holds the modelled tables. The marts narrow them for one team or one question, and the BI layer is where a human finally sees a number.

In one line

Count the rows

select count(*) from orders