ETL and ELT
Abbreviation: ETL / ELT. Also known as: extract, transform, load, extract, load, transform.
Two orders for moving data into an analysis store: transform it first and then load it (ETL), or load it raw and transform it afterwards (ELT).
Synthetic dataExamples on this page use synthetic data created for illustration.
Definition
ETL extracts data from its sources, transforms it, and only then loads the result into the target store. ELT loads the raw data first and transforms it inside the target. The choice decides where the work happens and whether the untouched original is kept.
In plain terms
Both start with extract (copy from the source) and end with data that analysts can query. The difference is the middle. In ETL the cleaning, joining and reshaping happen on the way in, so the store only ever sees the finished result. In ELT the raw data lands first and the same work is done afterwards, usually as queries or scheduled jobs inside the store.
Keeping the raw copy is the practical advantage of ELT: when a rule turns out to be wrong you can re-run the transformation from the original instead of asking the source again. The price is more storage, and the need to control who may read raw data, which can contain things you would not want to keep.
Example
A feed delivers 1,000 order rows, of which 3 repeat an earlier row (same order id).
| ETL | ELT | |
|---|---|---|
| Rows the store receives | 997 (duplicates removed on the way in) | 1,000 (raw, untouched) |
| Rows in the analysis view | 997 | 997 (a view that removes duplicates) |
| If the de-duplication rule was wrong | Fetch the feed again, if the source still has it | Change the view and re-run; the raw rows are still there |
The analysis result is the same. What differs is how cheaply a mistake can be repaired.
Common misunderstandings
- “ELT is the modern way and ETL is obsolete.” Both are in use. ETL is natural when data must be cleaned or minimised before it may be stored; ELT is natural when storage is cheap and the rules change often.
- “Transform just means cleaning.” It also covers joining, reshaping, deriving new fields and applying agreed business definitions.
- “With ELT the raw layer looks after itself.” Keeping raw data is a decision that needs its own access rules and retention period.
References
- Ralph Kimball, Joe Caserta. The Data Warehouse ETL Toolkit . Wiley, 2004. ISBN 978-0764567575.
- Martin Kleppmann. Designing Data-Intensive Applications . O'Reilly Media, 2017. ISBN 978-1449373320.