ETL vs ELT — History, Difference, When to Use Each

ECONOMIC REALITY OF DATA WAREHOUSING IN 2000:

  On-premises warehouse hardware (Teradata, IBM Netezza, Oracle Exadata):
    Storage cost: $5,000 – $50,000 per terabyte
    Typical warehouse: 5–20 TB = $25,000 – $1,000,000 just for storage
    Hardware refresh: every 3–5 years, another $millions

  CONSEQUENCE: warehouse storage was precious
    You could NOT afford to store raw, unprocessed data in the warehouse.
    Every byte had to earn its place. Only clean, modelled, business-ready
    data was allowed through the warehouse door.

  THEREFORE: transformation MUST happen before loading (ETL)
    Extract from source systems (nightly, during off-peak hours)
    Transform on a separate processing server (ETL server)
      → clean, type, deduplicate, join, validate
      → discard raw data after transformation
    Load ONLY the clean data into the warehouse
      → warehouse stores only modelled, compressed, valuable data

  ETL tools of the era:
    IBM DataStage, Informatica PowerCenter, Oracle Data Integrator,
    Microsoft SSIS (SQL Server Integration Services), Talend
    → GUI-based, proprietary, $100k+ enterprise licences
    → Each transformation step configured in drag-and-drop interfaces
    → Version control was difficult; collaboration was harder
    → "ETL developers" were a separate job title from software engineers

WHAT CHANGED IN THE 2010s:

  Amazon S3 launched 2006:
    Storage cost: $0.023 per GB per month ($23 per TB)
    Compare to on-premises warehouse hardware: $5,000–$50,000/TB
    Reduction: 99.5% cheaper
    → Suddenly affordable to store ALL raw data indefinitely

  Cloud data warehouses (Redshift 2012, BigQuery 2012, Snowflake 2014):
    Separated compute from storage
    Compute: pay-per-query or pay-per-warehouse-minute
    Storage: cheap (Snowflake ~$40/TB/month, BigQuery ~$20/TB/month)
    Massively parallel: 100s of nodes available on demand
    → Warehouse compute was no longer the bottleneck

  CONSEQUENCE: the ETL constraint evaporated
    You CAN afford to store raw data (S3 is nearly free)
    You CAN afford to transform inside the warehouse (cheap on-demand compute)
    You DO NOT need a separate ETL server (warehouse IS the compute engine)

  THEREFORE: transformation can happen AFTER loading (ELT)
    Extract from source and load raw to S3 or warehouse staging
    Transform inside the warehouse with SQL
    → Raw data preserved in S3 forever
    → No separate transformation server
    → SQL replaces GUI-based ETL tools
    → dbt (data build tool, launched 2016) codifies this with SQL + Git

  THE RESULT: ETL tools lost their purpose
    When the warehouse can run SQL on 100 nodes at $0.25/query,
    there is no reason to transform on a separate server first.
    The transformation logic belongs in SQL, in the warehouse, under version control.

ETL — Extract, Transform, Load

ETL DATA FLOW:

  SOURCE              ETL ENGINE (Python/Spark/Informatica)    DESTINATION
  ─────────────────────────────────────────────────────────────────────────
  PostgreSQL     →    [Extract]                                Snowflake
  orders table        SELECT * FROM orders                     silver.orders
                      WHERE updated_at > checkpoint

                      [Transform — all in pipeline code]
                      ├── Cast types (string → decimal)
                      ├── Deduplicate (ROW_NUMBER window)
                      ├── Normalise status (lowercase/trim)
                      ├── Join to customers (enrichment)
                      ├── Validate (amount > 0, status in set)
                      ├── Reject invalid rows to DLQ
                      └── Add ingested_at, pipeline_run_id

                      [Load]
                      INSERT INTO silver.orders
                      ON CONFLICT (order_id) DO UPDATE ...

  WHAT THE DESTINATION RECEIVES:
    Typed, validated, deduplicated, enriched rows only.
    Invalid rows never reach the destination.
    Raw source data is never stored in the destination.

  WHAT IS LOST:
    The original raw data is discarded after transformation.
    If a bug was introduced in the transformation:
      → Cannot reprocess raw data from the destination
      → Must re-extract from the source (if still available)
      → Or accept the incorrect data until a correction run

TYPICAL ETL TOOLS AND WHEN USED:
  Python + Pandas/PySpark:  Most common in modern pipelines
  Apache Spark:             Large-scale transformations
  Apache Flink:             Streaming transformations
  Informatica PowerCenter:  Legacy enterprise pipelines
  IBM DataStage:            Legacy enterprise, banking/finance
  Microsoft SSIS:           SQL Server ecosystem
  Talend:                   Open-source ETL alternative
  AWS Glue:                 Serverless Spark ETL on AWS

ELT — Extract, Load, Transform

