ETL vs ELT

Definition: ETL (Extract, Transform, Load) and ELT (Extract, Load, Transform) are two orderings for moving data from source systems into a warehouse or lake, and they differ in exactly one place — when transformation happens relative to loading. ETL cleans, joins, and reshapes data in a separate processing step before anything is written to the destination, while ELT loads data in its raw form first and transforms it afterward using the destination’s own compute. That single reordering cascades into very different answers for cost, flexibility, tooling, and how fast a team can iterate on business logic. Neither ordering is universally correct — the right choice depends on the economics of the systems involved, which is exactly why cloud data warehouses shifted the industry’s default from one to the other over the last decade.

How Each Approach Works

Extract is identical in both approaches: data is pulled from source systems — operational databases, APIs, event streams, flat files — into a staging area or processing engine, regardless of what happens next
ETL’s Transform step runs outside the destination: a dedicated processing engine (commonly Informatica, Talend, or an Apache Spark cluster) cleans, deduplicates, joins, and aggregates data before it’s ever written to the warehouse
ETL loads only finished, modeled data: the warehouse typically only ever sees the final transformed result — raw source data usually isn’t retained anywhere queryable downstream
ETL logic is comparatively rigid to change: modifying a business rule commonly means re-running the whole pipeline against source systems again, since raw inputs were never persisted past the processing step
ELT’s Load step happens immediately after extraction: raw, untransformed data lands in the warehouse or lake first, commonly into a dedicated staging schema, with little to no reshaping applied
ELT’s Transform step runs inside the destination: transformation logic is expressed as SQL models (commonly orchestrated with dbt) that run directly against tables already sitting in the warehouse
ELT keeps raw data queryable indefinitely: because the untransformed copy is retained, transformation logic can be rewritten and rerun without ever touching the source systems again
ELT logic is versionable like application code: SQL transformation models can live in a git repository, get code-reviewed, and be tested — a workflow that’s awkward to bolt onto a standalone ETL processing engine

Why It Matters

The choice determines where transformation logic physically lives, and therefore how expensive it is to change, test, and audit
ELT retains a full raw copy, letting a team re-derive entirely new transformations later without re-extracting from source systems that may have changed or gone away
ETL enforces a clean, minimal footprint in the destination since nothing lands there until it’s already validated and modeled — attractive when storage itself is the expensive resource
The decision drives tooling choice and team structure: ETL commonly needs dedicated data-engineering pipeline expertise, ELT lets analysts fluent in SQL own more of the transformation layer directly
Compliance and data-residency requirements can force the decision outright — if raw data legally cannot leave a system unmasked, transformation has to happen before load regardless of what’s otherwise more convenient

Under the Hood: Why Cloud Warehouses Made ELT Practical

ELT was theoretically possible long before it became the default — what changed was the economics of warehouse compute, not the underlying concept. Traditional on-premises warehouses bundled storage and compute into one fixed resource, so running heavy transformation workloads directly against the warehouse competed with the queries analysts were actually trying to run:

Storage and compute used to be one resource. On-prem warehouses and early cloud databases scaled storage and compute together, so a heavy transformation job competed directly with analyst queries for the same fixed capacity
Snowflake-style architecture separated the two. Data sits in cheap, shared object storage while independent, resizable compute clusters read from it on demand — see Snowflake
Compute became elastic and short-lived. A transformation job can spin up a large cluster for ten minutes and shut it down immediately after, paying only for that window instead of a permanently provisioned server
This made “just run it in the warehouse” economical. Heavy SQL transformations no longer had to compete with analyst dashboards for the same fixed hardware, since they could run on entirely separate, temporary compute
Columnar storage and modern query engines absorbed the workload. Warehouses are now optimized for exactly the large scans and aggregations that transformation logic performs, closing most of the performance gap that used to justify a separate processing engine
Separation enabled specialization. Data engineers could focus on reliable extraction and loading, while analytics engineers owned transformation as SQL — a division of labor elastic compute made economically viable, not just technically possible
The net effect flipped the default trade-off. Extracting and loading raw data became the cheap, fast part, while transformation — now just SQL — became something teams could iterate on constantly instead of treating as an expensive, infrequent pipeline change

None of this made ETL obsolete; it made ELT economically competitive for the first time, which is why the shift took hold gradually through the 2010s rather than overnight.

