ETL vs ELT: Differences, Pros and Cons, and When to Use Each

ETL (extract, transform, load) and ELT (extract, load, transform) are two orders for the same three steps of a data pipeline. In ETL, data is transformed on a separate processing system before it reaches the warehouse. In ELT, raw data is loaded into the warehouse or lakehouse first and transformed there, usually with SQL. The difference sounds small, but it changes where compute runs, what you can reprocess, who can change the logic, and what it costs.

The two pipelines side by side

ETL vs ELT compared

Each pattern in detail

Traditional ETL: transform before loading

1. Extract raw data from databases, APIs, log files and third-party services.
2. Transform in a separate engine (Spark, scripts, ETL tools): clean malformed records, deduplicate, normalize schemas, aggregate, join sources.
3. Load the refined, ready-to-use result into the warehouse.

ETL grew out of old constraints: warehouse storage was priced per terabyte and warehouse compute was slow and costly, so shrinking data before loading saved on both. Strengths: a clean warehouse of validated data, queries fast because data arrives shaped for common access patterns, lower storage cost, and sensitive fields filtered or masked before they ever land. Limitations: raw data is transformed away (need a field you dropped? re-extract from the source), schema changes need pipeline changes before data flows again, and transformation logic lives outside the warehouse in tools with different workflows than SQL.

Modern ELT: load first, transform in place

1. Extract raw data from sources.
2. Load it directly into the warehouse in its original shape (often JSON or staging tables).
3. Transform with warehouse-native tools: SQL, dbt models, stored procedures.

The economics flipped: cloud storage costs pennies per GB per month, and BigQuery or Snowflake scale compute elastically. Strengths: raw data is preserved, so new requirements don't need re-extraction; new source fields simply appear in raw tables; many teams derive different views from the same raw data; transformations are SQL analysts already know, version-controlled with documentation. Limitations: storage grows (raw plus transformed), raw data may hold PII or credentials that shouldn't persist, and careless queries over big raw tables cost money.

Example: adding a new metric

Marketing wants average order value by customer segment, from orders, customers and segment assignments.

-- models/marts/aov_by_segment.sql (dbt): runs inside the warehouse on raw tables
select
    s.segment,
    count(distinct o.order_id)           as orders,
    sum(o.amount) / count(distinct o.order_id) as avg_order_value
from {{ source('raw', 'orders') }}    o
join {{ source('raw', 'customers') }} c on c.customer_id = o.customer_id
join {{ source('raw', 'segments') }}  s on s.customer_id = c.customer_id
where o.status = 'completed'
group by s.segment

Many organizations are hybrid: ETL for sensitive data that must be transformed before storage, ELT for everything else.

The modern data stack

Raw data stays as the source of truth, and transformations are SQL you can rerun any time: adding a metric or fixing a bug means editing a dbt model, not rebuilding extraction. When history must change, see backfill and reprocessing.

Why ELT became the default

Older on-premise warehouses had expensive, fixed capacity, so it made sense to shrink and clean data before loading it. Cloud warehouses and lakehouses separate cheap storage from elastic compute, which flipped the trade-off. Loading raw data is cheap, and keeping it gives you a replayable history: when a business rule changes, you rerun the transformation instead of asking the source team for last year's data again.

ELT also moved transformation logic into versioned SQL models that run inside the warehouse, so analysts can read, review and test the logic that produces a number. That's the workflow tools like dbt popularised: staging models clean raw tables, intermediate models join them, and mart models serve a business area.

ELT doesn't mean "skip modelling". It means modelling happens after loading. Without layers, owners and tests, an ELT warehouse becomes a swamp of near-duplicate tables.

When ETL is still the right call

• Data minimisation or compliance: personal data must be tokenised or removed before it's stored in an analytics system at all.
• Heavy non-SQL work: parsing binary formats, image or audio processing, complex ML feature extraction or large graph computations are easier in Spark or custom code.
• Streaming enrichment: events need to be joined and cleaned in flight because consumers read them in seconds, not after a warehouse job.
• Expensive warehouse compute: repeated transformations over huge raw tables can cost more inside a warehouse than on a dedicated cluster.

Most real platforms are hybrid: light extraction and loading into a raw layer, heavy processing in Spark where it's needed, and SQL models for the business layers. The useful question in an interview isn't "ETL or ELT?" but "where should each transformation run, and can I replay it?"

Design checklist for either approach

1. Keep an immutable raw copy, partitioned by load date, so any transformation can be replayed. See medallion architecture.
2. Make each load idempotent: overwrite a partition or MERGE on a key, never blind-append on retry. See idempotency.
3. Version transformation code and run it through CI with tests, like application code.
4. Add data quality checks between layers so bad data stops before the marts.
5. Record lineage so you know which reports a broken source affects.