What you will learn
- Explain the difference between ETL and ELT in one sentence
- Name what made ELT practical
- List the trade-offs: cost, flexibility, governance and skills
- Decide which pattern fits a given project
Every analytics setup has to answer the same question: data is created in one place (an application, a payment system, a website) and used in another (a warehouse, a dashboard, a model). Three things must happen on the way: Extract, Transform and Load. ETL and ELT are the same three steps in a different order.
The one-sentence difference
- ETL extracts data, transforms it on a separate server, then loads the finished result.
- ELT extracts data, loads it as it is into the warehouse, then transforms it there.
Where the transformation runs is the whole difference. Everything else follows from it.
How ETL works
The classic design from the era of expensive, limited databases. A dedicated tool pulls data from each source, cleans and reshapes it on its own servers, and loads only the polished tables into the warehouse.
Strengths
- The warehouse only ever holds clean, modelled data.
- Sensitive fields can be removed or masked before they reach the warehouse.
- Predictable load on the target, because the heavy work is elsewhere.
Weaknesses
- Raw data is not kept by default. Change a rule and you may have to extract again.
- Transformation logic lives in a separate tool, often with its own language and its own team.
- The transformation servers can become a bottleneck as volumes grow.
How ELT works
Modern cloud warehouses can store very large amounts of data cheaply and run SQL over it in parallel. ELT uses that: load raw data first, then transform it with SQL inside the warehouse.
Strengths
- Raw data is kept. A new question, or a bug fix in a transformation, means re-running SQL, not re-extracting from the source.
- Transformations are SQL, which analysts already know, and can be put under version control, tested and reviewed like code.
- Scaling the transformation is the warehouse's job.
Weaknesses
- Raw data, including personal data, now lives in the warehouse and needs governance (access control, retention, masking).
- Compute used for transformation is billed on the warehouse, so inefficient SQL costs money.
- Without discipline, "load everything" becomes a swamp of tables nobody understands.
What made ELT practical
Three changes: cheap storage that separates from compute, warehouses that run SQL in parallel across many machines, and tools that make in-warehouse transformation manageable (testing, dependency ordering, documentation). Before these, doing heavy work inside the database that also served reports was a risk to everyone querying it.
A practical layout
Most ELT projects settle on layers:
- Raw (or bronze): exactly what the source sent, with load timestamps. Never edited.
- Staging (or silver): cleaned and typed: renamed columns, fixed types, duplicates removed.
- Marts (or gold): business-ready tables built for reporting, such as a star schema.
Because raw is never changed, you can always rebuild the other layers.
How to choose
| Situation | Lean towards |
|---|---|
| Cloud warehouse, mostly SQL-skilled analysts, need to iterate quickly | ELT |
| Personal data must not be stored until masked or minimised | ETL, or a masking step before the load |
| Source is a stream of events that need light cleaning in flight | Transform in the stream, then load |
| Target system cannot do heavy transformation | ETL |
| Regulations require that only approved fields leave the source | ETL (extract only what is allowed) |
Many real pipelines mix the two: a small amount of cleaning or masking on the way in, then the bulk of the modelling inside the warehouse. The useful question is not "ETL or ELT?" but "what must happen before the data lands, and what can wait until it is safe inside?"
Glossary
- Extract
- Read data out of a source system: a database, an API, files.
- Transform
- Clean, reshape, join and calculate so the data is useful.
- Load
- Write data into the place it will be analysed, usually a warehouse.
- Staging area
- A place where data lands raw before it is transformed.
- Raw layer
- An untouched copy of what the source sent, kept so that transformations can be re-run.
Frequently asked questions
Is ELT always better than ETL?
No. ELT is the common default for analytics in modern warehouses, but ETL remains right when data must be cleaned or masked before it may be stored, or when the target cannot do heavy transformation.
Where do tools like dbt fit?
They are for the T in ELT: SQL transformations that run inside the warehouse, with version control, tests and documentation.
Next steps
- Sketch your current pipeline and mark where transformation happens
- Keep a raw copy of every source you load
References
From Grokem: https://www.grokem.com/data/data-engineering/etl-elt/etl-vs-elt
