ETL versus ELT: where the transformation runs and what that changes

What it is

The cited Azure Architecture Center page describes extract, transform, load (ETL) as a data integration process that consolidates data from diverse sources into a unified store, with the transformation phase run by a specialised engine, often through staging tables, and typical operations being filtering, sorting, aggregating, joining, cleaning, deduplicating and validating. It states that extract, load, transform (ELT) differs from ETL solely in where the transformation takes place: in the ELT pipeline the transformation occurs in the target data store, using that store's own processing capabilities instead of a separate engine. The page notes that this removes the transformation engine from the architecture, that scaling the target store also scales the pipeline, and that ELT only works well when the target system is powerful enough to transform the data efficiently. Tools such as dbt sit at the T of ELT; its documentation describes it as transforming raw warehouse data into trusted data products.

Why it matters

The order of the letters changes four practical things. Where compute is billed: ELT pays with warehouse query time, ETL with a separate cluster or service. What lands raw: ELT copies source tables as they are, so data-minimisation decisions must be made at extraction or by access control in the warehouse. How reruns work: an ELT transformation is a query over data that is still there, so a fix means re-running SQL; an ETL fix may require re-extracting from a source that has since changed. Who can change logic: SQL in the warehouse is readable by analysts; code in a separate engine belongs to whoever runs that engine.

How to apply

• Choose ELT when the target is a columnar warehouse or lakehouse that already handles the data volume, and the transformations are expressible as SQL over loaded tables.
• Choose ETL, or a transform-before-load step, when the source contains data that must never reach the target (secrets, fields excluded by policy), when the target is a small operational database, or when the transformation needs code or libraries the target cannot run.
• In either case keep the raw extract immutable and separate from transformed outputs, so that a transformation can be reproduced from the same input.
• Record for each output table which source snapshot and which transformation version produced it.

Pitfalls

Loading everything raw and postponing modelling produces a warehouse that only its author can query. ELT bills scale with how often models are rebuilt in full; incremental models need the idempotency discipline described in the related article. Mixed designs, with some cleaning before load and some after, must document which is which, or a dedup rule ends up applied twice or not at all.