Pipeline Design Foundations

Anatomy of a pipeline

Strip away the tooling and almost every data pipeline is the same four moves: ingest → store → transform → serve. Get this skeleton on the board first and you have a frame the interviewer can pull threads from.

  SOURCES            INGEST            STORE               TRANSFORM            SERVE
 ┌────────┐        ┌────────┐        ┌──────────┐        ┌───────────┐        ┌──────────┐
 │ app DB │──CDC──▶│        │        │  raw /   │        │ staging → │        │  marts / │
 │ events │──pull─▶│ loader │──land─▶│ landing  │──read─▶│  interm.  │──write▶│  BI / ML │
 │ 3p API │──push─▶│        │        │(immutable)│        │  → marts  │        │  serving │
 └────────┘        └────────┘        └──────────┘        └───────────┘        └──────────┘
                        │                  │                   │                    │
   ───────────────── ORCHESTRATION (schedule, deps, retries, backfill) ─────────────────
   ───────────────── OBSERVABILITY  (freshness, volume, lineage, DQ)   ─────────────────
                        └──────────── CATALOG / METADATA (schemas, ownership) ──────────

• Sources & sinks — where data comes from (operational DBs, event streams, third-party APIs, files) and where it lands (a warehouse, a lake, a serving store). Every source has a different contract for freshness, ordering, and completeness.
• Ingestion — moving bytes from source to storage, batch or streaming, push or pull. This is where most late-data and duplication problems are born.
• Storage — the durable landing zone. The best designs land immutable raw data first, then transform copies of it, so any downstream mistake is reproducible from source.
• Transform — cleaning, conforming, joining, aggregating. In a modern stack this is SQL running inside the warehouse (dbt), not a separate compute tier.
• Serve — the shaped outputs consumers actually query: BI marts, feature tables, reverse-ETL feeds.
• Cross-cutting concerns — orchestration (Chapter 03), observability (Chapter 07), and a catalog of schemas and ownership. Draw these underneath the happy path, not inline, exactly as the whiteboard advice in Chapter 01 suggests.

Batch, micro-batch, streaming

The first real design fork is cadence. How fresh does the served data have to be, and what are you willing to pay in complexity for it? Three regimes, and the honest answer is usually the least glamorous one.

A useful default: design the correct batch pipeline first, then ask whether any consumer's SLA actually forbids it. The late-data machinery you'll build in Chapter 04 applies to all three regimes — streaming just makes it non-optional.

ETL vs ELT

The order of the letters is the whole argument. ETL transforms data in a separate compute tier before loading it; ELT loads raw data first and transforms it inside the destination warehouse.

ELT won the analytics stack for concrete reasons: cheap, elastic, columnar warehouses (Snowflake, BigQuery, Redshift, Databricks SQL) made in-warehouse SQL transformation cheaper and faster than a bespoke Spark/Informatica tier, and landing raw data first means you can re-derive every downstream table without re-ingesting from source. That reproducibility is the quiet superpower — your transforms become version-controlled SQL (dbt) sitting on top of an immutable raw layer.

ETL still earns its place when you must not land the raw bytes — masking PII or secrets before they touch the warehouse — or when the transform is genuinely heavier than SQL (image/text featurization, large-scale ML preprocessing). Naming that nuance signals you understand ELT as a trade-off, not a religion.

The DAG mental model

Every pipeline is a directed acyclic graph: nodes are units of work, edges are dependencies, and "acyclic" means no step can (transitively) depend on itself. The orchestrator's core job is to walk that graph in topological order, running a node only once its upstreams have succeeded. Get comfortable thinking in DAGs because it's the shared vocabulary of the whole orchestration conversation in Chapter 03.

There are two ways to express the graph, and the distinction is a genuine leveling signal:

• Task-centric — you wire up operations: "run task A, then B, then C." The classic Airflow model. The graph describes what runs.
• Asset-centric / data-aware — you declare the data artifacts (tables, files) and their dependencies; the framework derives the execution graph and knows when an asset is stale. The Dagster and dbt model. The graph describes what exists.

Asset-centric orchestration maps more naturally onto ELT — your dbt ref() graph is the DAG — and it makes lineage and staleness first-class instead of bolted-on. We deep-dive the trade-off in Chapter 03; for now, know that "task vs asset" is a question interviewers use to see if you've felt the pain of a purely imperative DAG.

Idempotency & determinism

If you take one property from this chapter into the room, take this one. A pipeline step is idempotent if running it twice produces the same result as running it once, and deterministic if the same inputs always yield the same outputs. Together they mean: you can safely re-run any partition, at any time, without corrupting the result. Every retry, every backfill, every recovery from a half-failed run depends on it.

