What is ETL (Extract, Transform, Load)?

How it works

ETL and ELT differ in one thing: where and when the transformation runs. In ETL the data passes through a processing engine (Python, Spark, a managed service such as AWS Glue, or an integration tool) on its way to the target, and only the transformed result is loaded. The raw records are often not kept in the target at all.

That order is the right one when:

• Data must change before it lands. If policy says customer identifiers may never be stored in clear text in the analytics platform, they have to be removed or pseudonymised first.
• The work is not SQL-friendly, such as parsing binary files, deeply nested formats, images or ML feature extraction.
• Volume should shrink early, for example filtering or pre-aggregating very high-volume events to save storage and network.
• The target cannot do the compute, such as a small operational database.

ELT has become the default for warehouse and lakehouse analytics because loading raw data first lets you rebuild history with SQL when a rule changes. In practice many pipelines mix the two: a small ETL step for privacy or parsing, then ELT inside the warehouse for everything else.

Example

A transform step that pseudonymises email addresses with a keyed HMAC before loading. The same customer always gets the same token, so joins and distinct counts still work, but the warehouse never stores the address.

import hashlib
import hmac

SECRET = b"held-outside-the-warehouse"   # in practice from a secrets manager

def pseudonymise(value: str) -> str:
    normalised = value.strip().lower()
    return hmac.new(SECRET, normalised.encode(), hashlib.sha256).hexdigest()[:16]

extracted = [
    {"order_id": 1, "email": "[email protected] ", "amount": 40},
    {"order_id": 2, "email": "[email protected]", "amount": 25},
]
transformed = [
    {"order_id": r["order_id"], "customer_token": pseudonymise(r["email"]), "amount": r["amount"]}
    for r in extracted
]
print(transformed[0]["customer_token"] == transformed[1]["customer_token"])
print(sorted(transformed[0]))

True
['amount', 'customer_token', 'order_id']

Common mistakes

• Using a plain unsalted hash for pseudonymisation; names and emails are easy to guess, so anyone can hash candidates and match them.
• Doing every transformation in ETL code, so a changed business rule means re-extracting history from the source.
• Treating pseudonymised data as anonymous; under GDPR it is generally still personal data.
• Calling a pipeline “ETL” or “ELT” without saying where the transformation actually runs, which is the only difference that matters.