Lineage, Impact Analysis, and Dev vs Prod

Level 8: Batch Pipelines & Orchestrationhard30 minlineage and impact analysisdownstream closurerecursive CTEdev and prod environmentsdeployment disciplineend-to-end pipeline compositionper-row quarantine

Answer what breaks downstream when a source changes, separate dev builds from prod builds, then ship the level capstone unaided.

Lineage is the ref graph read in both directions

The previous lesson walked the graph forwards to decide what to build first. Read the same edges backwards from a changed source and you get impact analysis: the set of everything that would break. Same table, same recursive closure, opposite direction, and it is the question every schema-change email should already have answered.

The manifest graph, and the closure of raw_payments

Step 1 / 4

Stage 1 of 4: Three sources. Ingestion owns these; the transformation layer only reads them.

Impact analysis is a reachable set, not the whole project. Naming the wrong set is how a change gets blocked for no reason.

The four models reachable from raw_payments are stg_payments, fct_payments, mart_finance_daily, and mart_exec_kpis. fct_customer_revenue sits in the same project and is not affected at all. That distinction is the entire value of lineage: it turns "we might break something" into a list.

Look closely at mart_finance_daily. It reads fct_payments, and it also reads stg_payments directly, because it reports how many payments were staged next to how many reached the fact. That makes it reachable from raw_payments by two paths of different lengths, two hops and three, and it is why an impact query has to report each model once at its shortest distance rather than once per path. A closure that lists the same mart twice is a closure nobody will read twice.

Lineage is also the fifth of the five observability pillars, alongside freshness, volume, schema, and distribution. Four of them tell you a table is wrong. Lineage tells you who else is now wrong because of it.

Dev and prod are two targets, not two codebases

The model SQL never changes between environments. What changes is the target: a connection profile that says which schema the compiled DDL writes into. On your branch the tool builds into a personal dev schema; on merge, the scheduled production job builds into the prod schema from the same files.

In this lesson that separation is simulated with a prod_ table prefix, and the discipline it encodes is worth stating plainly: you never fix data by editing a production table. You change the model, build it in dev, run the tests there, and promote by merging. A hotfix typed straight into prod is invisible to the next scheduled run, which will overwrite it with the same wrong answer.

model_runs records what was actually built where and whether it succeeded, which is how you answer two questions no dashboard shows you: which models have never built successfully in prod, and which models are ahead in dev and waiting to be promoted.

The capstone

The Practice below composes everything in Level 8: build order from the graph, the four data tests, per-row quarantine of the rows that fail them, and a script that survives the grader running it twice. It ships with no scaffold and no hints, only the goal state, because that is the shape of the take-home a junior data engineering loop actually assigns.

One design decision inside it is worth flagging before you start. A payment can pass all four data tests and still be absent from the fact, because its order was cancelled and never reached staging. A test answers "is this row valid". A join answers "does this row belong". Do not try to solve the second with the first, and note that the daily mart is the place that difference becomes visible: one day in the seed has a staged payment and no fact row at all.

Common mistake: quarantining into a table that is appended rather than rebuilt. The rejects table grows on every run, the same bad payment appears five times by Friday, and the "new failures today" number is meaningless. Rebuild the rejects table with the rest of the graph.

Interview nuance: "a source column is being renamed, what do you do" is answered lineage-first, in this order: enumerate the downstream closure from the graph, fix every model in it, rebuild in dev, run the tests, then promote. Candidates who start with "I would search the codebase for the column name" are describing the manual version of a query the manifest already answers.

On a real platform this differs. Here the graph is a model_refs table you query yourself. In a real project the tool serves the same closure from its compiled manifest, and dbt build --select source:shop.raw_payments+ builds exactly the models this lesson's Apply returns. A bare raw_payments+ would match nothing, because the default selector method looks at models, seeds, snapshots, and tests, and a source has to be named with the source: method. dbt Fusion extends the closure to column-level lineage, so it narrows to models that read the specific renamed column rather than the whole table, and an open lineage standard such as OpenLineage, or a catalog such as Microsoft Purview or DataHub, stitches graphs across tools. The idea is identical: an edge list, walked transitively.

