ETL vs ELT: How to Choose for Your Workloads
ETL and ELT contain the same three steps. They differ in where the transformation runs, and that one difference changes what is kept, who can change the logic, where sensitive data lands and what the work costs. The choice is made per workload, and many organisations rightly run both.
ETL and ELT are two orders for the same work. Data is extracted from a source, transformed into a usable shape and loaded into a store where it will be used. In ETL the transformation happens before the load, in a separate engine. In ELT the data is loaded first and transformed inside the store that receives it.
The question is sometimes asked as though one had replaced the other. It has not. Each suits particular conditions, and the conditions vary between workloads in the same organisation. This article compares the two on the criteria that decide the matter, treats batch and streaming as a separate choice, and ends with a way to decide for a given workload. It names no products.
The difference is where transformation happens
Microsoft’s architecture guidance gives a definition of the older pattern: “Extract, transform, load (ETL) is a data integration process that consolidates data from diverse sources into a unified data store”1. Its description of the newer one is deliberately narrow: “Extract, load, transform (ELT) differs from ETL solely in where the transformation takes place”1. It continues: “In the ELT pipeline, the transformation occurs in the target data store”1.
Google’s explanation makes the same point in terms of time, saying of the two that “their distinction lies in the timing of data transformation”2.
Everything else follows from that. In ETL, a transformation engine sits between source and target, and only the finished result arrives. In ELT, the raw data arrives, and the target’s own processing power does the transformation. Microsoft notes the structural effect: “This simplifies the architecture by removing the transformation engine from the pipeline”1.
Comparing the two patterns
ETL and ELT compared
| Criterion | ETL: transform, then load | ELT: load, then transform |
|---|---|---|
| Where transformation runs | A separate engine before the target | Inside the target store |
| What reaches the target | Shaped, filtered data only | Raw data, with shaped data built from it |
| Rebuilding history after a logic change | Needs the source or a separate archive | Raw data is already there to reprocess |
| Sensitive data | Can be masked or removed before it lands | Lands raw; must be controlled in the target |
| Who can change the logic | Specialists in the transformation tool | Anyone who can write the target’s query language |
| Where the processing cost falls | The transformation engine | The target’s compute |
The table is DII’s summary. The comparison that matters is in the detail, and the sections below take the points on which real choices turn.
What is kept, and what can be rebuilt
In ELT, raw data is in the target before anything is done to it. When a transformation turns out to be wrong, or a new question needs a field that was previously discarded, the raw data is there to reprocess. In ETL, what was filtered out before loading is gone from the target, and recovering it means going back to the source, if the source still holds it.
This is the strongest practical argument for ELT, and it connects directly to one of the properties of a dependable pipeline, the ability to replay the past, described in what makes a data pipeline reliable. It is also a liability. Keeping everything means storing, securing and paying for everything, including data nobody goes on to use.
Where sensitive data lands
ETL lets an organisation decide what enters the target. Personal data can be masked, tokenised or dropped in the transformation engine, and the target never holds it. ELT places the raw data in the target first, so the controls have to be in the target: restricted raw areas, column-level access, masking applied on read.
Amazon’s comparison states the ELT position plainly: “With ELT, all data cleansing, transformation, and enrichment occur within the data warehouse”3. Where a regulatory or contractual obligation says certain data must not be stored in a given platform or region at all, that sentence describes a problem, and transforming before loading may be the only compliant order. Where the obligation is about access, controls in the target can satisfy it. The obligation should be read before the pattern is chosen.
The same source reaches the opposite emphasis on this point, and the difference is worth seeing. It observes that “In ETL, developers have to build custom solutions, like masking PII to monitor and protect data”3, and presents the access controls built into modern warehouses as an advantage of loading first. Both statements are true. Masking before load is work the team must build and maintain. Controls in the target are provided, and they protect data that is nonetheless stored there. Which matters more depends on what the obligation forbids: holding the data, or exposing it.
The capability of the target
ELT moves the heavy work into the target, so the target must be able to do it. Microsoft’s guidance is direct: “However, ELT only works well when the target system is powerful enough to transform the data efficiently”1. Google gives the positive form of the same condition: “ELT is generally better suited for large volumes of data because it leverages the processing power of cloud data warehouses”2.
The rise of ELT followed the arrival of analytical stores that separate storage from compute and scale each on demand. Where the target is of that kind, as in the warehouse and lakehouse designs compared in data warehouse, data lake or lakehouse, ELT is a natural fit. Where the target is an operational database, or an older warehouse with fixed capacity shared with reporting users, transformation inside it competes with everything else it does.
Who maintains the logic
ELT transformations are written in the target’s query language, which a wide group of analysts and engineers can read. That broadens who can contribute and review. ETL logic lives in a separate tool with its own skills.
The breadth cuts both ways. Logic that many people can change needs the disciplines of software: version control, review, tests and a deployment process. ELT without them produces a large body of interdependent queries that nobody can change safely.
Where the cost falls
ELT shifts processing cost to the target’s compute, which on consumption-priced platforms is metered. Transformations that are cheap to write can be expensive to run, particularly when they reprocess full history on every run. ETL concentrates cost in the transformation engine and its licences or infrastructure. Neither is inherently cheaper. The useful question is which costs the organisation can see and control, taken up in controlling data platform costs.
When ETL is the better fit
Google’s summary of the case for the older pattern is that “ETL is often used for complex transformations that require specialized tools and expertise”2. On DII’s reading of the comparison, it is favoured where any of the following holds.
- Data must not land raw. A legal, contractual or policy constraint requires masking or removal before storage.
- The target cannot carry the load. It is an operational system or has fixed capacity needed for other work.
- The target has a strict structure. Amazon observes that in ETL “The transformation stage ensures compliance with the target database’s structural requirements”3.
- The transformation is not expressible in the target. It needs procedural logic, external calls or specialised processing that a query engine does poorly.
When ELT is the better fit
Loading first is favoured by the opposite circumstances.
- The target scales. It is an analytical store designed to process large volumes on demand.
- Requirements are still moving. Keeping raw data preserves the option to answer questions not yet asked.
- History must be rebuildable. Corrections to logic need to be applied to the past.
- The people who know the data can write the logic. Analysts fluent in the query language can own transformations with engineering support.
Why many estates run both
The two patterns are not exclusive, and a single pipeline can combine them. A light transformation before loading removes or masks what must not land. The bulk of the modelling then happens in the target. Microsoft’s guidance acknowledges that the boundary is not sharp, observing that “ELT doesn’t always imply purely virtualized transformations”1.
An estate with an operational reporting database, a cloud analytical store and a regulated data set will reasonably use different orders for each. A standard that mandates one pattern for everything trades fit for uniformity. A better standard states the default, the conditions for exceptions and the engineering practices that apply to both.
Batch or streaming is a separate choice
Whether transformation happens before or after loading is one decision. Whether data is processed in scheduled batches or continuously as it arrives is another. The two are independent: either order can be run either way.
The streaming decision should start from the latency the consumer needs. A report read each morning gains nothing from data that is seconds old. Fraud screening or a live operational view may need it. Continuous processing is harder to build, test and operate than scheduled processing, so the need should be established before the cost is taken on.
Running both for the same data carries a specific cost. Jay Kreps, writing about architectures that maintain a batch path and a streaming path side by side, identified it: “You implement your transformation logic twice, once in the batch system and once in the stream processing system”4. Two implementations of one rule will drift apart. Where both speeds are needed, the aim should be one definition of the logic that serves both.
Choosing for a workload
For a given workload the order is settled by a short run of questions, taken in turn.
- Is there data that must not be stored raw in the target? If so, transform at least that part before loading.
- Can the target carry the transformation? If it has elastic analytical compute, yes. If it is shared or fixed, perhaps not.
- Will the logic change, and must history follow? If so, keep raw data and transform after loading.
- Who will maintain the logic, and with what discipline? Choose the order whose skills and practices the team has.
- What latency does the consumer need? This decides batch or streaming, separately from the first four.
Whichever order is chosen, the pipeline needs the same things around it: monitoring that shows when it is late or wrong, covered in data observability: what to monitor and where to start, and a clear agreement with the teams that supply its inputs, covered in how to implement data contracts between producers and consumers. The Data Pipeline Design Template records the answers to those questions with the other design decisions for a new pipeline.
Sources
- Microsoft. Extract, transform, load (ETL) (Azure Architecture Center, Microsoft Learn).
- Google Cloud. What is ETL?.
- Amazon Web Services. What’s the Difference Between ETL and ELT?.
- Kreps, J. Questioning the Lambda Architecture (O’Reilly Radar). 2 July 2014.
This article is general professional information. It is not legal or regulatory advice.