ETL Guide

TL;DR

• ETL extracts data from sources, transforms it, and loads it into a destination - typically a data warehouse or data lake.

• ELT flips the order - load raw data first, transform inside the destination using its compute engine (common with Snowflake, BigQuery, Redshift).

• Batch processing dominates traditional ETL - scheduled jobs move data in chunks (hourly, nightly) rather than event-by-event.

• Idempotency and observability separate prototypes from production - pipelines must survive reruns and expose clear failure signals.

• Choose ETL when transformation is complex and upstream; choose ELT when the warehouse is powerful and schemas evolve fast.

Why This Matters

Every analytics dashboard, ML model, and AI application depends on data arriving reliably at the right place in the right shape. Sales data trapped in a CRM, logs sitting in S3 as gzipped JSON, and transaction records in PostgreSQL do not help analysts until something moves and reshapes them.

ETL is that something. It is the plumbing of data engineering - unglamorous, essential, and the root cause of most "the numbers don't match" incidents when done poorly.

Whether you use Airflow, dbt, Fivetran, or custom Spark jobs, the underlying pattern is the same: extract from source, transform to target schema, load with guarantees. Understanding ETL deeply helps you debug pipeline failures, choose between batch and streaming, and design systems that feed data lakes and warehouses without silent data corruption.

The Problem ETL Solves

Operational systems are optimized for transactions, not analytics. The CRM schema normalizes for write throughput. Application logs are append-only blobs. Legacy mainframes export fixed-width files.

Analytics needs denormalized, historical, joined data in a query-optimized store. ETL bridges this gap:

Without ETL (or ELT), every analyst writes one-off scripts. Definitions diverge. Revenue means something different in finance vs. product. Trust erodes.

What Is ETL?

Extract - Read data from source systems: databases (JDBC, CDC), APIs, files (S3, SFTP), SaaS connectors (Salesforce, Stripe).

Transform - Apply business logic: joins, aggregations, type conversions, deduplication, slowly changing dimensions (SCD), PII masking, unit normalization.

Load - Write results to destination: warehouse tables, lake partitions, graph stores, vector indexes.

ETL vs ELT

ELT became popular as cloud warehouses (Snowflake, BigQuery, Redshift) gained enough compute to run heavy transforms on loaded data. The pattern: replicate raw → transform with dbt → serve curated tables.

Tip

Hybrid pipelines are common: light cleansing in extract (ETL), heavy modeling in warehouse SQL (ELT). Call it what you want; the stages matter more than the acronym.

How Batch ETL Works

Most ETL runs in batch - process a bounded dataset on a schedule.

Batch Pipeline Stages

1. Watermark / cursor - Track last successful extract (updated_at > '2026-07-05').

2. Extract - Pull incremental or full snapshot from source.

3. Stage - Land raw data in staging area (S3, staging schema) unchanged.

4. Transform - Apply rules; write to intermediate tables.

5. Load - Merge into production tables (upsert, partition swap).

6. Validate - Row counts, null checks, referential integrity.

7. Commit watermark - Only after successful load.

Incremental ETL tracks a watermark, extracts only changed rows, stages and transforms them, validates results, and commits the watermark only after a successful load - preventing data loss on failure.

Source: dbt Labs

Incremental vs Full Load

Slowly Changing Dimensions (SCD)

Dimension attributes change - a customer moves cities, a product renames. SCD strategies preserve history:

Architecture

A production ETL platform has distinct layers:

Decouple orchestration from transformation. dbt models should run independently of how raw data arrived.

Real Production Example

A SaaS company syncs product usage events from Kafka (archived to S3), billing from Stripe API, and accounts from PostgreSQL into Snowflake for analytics.

# Simplified incremental load with idempotent merge
from datetime import datetime

def run_daily_usage_etl(watermark_store, snowflake_conn, s3_client):
    last_run = watermark_store.get("usage_events")  # e.g. 2026-07-05T00:00:00Z
    partition_prefix = f"s3://events/usage/dt={last_run.date()}/"

    # Extract - read new partitions since watermark
    raw_files = s3_client.list_new_files(partition_prefix, since=last_run)

    # Stage - COPY INTO staging table (Snowflake)
    snowflake_conn.execute(f"""
        COPY INTO staging.usage_events_raw
        FROM '{partition_prefix}'
        FILE_FORMAT = (TYPE = PARQUET)
    """)

# Transform + Load - idempotent merge on event_id
    snowflake_conn.execute("""
        MERGE INTO analytics.usage_events AS target
        USING (
            SELECT
                event_id,
                account_id,
                event_type,
                TRY_CAST(properties:duration AS INT) AS duration_sec,
                event_timestamp
            FROM staging.usage_events_raw
            WHERE event_timestamp > %(last_run)s
              AND event_id IS NOT NULL
        ) AS source
        ON target.event_id = source.event_id
        WHEN MATCHED THEN UPDATE SET
            duration_sec = source.duration_sec
        WHEN NOT MATCHED THEN INSERT (
            event_id, account_id, event_type, duration_sec, event_timestamp
        ) VALUES (
            source.event_id, source.account_id, source.event_type,
            source.duration_sec, source.event_timestamp
        )
    """, {"last_run": last_run})

    # Validate
    row_count = snowflake_conn.scalar(
        "SELECT COUNT(*) FROM staging.usage_events_raw"
    )
    assert row_count > 0, "Empty batch - possible upstream failure"

    watermark_store.set("usage_events", datetime.utcnow())

