ETL vs ELT: Modern Data Pipeline Design

ETL (Extract, Transform, Load) transforms data before it reaches the warehouse. ELT (Extract, Load, Transform) loads raw data first and transforms it inside the warehouse. The shift from ETL to ELT is one of the defining changes of the modern data stack. This guide explains why it happened, when ETL still wins, and how to design pipelines that combine both.

The Two Models

ETL:  extract ──► transform (staging server) ──► load (warehouse)
ELT:  extract ──► load (raw into warehouse) ──► transform (in warehouse)

ETL transforms in a separate compute environment, then loads clean data. The warehouse only ever sees processed data.

ELT loads raw data into the warehouse and transforms it there with SQL. The warehouse does the heavy lifting.

Why ELT Won

Three forces pushed the industry to ELT:

1. Cheap storage: cloud warehouses (Snowflake, BigQuery, Redshift) made storing raw data nearly free. There is no cost penalty for loading everything.
2. Massive compute: warehouses scale compute independently and on demand. Transforming in the warehouse is often faster than a dedicated ETL server.
3. Raw data is valuable: analysts can explore raw data, backfill transformations, and re-derive metrics when business logic changes. ETL destroyed that flexibility by discarding raw data.

The ELT Pattern with dbt

The modern ELT stack: extract with a tool like Fivetran or Airbyte, load raw into the warehouse, transform with dbt:

-- models/staging/stg_orders.sql
-- Stage raw data: clean types, rename fields, deduplicate
SELECT
    id AS order_id,
    customer_id,
    CAST(amount_cents AS NUMERIC) / 100 AS amount,
    status,
    created_at::timestamp AS created_at
FROM {{ source('raw', 'orders') }}
WHERE _airbyte_emitted_at >= '2026-01-01'

-- models/marts/daily_revenue.sql
-- Business logic lives in the warehouse, versioned in git
SELECT
    DATE_TRUNC('day', created_at) AS day,
    SUM(amount) AS revenue,
    COUNT(DISTINCT customer_id) AS customers
FROM {{ ref('stg_orders') }}
WHERE status = 'completed'
GROUP BY 1

# dbt_project.yml
models:
  my_project:
    staging:
      +materialized: view
    marts:
      +materialized: table

dbt gives ELT what ETL tools had: versioned, testable, documented transformations. dbt test runs data quality checks (uniqueness, not-null, accepted values) as part of the pipeline:

# models/marts/schema.yml
version: 2
models:
  - name: daily_revenue
    columns:
      - name: day
        tests: [not_null, unique]
      - name: revenue
        tests:
          - not_null
          - dbt_utils.accepted_range:
              min_value: 0

When ETL Still Wins

ELT is not universally better. ETL remains the right choice when:

1. The target cannot transform: loading into a legacy database, an API, or a system with no compute. The transformation must happen before load.
2. Data volume is prohibitive: transforming before load reduces what you ship. For massive raw streams, ETL filters first.
3. Compliance requires it: PII must be removed or masked before data leaves the source environment.
4. The source is fragile: transform and validate before the data touches the warehouse, so bad data never lands.

# Classic ETL: transform in Python, load clean data
import pandas as pd
from sqlalchemy import create_engine

def etl_pipeline():
    raw = pd.read_csv("s3://raw/events.csv")

    # Transform before load
    clean = (
        raw
        .drop_duplicates(subset=["event_id"])
        .assign(timestamp=lambda df: pd.to_datetime(df["timestamp"]))
        .query("status == 'completed'")
    )

    engine = create_engine("postgresql://warehouse")
    clean.to_sql("events_clean", engine, if_exists="replace", index=False)

The Hybrid: ELT with ETL Stages

Most production pipelines are hybrids. The pattern:

1. Extract + load raw (ELT): land everything in a raw schema.
2. Light staging transforms (ETL-style): clean, type, dedupe in the warehouse.
3. Business transforms (ELT): build marts with SQL.
4. Export transforms (ETL): for targets that cannot transform, export from the warehouse.

source ──► raw schema (ELT) ──► staging views (SQL) ──► marts (SQL) ──► export (ETL)

Cost Analysis

Aspect	ETL	ELT
Storage	Only clean data stored	Raw + clean data stored
Compute	Dedicated ETL servers	Warehouse compute (on demand)
Reprocessing	Re-run ETL from source	Re-run SQL on raw data
Flexibility	Low: raw data discarded	High: raw data always available
Latency	Batch, transform-bound	Batch, load-bound

ELT's hidden cost is warehouse compute: transforming terabytes in Snowflake costs credits. ETL's hidden cost is inflexibility: when business logic changes, you cannot re-derive history without the raw data.

Decision Framework

Question	ETL	ELT
Can the target run transformations?	No	Yes
Is raw data needed for future analysis?	No	Yes
Must PII be removed before leaving source?	Yes	No
Is the raw volume too large to load?	Yes	No
Do analysts need to explore raw data?	No	Yes
Is the warehouse modern (Snowflake, BigQuery)?	No	Yes

Implementation Checklist

• Check whether the target can transform in place
• Keep raw data unless compliance forbids it
• Use ELT for modern warehouses with SQL transformations
• Use ETL for legacy targets and compliance boundaries
• Version transformations in git (dbt or equivalent)
• Add data quality tests to the transformation layer
• Plan reprocessing: how do you re-derive history?

---

MatterAI builds frontier AI infrastructure for engineering teams — from inference-optimized models to autonomous coding agents and agentic code reviews.

Explore what we're building:

• Orbital IDE — Autonomous AI coding agent with background agents and deep codebase memory
• AI Code Reviews — Agentic pre-commit reviews across GitHub, GitLab, and Bitbucket
• Axon Models — Frontier-grade reasoning models at 70% lower inference cost

Get started free - https://app.matterai.so

• Read the docs
• View pricing

---

Follow us on X · LinkedIn · GitHub