Pipeline DAG Design
Tasks, edges and no cycles
Every table in the previous sections is written by a task, and every task reads tables written by other tasks. A pipeline DAG is that set of tasks with an arrow from each writer to each reader. Directed means the arrows have a direction. Acyclic means following them never returns to the start, so some task can always run first. An orchestrator walks the graph and starts a task when every task pointing into it has finished.
- load_bronze_payments (transform, orchestrator); freshness: done by 03:00; retries: 3; backoff: 5 min, doubling
- build_silver_payments (transform, orchestrator); idempotency: delete and insert by load_date
- check_silver_payments (quality gate, orchestrator); on error: halt downstream, page on-call
- build_gold_daily_revenue (transform, orchestrator); idempotency: delete and insert by revenue_date
- load_bronze_customers (transform, orchestrator); retries: 3
- build_silver_customers (transform, orchestrator); idempotency: full rebuild
- check_silver_customers (quality gate, orchestrator); on error: halt downstream, page on-call
- build_payments_wide (transform, orchestrator); idempotency: full rebuild
- load_bronze_payments -> build_silver_payments
- build_silver_payments -> check_silver_payments
- check_silver_payments -> build_gold_daily_revenue
- check_silver_payments -> build_payments_wide
- load_bronze_customers -> build_silver_customers
- build_silver_customers -> check_silver_customers
- check_silver_customers -> build_payments_wide
Depend on completion, never on the clock
A common first design runs the bronze load at 01:30 and the silver build at 02:00, because bronze usually takes 20 minutes. Here is the night it takes 50:
| Time | What happens |
|---|---|
| 01:30 | load_bronze_payments starts, 40 pages to fetch |
| 01:41 | API returns rate-limit errors, the loader backs off and retries |
| 02:00 | build_silver_payments starts on schedule and reads 23 of 40 pages |
| 02:20 | load_bronze_payments finishes; silver already wrote the day |
| 06:00 | Finance dashboard shows 58% of the day's revenue, no alert fired |
The silver build never failed, so nobody was paged. The fix is an edge in the orchestrator from the bronze task to the silver task: silver starts when bronze reports success, at 01:50 on a fast night and 02:20 on this one. A clock schedule belongs on the first task in the graph, and every other task waits for its inputs.
load_bronze_payments starts at 01:30 and build_silver_payments at 02:00. On a night when bronze runs until 02:20, which design guarantees silver never reads a partial day?
- Move the silver build to 03:00, leaving a 90-minute gap for the bronze load.
- Declare an edge from the bronze task to the silver task, so silver starts when bronze reports success.
- Have silver poll the bronze row count and start once it stops growing for 5 minutes.
- Merge bronze loading and silver building into one task so the 2 steps cannot drift apart.
Rerunning a task must give the same table
Tasks get rerun: a fix lands, a source resends a day, an engineer presses retry. A task that appends rows produces 2 copies of the day on its second run. A task that first removes what it wrote for that day, then writes, produces the same table however many times it runs:
DELETE FROM gold_daily_revenue
WHERE revenue_date = DATE '2026-10-20'Run the DELETE and the INSERT in one transaction, or as one overwrite of the date's partition, and the pair always leaves exactly 1 copy of 2026-10-20; a run that fails halfway leaves the old copy in place. The date is the task's parameter, handed in by the orchestrator, so a 90-day backfill runs as 90 copies of the task, in parallel where the graph allows.
A gate between layers
A quality gate is a task that reads a table and fails when a condition breaks, with every downstream task waiting on it:
SELECT
COUNT(*) AS silver_rows,
COUNT(DISTINCT event_id) AS distinct_events,
SUM(CASE WHEN event_type = 'payment' AND amount_cents <= 0 THEN 1 ELSE 0 END) AS non_positive
FROM silver_payments
WHERE CAST(paid_at AS DATE) = DATE '2026-10-20'If silver_rows differs from distinct_events, deduplication failed. If non_positive is above 0, a refund was classified as a payment. Either way the gate fails, the gold build never starts, and the dashboard shows yesterday's number with a staleness flag instead of today's wrong one.
- One task writes one table, so a failure names the table that is missing.
- Edges follow data: if task B reads what task A writes, B depends on A.
- Gates sit between layers and block; a gate that only warns is a log line.
- Retries belong on tasks that call external systems; a SQL bug fails 3 times the same way.
After a bug fix, the whole graph is rerun for 2026-10-20. silver_payments is correct and passes its gate, but gold_daily_revenue shows every country at exactly double its true revenue. Which task is at fault?
- load_bronze_payments, because it appended the day's pages a second time.
- build_silver_payments, because its row_number ranking kept both copies of each event.
- check_silver_payments, because it validates silver and never checks the gold table.
- build_gold_daily_revenue, because its INSERT ran without the DELETE, adding a second copy of the day.
About this lesson
This section is part of the Design Patterns lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.