The Transformation DAG
raw_orders to stg_orders to int_orders_enriched to fct_orders to customer_metrics — five nodes, four edges, and everything you can ask of a graph you did not have to draw.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
What does turning a pile of transformations into an explicit dependency graph let you ask that you could not ask before?
Two people, asking opposite questions. The engineer with a broken model asking "what feeds this", and the engineer about to change a column asking "what else reads this". A dependency graph is the only artifact that answers both.
A node is one dataset and one transformation that produces it — the two are the same thing here, which is why one model per relation matters. An edge is one dataset being read by one transformation. Nothing else in the graph carries meaning (DAGs in Data Pipelines).
Keep the graph in your head, or on a whiteboard, or in a diagram in the team wiki. Everyone who works on the platform knows roughly what feeds what, and the diagram was accurate when it was drawn.
The diagram was accurate in March. Since then four models were added, two were renamed and one now reads a table it did not read before, and nobody updated the picture because the picture is not what runs (Data Lineage).
- The diagram was accurate in March. Since then four models were added, two were renamed and one now reads a table it did not read before, and nobody updated the picture because the picture is not what runs (Data Lineage).
- A dashboard is wrong. The upstream walk takes an hour of reading SQL to establish something the code already knows exactly, and the hour is spent during an incident when it costs the most (Where Did This Number Come From?).
- Someone needs to drop a column from a staging model. The question "who reads this" cannot be answered, so the column is kept forever and the staging layer accumulates fields nobody has used in two years (Impact Analysis).
- A model is fixed and rebuilt. Everything downstream of it still holds the old values, because rebuilding downstream requires knowing what downstream is, and nobody does (What Backfills Break).
- Two models are written that read each other, indirectly, through a third. Nothing detects it because nothing has the graph, and the build works right up until the run order shifts.
What is actually happening
- A transformation DAG is a directed acyclic graph where nodes are datasets-with-their-producing-transformation and edges are "reads from". Once it exists as data rather than as a picture, it answers four separate questions with four standard graph operations (DAG (Directed Acyclic Graph)).
- Execution order is a topological sort. Parallelism is the set of nodes whose predecessors are all complete. Selective rebuild is the descendant set of a chosen node — reachability by forward traversal (Depth-First Search (DFS)). Impact analysis is that same traversal, and root-cause search is the ancestor set, the traversal run on the reversed edges (Topological Execution).
- The chain from raw to metric is not arbitrary. Each node exists because it is a place someone might need to start again from: raw because it is the only untouched copy, staging because cleaning is per-source and must be re-runnable alone, intermediate because reshaping is where joins change grain, fact because that is the contract consumers depend on (Model Layering).
- The edges carry the actual risk. A node is a
SELECTyou can read; an edge is a grain assumption you cannot see.fct_ordersreadingint_orders_enrichedassumes that model is unique onorder_id, and that assumption lives on the edge rather than in either file (Grain: What Does One Row Represent?). - Because the graph is derived from code, it is current by construction. This is a much stronger property than it sounds: it means the debugging artifact and the execution artifact are the same object, so they cannot disagree (Column-Level Lineage).
Five nodes and why each one exists
The canonical chain is short enough to hold in your head and complete enough to show every property that matters. Each node is a place where a different kind of thing happens, and — more usefully — a place you might need to start again from.
raw_orders is what arrived, unmodified. stg_orders is that data cleaned, cast, renamed and deduplicated, with no business logic at all. int_orders_enriched is where joins happen and where the grain is most at risk. fct_orders is the contract: one row per order, tested, and the thing consumers are allowed to depend on. customer_metrics is an aggregate at a different grain, built for a specific question.
The reason to draw it is not tidiness. It is that four operations become available the moment the graph is data: order the build, run independent nodes together, rebuild only what a change affects, and walk upstream from a wrong number. All four are the same graph read four ways.
Walking the graph upstream from a wrong number
Nobody debugs a transformation chain forwards. The report is always "customer lifetime value looks too high", and the productive direction is against the arrows: which node produced it, what fed that, and at each step, is the affected data correct *here*?
The table below is that walk with the answer written down in advance. The couldCorrupt column is the useful one — it is the list of things to check at each node, and it is different at every node, which is why "check the data" is not a debugging strategy.
The walk terminates in one of three places, and knowing which one you are in decides who owns the fix. The source is wrong, which is an application incident and not yours. A node lost or duplicated rows, which is a pipeline problem. Or a node computed something the business did not mean, which is a definition problem and the hardest of the three to resolve, because everyone involved is technically correct (Two Dashboards, Two Numbers).
- customer_metrics
holds One row per customer with lifetime measures, computed as of a stated boundary.
could corrupt Aggregating a measure that was already aggregated; a re-run that appended rather than replaced; an as-of boundary nobody published, so consumers compare two different vintages.
↑ reads from - fct_orders
holds One row per order, with dimension keys and net measures. The consumer contract.
could corrupt A fan-out from a dimension join that lost uniqueness; a status filter that excludes a category the business counts; a currency conversion with a missing rate silently nulling rows.
↑ reads from - int_orders_enriched
holds Orders joined to customers, products and refunds — the node where grain is most at risk.
could corrupt Joining order lines to orders and never returning to order grain; a left join producing nulls that vanish downstream inside aggregates (Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELF).
↑ reads from - stg_orders
holds One row per order: cast, renamed, deduplicated, no business logic.
could corrupt A deduplication ordered by arrival rather than by the source's commit sequence, so an out-of-order update loses; a permissive cast nulling a whole column (Deduplication).
↑ reads from - raw_orders
holds Every delivered change record, exactly as it arrived, including duplicates.
could corrupt Nothing, by design — but it can be *incomplete*, if ingestion had a gap or a window closed early (Missing Rows).
↑ reads from - Source `orders` table
holds The authoritative current state of every order.
could corrupt From this domain's point of view, nothing. If the number is wrong here it is an application incident, and the correct response is to stop debugging the pipeline (Source of Truth).
Ask the same question at every node: is the affected period complete and correct *here*? The first "no" walking upstream is where the incident lives, and everything below it is a symptom.
What the graph saves you: rebuild the descendant set, not everything
The economic argument for the graph is one operation. A bug is found in stg_orders. Without a graph the safe response is to rebuild everything, because you cannot prove what is unaffected. With a graph the response is to rebuild the descendant set of that one node, and the rest of the platform is provably untouched.
The saving is not uniform, and that is the interesting part. In a shallow, wide graph — one staging layer feeding many independent marts — a fix at the root still touches everything, and the graph mostly buys ordering. In a deep, narrow graph the descendant set is small and selective rebuild is transformative. Which one you have is a property of your modelling, not of your tool.
The relative weights below are shown to establish an ordering between the drivers, not to predict a bill. They are directional, not measured, and the ordering is the teaching: what dominates a rebuild is how much history each node re-reads, and how many nodes are downstream of the one you changed.
A node that scans all history to correct one day dominates everything else. This is the driver that incremental materialisation exists to move (Full Refresh vs Incremental).
How many nodes are downstream of the fix. Decided by modelling, not by the tool — a wide fan-out from a staging model means almost every fix is a full rebuild.
Deduplication and wide joins move data between workers. Grows worse than linearly with skew, because one key's share lands on one worker (Data Skew).
Pure waste and the most common one — rebuilding upstream nodes that were already correct, because nobody could prove they were.
One query per assertion. Usually the smallest line here and consistently the first thing people propose skipping under time pressure.
Relative weights, not measurements. The ordering is what transfers between platforms; the magnitudes do not, and this domain never publishes a price.
Relative weights for a warehouse-centric project, shown to order the drivers rather than to size them. The two that dominate are both modelling decisions made months earlier: how much history each node re-reads, and how wide the graph fans out below the node you had to fix.
How to build it
Most important first.
- One model per relation, and every upstream named by reference rather than by table name. Both rules exist to keep the graph derivable; violating either produces a graph that is confidently wrong, which is worse than no graph (dbt Concepts).
- Put a node where you would want to restart. That is the actual criterion for splitting a transformation, and it produces better boundaries than any naming convention.
- Declare and test the grain at every node. The edge assumption becomes checkable only when the node it points at asserts its own uniqueness.
- Keep the graph shallow where you can. Every additional layer is another rebuild in the chain and another place freshness accumulates, and a chain of eight models where three would do is a cost with no corresponding guarantee (Raw, Staging, Curated: Layers by Purpose).
- Emit the graph to a catalog so consumers can walk it without repository access. An analyst asking what feeds a dashboard should not need to read SQL (The Data Catalog).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- The graph guarantees ordering: nothing runs before what it reads. That is the one promise, and it is unconditional as long as every edge is present.
- It guarantees that the ancestor set of a node is complete within the project. Dependencies on things outside it — an ingestion job, a source table refreshed by someone else, a manually loaded spreadsheet — are invisible, and that boundary is where most surprises live (Orchestration).
- It guarantees nothing about time. A topologically correct build can still read a source that has not been refreshed today, and the graph will report success (Freshness Checks).
- It guarantees nothing about correctness at any node. A graph is a statement about dependency, not about data (The Pipeline Succeeded. The Data Is Wrong.).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- Test the grain at each node — uniqueness and not-null on the key. The reason to do it at every node rather than at the end is attribution: a failure at
stg_orderstells you where the problem is, a failure atcustomer_metricstells you only that there is one. - Test row-count relationships across edges: staging should not exceed deduplicated raw, the enriched model should equal staging if its joins are grain-preserving.
- These miss anything the graph cannot see. A source that stopped updating produces a graph that builds perfectly over stale inputs, which is why source freshness is a separate assertion (Freshness Checks).
- End-to-end freshness is the length of the longest path through the graph plus the schedule interval that starts it. Adding a layer in the middle adds its build time to every consumer downstream, whether or not that consumer needed the layer.
- Freshness is per-node and should be published per-node. One number for "the warehouse" hides the model that has not built since Friday because a test upstream has been failing (Freshness Monitoring).
- Selective rebuild changes the freshness story during an incident: repairing one branch does not require waiting for the whole graph, which is often the difference between fixing a metric before the morning report and after it.
- Adding a node is a change for everything downstream of it, even if the output shape is identical, because build time and failure surface both grew.
- Renaming a model is a graph edit, and because references are code, it breaks at parse time in every downstream model — the good failure. Renaming a *source* table breaks at run time instead (Schema Evolution).
- Removing a node requires knowing its descendants, which is the one question the graph answers instantly and the reason unused models get deleted in projects with a graph and never get deleted in projects without one.
- The recovery primitive is: select the earliest wrong node, take its descendant set, rebuild exactly that. Everything not in the descendant set is known-good and must not be touched (Reprocessing vs Retrying).
- Rebuilding the ancestor set instead is the common mistake and is usually unnecessary — if
stg_ordersis correct, rebuilding raw ingestion to fixfct_orderscosts a great deal and changes nothing. - The rebuild must go in topological order, and it must go all the way to the leaves. A partial descendant rebuild leaves the graph internally inconsistent, which is worse than the original bug because now two models disagree (Validating a Backfill Before You Publish).
What can go wrong
- A hard-coded table name that omits an edge, so a model builds before its real upstream and reads yesterday's data — successfully, on every run.
- A dependency on something outside the project — an ingestion job, a manual load — that the graph cannot represent and therefore cannot order.
- A model that fails mid-graph, leaving its ancestors fresh and its descendants stale, so a join across the boundary mixes two points in time.
- A descendant rebuild that stopped early, leaving
fct_orderscorrected andcustomer_metricsstill carrying the old aggregate. - The graph itself becoming the plan of record while a legacy scheduled script also writes one of the tables, so a node has two producers and only one of them is in the graph.
- "The graph is documentation." It is the execution plan. Documentation drifts from what runs; a derived graph cannot, which is precisely why it is worth deriving rather than drawing.
- "If the graph is green, the data is right." The graph reports that every node ran in the correct order. Ordering is not correctness, and every node can succeed over empty or duplicated inputs (The Pipeline Succeeded. The Data Is Wrong.).
- "More layers is better modelling." Each layer must justify itself as a restart point or a contract boundary. A layer that is neither is a table with a name (Model Layering).
- "Lineage is for auditors." Lineage is for the person debugging at seven in the morning. The audit use case is real and it is not what pays for it (Lineage Debugging).
Operating it
- The rendered graph, with per-node last-build time and last-test result overlaid. That single view answers most incident questions before anyone opens a file (Data Observability).
- Longest path build duration, tracked over time. It grows quietly as layers are added and it is the number that decides end-to-end freshness (Pipeline Metrics).
- Nodes with no downstream consumers and no dashboard reads — the graph's own dead code, which is pure cost (Compute Waste).
- At ten nodes, order is obvious and the graph is documentation. At two hundred, the graph is the only navigable representation and the longest path becomes a design constraint.
- Graph *width* determines how much parallelism is available; graph *depth* determines the floor on build time that no amount of parallelism removes (Amdahl's Law).
- Consumer count grows the impact-analysis problem rather than the build problem. Eighty dashboards reading one fact table means every change to that table is a coordination exercise (Impact Analysis).
- A full graph rebuild costs the sum of every node. A subgraph rebuild costs the descendant set. The ratio between those two is the entire economic argument for having a graph at all (Scan Cost).
- Cost is not evenly distributed across nodes. A single wide join or a deduplication window function usually dominates, and finding it requires per-node cost attribution rather than a platform total (Cost Attribution).
- Extra layers cost a materialisation each. Three thin staging models that each rename two columns cost three table writes to save one join, which is a trade worth making deliberately rather than by convention.
- A derived graph requires the discipline that keeps it derivable — references everywhere, one output per model. That discipline is cheap to keep and impossible to retrofit once half the project hard-codes names.
- More nodes give finer restart granularity and better attribution, and cost more materialisations, more names and a longer critical path. The right node count is decided by where you would want to restart, not by how tidy the diagram looks.
- The graph tells you what depends on what and never what the dependency means. Column-level lineage narrows that gap and costs considerably more to produce and maintain (Column-Level Lineage).
Rebuild explorer — what a change actually costs
Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALNode, edge, acyclicity, topological order and reachability are graph properties, independent of any tool. What differs is whether your platform derives the graph from code or expects you to declare it, and a declared graph drifts from reality while a derived one cannot.
- SIMPLIFIEDThe five-node chain shown here is a line. Real graphs fan out — one staging model feeding six marts — and fan in — one fact table joining four sources — and the interesting operational properties (blast radius, critical path, parallel width) only appear once it stops being a line.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — DevOps / Production Engineering owns the equivalent graph for software delivery — build dependencies, deployment ordering and what a partial rollout leaves behind. The reasoning is the same and the artifacts are different.