ETL vs ELT: everything engineers need to know in practice

ETL vs ELT: everything engineers need to know in practice

The choice between ETL and ELT shapes your entire data pipeline architecture, affecting latency, cost, and operational complexity. We've seen teams make this decision based on trend rather than their actual workload, leading to months of refactoring. This guide cuts through the noise and gives you the framework to pick the right pattern for your stack.

Understanding the core difference

ETL stands for Extract, Transform, Load. ELT stands for Extract, Load, Transform. The letter order matters because it reflects where the heavy computational work happens.

In ETL, you pull data from a source, process it through a dedicated engine (Spark, Talend, custom Python), clean it, aggregate it, apply business logic, and only then write the polished result to your warehouse or data lake. The source system stays isolated from raw data landing zones.

In ELT, you dump raw data into your warehouse or lake as quickly as possible, then use SQL or SQL-like tools (dbt, Spark SQL, BigQuery) to transform it in place. The warehouse becomes both your landing zone and transformation engine.

Neither is universally better. The right choice depends on your data volume, latency requirements, team skills, and infrastructure budget.

ETL — transform in a processing engine, then load

ETL made sense before cloud warehouses were fast and cheap. You'd extract from production databases at night, run a Spark job that cleaned, deduplicated, and joined data across sources, then write clean tables to your data warehouse. The warehouse was expensive per query, so you minimized its load.

ETL still makes sense when:

• You need complex transformations that SQL handles poorly (machine learning feature engineering, custom aggregations, graph traversals).
• Your source systems are messy and you want filtering and deduplication before warehouse ingestion.
• You have strict data governance rules requiring transformation audit trails before storage.
• Your warehouse is still on-premises or cost-per-compute is high.

A typical ETL pipeline using Spark might look like this:

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, when, explode, row_number
from pyspark.sql.window import Window

spark = SparkSession.builder.appName("OrderETL").getOrCreate()

# Extract from source
orders_raw = spark.read.jdbc(
    url="jdbc:postgresql://prod-db:5432/ecommerce",
    table="orders",
    properties={"user": "etl_user", "password": "***"}
)

customers_raw = spark.read.csv("s3://source-bucket/customers.csv", header=True)

# Transform
orders_cleaned = orders_raw.filter(col("created_at") >= "2024-01-01") \
    .dropDuplicates(["order_id"]) \
    .withColumn("total_usd", col("total_cents") / 100.0)

# Enrich with customer data
orders_enriched = orders_cleaned.join(
    customers_raw,
    on="customer_id",
    how="left"
)

# Apply business logic
orders_transformed = orders_enriched.withColumn(
    "customer_tier",
    when(col("lifetime_value") > 10000, "gold")
        .when(col("lifetime_value") > 1000, "silver")
        .otherwise("bronze")
).select(
    "order_id",
    "customer_id",
    "customer_tier",
    "total_usd",
    "created_at"
)

# Load into warehouse
orders_transformed.write \
    .mode("overwrite") \
    .option("header", "true") \
    .parquet("s3://warehouse-bucket/gold/orders/")

# Or to Redshift/Snowflake
orders_transformed.write \
    .format("redshift") \
    .option("url", "jdbc:redshift://warehouse.region.redshift.amazonaws.com:5439/analytics") \
    .option("dbtable", "public.orders") \
    .mode("overwrite") \
    .save()

This pattern gives you control. Every transformation is logged, versioned, and testable. You catch data quality issues before they pollute your warehouse. But it also means maintaining a separate processing cluster, orchestrating jobs, and handling failure recovery yourself.

ELT — load raw data, transform in the warehouse

Cloud warehouses changed the game. Snowflake, BigQuery, and Redshift are now fast enough and cheap enough that landing raw data and transforming it in SQL makes economic sense. You pay for compute when you query, not when you store.

ELT makes sense when:

• You use a modern cloud warehouse that scales compute independently of storage.
• Your transformations are SQL-expressible (joins, aggregations, case logic).
• You want team velocity: SQL developers outnumber Spark engineers.
• You value audit trails of raw data (regulatory compliance, debugging).
• Latency to "useful data" is acceptable in hours, not minutes.

A typical ELT pipeline might use a tool like Fivetran or custom Airflow DAG to land CSVs or stream from Kafka, then rely on dbt for the transformation layer:

# Airflow DAG for ELT
from airflow import DAG
from airflow.operators.bash import BashOperator
from airflow.utils.dates import days_ago

default_args = {
    "owner": "data-eng",
    "start_date": days_ago(1),
    "retries": 1,
}

with DAG("elt_pipeline", default_args=default_args, schedule_interval="0 2 * * *") as dag:
    # Load raw CSV to Snowflake raw schema
    load_raw = BashOperator(
        task_id="load_customers_raw",
        bash_command="aws s3 cp s3://source-bucket/customers.csv /tmp/ && snowsql -c prod -f load.sql"
    )
    
    # Run dbt to transform
    run_dbt = BashOperator(
        task_id="run_dbt_transformations",
        bash_command="cd /dbt && dbt run --profiles-dir . --target prod"
    )
    
    load_raw >> run_dbt

The dbt models then handle your transformations in SQL:

-- models/staging/stg_orders.sql
select
    order_id,
    customer_id,
    round(total_cents / 100.0, 2) as total_usd,
    created_at,
    row_number() over (partition by order_id order by updated_at desc) as rn
from raw.orders
where created_at >= '2024-01-01'
qualify rn = 1

-- models/mart/fct_orders.sql
with orders as (
    select * from {{ ref('stg_orders') }}
),
customers as (
    select * from {{ ref('stg_customers') }}
)
select
    o.order_id,
    o.customer_id,
    c.lifetime_value,
    case
        when c.lifetime_value > 10000 then 'gold'
        when c.lifetime_value > 1000 then 'silver'
        else 'bronze'
    end as customer_tier,
    o.total_usd,
    o.created_at
