Modern Data Pipelines: Design Choices and Reliability Practices

A data pipeline moves data from where it is produced to where it is useful, changing it along the way. Modern pipelines share a small set of design choices (where to transform, how often to run, how to organise data) and a set of reliability practices (idempotency, incremental loading, quality checks, observability, retries, testing). Interviews probe both, and this course covers them in that order.

A pipeline in one picture

  sources            ingestion             storage layers                  consumers
 ─────────        ───────────────     ──────────────────────────────     ────────────
 app database ─CDC─▶                  raw / bronze  (as received)
 SaaS APIs  ─batch─▶  orchestrated  ─▶ staging / silver (typed, deduped) ─▶ BI dashboards
 event stream ─────▶  jobs            marts / gold  (business models)    ─▶ ML features
 partner files ────▶                                                     ─▶ reverse ETL
                         │ checks, run records, lineage, alerts at every step │

Each layer can be rebuilt from the one before it, which is what makes fixes and backfills possible.

Design choices

Where transformation happens: ETL or ELT

ETL transforms before loading; ELT loads raw data and transforms inside the warehouse or lakehouse. ELT dominates where compute is elastic, but compliance or format constraints can still require transformation before load, so most pipelines are hybrids.

Read: ETL vs ELT · Practise: Deciding factors

When it runs: batch or streaming

Choose by the freshness the decision needs. Batch is simpler and cheaper; streaming is justified when decisions depend on near-real-time data and brings event time, watermarks, state and duplicates to handle. Micro-batch sits in between.

Read: Batch vs streaming · Practise: Batch or streaming?

How it is organised: layers

Raw (as received), staging or silver (typed, cleaned, deduplicated), marts or gold (business models). Keep raw data long enough to rebuild everything downstream.

Read: Star schema · Data lakes and the lakehouse

What runs it: orchestration

An orchestrator runs tasks in dependency order, on schedule, with retries and alerts, and passes each run its data interval so the run is reproducible. Airflow is the most common in job descriptions; Dagster, Prefect and cloud-native schedulers fill the same role.

Read: Airflow DAGs, scheduling and retries · Practise: Choosing an orchestrator

Reliability practices

Idempotency

Every step must be safe to rerun: a deterministic input slice, overwrite or merge instead of append, atomic publish and guarded side effects.

Read: Idempotency in data pipelines

Incremental loading and backfills

Load only what changed with a watermark that moves atomically with the data, re-read a lookback window, handle late events and deletes, and backfill through the normal job.

Read: Incremental loading, late data and backfills

Change data capture

Replicate databases from their change logs, applying ordered, idempotent merges, with a plan for snapshots, deletes and schema changes.

Read: CDC patterns and failure modes

Data quality

Check freshness, volume, schema, keys and validity at ingestion, after transformation and before publishing; quarantine bad rows; block on critical failures.

Read: Checks, contracts and failure handling

Observability

Record status, duration, volume, freshness and quality per run; alert first on freshness; agree SLOs with consumers; use lineage to scope impact.

Read: Pipeline observability

Failure handling and recovery

Retry transient failures with capped, jittered backoff; dead-letter bad records; fail fast on systemic errors; set timeouts everywhere.

Read: Reliability and retry design

Testing and CI/CD

Unit-test transformations, run SQL against a real engine, keep data tests on every run, diff logic changes against production, and deploy through CI with isolated environments.

Read: Testing data pipelines and CI/CD

Anatomy of one run

The practices above are easiest to see in code. This skeleton is deliberately small, but every production batch job has the same steps: a run id and interval from parameters, extract a fixed slice, validate, load idempotently, check, publish atomically, and record what happened.

import sqlite3
from datetime import date

def run(conn, logical_date: date, source_rows: list[dict]) -> dict:
    run_id = f"orders__{logical_date.isoformat()}"
    day = logical_date.isoformat()

    rows = [r for r in source_rows if r["order_date"] == day]                       # 1. fixed slice
    good = [r for r in rows if r.get("amount") is not None and r["amount"] >= 0]     # 2. validate
    rejected = len(rows) - len(good)

    with conn:                                                                      # 3-5. one transaction
        conn.execute("DELETE FROM fct_orders WHERE order_date = ?", (day,))         # idempotent overwrite
        conn.executemany("INSERT INTO fct_orders VALUES (?, ?, ?, ?)",
                         [(r["order_id"], day, r["amount"], run_id) for r in good])
        dupes = conn.execute("SELECT count(*) - count(DISTINCT order_id) FROM fct_orders "
                             "WHERE order_date = ?", (day,)).fetchone()[0]
        if dupes or rejected / max(len(rows), 1) > 0.1:                             # blocking checks
            raise RuntimeError(f"{run_id}: {dupes} duplicate keys, {rejected} rejected rows")
        conn.execute("INSERT OR REPLACE INTO pipeline_runs VALUES (?, ?, ?, 'success')", # 6. record
                     (run_id, len(good), rejected))
    return {"run_id": run_id, "rows": len(good), "rejected": rejected}

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE fct_orders (order_id INTEGER, order_date TEXT, amount REAL, run_id TEXT)")
conn.execute("CREATE TABLE pipeline_runs (run_id TEXT PRIMARY KEY, rows INTEGER, rejected INTEGER, status TEXT)")
source = [{"order_id": 1, "order_date": "2026-10-05", "amount": 50.0},
          {"order_id": 2, "order_date": "2026-10-05", "amount": 20.0},
          {"order_id": 3, "order_date": "2026-10-06", "amount": 75.0}]

print(run(conn, date(2026, 10, 5), source))
print(run(conn, date(2026, 10, 5), source))      # a retry: same result, no duplicates
print(conn.execute("SELECT count(*) FROM fct_orders").fetchone()[0])

try:
    run(conn, date(2026, 10, 5), source + [{"order_id": 1, "order_date": "2026-10-05", "amount": 50.0}])
except RuntimeError as exc:
    print("blocked:", exc)
print(conn.execute("SELECT count(*) FROM fct_orders").fetchone()[0])  # the failed run rolled back

{'run_id': 'orders__2026-10-05', 'rows': 2, 'rejected': 0}
{'run_id': 'orders__2026-10-05', 'rows': 2, 'rejected': 0}
2
blocked: orders__2026-10-05: 1 duplicate keys, 0 rejected rows
2

The retry changed nothing, and the run with a duplicate key was blocked and rolled back, so consumers still see the previous good data. Each numbered step maps to a lesson in this course.

Learning order and checkpoints

Lesson	You are ready to move on when you can…
ETL vs ELT	Explain where transformation should run for a given dataset, including PII
Batch vs streaming	Justify a latency choice and explain event time and watermarks
Idempotency	Write a partition overwrite and a deduplicated MERGE, and test them by running twice
Incremental loading	Implement a watermark load with a lookback and plan a backfill
CDC	Apply change events in order, including deletes, and name CDC failure modes
Data quality	Decide which checks block and which alert, and quarantine bad rows
Observability	Define a freshness SLO and measure it from run records
Reliability	Classify failures and implement capped, jittered retries
Testing and CI/CD	Describe the test layers and a CI pipeline for data code

Put it together

Work through the scalable batch pipeline case study and build the CSV to warehouse project.