From ETL to ELT: Where Should Data Transformation Happen?
A technical comparison of ETL and ELT: why transformation placement decides layered pipelines, raw-data retention, reusable logic, reprocessing, and file-first ingestion.
Picture a factory with a dedicated machine shop upstream of the assembly line: parts are cut, drilled and finished before they ever reach the floor. Now picture the alternative — raw stock arrives at the floor and the finishing work happens where the parts are actually used. ETL and ELT differ in exactly this way, and only in this way: not in whether transformation happens, but in where the transformation compute runs relative to the load. ETL transforms in a staging area or a separate engine before loading; ELT loads raw data into the target and transforms it there, using the target's own compute. That single relocation cascades into layered pipelines, raw-data retention, reusable logic, reprocessing, and ingestion patterns such as file-first landing. This article works through each consequence in turn and closes with decision criteria you can apply to your own pipelines.
The One Real Difference: Where the Transformation Runs
Microsoft's architecture guidance states the distinction directly: extract, load, transform "differs from ETL solely in where the transformation takes place." In an ELT pipeline the transformation occurs in the target data store, and "instead of using a separate transformation engine, the processing capabilities of the target data store are used to transform data." In ETL, by contrast, data is modified according to business rules "using a specialized engine," often with staging tables that hold data temporarily before it reaches its destination (Azure Architecture Center).
Conceptual placement of transformation compute, not a measured comparison. In ETL, data is transformed by a specialized engine or in staging tables before it reaches the target; in ELT, raw data is loaded first and transformed by the target store's own compute. The transformation operations themselves are the same in both lanes. Based on the Azure Architecture Center's definition of ETL/ELT.
IBM's comparison table frames the same difference as order and location: ELT loads raw data into a target system and transforms it within that system, while ETL transforms data "in transit, within a staging area, before the data is loaded into a target system" (IBM). Google's definition agrees — in ELT, raw data is loaded "often in its original format or with minimal processing" and transformations are applied only after loading (Google Cloud).
Both patterns transform. The transformation operations themselves — filtering, sorting, aggregating, joining, cleaning, deduplicating, validating — are the same work in either case. What changes is the engine that executes them and the moment at which they run.
Two architectural consequences follow, and they pull in opposite directions. First, ELT simplifies the architecture by removing the transformation engine from the pipeline, and scaling the target data store also scales the ELT pipeline's performance. Second, and as a direct limit on the first: ELT "only works well when the target system is powerful enough to transform the data efficiently" (Microsoft). An underpowered target turns ELT into a bottleneck rather than a simplification. That condition is not a formality — it is the hinge on which most of the rest of this article turns.
Why the Distinction Matters: Layered Pipelines Only Work After Loading
The bronze/silver/gold pattern — Databricks calls it the medallion architecture — is often presented as a design style. It is better understood as a structural consequence of transformation placement. The three layers are defined by what happens in them: bronze is raw data ingestion, silver is data cleaning and validation, and gold is dimensional modeling and aggregation (Databricks).
Read that sequence against the ETL/ELT distinction and the dependency becomes visible. A bronze layer that holds raw, unvalidated data in its original format can only exist if data is loaded before it is transformed. If transformation is forced upstream — in a staging area, before the load — then whatever arrives in the target has already been cleaned, typed and conformed. There is no raw stage to retain, because the raw state never reaches the store. The layering does not merely lose its first tier; it loses the boundary between ingestion and refinement that the other tiers are defined against.
Databricks adds an operational reason to keep the boundary sharp: it does not recommend writing to silver tables directly from ingestion, because doing so "will introduce failures due to schema changes or corrupt records in data sources." The bronze layer absorbs that variability — it "contains and maintains the raw state of the data source in its original formats" and "is appended incrementally and grows over time" — so that cleaning logic in silver can be written against a stable, complete record of what arrived.
The practical upshot: if you want a layered pipeline, the placement decision is not open. Transformation has to happen after the load, because the layers are stages of progressive refinement applied to data that is already resident. Choosing ETL is choosing a different pipeline shape — one where refinement is a precondition of arrival rather than a sequence of stages inside the store.
What Raw-Data Retention Buys and What It Costs
Retention is the property that makes the rest of the ELT argument work, so it is worth separating what it buys from what it costs.
What it buys. IBM notes that ELT lets enterprises "retain access to raw data for future use cases" (IBM). Databricks is more specific about the mechanism: the bronze layer "enables reprocessing and auditing by retaining all historical data." Three distinct benefits sit inside that sentence. Auditing: the record of what actually arrived is preserved independently of what downstream logic later decided it meant. Changed requirements: a new question can be answered from the original data rather than from a summary that discarded the needed dimension. Recovery from logic bugs: when a transformation turns out to be wrong, the correct output can be rebuilt from the source of truth instead of reconstructed from downstream artifacts.
What it costs. The costs below are engineering reasoning, not sourced findings, and they should be budgeted explicitly rather than assumed away.
Storage growth. The raw layer is append-only and grows without bound unless you tier or expire it. Object-storage lifecycle policies are the usual lever; AWS, for example, documents storage classes and lifecycle transitions that move objects to lower-cost tiers as access patterns change (AWS S3 storage classes). Treat that as an illustration of the option, not a prescription — the right tiering depends on how often you actually reprocess.
Governance over sensitive raw data. IBM's comparison notes that ELT targets hold raw data and that governance programs and security measures "can mitigate the risk of cloud-based breaches that expose sensitive raw data." The same table notes that in ETL, sensitive data such as PII "can be masked during in-transit transformation before being loaded." Retention therefore shifts work onto access control, masking views and audit logging inside the target.
Discipline. An immutable raw layer stays useful only if it stays immutable. The moment someone patches a bronze table by hand to fix a downstream symptom, the audit and reprocessing guarantees weaken, usually silently.
The honest framing is that retention is a trade: you pay storage and governance overhead to buy the ability to answer questions you have not yet been asked.
Transformation Logic as a Durable, Shared Asset
When transformation runs in the target, it stops being an artifact of a particular pipeline run and becomes something the organization can own. That shift is the difference between logic as a durable asset and logic as scattered procedure.
In the scattered form, transformation lives wherever it was first needed: inside a per-pipeline script, in a stored procedure attached to one report, in a spreadsheet step that a analyst repeats monthly. Each copy drifts. A fix applied to one does not reach the others, and no one can say with confidence which version produced last quarter's number.
In the durable form, transformation is modeled, tested, versioned and reused. IBM lists "creating layered, reusable models that represent different stages of data transformation" among ELT's benefits, which follows from the placement: if the data is resident and the compute is in the store, the logic can be expressed as named, layered models over that data rather than as code bound to a specific extraction job.
The clearest worked example of the engineering discipline is dbt's treatment of transformations as code. A dbt model is idempotent if it "produces the same results whether you run it once or a hundred times" — it behaves as a pure function of the code and the current state of the database, not of how many times it has run before (dbt Labs). That property is what makes the logic testable in CI, safe to retry after a partial failure, and safe to backfill.
This is engineering-practice reasoning rather than an empirical claim about outcomes: shared, versioned logic is easier to review, test and reuse than duplicated logic, and the placement decision is what makes the shared form natural. The corollary is a cost. A shared asset needs an owner, a review process and a versioning convention. Teams that adopt ELT without adopting those practices get the architecture without the benefit.
Reprocessing: What ELT Makes Possible and What It Costs
Reprocessing is the payoff that raw retention and versioned logic jointly purchase — and like any purchase, it has a price and conditions.
The conditions. Four have to hold before reprocessing is a real capability rather than an aspiration:
An immutable raw layer. If bronze has been edited in place, reprocessing reproduces the edited history, not the actual one.
Idempotent transformations. Re-running must converge on the same result. dbt's documentation is blunt about where this breaks: the most common pitfall in incremental models is "appending rows without deduplication or a reliable unique key," which lets boundary rows be appended again on each run. Setting a
unique_keymakes dbt update or replace matching rows instead of duplicating them (dbt Labs).Versioned logic. Reprocessing with the current logic answers a different question than reprocessing with the logic that was in force at the time. Both are legitimate; they need to be distinguishable.
A deliberate choice between full recompute and incremental processing. dbt describes incremental models as reducing build time "by just transforming new records," which matters most for large datasets where processing everything is expensive — but it also notes that incremental models "require extra configuration and are an advanced usage," recommended when runs become too slow (dbt Labs).
The escape hatch. dbt treats --full-refresh as the safety net: it drops the existing target table and rebuilds it from scratch, and the documentation frames it as "proof that your underlying logic is still idempotent" — the full result should always be reproducible even when the incremental path is optimized (dbt Labs).
The trade. Incremental processing lowers routine compute cost but adds state, configuration and failure modes; full recompute is simpler and more obviously correct but scales its cost with data volume. Neither is universally right. The point is that reprocessing is a capability you engineer and pay for — not a free property that arrives with ELT.
File-First Ingestion: One Option, With Trade-offs
File-first ingestion is one way to land data, not a requirement of ELT and not inherently superior to the alternatives.
The pattern is described in Microsoft's ELT guidance: a typical ELT use case begins "by extracting source data to flat files in scalable storage, such as a Hadoop Distributed File System (HDFS), Azure blob storage, or Azure Data Lake Storage Gen2," after which engines such as Spark, Hive or PolyBase query the source data. The defining property is that the store performing the transformation is the same store where the data is ultimately consumed, reading directly from scalable storage rather than loading into separate storage first — which "skips data copy steps present in ETL" that can be time consuming for large data sets (Microsoft). Platform documentation for file ingestion in a lakehouse describes the mechanics of registering and querying landed files (Azure Databricks file ingestion).
The benefits follow from decoupling. Ingestion can run on its own schedule and write files without waiting on transformation; the raw files are preserved as landed, which supports the retention and reprocessing properties described earlier; and the transformation engine can be changed without re-extracting from sources.
The trade-offs are equally concrete:
File management. You now own layout, partitioning, file sizing and compaction. Small-file accumulation degrades query performance and needs periodic attention.
Schema handling. Files land without enforcement, so schema drift, type changes and malformed records have to be detected and handled downstream — which is precisely the variability the bronze layer is meant to absorb.
Latency. Landing files and then transforming them adds a stage between source and consumer. For workloads where data must be usable on arrival, that stage is a cost.
A related caveat from Microsoft's own description: ELT does not always imply purely virtualized transformations. Some workloads materialize transformed tables or views to improve query performance or enforce governance rules. File-first landing and materialized transformation layers are compatible choices, not opposites.
A Decision Framework: What Stays in ETL, What Moves to ELT
The framework below turns the preceding sections into criteria. It is not a ranking; different criteria will dominate for different pipelines, and a single organization will often land on a mix.
1. Regulatory and compliance constraints. This is the criterion most likely to override the others. Microsoft lists "regulatory or compliance requirements mandate curated staging audits before loading" among the conditions favoring ETL. IBM is more specific: enterprises in highly regulated industries "may require that security-oriented transformations, such as masking and encryption, take place prior to raw data being delivered to target systems," and notes that in ELT such transformation typically occurs only after the data is loaded. If your obligations require that sensitive fields never land unmasked, that requirement decides the placement for those fields regardless of architectural preference.
2. Whether the transformation benefits from the target's native capabilities. Microsoft lists this among the conditions favoring ELT. If the work is set-based SQL over large resident tables, joins, aggregations and window functions, running it in the store avoids moving data to a separate engine. If the work depends on specialized libraries or processing the target does not perform well, a separate engine may still be the right tool.
3. Data volume and target compute capacity. These two belong together because they interact. IBM's comparison positions ELT as designed for big data and ETL as best suited for smaller datasets, and Microsoft's condition — ELT works well only when the target is powerful enough — explains why. Large volume plus a capable target favors ELT; large volume plus a target that cannot transform efficiently favors keeping transformation elsewhere, or upgrading the target first.
4. The operational cost of a separate transformation engine. ETL carries higher upfront infrastructure and engineering costs and higher maintenance, since pipelines require reengineering to accommodate source changes and downstream requirements, per IBM's comparison. ELT's simpler stack lowers maintenance but, IBM cautions, brings "unpredictable data volumes" that "can lead to unanticipated costs later on." Count both sides: the engine you do not run, and the compute you now consume in the target.
5. Data shape. IBM's table notes ELT is designed for structured, semi-structured and unstructured data with a schema-on-read approach at the destination, while ETL is best suited for structured data transformed to fit a predefined schema. If sources are heterogeneous and evolving, that pushes toward loading first.
A workable default for many teams is a hybrid: land raw data, transform in the target for the bulk of analytical modeling, and keep pre-load transformation only where a compliance requirement or a genuinely specialized computation demands it. IBM notes that leading data integration solutions often support both patterns, which makes the hybrid a configuration choice rather than an all-or-nothing migration.
Common Misconceptions
"ELT means no transformation." No. The transformation step is identical in content; it has moved. Microsoft's definition turns entirely on location, and Google describes transformations applied after loading. What ELT removes is the separate transformation engine, not the transformation.
"ELT is always cheaper." No. IBM's comparison gives ELT a lower upfront cost from simpler stacks but warns that unpredictable data volumes can produce unanticipated costs later; ETL carries higher upfront infrastructure and engineering costs. Which total is lower depends on volume, target pricing and how much transformation you run — all of which you have to measure.
"Raw retention is free." No. Retention costs storage that grows over time, governance and access control over sensitive raw data, and the operational discipline of keeping the layer immutable. These are manageable costs, but they are costs.
"Reprocessing is automatic." No. It requires an immutable raw layer, idempotent transformations, versioned logic and an explicit choice between full recompute and incremental processing. dbt's documentation shows how easily incremental models break idempotence without a unique key — the capability is engineered, not inherited.
Conclusion
ETL and ELT differ in where transformation compute runs relative to the load, not in whether transformation happens — and that placement determines whether a layered pipeline, retained raw data, shared logic and reprocessing are available to you at all.
Apply the decision criteria in order of override: compliance requirements first, then whether the target's native capabilities fit the work, then volume against target capacity, then the operational cost of a separate engine, then data shape. Most teams will end up with a hybrid rather than a wholesale switch.
Use this checklist to evaluate your own pipeline placement:
Every transformation has a named location — pre-load engine or in-target — and the reason is documented.
Compliance-mandated masking or encryption is confirmed to occur before data lands, where required.
The target's compute has been measured against the transformation workload, not assumed sufficient.
A raw layer exists, is append-only, and has an owner and an access-control policy.
Transformation logic is versioned, tested and shared rather than duplicated per pipeline.
Re-runs are idempotent: unique keys are defined and a full-refresh path is verified.
Storage growth in the raw layer has a tiering or expiry policy.
The cost of the separate transformation engine, if retained, is compared against in-target compute cost.
FAQ
Does ELT replace ETL entirely? Not necessarily. IBM notes that leading data integration solutions often support both, and Microsoft lists compliance-driven staging audits among the conditions favoring ETL. Expect a mix: ELT for most analytical modeling, ETL where pre-load masking or curated staging audits are required.
Is raw-data retention mandatory? No — it is a design choice with real benefits and real costs. Retaining raw data supports auditing, changed requirements and recovery from logic bugs, but it adds storage, governance and immutability discipline. If you forgo it, you also forgo the ability to reprocess from the source of truth.
How is reprocessing cost controlled? By choosing deliberately between full recompute and incremental processing. Incremental models reduce build time by transforming only new or changed records but require extra configuration and careful idempotence; full refresh is simpler and serves as the safety net that proves the logic is reproducible. Tier or expire raw storage so retention cost does not grow unchecked.
When is file-first ingestion the right choice? When you want ingestion decoupled from transformation, raw files preserved as landed, and freedom to change the transformation engine without re-extracting. It is a poorer fit when latency between source and consumer must be minimal, or when the team cannot absorb file management and downstream schema handling.
Does ELT work for small datasets? It can, but the case is weaker. IBM positions ETL as best suited for smaller datasets and ELT as designed for big data; the advantage of ELT comes largely from using a capable target's compute at scale.