Data platform
ETL vs ELT: where the transformation happens, and why it matters
ETL and ELT differ in one thing: where the data is transformed. That one difference changes cost, speed and who can do the work.
Explainer Published 6 min read
- Saudi technology company, in Riyadh since 1999 CR 1010156897
- ISO 9001:2015 and ISO/IEC 27001:2022 Certified by AQC
- Implements and operates, after go-live too The same team from design to operation
ETL transforms data before it is loaded into the target. ELT loads the raw data first and transforms it inside the target, usually a cloud data warehouse. ELT is common when the target has cheap, scalable compute. ETL is still preferred when sensitive data must be masked or filtered before it leaves the source environment.
ETL and ELT side by side
| Point of difference | ETL | ELT |
|---|---|---|
| Where the transformation happens | On a separate processing layer between the source and the target, before loading | Inside the target itself, after the raw data is loaded |
| What reaches the target | Data already cleaned, joined and shaped for its use | The raw data as extracted, plus the transformed tables built from it |
| Who does the work | Data engineers who build and maintain the pipeline jobs | Engineers load the data; analysts who write SQL often write the transformations in the target |
| Where the cost sits | In the transformation servers or engine, and the people who run them | In the target's processing and storage, which grow with every transformation run on it |
| Sensitive data | Masked, filtered or removed before it leaves the source environment | Arrives in the target raw, so the target must be cleared to hold it and access to it controlled |
| When it fits | Targets with limited processing, data that must be masked first, reports with stable rules | Targets with scalable processing, large volumes, analysts who want the raw history |
The first row is the whole difference; the other five follow from it. What each of the three steps involves is covered in extract, transform and load, step by step.
Two paths from the same source
ETL
-
Extract
from the source systems
-
Transform
on a separate processing layer, before loading
-
Load
clean, shaped tables into the target
ELT
-
Extract
from the source systems
-
Load
the raw data into the target, as it is
-
Transform
inside the target, on its own processing
Two alternatives, not one after the other: the same three steps in a different order, and the order decides where the transformation runs.
When ETL is the right choice
ETL is the right choice when data must change before it arrives. The clearest case is sensitive data: national ID numbers, salaries or patient details can be masked, replaced with tokens or dropped while still inside the source environment, so the target never holds them. A second case is a target with limited processing, such as a reporting database on a fixed server, which should receive finished tables rather than do heavy work itself. A third is data residency: when the target sits with a cloud provider, ETL decides exactly which columns leave the Kingdom and which stay. ETL also suits reports whose rules rarely change, where a tested pipeline that delivers the same tables every night is worth more than flexibility.
When ELT is the right choice
ELT is the right choice when the target can do the heavy work. A cloud data warehouse, or any analytical database that adds processing on demand, transforms large volumes faster than a separate server in front of it, and you no longer run that server at all. ELT also keeps the raw data: if a business rule changes, the tables are rebuilt from the history already in the target, without extracting everything again. It suits analysts who write SQL, because each transformation becomes a query they can read, test and change themselves, kept under version control like any other code. And it shortens the time to the first data: sources are loaded as they are, and modelling starts while more sources are added.
Sensitive data in Saudi Arabia
Decide what may reach the target before you choose
Before choosing between ETL and ELT, list the columns that hold personal or confidential data, and agree where each may be stored and processed. If a column may not reach the target, ELT alone cannot serve it: mask or remove it on the way, with the techniques in our guide to masking sensitive columns. Where the target sits with a cloud provider, check its region against your data residency requirements in the Kingdom.
Relatedmasking sensitive columnsdata residency requirements in the Kingdom
Moving systems rather than reshaping data
When data must move unchanged between live systems
ETL and ELT both reshape data for analysis. When the job is instead to move a system to a new database, once or in phases, or to keep two operational systems in step, that is the work of iblync, Interkey's platform for migration and continuous sync, delivered with the team that operates it.
Relatediblync
ETL and ELT in one pipeline
Many teams use both. A common pattern masks or filters the sensitive columns on the way out of the source, which is the ETL part, loads the result into the target as it is, then runs the remaining transformations there, which is the ELT part. The target never holds what it should not, and analysts still work on data close to the raw source.
When the target is a live system rather than a warehouse for analysis, the question changes to batch loading or change data capture, which our page on moving a live system with batch loads or CDC covers.
Five questions that settle the choice
Answer them for each data set, not once for the whole project.
- May the raw data reach the target? If any column may not, that column needs transforming before loading.
- Can the target scale its processing? A warehouse that adds processing on demand favours ELT; a fixed reporting server favours ETL.
- Who will write and maintain the transformations? Analysts who write SQL can own ELT models; complex logic inside a pipeline tool stays with engineers.
- Will the business rules change? Raw history kept in the target lets you rebuild the tables when they do.
- Which tool will run it? Many tools support both orders; the kinds of tool on the market, and how to judge them, are on a page of their own.
Questions about ETL and ELT
What is the difference between ETL and ELT?
The difference is where the transformation happens. ETL transforms data on a separate processing layer before loading it into the target; ELT loads the raw data first and transforms it inside the target. The rest follows from that: what the target holds, where the processing cost sits, who writes the transformations, and how sensitive data is handled.
What does ELT stand for?
ELT stands for extract, load, transform. The steps are the same as in ETL, in a different order: data is extracted from the sources, loaded into the target as it is, then transformed there, usually with SQL that runs on the target's own processing. The term names an approach, not a product.
Is ELT better than ETL?
Not always. ELT suits a target with scalable processing, such as a cloud data warehouse, and analysts who want the raw history. ETL suits data that must be masked or filtered before it leaves the source environment, and targets with limited processing. Many teams choose for each data set, and some pipelines use both.
Is ELT faster than ETL?
Loading is usually faster with ELT, because data reaches the target without waiting for transformation. How fast the transformation then runs depends on the target's processing capacity and how it is set up. The total time depends on volumes, the target and the transformations, so test it on your own data rather than rely on a general claim.
Can ETL and ELT be used together?
Yes, and it is common. Sensitive columns are masked or filtered before loading, which is ETL, and the remaining transformations run inside the target, which is ELT. The target then holds nothing it should not, while analysts keep data close to the raw source. The split is decided column by column, not once for the whole project.
Next
Read next
Next step
Ask an engineer one question
Tell us where your data comes from, where it must arrive and which columns are sensitive, and ask one question.
What happens next
- We read your request
- An engineer contacts you
- You get an estimate or a demo
Or directly
+966-11-2180999 [email protected]Tawuniya Towers, North Tower, 7th Floor, King Fahad Highway, Olaya, P.O. Box 56835, Riyadh 11564, Saudi Arabia
or What is ETL?