ETL

Last reviewed: 2026-09-17

Purpose: Extract, Transform, Load pipeline design — batch and streaming patterns, schema handling, data quality checks, orchestration, and the tooling choices behind each approach.

Overview

An ETL pipeline moves data from systems that produce it to systems that analyse it, reshaping and validating it on the way. The design questions are always the same: how fresh must the data be, how much of the transformation belongs in the source versus the warehouse, and what happens when a run fails halfway through.

The Three Stages

Stage	Typical work
Extract	Read incrementally from databases, APIs, event streams or files; capture watermarks (`updated_at`, offsets, sequence IDs) so the next run starts where the last finished
Transform	Parse nested payloads, cast types, normalise timestamps to UTC, deduplicate, derive fields, join reference data, mask PII
Load	Write to the target — a warehouse table, an OLAP store, an object-store lake, or a message queue — in a way that can be re-run safely

Design the watermark and the idempotency key before writing any transformation code; retrofitting both after the pipeline is in production is painful.

Batch vs. Streaming

Aspect	Batch	Streaming
Latency	Minutes to hours	Sub-second to seconds
Processing unit	Time window (hourly, daily)	One record or micro-batch
Typical tools	Airflow, dbt, Spark, SQL scripts	Kafka, Flink, Spark Structured Streaming, ksqlDB
Reprocessing	Trivial — rerun the window	Requires replaying the topic or a backfill path
Complexity	Lower	Higher: ordering, late data, exactly-once, state
Good for	Finance reporting, monthly aggregation, ML feature tables	Monitoring, alerting, real-time dashboards, event-driven apps

Most organisations end up with both: a streaming path for freshness-sensitive consumers, and a nightly batch path that rebuilds the same tables from the raw log as the source of truth. Keeping the raw events immutable makes that reconciliation possible.

ELT, CDC and Replication

ETL transforms before loading — transformation happens outside the target system, which keeps the warehouse clean but duplicates compute and schema logic.
ELT loads raw data first and transforms inside the warehouse with SQL (dbt and friends). Because warehouse compute is elastic, ELT has become the default for analytical workloads.
CDC (change data capture) reads a database's transaction log (Debezium and similar) and emits insert/update/delete events, giving near-real-time replication without polling queries.
Reverse ETL pushes modelled data back into operational tools (CRM, support desk) on a schedule.

Schema Handling

Prefer schema-on-write at the warehouse boundary (typed columns, enforced on load) with schema-on-read in the landing/staging zone (raw JSON or Parquet as received).
Register and version schemas for streaming data (Avro/Protobuf with a registry) so a producer cannot silently break consumers.
Evolve by adding optional columns; renaming or retyping a column is a breaking change that needs a migration plan.
Store the raw payload alongside the parsed columns so a parser bug can be fixed by replaying, not by re-querying the source.

# Typed parsing of a nested event, with defaults that keep bad records out of the warehouse
def parse_event(raw: dict) -> dict:
    return {
        "event_id": raw["meta"]["id"],
        "user": raw.get("user", "unknown"),
        "is_bot": bool(raw.get("bot", False)),
        "event_ts": datetime.fromtimestamp(raw["timestamp"], tz=timezone.utc),
        "payload": json.dumps(raw),          # keep the original for replay
    }

Data Quality & Idempotency

Validate row counts and key uniqueness between source and target after each load; a pipeline that silently drops 3% of rows is worse than one that fails loudly.
Define expectations as assertions — freshness (max(event_ts) is recent enough), completeness (non-null ratio), referential integrity — and fail the run when they break.
Make loads idempotent: a merge on a natural key, or delete-and-reinsert over a partition, so a retry does not duplicate rows.
Quarantine rejects with the reason attached rather than discarding them; they are the fastest route to finding a producer-side bug.

-- Idempotent load: re-running the same batch changes nothing
MERGE INTO analytics.usage_events AS t
USING staging.usage_events AS s
   ON t.event_id = s.event_id
WHEN MATCHED THEN UPDATE SET
     feature_name = s.feature_name,
     event_time   = s.event_time
WHEN NOT MATCHED THEN INSERT (event_id, feature_name, event_time)
     VALUES (s.event_id, s.feature_name, s.event_time);

Orchestration

Concern	Approach
Scheduling	Cron for simple jobs; Airflow/Dagster/Prefect once there are dependencies and backfills
Dependencies	Express as a DAG so a failed upstream task blocks downstream work instead of producing stale tables
Retries	Automatic retries with exponential backoff for transient failures; never retry a failed assertion blindly
Backfills	Every pipeline needs a documented way to reprocess a historical window
Observability	Emit run duration, row counts and assertion results as metrics; alert on staleness, not just failure
Secrets	Inject credentials from a secret manager — never bake them into DAG definitions

Worked Example: Streaming Pipeline

A common shape for turning a live public or internal event source into a queryable analytics store:

Extract — a client consumes a server-sent-event or WebSocket stream, or polls an API, and emits one record per event.
Transform — the consumer normalises timestamps to epoch milliseconds, flattens nested JSON paths, drops malformed frames, and attaches a key for partitioning.
Load — records are produced to a Kafka topic, which decouples ingestion from processing and allows replays.
Serve — a stream-processing engine (or a real-time OLAP store such as Apache Pinot) consumes the topic, applies transforms declared in the table config, and makes the data queryable within seconds.
Visualise — a dashboard queries the serving layer over SQL and renders metrics with auto-refresh.

The batch counterpart uses the same shape with file or API sources, explicit windows, and the same idempotent merge into the target table.

Tooling Landscape

Tool	Role
Apache Airflow	Python-defined DAG orchestration; the de facto scheduler
dbt	SQL transformations with tests, docs and lineage inside the warehouse
Apache Spark	Distributed batch and micro-batch transformation at scale
Apache Flink	Stateful stream processing with event-time semantics
Kafka / ksqlDB	Event transport and stream processing close to the source
Debezium	Log-based CDC from relational databases
Airbyte / Meltano	Managed and self-hosted connector-driven ELT ingestion
Great Expectations / Soda	Data quality assertions as pipeline gates

Topics Covered

Extract, transform and load responsibilities, and choosing watermarks
Batch versus streaming trade-offs, including reprocessing
ETL, ELT, CDC and reverse ETL architectural options
Schema evolution, schema-on-write versus schema-on-read
Idempotent loads, merge patterns and data quality assertions
Orchestration: DAGs, retries, backfills and pipeline observability

ETL

Table of Contents