ELT DATA FLOW:

  SOURCE              EL LAYER (thin Python loader)     DESTINATION (warehouse/lake)
  ──────────────────────────────────────────────────────────────────────────────────
  PostgreSQL     →    [Extract]                          Snowflake
  orders table        SELECT * FROM orders               ├── raw.orders      (staging)
                      WHERE updated_at > checkpoint      ├── silver.orders   (cleaned)
                                                         └── gold.daily_rev  (aggregated)
                      [Load — minimal transformation]
                      INSERT INTO raw.orders             ← raw data lands here
                      (just type coercion for load compatibility)

                      ↓  (separate step, runs after load)

  dbt TRANSFORMS INSIDE THE WAREHOUSE:
  ├── silver/orders.sql:
  │     WITH source AS (SELECT * FROM raw.orders),
  │     deduped AS (... ROW_NUMBER ...),
  │     cleaned AS (... LOWER(status), DECIMAL cast, validated ...)
  │     SELECT * FROM cleaned
  │
  ├── gold/daily_revenue.sql:
  │     WITH orders AS (SELECT * FROM silver.orders)
  │     SELECT DATE(created_at), store_id, SUM(amount) ...
  │     FROM orders GROUP BY 1, 2
  │
  └── schema.yml (tests):
        - name: silver_orders
          columns:
            - name: order_id
              tests: [not_null, unique]
            - name: amount
              tests: [{dbt_utils.accepted_range: {min_value: 0}}]

  WHAT IS PRESERVED:
    raw.orders contains the original source data — always.
    If a bug is found in silver.orders 6 months later:
      → dbt run --select silver.orders --full-refresh
      → Reprocess from raw.orders in the same warehouse
      → No re-extraction from source needed

  WHAT THE DESTINATION CONTAINS:
    raw schema:    exact source data, typed minimally for load
    silver schema: cleaned, validated, deduplicated
    gold schema:   aggregated, business-ready metrics

dbt (data build tool) is the standard transformation layer for ELT pipelines.
It takes SQL SELECT statements and manages running them in the right order,
testing the outputs, and generating documentation — all from within the warehouse.

PROJECT STRUCTURE:
  freshmart_dbt/
  ├── models/
  │   ├── staging/         # stg_ models: raw → typed (thin layer)
  │   │   ├── stg_orders.sql
  │   │   └── _sources.yml    # define source tables here
  │   ├── intermediate/    # int_ models: joins, complex logic
  │   │   └── int_orders_with_customer.sql
  │   └── marts/
  │       ├── core/        # dim_ and fct_ models
  │       │   └── fct_orders.sql
  │       └── finance/     # domain-specific Gold models
  │           └── daily_revenue.sql
  ├── tests/
  ├── macros/
  └── dbt_project.yml

A SIMPLE dbt MODEL (models/staging/stg_orders.sql):
  -- Materialised as a table in staging schema
  -- {{ config(materialized='table') }}

  WITH source AS (
      SELECT * FROM {{ source('raw', 'orders') }}
  ),
  renamed AS (
      SELECT
          order_id::BIGINT                  AS order_id,
          customer_id::BIGINT               AS customer_id,
          amount::DECIMAL(10,2)             AS order_amount,
          LOWER(TRIM(status))               AS status,
          created_at::TIMESTAMPTZ           AS created_at
      FROM source
      WHERE order_id IS NOT NULL
  )
  SELECT * FROM renamed

dbt COMMANDS:
  dbt run                    # run all models
  dbt run -s stg_orders      # run one model
  dbt run -s +fct_orders     # run fct_orders and all its ancestors
  dbt test                   # run all data quality tests
  dbt docs generate          # generate documentation
  dbt docs serve             # view documentation in browser

dbt TESTS (schema.yml):
  models:
    - name: stg_orders
      columns:
        - name: order_id
          tests: [not_null, unique]
        - name: order_amount
          tests: [not_null, {dbt_utils.accepted_range: {min_value: 0}}]
        - name: status
          tests:
            - accepted_values:
                values: [placed, confirmed, delivering, delivered, cancelled]

HOW dbt FITS INTO ELT:
  1. Python/Fivetran/Airbyte: Extract + Load raw data to warehouse
  2. dbt: Transform raw → staging → intermediate → Gold (inside warehouse)
  3. BI tools (Metabase, Superset): Query Gold tables
  No separate compute engine. No code outside the warehouse for analytics logic.