Key properties: incremental extract, staging before production, MERGE for idempotency, validation before watermark commit.

Design Decisions

⚠ Common Mistakes

1. Non-idempotent loads. Rerunning a failed job duplicates rows. Use MERGE, partition overwrite, or deterministic keys.

2. Committing watermark before validation. Data loss or corruption goes unnoticed until dashboards break.

3. Transforming in application databases. Heavy ETL on production OLTP degrades user-facing latency. Extract to staging.

4. No schema evolution strategy. Source adds a column; pipeline crashes. Use schema registry or permissive parsing with defaults.

5. Silent null propagation. TRY_CAST failures become NULL; metrics drift. Monitor null rates per column.

6. Monolithic pipeline scripts. One 2,000-line job nobody can debug. Modularize by source and entity.

7. Ignoring late-arriving data. Events arrive out of order. Watermark logic must handle backfill windows.

Where It Breaks Down

Real-time requirements. Batch ETL with nightly runs cannot serve sub-minute dashboards. Streaming (Kafka, Flink) or CDC pipelines replace or supplement batch.

Source API rate limits. Full extracts from SaaS APIs hit limits. Incremental sync and respect for Retry-After headers are mandatory.

Schema drift across environments. Dev staging has different columns than prod. CI should test transforms against production-like schemas.

Complex cross-source joins at extract time. Joining 12 sources in Spark before load creates brittle mega-jobs. Often better to land raw separately and join in warehouse SQL.

Production Checklist

Important

Never advance the extraction watermark until load and validation succeed. This single rule prevents most silent data loss incidents.

Ecosystem

• Orchestration: Apache Airflow, Dagster, Prefect, Mage.

• Ingestion: Fivetran, Airbyte, Stitch, custom Python.

• Transformation: dbt, Spark, SQL stored procedures.

• Storage targets: Snowflake, BigQuery, Redshift, Databricks, S3/data lakes.

• Quality: Great Expectations, dbt tests, Monte Carlo, Elementary.

• CDC: Debezium, AWS DMS, Fivetran log-based replication.

Related Technologies

• Data Lakes: Raw storage layer many ELT pipelines load into first.

• Data Warehouses: Curated destination for transformed analytics data.

• Lakehouse: Architecture combining lake storage with warehouse semantics.

Learning Path

Prerequisites: None - foundational data engineering topic.

Next topics: Data Lakes · Data Warehouses · Lakehouse

Estimated time: 45 min · Difficulty: Intermediate

FAQs

What is the difference between ETL and ELT?

ETL transforms data before loading into the destination. ELT loads raw data first and transforms inside the destination (usually with SQL). ELT is common with cloud warehouses.

When should I use batch vs streaming?

Batch for daily/hourly reporting, backfills, and cost efficiency. Streaming when dashboards or applications need sub-minute freshness and the business justifies operational complexity.

What makes an ETL pipeline idempotent?

Rerunning the same job with the same input produces the same output without duplicates. Achieve this with MERGE statements, deterministic primary keys, or idempotent partition overwrites.

How do I handle schema changes in source systems?

Use schema-on-read in staging (permissive parsing), schema registries for strict contracts, and dbt tests to catch breaking changes in CI before production deploys.

What is CDC and when do I need it?

Change Data Capture reads database transaction logs to replicate inserts, updates, and deletes. Use it when incremental timestamp columns are unreliable or you need near-real-time sync.

ETL tool vs custom code?

Managed ingestion (Fivetran, Airbyte) for standard connectors saves months. Custom code for proprietary sources, complex transforms, or strict cost control at scale.

How do I monitor ETL pipeline health?

Track: last successful run time (freshness), row counts vs. baseline, duration trends, failure rate, and downstream dbt test results. Alert on freshness SLA breaches.

What is a staging area?

Intermediate storage for raw or partially transformed data before production load. Enables validation, replay, and separation of extract from transform failures.

References

• Hugging Face Transformers Documentation
• LangChain Documentation
• OpenAI API Documentation

Further Reading

• Anthropic Documentation
• Google AI for Developers
• LlamaIndex Documentation
• Pinecone Learning Center

Summary

• ETL (and ELT) is the foundational pattern for moving data from operational systems to analytics stores. - Batch processing with watermarks, staging, and validation is the default production pattern. - Idempotency and watermark discipline prevent the most common data loss bugs. - Choose ELT when your warehouse is the transformation engine; ETL when transforms belong upstream.

• Modular pipelines with observability beat monolithic scripts every time. - ETL connects directly to data lakes and warehouses - design both sides of the pipeline together.