Data Pipeline Architecture: ETL, ELT, and Stream Processing Compared

Editorial Team · · 3 min read

ETL Is Dead, Long Live ETL

Every few years, someone declares ETL dead. First it was going to be replaced by ELT. Then by streaming. Then by the "modern data stack." And yet, every company I've worked with still has ETL pipelines running somewhere — often the most critical ones.

The reality is that ETL, ELT, and stream processing solve different problems. Picking the right one depends on your data volume, freshness requirements, and team capabilities.

ETL: Extract, Transform, Load

Traditional ETL transforms data before loading it into the destination. The transformation happens in a processing layer — often Apache Spark, a Python script, or a dedicated ETL tool.

# Classic ETL pattern with Python
def etl_daily_sales():
    # Extract
    raw = extract_from_postgres("SELECT * FROM orders WHERE date = yesterday()")

    # Transform
    cleaned = remove_test_orders(raw)
    enriched = join_customer_data(cleaned)
    aggregated = compute_daily_metrics(enriched)

    # Load
    load_to_warehouse(aggregated, table="daily_sales_summary")

ETL works well when:

  • You need to clean or restructure data before it enters the warehouse
  • Transformation is computationally expensive and you don't want to burden your warehouse
  • Data governance requires that only cleaned, validated data enters the warehouse
  • You're dealing with complex data formats that need specialized parsing

The downside: transformations are coded outside the warehouse, which means your data team can't iterate on them using SQL. Changing a transformation requires a code deploy, not a query edit.

ELT: Extract, Load, Transform

ELT flips the order. Raw data gets loaded into the warehouse first, and transformations happen inside using SQL. This is the model that dbt popularized.

The key insight: modern cloud warehouses (BigQuery, Snowflake, Redshift) are powerful enough to handle the transformation step. You don't need a separate processing layer — just write SQL.

-- dbt model: daily_sales_summary.sql
WITH cleaned_orders AS (
    SELECT *
    FROM {{ ref('raw_orders') }}
    WHERE NOT is_test_order
    AND status != 'cancelled'
),
enriched AS (
    SELECT
        o.*,
        c.segment,
        c.lifetime_value
    FROM cleaned_orders o
    JOIN {{ ref('dim_customers') }} c ON o.customer_id = c.id
)
SELECT
    date_trunc('day', ordered_at) AS order_date,
    segment,
    count(*) AS order_count,
    sum(total_amount) AS revenue
FROM enriched
GROUP BY 1, 2

ELT advantages:

  • Data analysts can write and modify transformations (it's just SQL)
  • The raw data is preserved — you can always recompute if your transformation logic was wrong
  • Faster iteration cycle: change a query, run it, see results
  • The warehouse handles scaling automatically

ELT disadvantages: you're paying for warehouse compute to do the transformations. For massive datasets, this can get expensive. Also, some transformations are genuinely hard to express in SQL — ML feature engineering, complex text processing, geospatial calculations.

Stream Processing

When batch isn't fast enough, you need streaming. Stream processing handles data as it arrives, producing results in near real-time.

The main tools:

Apache Kafka + Kafka Streams/ksqlDB — Kafka as the message backbone, with either Java-based Kafka Streams or SQL-like ksqlDB for transformations. Good when you're already running Kafka for event-driven architecture.

Apache Flink — The most powerful stream processing engine. Handles complex event processing, windowed aggregations, and exactly-once semantics. Also does batch processing well. The downside: it's complex to operate.

Apache Spark Structured Streaming — If you're already using Spark for batch, Structured Streaming lets you reuse your batch code for streaming with micro-batches. Not true real-time (latency measured in seconds, not milliseconds), but good enough for many use cases.

Stream processing is the right choice when:

  • You need sub-minute latency (fraud detection, real-time recommendations, live dashboards)
  • Data volume is so high that batching isn't practical
  • You're reacting to events (a new order triggers inventory updates, notifications, analytics)

The Hybrid Approach

Most mature data platforms use all three. Here's a common pattern:

  • Streaming for operational data (fraud checks, real-time inventory, live dashboards)
  • ELT for analytics (daily/hourly transformations in the warehouse using dbt)
  • ETL for external data ingestion (third-party APIs, file imports, legacy system feeds)

The "modern data stack" — Fivetran/Airbyte for extraction, Snowflake/BigQuery for storage, dbt for transformation, and Looker/Metabase for visualization — is essentially an ELT pattern with managed tooling. It works great for analytics-focused teams. But it doesn't replace the need for streaming when real-time processing is required.

Practical Advice

Start with batch ELT unless you have a specific real-time requirement. It's simpler to build, cheaper to operate, and easier to debug. You can always add streaming for the use cases that genuinely need it.

Don't stream everything just because you can. I've seen teams build elaborate Kafka-based architectures for data that's consumed once a day in a dashboard. That's a batch job with extra steps and extra operational burden.