The Pipeline: ETL vs. ELT

Moving Data from Source to Warehouse

Raw data lives in application databases, SaaS APIs, event streams, and log files. Getting it into the warehouse in a usable form requires a pipeline. Two architectural patterns dominate: ETL and ELT.

ETL (Extract, Transform, Load) — The Traditional Approach

ETL was the standard from the 1980s through roughly 2015. The sequence:

1. Extract: Pull raw data from the source system (e.g., Postgres, Salesforce API).
2. Transform: Clean, reshape, and aggregate the data on a separate processing server — a dedicated ETL tool like Informatica, Talend, or a custom Python script. This is where you fix nulls, standardize date formats, join datasets, and compute derived fields.
3. Load: Write the finished, clean, analysis-ready data into the warehouse.

Why this made sense: Early data warehouses (Teradata, Oracle) were expensive, proprietary hardware. CPU time on the warehouse was precious. You did not want to waste it on messy data cleaning. You cleaned first, then loaded only what was needed.

Drawbacks:
- The transformation server is a bottleneck and a single point of failure.
- Transformations are written in proprietary ETL tool languages, not standard SQL.
- Raw data is discarded — if your transformation logic was wrong, you cannot re-derive the correct output without re-extracting from the source.
- Schema changes in the source break the transformation layer.

ELT (Extract, Load, Transform) — The Modern Cloud Approach

ELT inverts the last two steps. The sequence:

1. Extract: Pull raw data from the source.
2. Load: Write the raw, messy data directly into the warehouse — typically into a dedicated raw or _staging schema. Store everything, exactly as it arrived.
3. Transform: Use the warehouse's own compute engine to run SQL transformations, producing clean, modeled tables in a separate schema.

Why this works now: Cloud warehouses (Snowflake, BigQuery, Redshift) have effectively unlimited, elastically scalable compute. Running a complex SQL transformation across 100 billion rows is a routine operation. There is no reason to maintain a separate transformation server when the warehouse itself is faster and more capable.

The dominant ELT tool is dbt (data build tool). dbt lets data engineers write transformations as .sql files with Jinja templating, manages dependencies between models, runs tests on data quality, and generates documentation — all using standard SQL that runs inside the warehouse.

# dbt project structure
models/
  staging/
    stg_orders.sql       -- Clean raw orders
    stg_customers.sql    -- Clean raw customers
  marts/
    fact_sales.sql       -- Join and aggregate into fact table
    dim_customers.sql    -- Build customer dimension

Advantages of ELT:
- Raw data is always preserved. If transformation logic was wrong, re-run the transformation — no re-extraction needed.
- Transformations are plain SQL — every data engineer knows it.
- The warehouse scales automatically; no separate infrastructure to manage.
- Schema-on-read: you can add new transformation logic against historical raw data at any time.

Senior depth — the raw layer as an audit trail: In ELT, the raw schema is not just a staging area — it is an immutable audit log of every record as it arrived from the source. This is invaluable for debugging data quality issues ("did the source send us bad data, or did our transformation introduce the error?"), for regulatory compliance (GDPR, SOX), and for replaying history when business logic changes. Treat the raw layer as append-only and never modify it after load.