MODERN DATA PLATFORM ARCHITECTURE — BOTH PATTERNS IN USE:

  SOURCES
  ─────────────────────────────────────────────────────────────────────────
  PostgreSQL (orders, customers)
  Razorpay API (payments)
  ShipFast API (deliveries)
  Internal customer review data (contains PII)
  ML pipeline (model predictions)

  INGESTION LAYER (thin EL — Python scripts / Fivetran / Airbyte)
  ─────────────────────────────────────────────────────────────────────────
  PostgreSQL CDC → raw.orders (EL — no transformation)
  Razorpay API  → raw.payments (EL — minimal typing for load)
  ShipFast API  → raw.deliveries (EL — minimal typing for load)

  ETL EXCEPTIONS — Python pipelines that transform BEFORE loading:
  Customer reviews → [Python ETL: mask email, hash user_id, extract sentiment]
                   → raw.reviews (PII-free, sentiment_score added)
  ML predictions   → [Python ETL: parse model output, apply business rules]
                   → raw.risk_scores

  TRANSFORMATION LAYER (dbt — ELT inside Snowflake)
  ─────────────────────────────────────────────────────────────────────────
  raw.orders     → stg_orders (typed, deduplicated)
  raw.payments   → stg_payments (typed, validated)
  raw.deliveries → stg_deliveries (typed, validated)
  raw.reviews    → stg_reviews (already cleaned by ETL)

  stg_orders + stg_customers → int_orders_enriched (join)

  int_orders_enriched → fct_orders (Gold fact table)
  fct_orders          → daily_revenue (Gold aggregate)
  fct_orders          → customer_ltv (Gold aggregate)

  SERVING LAYER
  ─────────────────────────────────────────────────────────────────────────
  Metabase / Power BI → queries Gold tables in Snowflake
  FastAPI / GraphQL   → queries Silver tables for product features
  ML training         → reads raw.* tables in S3 directly (Spark)


DECISION RULE: ELT or ETL for each source?
  Ask: can the raw data touch the warehouse safely?
    Yes (no PII, no compliance issue, schema is loadable): ELT
    No (PII masking needed, complex Python transform, incompatible schema): ETL

FreshMart's original data platform was built in 2019. It uses a 500-line Informatica workflow that extracts from PostgreSQL, applies 40 transformation steps in a GUI-based ETL tool, and loads clean data into Redshift. The data team hates it: changes require opening the GUI, transformation logic is invisible to git, and when a bug is found, re-extraction from PostgreSQL takes 8 hours.

You are asked to migrate this to a modern ELT architecture. Here is the approach a senior data engineer uses.

Step 1 — Audit the existing ETL transformations. Export the Informatica workflow documentation and categorise every transformation:

TRANSFORMATION AUDIT (from Informatica workflow):

Step 1:  Extract orders from PostgreSQL WHERE updated_at > checkpoint
         → Category: EXTRACTION — keep in Python EL layer

Step 2:  Cast amount from VARCHAR to DECIMAL
         → Category: TYPE CAST — move to stg_orders.sql (dbt staging model)

Step 3:  Lowercase and trim status field
         → Category: NORMALISE — move to stg_orders.sql

Step 4:  Filter rows where status = 'test_order'
         → Category: BUSINESS FILTER — move to stg_orders.sql

Step 5:  Join to customer lookup table for city enrichment
         → Category: ENRICHMENT JOIN — move to int_orders_with_customer.sql

Step 6:  Compute order_tier (high/mid/low) based on amount thresholds
         → Category: BUSINESS RULE — move to fct_orders.sql

Step 7:  Hash customer_phone for privacy
         → Category: PII MASKING — KEEP in ETL (Python), must happen BEFORE load

Step 8:  Compute daily revenue aggregates
         → Category: AGGREGATION — move to gold/daily_revenue.sql

Step 9:  Reject rows with NULL order_id to error file
         → Category: VALIDATION — add dbt test not_null on order_id in staging
                                   add Python DLQ for load-time rejections

Summary:
  8 of 9 steps → ELT (dbt models)
  1 of 9 steps → Stays ETL (PII masking before raw data reaches warehouse)
  This is the typical pattern — most logic migrates to dbt, PII stays in Python

Step 2 — Build the EL layer. Write a thin Python loader that extracts from PostgreSQL with incremental watermark, applies the PII masking (the one step that must stay ETL), and loads to Snowflake's raw schema with no other transformations.

Step 3 — Build the dbt models. Create stg_orders.sql (type casting, normalisation, business filter), int_orders_with_customer.sql (enrichment join), fct_orders.sql (business rules), and daily_revenue.sql (aggregation). Add schema.yml tests at each layer.

Step 4 — Run both in parallel and compare outputs. For two weeks, both the old Informatica pipeline and the new ELT pipeline run. Daily row counts, SUM(revenue), and a sample of individual rows are compared. When the outputs match, the old pipeline is decommissioned.

The result after migration: transformation logic is 8 SQL files in git. Any engineer on the team can read, review, and change them. CI runs dbt tests on every PR. When a bug is found six months later, it is fixed in one SQL file and dbt run --full-refresh reprocesses from the preserved raw data in 40 minutes. The 8-hour re-extraction from PostgreSQL is gone forever.