What Is ETL? – Definition and How It Works
Learn what extract, transform, load means, how ELT differs and why it became common, and where each pattern fits for collected data.
ETL is the pattern of extracting data from a source, transforming it into the shape a destination expects, and loading it there. The ordering of those three verbs is the whole distinction from its variant, ELT.
1. The Three Stages
Extract — read from the source. Databases, APIs, files, or web pages, each with its own reliability characteristics.
Transform — clean, type, normalise, join, aggregate. This is where most of the code and most of the bugs live.
Load — write to the destination in a queryable form.
2. ETL Versus ELT
| Aspect | ETL | ELT |
|---|---|---|
| Transform happens | Before loading | After loading, in the destination |
| Compute used | A separate processing tier | The warehouse itself |
| Raw data retained | Often not | Yes, by construction |
| Reprocessing | Requires re-extraction | Re-run the transformation |
| Suits | Constrained destinations | Modern warehouses with ample compute |
ELT became common because warehouse compute got cheap and elastic. Loading raw and transforming in place means history can be reprocessed without touching the source — which matters enormously for web data, where the source may not be re-readable later.
3. Why Raw Retention Matters for Collected Data
A web page fetched today cannot be fetched as it was tomorrow. If a transformation discards the original and the parsing turns out to be wrong, that data is gone.
Landing raw payloads first and transforming afterwards is therefore not just a modern convenience for web sources — it is the only way to make extraction improvable.
4. Where Transformation Effort Goes
- Typing — strings from HTML and CSV into numbers, dates, and booleans.
- Normalising — units, currencies, casing, whitespace, and identifiers.
- Deduplicating — the same record arriving from several pages or runs.
- Joining — matching records across sources, which is where quality is won or lost.
- Aggregating — into the shape the consuming query expects.
5. Scheduling and Idempotency
Runs are re-executed after failures and fixes, so each must be safe to repeat. The standard approach is partitioning by period and replacing a partition wholesale rather than appending.
Deterministic record identifiers make upserts possible, which turns a re-run from a duplication risk into a routine operation.
6. Where It Breaks
- Silent schema drift at the source, transforming without error into wrong values.
- Partial loads leaving the destination inconsistent.
- Non-idempotent steps duplicating on re-run.
- Transformations that depend on run time rather than on the period being processed.
- No raw retention, making backfills impossible.
7. Choosing a Pattern
Use ELT where the destination has the compute and raw retention is valuable — which describes almost all collected-data work. Use ETL where the destination is constrained, where data must be reduced or anonymised before landing, or where regulation restricts what may be stored.
8. Summary
ETL extracts, transforms, and loads; ELT loads first and transforms in the destination, keeping the raw data available.
For collected web data ELT is usually right, because sources cannot be re-read later and extraction always improves. Whichever order, idempotency and partitioning are what make runs repeatable.