The enemy is the naive INSERT ... SELECT that appends: re-run it and you double-count. The fix is to make each run own a partition and replace it wholesale — partition-overwrite — or to merge on a natural key so re-processing updates in place instead of appending. Here's a partition-overwrite that's safe to re-run any number of times:

-- Idempotent partitioned load: each run OWNS one event_date partition.
-- Re-running for the same date replaces the partition atomically — no double-counting.
-- {{ order_date }} is bound by the orchestrator to the run's data-interval date.

BEGIN;

DELETE FROM analytics.orders
WHERE order_date = DATE '{{ order_date }}';

INSERT INTO analytics.orders (order_id, customer_id, order_date, status, amount, loaded_at)
SELECT
    o.order_id,
    o.customer_id,
    o.order_date,
    o.status,
    o.amount,
    CURRENT_TIMESTAMP                       AS loaded_at
FROM raw.orders AS o
WHERE o.order_date = DATE '{{ order_date }}'
  -- keep only the latest version of each order within the partition
  QUALIFY ROW_NUMBER() OVER (
      PARTITION BY o.order_id
      ORDER BY o.updated_at DESC
  ) = 1;

COMMIT;

The delete-then-insert inside a transaction gives you an atomic partition swap: readers see either the old partition or the new one, never a half-loaded mess. The QUALIFY ROW_NUMBER() dedup makes it robust to duplicate source rows. On engines with native partition replace (Spark INSERT OVERWRITE, BigQuery partition decorators, dbt's insert_overwrite strategy) you'd use that instead of explicit delete — same idea, cheaper mechanics. We'll build the incremental-merge variant, with lookback for late data, in Chapter 05.

The same discipline in Python, wrapping a partitioned write so a retry is a no-op-equivalent rather than an append:

from datetime import date

def load_orders_partition(warehouse, order_date: date) -> int:
    """Idempotent load of one day's orders. Safe to re-run for the same date."""
    rows = extract_orders(order_date)          # deterministic: same date -> same set
    deduped = latest_by_key(rows, key="order_id", version="updated_at")

    # Overwrite the whole partition atomically: stage -> swap, never append.
    with warehouse.transaction() as tx:
        tx.delete("analytics.orders", where={"order_date": order_date})
        tx.insert("analytics.orders", deduped)
    return len(deduped)

# Re-running is safe because the run OWNS order_date's partition:
#   load_orders_partition(wh, date(2026, 9, 10))   # first run
#   load_orders_partition(wh, date(2026, 9, 10))   # retry -> identical result

Modeling for pipelines

Raw data is rarely servable as-is. The standard shape is a layered flow — often called medallion (bronze → silver → gold) or, in dbt terms, staging → intermediate → marts:

• Bronze / staging — raw source data, lightly cleaned and typed, one model per source table. No business logic. This is your reproducible base.
• Silver / intermediate — conformed, deduplicated, joined entities. Business logic lives here.
• Gold / marts — the shaped, aggregated tables consumers query, modeled to a clear grain.

Two vocabulary anchors interviewers listen for:

• Grain — the precise meaning of one row ("one row per order line per day"). Naming the grain of every table is the fastest way to look like you model deliberately; a fuzzy grain is where double-counting and fan-out bugs hide.
• Surrogate keys — stable, warehouse-generated keys (a hash of the natural key, or a deterministic surrogate) that decouple your model from source system keys and give SCD dimensions a place to hang.

Slowly changing dimensions (SCD) handle attributes that change over time — a customer's tier or address. Type 1 overwrites (you lose history); Type 2 keeps a new row per version with valid_from / valid_to effective-dating, so a fact can join to the dimension value that was true at event time. That effective-dating is exactly what saves you when a dimension arrives late — the full treatment is in Chapter 04, and the mechanics (dbt snapshots) in Chapter 05.

Push, pull, and CDC

How data enters the pipeline shapes its failure modes. Two postures:

• Pull — you poll the source on a schedule ("give me everything with updated_at > my last watermark"). Simple, but you own the freshness/latency trade-off and you can miss hard deletes.
• Push — the source emits to you (webhooks, events onto Kafka). Fresher, but you must handle bursts, retries, out-of-order arrival, and at-least-once duplication — which loops right back to idempotency.

Change Data Capture (CDC) is the high-completeness option: instead of polling a table, you read the database's transaction log (Debezium, log-based replication) and receive every insert, update, and delete in commit order. It gives you ordering and delete-visibility that query-based pulls can't, at the cost of operational complexity. It's the L4 answer when the correctness bar is high — we develop it fully in Chapter 05.

Leveling ladder

The through-line of this guide is visible skill progression. Here's what "design a pipeline that loads orders into the warehouse" sounds like as you climb — each rung a superset of the one below.

With the foundations in place, the next question is who runs all this, in what order, and what happens when a step fails — that's orchestration. Next: 03 · Orchestration.

✓ Check yourself

Answer from memory before opening each one.

1. An orders load runs a plain INSERT ... SELECT into the target. The worker dies after the insert commits but before the task reports success, and the scheduler retries. What is in the table afterwards, and what is the smallest change that fixes it?

   Show answer

   Every row from that run is in the table twice, so counts and sums double. Make the run own its partition: replace the whole order_date partition, or merge on the natural key, so a retry converges on the same result. See idempotency and determinism.

2. The partition-overwrite does its delete and insert in one transaction. What goes wrong if you run them as two separate committed statements?

   Show answer

   A crash between them leaves the partition empty, and readers see missing data until someone notices. Even without a crash, readers can query between the two statements and see a partial day. One transaction, or a native partition swap, means readers see the old partition or the new one and nothing between. See idempotency and determinism.

3. A product manager asks for a "real-time" orders dashboard. The people using it check it a few times an hour. Which cadence do you propose, and what do you avoid taking on by doing so?

   Show answer

   A batch or micro-batch refresh every few minutes. Streaming makes out-of-order and late events a continuous problem, with watermarks and state to run, while a late record in batch is absorbed by re-running a partition. Choose streaming only when a consumer's latency target rules batch out. See batch, micro-batch, streaming.

4. A backfill job filters rows with WHERE updated_at > CURRENT_TIMESTAMP - INTERVAL '1 day'. Which property does it break, and how would you rewrite it?

   Show answer

   It breaks determinism and with it re-runnability: the result depends on when the job runs, so a backfill of last month loads the wrong rows. Bind the filter to the run's data interval, as in a templated date parameter, so the same inputs always give the same output. See determinism killers.

5. A regulator says card numbers must never land in the warehouse. Does that mean ETL for the whole stack?

   Show answer

   No. Redact or tokenise in a small step before landing, which is the case where ETL is still right, and keep ELT for everything after it. You still get an immutable raw layer, just one that holds already-masked data, and transforms stay as versioned SQL. ETL is the exception you scope, not the default. See ETL vs ELT.

6. The nightly pull on updated_at has run for a year. Finance finds orders in the warehouse that were deleted in the app. Why did the pipeline never remove them, and what are your options?

   Show answer

   A hard delete leaves no row with a newer updated_at, so the query never sees it. Options are log-based CDC, which delivers deletes in commit order; soft deletes at source; or a periodic full-key reconciliation. Pick by correctness bar and by how much operating cost you can carry. See push, pull and CDC.

Takeaway

• Almost every pipeline is ingest, store, transform and serve, with orchestration, observability and a catalog drawn underneath rather than inline.
• Streaming does not remove the hard problems, it makes late and out-of-order data permanent, so choose it for a stated latency need and start from a correct batch design.
• ELT is the default because raw data lands immutably and every downstream table can be re-derived by re-running SQL; ETL stays for masking before landing and heavy non-SQL work.
• A DAG describes what runs when it is task-centric and what exists when it is asset-centric, and the second fits ELT better.
• Idempotency means each run owns a partition and replaces it atomically, or merges on a natural key, so retries and backfills are safe.
• Determinism fails when logic depends on the wall clock or unordered limits, so parameterise on the data interval, not on now.
• Incremental pulls on a timestamp miss hard deletes, so say how deletes reach the warehouse before you are asked.

Something has to run these steps in order and recover when one fails, which is the job of the orchestrator. → Orchestration

Further reading

• Airflow: DAGs — Apache Airflow docs. The task-centric graph model and how runs are scheduled against data intervals.
• dbt: About incremental strategy — dbt Labs docs. Append, merge, delete+insert and insert_overwrite, with which warehouse supports which.
• What is the medallion lakehouse architecture? — Databricks docs. The bronze, silver and gold layering, described by the vendor that named it.
• Debezium connector for PostgreSQL — Debezium docs. How log-based CDC reads the write-ahead log and surfaces inserts, updates and deletes.
• Idempotence Is Not a Medical Condition — Pat Helland, ACM Queue, 2012. Why messaging and retries force idempotent handling, argued from first principles.