Data Pipelines: ETL/ELT, Orchestration & Idempotency

Moving and transforming data reliably. Learn ETL vs ELT, ingestion patterns (full loads, incremental loads with watermarks, change data capture), idempotency and exactly-once effects, backfills, DAGs and orchestration (Airflow, Dagster, Prefect), scheduling and dependencies, retries and alerting, dbt for SQL transformations with tests, the medallion architecture (bronze/silver/gold), data contracts and CI/CD for pipelines — with Python ordering a DAG topologically and running an idempotent incremental load.

Pipelines run every hour or day for years. They must handle late data, failures, reruns and schema changes without producing duplicates or gaps — reliability matters more than cleverness.

Orchestrators schedule tasks in dependency order (a directed acyclic graph), retry failures and alert humans, while tools like dbt manage SQL transformations as tested, version-controlled code.

An analogy

An orchestrated pipeline is like a kitchen during dinner service: some tasks must happen before others (chop before cook), several run in parallel, if a dish fails it's redone without serving it twice, and the head chef (orchestrator) tracks every order.

ETL vs ELT

ETL transforms data before loading into the target (traditional, when warehouses were expensive). ELT loads raw data first into cheap cloud storage/warehouses, then transforms with SQL inside the warehouse — the modern default, preserving raw data for reprocessing. Reverse ETL pushes modelled data back into operational tools (CRM, marketing).

Ingestion patterns

Getting data in:

Orchestration and dbt

Orchestrators (Airflow, Dagster, Prefect) define DAGs of tasks with schedules, dependencies, retries with backoff, timeouts, SLAs, alerting and backfills (re-running historical dates). dbt turns SQL SELECT statements into managed models with dependencies (ref()), incremental materialisations, snapshots (SCD2), documentation and tests (unique, not_null, relationships, accepted_values).

Reliability engineering for pipelines

Design for idempotency (MERGE, partition overwrite, deterministic outputs), handle late-arriving data (reprocess recent partitions), and make backfills cheap. Medallion architecture: bronze (raw, append-only), silver (cleaned, conformed), gold (business aggregates). Data contracts agree schemas and SLAs with producers; CI/CD runs tests on pull requests with sample data; blue-green deployments of tables avoid serving half-built data. Observability tracks run times, row counts, freshness and lineage.

Formulas

Worked example: Critical path

Given: extract 10 min, clean 15, join 20, aggregate 5, publish 2 (customers extract 8 in parallel)

Find: Pipeline duration.

Answer: 52 min

Worked example: Incremental savings

Given: Table 500 M rows, 1 M change daily

Find: Rows processed incremental vs full.

Answer: 500× less work

Worked example: Backfill

Given: Bug fixed; recompute last 90 daily partitions at 3 min each, 6 in parallel

Find: Time.

Answer: 45 min

DAG ordering and idempotent incremental load

from graphlib import TopologicalSorter
# Orchestration: DAG ordering, and an idempotent incremental load with a watermark
dag = {"extract_orders": set(), "extract_customers": set(),
       "clean_orders": {"extract_orders"}, "join": {"clean_orders", "extract_customers"},
       "aggregate_daily": {"join"}, "publish_dashboard": {"aggregate_daily"}}
print("run order:", list(TopologicalSorter(dag).static_order()))
source = [(1, "2026-03-01T10:00"), (2, "2026-03-01T11:30"), (3, "2026-03-02T09:15"), (4, "2026-03-02T12:00")]
target = {}
watermark = "2026-03-01T11:00"            # last successfully loaded timestamp
def load(since):
    new = [(i, ts) for i, ts in source if ts > since]
    for i, ts in new:
        target[i] = ts                    # upsert by key -> rerunning is safe (idempotent)
    return len(new), max((ts for _, ts in new), default=since)
n, watermark = load(watermark)
print(f"loaded {n} new rows, new watermark {watermark}")
n2, _ = load("2026-03-01T11:00")          # accidental re-run of the same window
print(f"re-run loaded {n2} rows again but target still has {len(target)} rows (no duplicates)")
# run order: ['extract_orders', 'extract_customers', 'clean_orders', 'join', 'aggregate_daily', 'publish_dashboard']
# loaded 3 new rows, new watermark 2026-03-02T12:00
# re-run loaded 3 rows again but target still has 3 rows (no duplicates)

The orchestrator runs tasks only after their dependencies (extracts first, publish last). The incremental load picks up rows newer than the watermark and upserts by key, so re-running the same window — which happens after failures and backfills — produces the same result instead of duplicates. That property, idempotency, is the single most important pipeline design rule.

Common mistakes

Applications

Key takeaways

Going further