Concretely, this is what “transform inside the warehouse” looks like as a dbt-style project — raw tables loaded untouched, then a chain of SQL models building progressively more refined layers:

Comparison: ETL vs ELT vs Reverse ETL

Reverse ETL is a related, increasingly common pattern worth distinguishing from the other two — it syncs already-transformed warehouse data back out to operational tools rather than bringing data in:

Dimension	ETL	ELT	Reverse ETL
Where transformation happens	Separate processing engine, before load	Inside the warehouse, after load	Inside the warehouse, before syncing back out
Flexibility to reprocess	Low — requires re-extracting from source to change logic	High — raw data retained, rerun SQL anytime	Depends on the upstream ELT layer it reads from
Typical tooling	Informatica, Talend, SSIS, Spark-based pipelines	dbt, SQL, warehouse-native scheduling	Hightouch, Census, custom sync jobs
Typical use case	Legacy on-prem warehousing, pre-load compliance scrubbing	Modern cloud-warehouse analytics stacks	Pushing modeled data into a CRM, ad platform, or support tool
Data direction	Source systems → warehouse	Source systems → warehouse	Warehouse → operational tools
Storage footprint	Minimal — only final modeled data typically stored	Larger — raw and modeled copies both retained	N/A — reads from tables the ELT layer already produced

Common Pitfalls

Assuming ELT means “no transformation planning needed” — it only moves the transformation step, it doesn’t remove the need for clean, well-tested logic
Loading raw, unvalidated data with ELT and letting bad data flow all the way into dashboards before anyone catches it — see Data Quality and Validation
Letting warehouse compute costs from transformation logic grow unexpectedly, since every run consumes billed compute and a sprawling, unoptimized model graph can quietly become the largest line item on the bill
ETL’s rigid, separate pipeline making iteration painfully slow — a one-line business-logic change can require redeploying an entire processing job instead of editing and re-running a single SQL file
Not version-controlling transformation logic either way, leaving “what changed and why” undocumented regardless of whether the logic lives in a processing engine or in SQL models
Treating raw, untransformed ELT tables as safe to query directly from a dashboard, when only the modeled, tested layer downstream should be exposed to end users
Skipping incremental processing and re-transforming an entire history table on every run, turning what should be a cheap update into an expensive full rebuild

Worked Example

The same raw clickstream data, traced through both paths, shows exactly where the two diverge:

Step	ETL Path	ELT Path
1. Extract	Raw click events pulled from the application’s event stream	Raw click events pulled from the application’s event stream
2. Transform	Events cleaned, sessionized, and aggregated in a Spark job outside the warehouse	Skipped for now — nothing is transformed yet
3. Load	Only the finished, aggregated session table is written to the warehouse	Raw, untouched click events are loaded directly into a staging table
4. Transform (ELT only)	N/A — already done in step 2	SQL models clean, sessionize, and aggregate the data using warehouse compute
5. Result	Analysts query a pre-aggregated `sessions` table; changing the logic means re-running the Spark job against source data again	Analysts query a `fct_sessions` mart table; changing the logic means editing SQL and re-running it against data already in the warehouse
6. Cost profile	A processing cluster billed on its own schedule, regardless of how much analysts actually query	Warehouse compute billed per transformation run, idle cost drops close to zero between runs

Real-World Use

ELT dominates modern cloud-warehouse-centric analytics stacks — a “modern data stack” built on Snowflake (or BigQuery, Redshift) plus dbt plus a managed ingestion tool is now the default for most analytics teams starting fresh
ETL persists in legacy on-premises systems — long-running enterprise warehouses built before elastic cloud compute existed still run scheduled ETL jobs because rearchitecting them carries real migration cost
ETL is sometimes required, not just preferred, under strict compliance regimes — healthcare and financial systems handling PII sometimes must scrub, mask, or tokenize sensitive fields before data ever lands in a shared warehouse
Streaming pipelines commonly blend both patterns — raw events land via ELT for exploratory analytics while a parallel ETL-style path pre-aggregates the same events for latency-sensitive dashboards, see Batch vs Stream Processing
Reverse ETL has grown alongside ELT — once transformed data lives in the warehouse, syncing it back out to a CRM or ad platform closes the loop from raw event to operational action

Best Practices

