ETL and ELT look like the same acronym with the letters reshuffled, and most introductions treat them that way — a terminology distinction worth memorizing, not a decision worth thinking carefully about. The actual difference is architectural: where transformation happens in your pipeline shapes how you debug failures, how you handle business rule changes, and whether you can answer the question "what did the raw data look like?" six months after a pipeline has been running.
What the terms actually mean
Both describe the same three stages: you extract data from a source system, you transform it — cleaning, filtering, reshaping — and you load it into somewhere you can query it. The difference is the order of the last two steps, and that sequence determines where the heaviest processing work in your pipeline actually lives.
In ETL, transformation happens before the data lands anywhere permanent. You pull raw records from your source, run cleaning logic in an intermediate layer, and only write the finished result into your data warehouse. The warehouse receives data that's already been processed.
In ELT, you flip it. You extract raw data and push it directly into your warehouse first, then write transformation logic that runs inside the warehouse after the fact. Your storage layer gets the raw data immediately; the cleaning happens later, against data that's already sitting in a table.
Where transformation happens determines what infrastructure you need, how flexible your pipeline is, and what your options are when something breaks.
Why ETL was the default for decades
ETL made sense when storage was expensive and warehouse compute was slow. If you were paying enterprise licensing fees for an on-premise data warehouse and running queries against spinning disks, you had a strong incentive to minimize what went in. Storing raw data — with all its duplicates, malformed rows, and irrelevant columns — was genuinely costly. So teams invested in dedicated transformation servers: ETL tools like Informatica or SSIS that cleaned and shaped data before it ever touched the warehouse.
The real cost was rigidity. When a business rule changed — say, the definition of "active customer" shifted from 90-day activity to 30-day — you had to update transformation logic in a separate system, re-run historical data through the pipeline, and verify the results across two different environments. Debugging a transformation failure meant chasing logs through an ETL server and a warehouse that had no shared context, and any business rule change required updating logic in both systems before you could trust the numbers again.
Why ELT took over
Cloud warehouses changed the underlying economics. Storage in Snowflake, BigQuery, or Redshift is cheap enough that holding raw data isn't a budget risk. More importantly, these systems are built to run heavy SQL transformations efficiently at scale — so doing your transformation work inside the warehouse is often faster than running it on an external server anyway.
ELT takes direct advantage of that. You land raw data first, which keeps your ingestion layer simple and fast. Transformation logic lives in SQL — or in dbt, which compiles down to SQL — and runs directly inside the warehouse against data that's already there. When a business rule changes, you update a SQL model and re-run it against the raw data you've already stored. You don't re-ingest anything.
There's also a recovery argument that's easy to overlook. In ELT, raw data is always sitting in your warehouse. If a transformation has a bug, you fix the logic and re-run. In a pure ETL setup where only transformed data is stored, a bad transformation step can silently corrupt your historical record — unless you kept the source data somewhere else, which teams frequently don't.
What this looks like on a real pipeline
Say you're building a daily reporting pipeline for an e-commerce company. The source is a PostgreSQL transactional database with an orders table seeing roughly 50,000 inserts per day. Finance needs a reporting table showing revenue, refunds, and volume by region.
In an ETL setup, you write a script — probably Python — that queries new orders since the last run, applies cleaning logic (null shipping addresses, test orders in a dummy currency code, timestamps in the wrong timezone), and writes cleaned rows directly into the warehouse reporting table. Finance sees clean data immediately. But if someone asks six months later what the raw order record looked like before your script touched it, you can't answer that from the warehouse.
In an ELT setup, your ingestion job does one thing: copy raw order rows from PostgreSQL into a raw schema in your warehouse, unchanged. A dbt model then runs the transformation — filtering test orders, correcting timezones, calculating normalized revenue — and writes the result into the schema finance actually queries. The raw schema stays intact. When a revenue number gets questioned, you can trace the final figure back to the exact source row and show precisely which transformation logic produced it.
-- dbt model: orders_cleaned.sql
select
order_id,
customer_id,
convert_timezone('UTC', order_timestamp) as order_timestamp_utc,
amount_usd,
region
from {{ source('raw', 'orders') }}
where currency_code != 'TEST'
and shipping_address is not null
That model runs inside the warehouse, against raw data that's already there. Change the filter logic, run dbt run, and your entire history updates.
The mistake that quietly breaks ELT pipelines
The most common ELT failure I've seen isn't a technical one — it's treating "load first, transform later" as if it means transformation is optional. Teams land raw data into the warehouse, call the ingestion pipeline done, and leave analysts writing the same cleaning logic in four different dashboards. Revenue numbers start disagreeing across reports. Nobody can explain which number is right because the transformation step was never formalized.
ELT doesn't mean raw data is analysis-ready. It means transformation happens inside the warehouse — not never. If your raw orders table has null addresses and no model handles them, every analyst touches that table differently, and your data drifts apart over time. The transformation layer in ELT needs to be version-controlled, documented, and owned by someone. That's the gap dbt fills — it brings software engineering practices to the part of ELT that's easiest to skip.
Where to start
If you're building on a cloud warehouse today, ELT is almost certainly the right default. Keep ingestion simple, land raw data, and define transformations in SQL models you can test and version. ETL still makes sense when data genuinely cannot be stored raw — PII that needs masking before it touches your warehouse, for example — or when you're working within a legacy stack built around ETL tooling that isn't worth migrating yet.
The next time you're designing a pipeline, ask one concrete question first: does the raw data need to be cleaned before it enters storage, or can I afford to store it and clean it there? That answer will tell you which pattern fits.
Top comments (0)