Sample data for this example

CREATE TABLE model_runs (
  model    TEXT,
  env      TEXT,   -- dev | prod
  built_at TEXT,
  status   TEXT    -- success | error
);
INSERT INTO model_runs (model, env, built_at, status) VALUES
  ('stg_orders',           'dev',  '2026-06-14 09:00:00', 'success'),
  ('stg_orders',           'prod', '2026-06-14 02:00:00', 'success'),
  ('stg_customers',        'dev',  '2026-06-13 09:05:00', 'success'),
  ('stg_customers',        'prod', '2026-06-14 02:05:00', 'success'),
  ('stg_payments',         'dev',  '2026-06-14 09:10:00', 'success'),
  ('stg_payments',         'prod', '2026-06-14 02:10:00', 'success'),
  ('fct_customer_revenue', 'dev',  '2026-06-14 09:20:00', 'success'),
  ('fct_customer_revenue', 'prod', '2026-06-14 02:20:00', 'error'),
  ('fct_payments',         'dev',  '2026-06-13 09:25:00', 'success'),
  ('fct_payments',         'prod', '2026-06-14 02:25:00', 'success'),
  ('mart_finance_daily',   'dev',  '2026-06-13 09:40:00', 'success'),
  ('mart_finance_daily',   'prod', '2026-06-14 02:40:00', 'success'),
  ('mart_exec_kpis',       'dev',  '2026-06-14 09:45:00', 'success');

Worked example (SQL)

-- The run-results log: one row per model build per environment.
-- The same model SQL produced both rows; only the target schema differed.
SELECT model, env, built_at, status
FROM model_runs
ORDER BY env, built_at;

Apply

Your turn

The task this lesson builds to.

Write a query that returns every model downstream of raw_payments, as (model, depth), nearest first, over model_refs(model, ref_model).

A model that reads raw_payments directly is at depth 1, a model that reads that model is at depth 2, and so on. Report each model once, at its shortest distance from the source. Alias the columns exactly model and depth, and break ties within a depth by model ascending.

This is impact analysis: the exact list you would put in the email before renaming a column in raw_payments. Models that are not reachable from it must not appear.

4 hints and 1 automated check are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, plus 2 bonus drills.

Write a script that builds the production tables for the payments graph from raw_customers, raw_orders, and raw_payments, publishing only rows that pass the four data tests, quarantining the rest, and surviving a rerun. The grader runs your script twice.

Goal state, seven tables:

prod_stg_orders(order_id, customer_id, status, amount_usd, ordered_at): every non-cancelled raw_orders row.
prod_stg_customers(customer_id, name, region): every raw_customers row.
prod_payments_rejects(payment_id, order_id, reason): one row per offending payment_id and reason, with reason exactly 'duplicate_payment_id' when the payment_id repeats, 'null_amount_usd' when amount_usd is NULL, 'invalid_method' when method is outside ('card', 'cash', 'transfer'), and 'missing_order' when the order_id is absent from raw_orders.
prod_stg_payments(payment_id, order_id, method, amount_usd, paid_at): every raw_payments row whose payment_id is not in the rejects.
prod_fct_payments(payment_id, order_id, customer_id, method, amount_usd, paid_at): prod_stg_payments joined to prod_stg_orders, carrying the order's customer_id.
prod_mart_finance_daily(paid_on, staged_payments, payments, revenue_usd): one row per paid_at value in prod_stg_payments, where staged_payments counts the payments staged that day, payments counts how many of them reached prod_fct_payments, and revenue_usd is ROUND(SUM(amount_usd), 2) over those fact rows. A day on which nothing reached the fact still gets a row, with 0 and 0.0.
prod_mart_exec_kpis(region, customers, payments, revenue_usd): one row per region that has at least one row in prod_fct_payments, joining prod_fct_payments to prod_stg_customers for the region, where customers is COUNT(DISTINCT prod_fct_payments.customer_id) (customers who actually paid, not customers in the region), payments is the number of prod_fct_payments rows, and revenue_usd is ROUND(SUM(amount_usd), 2).

Build them in an order the dependencies allow.

10 automated checks are waiting in the workspace.