Default to ELT with a modern cloud warehouse unless there’s a specific, identifiable reason not to — the flexibility and lower iteration cost usually outweigh ETL’s tighter upfront control
Still validate, mask, or redact sensitive data before or during load even under ELT — “load raw first” shouldn’t mean “load unmasked PII first,” compliance requirements don’t disappear just because transformation moved later
Version-control all transformation logic as code, regardless of approach — SQL models belong in git with code review and tests exactly like application code does
Layer transformations into staging, intermediate, and mart models rather than one giant SQL script — smaller, single-purpose models are easier to test, debug, and reuse across multiple downstream tables
Use incremental models for large, frequently updated tables instead of rebuilding full history on every run, to keep warehouse compute costs predictable
Document and test each transformation layer’s assumptions explicitly — see Data Quality and Validation — since a silent upstream schema change can break every downstream mart table at once

FAQ

Is ELT always better than ETL? Not universally — ELT usually wins on flexibility and iteration speed with a modern cloud warehouse, but ETL can still be the right call under strict pre-load compliance requirements or when the destination itself has limited compute.

Does ELT mean transformation logic matters less? No — it just moves where that logic lives and runs; the same care around correctness, testing, and validation is still required, only expressed as SQL models instead of a separate processing engine.

Is dbt the same thing as ELT? No — dbt is a popular tool for the “T” in ELT, letting teams write transformation logic as version-controlled SQL that runs inside the warehouse; ELT is the broader pattern dbt happens to be well-suited for.

Can a pipeline use both ETL and ELT at once? Yes — a common pattern extracts and lightly transforms sensitive fields before load for compliance reasons, then loads the result and does the bulk of business-logic transformation afterward inside the warehouse.

What is Reverse ETL, exactly? The mirror image of ingestion — instead of moving data into the warehouse, it takes already-transformed warehouse data and syncs it back out to operational tools like a CRM, support desk, or ad platform.

Did ELT replace ETL entirely? No — ELT became the new default for analytics workloads on cloud warehouses, but ETL-style pre-load transformation is still standard practice anywhere compliance, data volume, or legacy infrastructure requires it.

History

ETL was the default architecture throughout traditional on-premises data warehousing, dating back to relational warehouses becoming common in enterprises during the 1980s-90s
Dedicated ETL tools like Informatica PowerCenter and IBM DataStage emerged in the 1990s, standardizing the separate transform step as its own product category
Cloud data warehouses — Amazon Redshift (2012), Google BigQuery, and Snowflake (2014) — made elastic, separately-billed compute cheap enough to make transforming data inside the warehouse practical at scale
dbt’s 2016 release popularized “transform with SQL inside the warehouse” as ELT’s practical, version-controllable toolkit, turning a vague architectural preference into a concrete workflow
The term “modern data stack” emerged in the late 2010s to describe the ELT-centric combination of a cloud warehouse, a managed ingestion tool, and dbt-style transformation
Reverse ETL tools like Hightouch and Census emerged around 2020 to close the loop, treating the warehouse as the source of truth and syncing modeled data back out to operational systems

Common Interview Questions

“What’s the fundamental difference between ETL and ELT?” — expect an answer centered on the order of transform versus load, not just the acronym expansion
“Why did ELT become more popular with cloud warehouses?” — expect an explanation involving separated storage and compute, and elastic, cheaply billed compute making in-warehouse transformation economical
“When would you still choose ETL over ELT today?” — expect reasoning around compliance requirements to scrub data before it lands, or legacy systems where the destination lacks the compute to transform efficiently
“What is Reverse ETL and how does it relate to ELT?” — expect recognition that it’s the outbound mirror of ELT, syncing already-transformed warehouse data to operational tools rather than bringing data in
“How would you structure a dbt project’s models?” — expect a layered answer: staging models for one-to-one source cleanup, intermediate models for joins and logic, mart models as the final consumable layer

Example

A company loads raw clickstream events into Snowflake untouched (EL), then uses dbt to transform them into clean, aggregated tables (T) — that’s ELT.

A second company handling the exact same clickstream feed instead runs it through Informatica first — events are extracted, cleaned, and aggregated in Informatica’s processing engine, and only the finished aggregate table is ever written to the warehouse — that’s ETL, arriving at a similarly-shaped final table by a structurally different path, and unable to cheaply reprocess the raw events differently later without going back to Informatica and the original source system.

Referenced by

Dig deeper

Spot an error in this note?