ETL and ELT

Every pipeline extracts data from a source, transforms it (cleans, joins, reshapes) and loads it into the warehouse. The question is the order.

ETL transforms before loading, on a separate server. It was the norm when warehouse storage and compute were expensive. It's still the right choice when data must be changed before it lands, for example to remove or mask personal data the warehouse mustn't hold.

ELT loads the raw data first and transforms it inside the warehouse with SQL. Modern warehouses are powerful enough that this is usually simpler: the raw copy is always there to rebuild from, and transformations are SQL that analysts can read. Tools such as Fivetran or Airbyte do the extract-and-load part; dbt is a common choice for the transform part.

Extracting everything every time (a full extract) is simple and fine for small tables. For big ones, extract incrementally: only rows changed since the last run, using a watermark such as the highest updated_at already loaded.

-- the last run loaded orders up to 2024-10-02; fetch only what's newer
SELECT id, customer_id, ordered_on, status
FROM orders
WHERE ordered_on > '2024-10-02'
ORDER BY ordered_on;

Watermarks have traps: rows updated without touching updated_at, clocks that differ between servers, and deletes, which leave no row behind to find. Change data capture (CDC) reads the database's own change log instead, so it sees every insert, update and delete in order. Debezium is a well-known open-source tool for it.

Whichever way, keep the raw extract as it arrived, with the time it was loaded. When a transformation turns out to be wrong, you fix the SQL and rebuild from the raw data rather than extracting again.