ETL and ELT

ETL and ELT

ETL, ELT and Where Transformation Happens

In ETL a program outside the database transforms records before loading them; in ELT raw records land in the target untouched and SQL inside it does the transforming. BookNest's orders took both routes. JSON, Columnar and Binary Formats's to_csv.py flattened the JSON in Python before load.sh copied it in (ETL). load/raw_load.sh copies orders.jsonl as it is into a one-column jsonb table, and SQL does the typing (ELT):

ELT: typing raw JSON inside PostgreSQL, checked against the ETL path

CREATE OR REPLACE VIEW raw.orders_typed AS
SELECT (doc->>'order_id')::int AS order_id, (doc->>'customer_id')::int AS customer_id,
       (doc->>'order_ts')::timestamptz AS order_ts, doc->>'channel' AS channel,
       doc->>'status' AS status, (doc->>'total')::numeric(10,2) AS total
FROM raw.orders_json;
SELECT count(*) AS rows_that_differ FROM (
  SELECT * FROM raw.orders_typed EXCEPT
  SELECT order_id, customer_id, order_ts, channel, status, total FROM orders) d;

Output

CREATE VIEW
0

The rows are identical; what differs is what you keep. ELT keeps the raw JSON, so a transformation bug is fixed by re-running edited SQL, not by extracting again, and the work runs on the warehouse's own scalable compute. ETL still wins when personal data must be removed before it may land at all (Security and Privacy Basics). Pipeline Foundations compares the two as pipeline designs.

MENU