ETL and ELT

The three letters are the same; the order changes.

• ETL (*extract, transform, load*): extract from the source, transform on an intermediate server, and load the finished result into the destination. It was the natural path when data warehouse storage was expensive: only what was already clean got in.
• ELT (*extract, load, transform*): load the raw data first and transform inside the platform itself (Spark, Databricks SQL, the warehouse), where the processing power is.

Why ELT gained ground

• Cloud storage is cheap, and keeping the raw data lets you reprocess when the rule changes.
• The transformation runs close to the data, on a distributed engine, instead of on a server in the middle.
• The bronze layer of the medallion architecture is exactly the "L before the T".

When the T comes first

• Sensitive data that cannot reach the lake raw (mask or discard it at extraction).
• A source that only delivers in a format that needs conversion before it can be loaded.
• Volume reduced at the source to save on transfer.

The same transformation in both worlds

import csv
import io

raw = "id,amount\n1,230.50\n2,\n3,abc\n"
valid, rejected = [], []
for row in csv.DictReader(io.StringIO(raw)):
    try:
        valid.append({"id": int(row["id"]), "amount": float(row["amount"])})
    except ValueError:
        rejected.append(row["id"])
print("loaded:", valid)
print("rejected:", rejected)

loaded: [{'id': 1, 'amount': 230.5}]
rejected: ['2', '3']

In ETL, the rejected rows never reach the destination (and vanish, if nobody keeps them). In ELT, all three rows reach bronze, and the rule runs in silver, where you can change your mind about the abc and reprocess.