from orders o
left join customers c on o.customer_id = c.customer_id

ELT trades operational complexity for flexibility. Your raw layer is immutable, your transformation logic is version-controlled SQL, and your team can iterate quickly. The downside: you're paying warehouse compute for transformation work that might be cheaper on Spark, and you're storing raw data that might contain sensitive information you'd rather filter upstream.

Modern stacks — ELT with dbt in cloud warehouses is common

The industry has shifted toward ELT in the last three years, especially for SaaS and e-commerce teams. Fivetran or custom loaders land raw JSON or CSVs into Snowflake, BigQuery, or Redshift. dbt models orchestrate the transformation layer. Looker or Tableau query the mart layer. It's the de facto standard now.

Why? Cloud warehouses kill the main advantage of ETL (cost control), and dbt solved the operational complexity that plagued SQL-based transformations. Plus, keeping raw data means your analysts can debug and rebuild models without re-extracting from source systems.

A real example: a ride-hailing platform receives 50 GB of trip events daily from Kafka. Five years ago, they'd run a Spark job to parse, deduplicate, and aggregate. Today, they stream raw events to Snowflake (one schema, one table), then use dbt to build staging models (deduplicate, parse JSON), intermediate models (join with driver profiles), and marts (daily driver earnings, trip metrics). Total transformation time: 2 hours. Total cost: ~$400/day in Snowflake compute. They replaced a 15-node Spark cluster (5x that cost) and eliminated Spark job maintenance entirely.

The ELT pattern also maps neatly to modern data governance. Raw zone (untouched data), staging zone (clean and conformed), marts zone (business logic). Everyone on the team understands the lineage because it's SQL. dbt's dependency graph makes debugging trivial.

Spark fits both — heavy transforms and lakehouse ELT

Spark is flexible enough to power either pattern, and increasingly, teams use both simultaneously.

For ETL, Spark excels at:

• Complex feature engineering (ML pipelines, window functions over weeks of data).
• Working with unstructured data (images, text, nested JSON).
• Applying custom business logic that SQL can't express cleanly.
• Processing at massive scale before warehouse ingestion.

For ELT in a lakehouse architecture, Spark serves as the query engine alongside SQL:

# Spark as part of lakehouse ELT
# Raw data lands in Delta Lake via Fivetran
# Spark SQL transforms it

from delta.tables import DeltaTable

spark.sql("""
    create or replace table analytics.stg_orders as
    select
        order_id,
        customer_id,
        total_usd,
        created_at,
        row_number() over (partition by order_id order by _fivetran_synced desc) as rn
    from analytics.raw_orders
    where _fivetran_deleted = false
    qualify rn = 1
""")

# Or use Spark DataFrame API for complex transforms
raw_orders = spark.read.format("delta").load("/mnt/lakehouse/raw_orders")
clean_orders = raw_orders.filter(col("_fivetran_deleted") == False) \
    .dropDuplicates(["order_id"])

clean_orders.write.format("delta").mode("overwrite").saveAsTable("analytics.stg_orders")

Lakehouses (Delta Lake, Iceberg, Hudi) blur the line between "processing engine" and "warehouse." You get raw data storage like a lake, ACID transactions and schema enforcement like a warehouse, and you query it with both Spark and SQL. This means you can use Spark for heavy lifting in ELT without building a separate ETL pipeline.

A trading firm we worked with uses this hybrid approach: raw market data streams into Delta Lake via Kafka (Spark Structured Streaming). dbt models run SQL transforms for standard metrics (daily volumes, price movements). When quants need custom ML features (volatility forecasts, liquidity models), they write Spark jobs that read the Delta tables, apply models, and write results back. Single source of truth, single query engine, flexible compute.

Comparing the two patterns

Common mistakes

Choosing ELT to avoid Spark learning curve. ELT is not "simpler." It just moves complexity to SQL and warehouse administration. If your team doesn't know SQL well, you'll hit the same walls faster. Pick ELT because your workload and economics fit, not because you heard it's trendy.

Keeping raw data forever in ELT without a retention policy. Storage costs add up. Cloud warehouses charge per GB stored. Define what "raw" means (how many versions? how long?), then enforce it. Use time-travel features (Snowflake, Delta) instead of keeping copies.

Running ETL and ELT in parallel to hedge your bets. We've seen teams build both to avoid commitment. Now they're debugging two pipelines, paying for two clusters, and their data is inconsistent. Pick one, optimize it, then migrate if it doesn't fit.

Ignoring data quality in ELT because raw data "should be clean." It won't be. Build dbt tests and assertions on raw data. Treat your staging models as quarantine zones where you catch garbage before it reaches marts.

Assuming Spark means ETL only. Spark works beautifully in ELT architectures. Don't rule it out just because you're using a warehouse. Use what fits the specific transformation.

Key takeaways

• ETL transforms before loading; ELT transforms after. Choose based on workload, economics, and team skills, not trend.
• Cloud warehouses made ELT economical. dbt made it operationally sane. Most new projects should start with ELT.
• ETL still wins for complex feature engineering, ML pipelines, and unstructured data processing.
• Lakehouses blur the line. Spark + Delta/Iceberg lets you get both patterns' benefits in one stack.
• Raw data is an asset in ELT (enables debugging, compliance). In ETL, decide upfront: keep it or drop it after transformation.
• Build data quality checks and testing into whichever pattern you choose. Don't assume the source or the transformation is correct.
• Document your choice and the reasoning. When new engineers ask why you're not using the other pattern, they should have an answer.