ETL & ELT

ETL (Extract, Transform, Load) and ELT (Extract, Load, Transform) are the two dominant paradigms for moving data from source systems into analytical stores. Choosing between them depends on your data volume, transformation complexity, and infrastructure.

ETL: Extract, Transform, Load

In the classic ETL pattern, data is extracted from sources, transformed in a staging area (often a dedicated transformation engine), and then loaded into the target database or warehouse.

When to use ETL

• The target system has limited compute capacity
• You need to mask or clean sensitive data before loading
• Data volume is moderate and transformations are complex
• You maintain strict data governance requirements

Example ETL Pipeline

# ETL with Python and Pandas import pandas as pd from sqlalchemy import create_engine # Extract engine = create_engine("postgresql://user:pass@host:5432/source") df = pd.read_sql("SELECT * FROM raw_events WHERE event_date = '2025-01-15'", engine) # Transform df = df.dropna(subset=["user_id", "event_name"]) df["event_date"] = pd.to_datetime(df["event_time"]).dt.date df["session_duration"] = df.groupby("session_id")["event_time"].transform( lambda x: x.max() - x.min() ) # Load warehouse = create_engine("snowflake://user:pass@account/warehouse/schema") df.to_sql("fct_events", warehouse, if_exists="replace", index=False)

ELT: Extract, Load, Transform

ELT flips the order: raw data is loaded directly into the target system (a cloud data warehouse or data lake), and transformations happen in-place using the warehouse's own compute.

When to use ELT

• Cloud warehouse with elastic compute (Snowflake, BigQuery)
• Data lake architectures (S3 + Athena, Databricks)
• Schema-on-read workflows and exploratory analytics
• High data volumes that would bottleneck a transformation server

Example ELT Pipeline with dbt

-- models/staging/stg_events.sql
-- Raw events are already loaded into the warehouse

WITH source AS (
    SELECT *
    FROM raw_events
    WHERE event_date = '{{ var("execution_date") }}'
)

SELECT
    event_id,
    user_id,
    session_id,
    event_name,
    event_time,
    -- clean fields
    CASE
        WHEN event_name IS NULL THEN 'unknown_event'
        ELSE event_name
    END AS clean_event_name,
    -- duration calculation via window
    TIMESTAMPDIFF(
        second,
        FIRST_VALUE(event_time) OVER (
            PARTITION BY session_id
            ORDER BY event_time
        ),
        event_time
    ) AS session_elapsed_seconds
FROM source

ETL vs ELT: Key Differences

Incremental Loading Strategies

Both ETL and ELT benefit from incremental processing to avoid reprocessing the full dataset on every run:

• Timestamp-based - Filter byupdated_at > last_run
• Full table scan with dedup - Load everything and use ROW_NUMBER() to keep the latest version
• CDC (Change Data Capture) - Stream database change events via Debezium, AWS DMS, or Fivetran
• Partition pruning - Load only new/modified partitions in systems like Iceberg or Delta Lake

Resource Links

• dbt Incremental Models
• Fivetran: What is ETL?
• Airbyte: ELT vs ETL