Column-Level Lineage Deep Dive: SQL Parsing, Impact Analysis & Blast-Radius Mapping

column-level lineage is the difference between a schema change that ships on a Tuesday and a schema change that pages you at 3 AM because a finance dashboard three hops downstream silently started returning NULL. Every warehouse and lakehouse is a graph of derivations — one column feeds another, which feeds a metric, which feeds a report — and the single question a senior data engineer must be able to answer before touching any of it is: if I rename, retype, or drop this one column, what exactly downstream breaks, and how far does the damage travel? Table-level lineage answers "which tables depend on this table"; it is coarse, it over-reports, and it cannot tell you that only revenue.gross_amount — and nothing else in that table — reads the column you are about to change. Getting to the column requires two things most teams skip: real SQL parsing into an abstract syntax tree, and a graph you can traverse.

This guide is the senior-data-engineering walkthrough of how column-level lineage is actually built and used — framed the way interviewers probe it. It moves from SQL parsing (why regex loses and how sqlglot turns a query into a resolved AST), to composing per-model column edges into a single DAG, to the two traversals that matter — forward for impact analysis of downstream dependencies and backward for provenance — to blast radius mapping that turns "this feels risky" into a ranked number your CI can gate on, and finally to OpenLineage, the standard for emitting data lineage so your graph is not a bespoke island. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse transformations on the data transformation practice library →, and sharpen the graph-design axis on the design practice library →.

---

On this page

• Why column-level lineage de-risks every schema change
• SQL parsing — from query text to a resolved AST
• Building the column-level lineage graph
• Impact analysis and downstream dependencies
• Blast-radius mapping and OpenLineage
• Cheat sheet — column-level lineage recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why column-level lineage de-risks every schema change

Table lineage tells you which tables are connected; column lineage tells you which change is safe

The one-sentence invariant: column-level lineage is a directed graph whose nodes are individual columns and whose edges are the derivations that produce one column from others — and it exists to answer, with column precision rather than table-coarse over-approximation, the two questions that gate every warehouse change: "what downstream depends on this column" (impact) and "where did this column's value come from" (provenance). Table-level lineage draws an edge from orders to revenue if any column of revenue reads any column of orders; it is cheap to compute (schedulers already know which table each job reads and writes) and almost useless for change safety, because it tells you a hundred consumers depend on orders when the column you are actually changing feeds three of them. Column lineage collapses that false blast radius to the real one.

Table-level vs column-level — the granularity gap.

• Table lineage. Edge = "table A is read to build table B." Derivable from query logs, orchestrator DAGs, or INFORMATION_SCHEMA view dependencies without parsing a single expression. Answers "what reads orders?" — but over-reports wildly for change safety.
• Column lineage. Edge = "column orders.total_cents is read to compute revenue.gross." Requires parsing the SQL that builds each model and attributing each output column to its source columns. Answers "what reads orders.total_cents?" — the question a schema change actually needs.
• The gap in one number. A wide fact table with 60 columns feeding 40 downstream models yields one coarse table edge but up to 60×40 possible column edges; the real graph is sparse, and only the column graph tells you a change to column 17 touches 4 models, not 40.
• Why coarse lineage is dangerous. Over-approximation trains people to ignore lineage ("it says 200 things depend on this, that can't be right, I'll just ship it"). Precise lineage is trusted because when it says "3 columns break," exactly 3 columns break.

The four axes interviewers actually probe.

• Granularity. Table, column, or transformation-aware column (does the edge know it was an aggregate vs a rename vs a CASE)? Senior answer names the target as column-level with transformation type on each edge — the minimum to reason about type changes and semantic drift, not just structural breaks.
• Coverage. Which SQL does your lineage actually parse? Views and models, yes — but also INSERT … SELECT, CREATE TABLE AS, MERGE, CTEs, window functions, UNION, lateral joins, and dialect quirks (BigQuery STRUCT, Snowflake QUALIFY). A lineage tool that silently drops the 5% of queries it cannot parse produces a graph with invisible holes — the worst failure mode, because it looks complete.
• Freshness. Is the graph rebuilt from the current SQL on every merge, or is it a stale quarterly export? Lineage that lags the code base is lineage you cannot gate a PR on. The senior answer wires graph construction into CI so the graph is never older than main.
• Actionability. Can you do something with it — fail a PR, rank a migration, open the exact three files that read a doomed column — or is it a pretty diagram nobody queries? Blast-radius numbers and PR gates are what turn lineage from documentation into a control.

The 2026 reality — table lineage is commodity, column lineage from SQL is the moat.

• Catalogs ship table lineage cheaply. Every modern catalog (DataHub, OpenMetadata, Unity Catalog, dbt's own docs) draws table-to-table edges from run metadata almost for free. It is table stakes and it is not what differentiates a senior engineer.
• Column lineage from SQL is the hard part. It requires a real SQL parser, per-dialect grammar coverage, schema-aware SELECT * expansion, and scope resolution across CTEs and subqueries. This is exactly why sqlglot (a pure-Python SQL parser and transpiler with a built-in lineage helper) has become the default building block — it parses ~20 dialects into one AST and resolves columns against a supplied schema.
• OpenLineage standardises the wire format. Rather than each tool inventing a lineage schema, OpenLineage defines a columnLineage dataset facet — output field → input fields, with a transformationType. Emitting that facet makes your graph portable into Marquez, DataHub, or any OpenLineage-aware backend.
• The moat is trust. A column graph that is parsed (not guessed), fresh (rebuilt in CI), and complete (fails loudly on unparseable SQL) is the thing teams actually gate deploys on. That combination — not the diagram — is the senior deliverable.

What interviewers listen for.

• Do you distinguish table-level from column-level lineage and say why column precision matters for change safety? — required answer.
• Do you insist on parsing SQL into an AST rather than regex / string matching? — senior signal.
• Do you name the graph as a DAG and describe forward (impact) vs backward (provenance) traversal? — required answer.
• Do you express blast radius as a number you can rank and gate on, not a vibe? — senior signal.
• Do you mention coverage / unparseable-SQL as the silent failure mode and OpenLineage as the interchange standard? — senior signal.

Worked example — the granularity ladder

Detailed explanation. The single most useful artifact for a lineage interview is the granularity ladder: the same change viewed at table, column, and transformation-aware column granularity, showing how the reported blast radius shrinks and sharpens as you climb. Every senior lineage discussion converges on this ladder within the first ten minutes; having it in your head is what separates "lineage is a diagram" from "lineage is a control." Walk through the ladder for a single proposed change: dropping orders.coupon_cents.

• The change. ALTER TABLE orders DROP COLUMN coupon_cents.
• The estate. orders (18 columns) is read by 22 downstream models; only 2 of them reference coupon_cents.
• The consumers. One BI dashboard reads a metric derived from one of those 2 models.

Question. Report the blast radius of the drop at each of the three granularities and explain which one a reviewer should trust.

Input.

Granularity	Edge meaning	Reported blast radius for dropping coupon_cents
Table-level	any column of A reads any column of B	22 models (all readers of orders)
Column-level	this column of A reads that column of B	2 models (the only readers of coupon_cents)
Transformation-aware column	column edge + how (identity / transform / aggregate)	2 models: 1 identity pass-through, 1 inside a SUM

Code.

# The three granularities as three different graph queries.
# G_table:  nodes are tables,  edges are table->table
# G_col:    nodes are "schema.table.column", edges are column->column
# G_col has a "transform" attribute on every edge.

import networkx as nx

def table_blast_radius(G_table, table):
    return sorted(nx.descendants(G_table, table))

def column_blast_radius(G_col, column):
    return sorted(nx.descendants(G_col, column))

def column_blast_with_transform(G_col, column):
    hits = []
    for down in nx.descendants(G_col, column):
        # find the edge kind on the path's first hop for reporting
        for pred in G_col.predecessors(down):
            if pred == column or column in nx.ancestors(G_col, down):
                kind = G_col.edges[pred, down].get("transform", "identity")
                hits.append((down, kind))
                break
    return hits

print(table_blast_radius(G_table, "prod.orders"))
# -> 22 tables — the coarse, over-reported answer

print(column_blast_radius(G_col, "prod.orders.coupon_cents"))
# -> ['prod.revenue.discount_cents', 'prod.mart_promos.coupon_total']

print(column_blast_with_transform(G_col, "prod.orders.coupon_cents"))
# -> [('prod.revenue.discount_cents', 'identity'),
#     ('prod.mart_promos.coupon_total', 'aggregate')]

Step-by-step explanation.

1. table_blast_radius walks the table graph and returns every table reachable from orders. Because the edge means "reads some column," dropping any column reports all 22 downstream tables — the over-approximation that makes coarse lineage untrustworthy.
2. column_blast_radius walks the column graph starting from the specific node prod.orders.coupon_cents. Only two columns are reachable, so the drop touches exactly two models. This is the number a reviewer can act on.
3. column_blast_with_transform adds the transform attribute on each edge. It reports that revenue.discount_cents is an identity copy (dropping the source hard-breaks it) while mart_promos.coupon_total consumes the column inside an aggregate (a SUM), which fails differently — the query errors on a missing column rather than silently returning wrong values.
4. The transformation kind matters for the repair: an identity pass-through can sometimes be re-pointed at a replacement column; an aggregate must be rewritten. Table lineage cannot express any of this.
5. The reviewer trusts the column-level (and especially the transformation-aware) answer because it is precise: it names the two exact columns and the two exact files to edit, instead of a 22-item list that is 91% noise.

Output.

Granularity	Blast radius	Trustworthy for review?
Table-level	22 models	No — 20 of 22 are false positives
Column-level	2 models	Yes — names the real dependents
Transformation-aware	2 models (1 identity, 1 aggregate)	Yes — also tells you how each breaks

Rule of thumb. Never size a schema change off table-level lineage — it over-reports by design and trains people to ignore it. Climb to column granularity, and put a transform attribute on every edge so you know not just what breaks but how.

Worked example — what interviewers actually probe

Detailed explanation. The senior lineage interview has a predictable arc: an ambiguous opener ("how would you know what a schema change affects?"), then progressive narrowing to test whether you know the axes. Candidates who say "we'd check the catalog diagram" score low; candidates who describe parsing SQL into a graph and traversing it score high. Walk through the grading rubric.

• Ambiguous opener. "Someone wants to drop a column — how do you know if it's safe?" — invites you to name column lineage.
• Follow-up 1. "How do you build that graph — do you read the scheduler or the SQL?" — probes the parse-vs-metadata axis.
• Follow-up 2. "What about SELECT * and CTEs?" — probes coverage / resolution.
• Follow-up 3. "How do you make it actionable in a PR?" — probes actionability.
• Follow-up 4. "How do other tools consume your lineage?" — probes OpenLineage / interchange.

Question. Draft a 5-minute senior lineage answer that covers all four axes without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Granularity	"the catalog shows dependencies"	"column-level, transformation-aware, as a DAG"
Build method	"read the Airflow DAG"	"parse each model's SQL to an AST with sqlglot, resolve columns against the schema"
Coverage	"mostly works"	"expand SELECT * and resolve CTE/subquery scopes; fail loudly on unparseable SQL"
Actionability	"we look at the diagram"	"CI computes blast radius and fails the PR above a threshold"
Interchange	"it's our own format"	"emit the OpenLineage columnLineage facet"

Code.

Senior column-lineage answer template (5 minutes)
==================================================

Minute 1 — name the shape
  "I'd model lineage as a DAG of columns, not tables. Nodes are
   fully-qualified columns; edges are derivations with a transform type."

Minute 2 — how it's built
  "I don't infer it from the scheduler — that only gives table edges.
   I parse each model's SQL into an AST (sqlglot), qualify it against
   the warehouse schema to expand SELECT * and bind aliases, then
   attribute every output column to its source columns."

Minute 3 — coverage and the silent failure
  "The trap is unparseable SQL. If the parser skips 5% of models the
   graph has invisible holes. I fail the build on a parse error and
   track dialect coverage as a metric, so gaps are loud, not silent."

Minute 4 — make it actionable
  "On every PR, CI diffs the changed models, recomputes the column
   graph, and reports blast radius: which downstream columns, tables,
   and tier-1 dashboards a change touches. Above a threshold the PR
   needs an explicit sign-off."

Minute 5 — interchange
  "I emit the OpenLineage columnLineage facet so the graph flows into
   Marquez / DataHub and lines up with runtime lineage from Airflow
   and Spark. Static parse-time lineage plus runtime lineage is the
   complete picture."

Step-by-step explanation.

1. Minute 1 sets the frame: "DAG of columns, not tables." Naming the data structure immediately signals you have built this, not just clicked around a catalog.
2. Minute 2 addresses the build-method axis before it is asked. "Parse the SQL, don't read the scheduler" is the line that separates people who know table lineage is free from those who know column lineage is earned.
3. Minute 3 names the silent failure mode — unparseable SQL producing invisible holes — and the fix (fail loudly, track coverage). This is the single most senior thing you can say about lineage.
4. Minute 4 is the actionability axis: CI computes blast radius and gates the PR. This converts lineage from a diagram into a control.
5. Minute 5 covers interchange: emitting the OpenLineage facet so static lineage composes with runtime lineage. Naming the standard shows you have thought past your own tool.

Output.

Grading criterion	Weak score	Senior score
Names column DAG in minute 1	rare	mandatory
Parses SQL rather than reads scheduler	occasional	required
Names unparseable-SQL failure mode	rare	senior signal
Wires blast radius into CI	rare	senior signal
Names OpenLineage for interchange	rare	senior signal

Rule of thumb. The senior lineage answer is a 5-minute monologue that names the column DAG, insists on parsing over metadata, calls out unparseable SQL as the silent killer, gates PRs on blast radius, and emits OpenLineage. Rehearse it once; deploy it every time.

Worked example — the "will this change break anything" decision tree

Detailed explanation. Given a proposed schema change, the senior engineer runs a short decision tree in their head that maps the kind of change to the kind of downstream break. Codifying it makes the interview answer reproducible: hand me any change and I can walk the tree out loud. Walk through it for three canonical changes — dropping a column, retyping a column, and renaming a column.

• Q1. Is the change structural (drop / rename) or semantic (retype / change units / change nullability)? Structural breaks are loud (missing column); semantic breaks are silent (wrong values).
• Q2. For the target column, is the descendant set empty? → yes = safe to change; no = go to Q3.
• Q3. Do any descendants reach a tier-1 consumer (finance, regulatory, exec dashboard)? → yes = require sign-off; no = notify owners.
• Q4. Is the change semantic (silent)? → yes = the descendants that need manual review are the transformation edges, not just identity copies, because a SUM or CASE can hide a units change.

Question. Walk the tree for the three changes and record the review action each ends up with.

Input.

Change	Q1 (structural/semantic)	Q2 (has descendants?)	Q3 (tier-1 reached?)
DROP COLUMN coupon_cents	structural	yes (2)	no
ALTER … total_cents → total (dollars)	semantic	yes (7)	yes
RENAME status → order_status	structural	yes (5)	no

Code.

def review_action(change_kind: str,
                  descendants: list[str],
                  tier1_hits: list[str],
                  transform_edges: list[str]) -> str:
    """Map a proposed column change to a required review action."""
    if not descendants:
        return "SAFE — no downstream columns depend on this"

    if tier1_hits:
        return f"BLOCK — needs sign-off; touches tier-1: {tier1_hits}"

    if change_kind == "semantic":
        # silent breaks: transform edges can hide a units/precision change
        return (f"REVIEW — semantic change, manually verify "
                f"{len(transform_edges)} transform edge(s): {transform_edges}")

    return f"NOTIFY — structural change; notify owners of {len(descendants)} columns"


print(review_action("structural",
                    ["revenue.discount_cents", "mart_promos.coupon_total"],
                    [], ["mart_promos.coupon_total"]))
# -> NOTIFY — structural change; notify owners of 2 columns

print(review_action("semantic",
                    ["revenue.gross", "...7 total..."],
                    ["finance_dash.net_revenue"],
                    ["revenue.gross"]))
# -> BLOCK — needs sign-off; touches tier-1: ['finance_dash.net_revenue']

print(review_action("structural",
                    ["a", "b", "c", "d", "e"], [], []))
# -> NOTIFY — structural change; notify owners of 5 columns

Step-by-step explanation.

1. The empty-descendant check (Q2) short-circuits the common safe case: a column nothing reads can be changed freely. Column lineage is what lets you prove the set is empty rather than guess.
2. The tier-1 check (Q3) is the gate. A change whose blast radius reaches a finance or regulatory consumer is blocked pending sign-off regardless of how "small" the diff looks.
3. The semantic branch (Q4) is the subtle one: a retype from cents to dollars compiles fine everywhere — nothing errors — but every downstream SUM is now 100× off. The transform edges (aggregates, CASE, arithmetic) are exactly where silent corruption hides, so those are the edges a human must eyeball.
4. Structural changes (drop / rename) that don't reach tier-1 are demoted to "notify owners" — they will fail loudly at build time if missed, so they need visibility, not a hard block.
5. The tree is deterministic: same change + same graph → same action, which is what makes it a policy CI can enforce rather than a judgment call per reviewer.

Output.

Change	Review action
DROP COLUMN coupon_cents	NOTIFY — 2 downstream columns, no tier-1
total_cents → total (dollars)	BLOCK — semantic, reaches finance tier-1
RENAME status → order_status	NOTIFY — 5 downstream columns, no tier-1

Rule of thumb. Split changes into structural (loud) and semantic (silent) first, then gate on whether the descendant set reaches a tier-1 consumer. Semantic changes over transform edges are the ones that need human eyes — the compiler will never catch a units bug for you.

Senior interview question on column-level lineage

A senior interviewer often opens with: "You own a warehouse where a DROP COLUMN last quarter silently broke a finance dashboard because table-level lineage said 'everything depends on everything.' Design a column-level lineage capability: how you'd build the graph, how you'd tell whether a specific change is safe, and how you'd wire it into the deploy so this never ships again."

Solution Using a parsed column DAG with a CI blast-radius gate

# lineage_gate.py — the shape of the capability (details in later sections)
import sys
import networkx as nx

TIER1 = {"finance_dash", "regulatory_export", "exec_kpi"}

def load_column_graph() -> nx.DiGraph:
    """Built by parsing every model's SQL with sqlglot (see section 2-3)."""
    ...

def changed_columns_in_pr() -> list[str]:
    """Diff the PR against main; return fully-qualified columns touched."""
    ...

def gate() -> int:
    G = load_column_graph()
    failures = []
    for col in changed_columns_in_pr():
        if col not in G:
            continue
        downstream = nx.descendants(G, col)
        tier1_hits = sorted({d.split(".")[0] for d in downstream} & TIER1)
        report = {
            "column": col,
            "downstream_columns": len(downstream),
            "downstream_tables": len({d.rsplit(".", 1)[0] for d in downstream}),
            "tier1": tier1_hits,
        }
        print(report)
        if tier1_hits:
            failures.append(report)

    if failures:
        print(f"\nBLOCKED: {len(failures)} change(s) reach tier-1 consumers.")
        return 1
    return 0

if __name__ == "__main__":
    sys.exit(gate())

Step-by-step trace.

Step	Before (table lineage)	After (column DAG + gate)
Graph granularity	table → table	column → column with transform type
Build source	scheduler / run metadata	parsed SQL AST (sqlglot), rebuilt in CI
"Is this safe?" answer	"200 tables depend on orders"	"3 columns, 0 tier-1 — safe" or "7 columns, 1 tier-1 — block"
Where it runs	quarterly manual review	on every PR, before merge
Tier-1 protection	none	hard block + required sign-off
Failure surface	silent break at 3 AM	loud block in CI, at PR time

After the capability ships, the PR that drops coupon_cents prints "2 downstream columns, 0 tier-1" and merges freely; the PR that retypes total_cents prints "7 downstream columns, tier-1: finance_dash" and is blocked until the finance owner signs off. The 3 AM breakage becomes a CI comment.

Output:

Metric	Before	After
Blast-radius precision	table-coarse (91% noise)	column-exact
When you learn of a break	in production	in the PR
Tier-1 dashboard protection	none	enforced gate
Lineage freshness	quarterly	every merge
Reviewer trust	ignored	acted on

Why this works — concept by concept:

• Column DAG over table lineage — modelling nodes as columns collapses the false blast radius; nx.descendants on the column node returns the exact dependents instead of every reader of the table.
• Parse-time construction — building the graph from parsed SQL (not scheduler metadata) is the only way to get column granularity; the AST is where the mapping from output column to source columns actually lives.
• Tier-1 gate — intersecting the descendant set with a tier-1 allowlist turns "size the change" into "block the dangerous ones," which is the actionability axis that makes lineage a control.
• CI freshness — rebuilding the graph on every PR guarantees it never lags main, so the gate reasons about the code being merged, not a stale export.
• Cost — parsing N models is O(total SQL size); a descendants query is O(V+E) on a sparse DAG. Both run in seconds on a few-thousand-model project — negligible next to the cost of one silent finance-dashboard break.

SQL
Topic — sql
SQL dependency and lineage query problems

Practice →

Design Topic — design Design problems on lineage and metadata systems

Practice →

---

2. SQL parsing — from query text to a resolved AST

sqlglot turns a query into a syntax tree you can walk — regex never could

The mental model in one line: SQL parsing for lineage means converting each model's query text into an abstract syntax tree (AST), then qualifying that tree against the warehouse schema so every SELECT * is expanded, every alias is bound to its real table, and every column reference resolves to a fully-qualified schema.table.column — only against a resolved AST can you reliably attribute each output column to the source columns that produced it. Regex and string-matching feel tempting for a first prototype and fail immediately on anything real: a SELECT *, a CTE that shadows a table name, a self-join with two aliases of the same table, a window function, a UNION of differently-shaped subqueries. A parser handles all of it because it understands the grammar; a regex only understands characters.

Why regex / string-matching loses.

• SELECT * carries no column names. A regex sees *; it cannot know the star expands to 18 columns without the schema. Only a schema-aware qualifier can expand it.
• Aliases hide the real table. FROM orders o JOIN order_items oi means o.id is orders.id — a regex has to re-implement alias resolution, badly. The parser tracks it natively.
• Scopes nest. A CTE named revenue can shadow a physical table; a subquery introduces a new scope where the same alias means something different. Column resolution is inherently scope-aware; string matching is not.
• Dialects differ. Snowflake QUALIFY, BigQuery EXCEPT/STRUCT, Postgres DISTINCT ON — a parser with per-dialect grammars reads them all; a regex needs a special case for each and still misses.

The parse → qualify → attribute pipeline.

• Parse. sqlglot.parse_one(sql, dialect=...) returns an Expression — the root of the AST. Every clause (Select, Join, Column, Func, Window) is a typed node you can walk with .find_all(...).
• Qualify. qualify(ast, schema=schema, dialect=...) rewrites the tree so SELECT * becomes an explicit column list, bare columns get their table prefix, and aliases are normalised. This step requires a schema — the mapping table → {column: type} — because star-expansion and ambiguity resolution are impossible without it.
• Attribute. With a qualified tree, sqlglot.lineage(output_column, sql, schema=...) walks from an output column down to the leaf source columns, returning a Node tree whose leaves are schema.table.column references in the source tables.
• Emit edges. Each output_column → leaf_source_column pair becomes one edge in the column graph, tagged with the transformation kind read off the AST node between them (identity, function, aggregate).

What sqlglot.lineage gives you.

• A Node tree, not a flat list. The returned Node has .name (the qualified column), .expression (the AST fragment that produced it), and .downstream (the nodes it derives from). Recurse .downstream until you hit leaves that map to real source tables.
• Intermediate steps preserved. CTE columns and subquery outputs appear as intermediate nodes, so you can see the path a value took, not just its endpoints — useful for provenance narratives.
• Schema-driven correctness. Pass the same schema you qualified with; lineage resolves * and aliases identically, so the leaves are always fully-qualified.
• Dialect-aware. dialect="snowflake" (etc.) makes the parser read the source SQL correctly; the lineage walk is dialect-agnostic once parsed.

Coverage — the axis that decides whether your graph is trustworthy.

• Track parse success as a metric. Wrap every parse_one in a try/except; count parses that fail. A lineage graph built while silently dropping 5% of models has invisible holes — the worst outcome, because it looks complete.
• Fail loudly in CI. A model that cannot be parsed should fail the build (or land on an explicit allowlist of "known-unparseable, manually reviewed"), never be skipped quietly.
• Provide the full schema. Missing tables from the schema make qualify unable to expand * and leave columns unresolved. Build the schema from INFORMATION_SCHEMA so it is complete and current.
• Handle the long tail. Dynamic SQL, UDFs with opaque bodies, and external stored procedures cannot be parsed for column lineage; record them as coverage gaps rather than pretending they don't exist.

Common interview probes on SQL parsing for lineage.

• "Why not regex?" — required answer: SELECT *, aliases, nested scopes, and dialects all need a real grammar.
• "What does qualify do and why does it need a schema?" — expands * and binds columns; impossible without the table→columns map.
• "How do you handle SELECT *?" — schema-driven star expansion during qualify.
• "What's your coverage story?" — count parse failures; fail the build; track dialect gaps as a metric.

Worked example — parse and qualify a JOIN

Detailed explanation. The foundational step is turning a JOIN with aliases and a SELECT *-adjacent shape into a fully-qualified, explicit column list. Walk through parsing a two-table join, then qualifying it against a schema so every column is bound to its source table.

• The query. A join of orders and order_items selecting a mix of aliased columns.
• The schema. orders(id, customer_id, total_cents), order_items(id, order_id, quantity, unit_price).
• The goal. After qualify, every column reads as orders.<col> or order_items.<col> with no bare names.

Question. Parse the join, qualify it against the schema, and show the fully-qualified SQL the qualifier emits.

Input.

Object	Value
Dialect	postgres
orders columns	id, customer_id, total_cents
order_items columns	id, order_id, quantity, unit_price
Selected	customer_id, quantity * unit_price AS line_total

Code.

import sqlglot
from sqlglot.optimizer.qualify import qualify

schema = {
    "orders":      {"id": "BIGINT", "customer_id": "BIGINT", "total_cents": "BIGINT"},
    "order_items": {"id": "BIGINT", "order_id": "BIGINT",
                    "quantity": "INT", "unit_price": "INT"},
}

sql = """
SELECT o.customer_id,
       oi.quantity * oi.unit_price AS line_total
FROM   orders o
JOIN   order_items oi ON oi.order_id = o.id
"""

ast = sqlglot.parse_one(sql, dialect="postgres")
qualified = qualify(ast, schema=schema, dialect="postgres")

print(qualified.sql(pretty=True, dialect="postgres"))

-- Qualifier output — every column bound to its source table
SELECT
  "o"."customer_id" AS "customer_id",
  "oi"."quantity" * "oi"."unit_price" AS "line_total"
FROM "orders" AS "o"
JOIN "order_items" AS "oi"
  ON "oi"."order_id" = "o"."id"

Step-by-step explanation.

1. parse_one builds the AST: a Select node with two Column/Alias projections, a From on orders, and a Join on order_items. At this stage o and oi are just alias strings — nothing is resolved yet.
2. qualify(ast, schema=schema) is where the schema earns its keep. The qualifier consults the schema to confirm customer_id belongs to orders (via alias o) and that quantity/unit_price belong to order_items (via oi), then rewrites every reference to be explicit.
3. Bare column names get their alias prefix, and every projection gets an output alias (line_total). This is what makes the tree walkable for lineage: each output column now has an unambiguous name and a known source.
4. Had the query used SELECT *, the qualifier would have expanded it to the five underlying columns using the schema — the single most important thing qualify does that regex cannot.
5. The qualified Expression is the input to lineage attribution: customer_id maps to orders.customer_id (identity), and line_total maps to both order_items.quantity and order_items.unit_price (a transform).

Output.

Output column	Resolves to	Kind
customer_id	orders.customer_id	identity
line_total	order_items.quantity, order_items.unit_price	transform (arithmetic)

Rule of thumb. Always qualify before you attribute. An unqualified tree has bare names and unexpanded stars; a qualified tree has fully-bound columns, which is the only reliable input to column lineage.

Worked example — column lineage over a CTE with sqlglot.lineage

Detailed explanation. The lineage helper walks the qualified AST from a single output column down to its leaf source columns, threading through CTEs and subqueries. Walk through it for a query with a revenue CTE feeding a final projection, and read the returned Node tree.

• The query. A CTE revenue aggregates order_items; the outer query joins it to customers.
• The target. Trace the output column total back to its source columns.
• The expectation. total derives from order_items.quantity and order_items.unit_price, through the CTE.

Question. Use sqlglot.lineage("total", sql, schema) and walk the returned node tree to list the leaf source columns.

Input.

Object	Value
CTE	revenue(customer_id, total) from order_items
Outer	join customers to revenue
Target column	total

Code.

from sqlglot.lineage import lineage

schema = {
    "customers":   {"id": "BIGINT", "name": "TEXT"},
    "order_items": {"id": "BIGINT", "order_id": "BIGINT",
                    "customer_id": "BIGINT", "quantity": "INT", "unit_price": "INT"},
}

sql = """
WITH revenue AS (
    SELECT customer_id,
           SUM(quantity * unit_price) AS total
    FROM   order_items
    GROUP  BY customer_id
)
SELECT c.name, r.total
FROM   customers c
JOIN   revenue   r ON r.customer_id = c.id
"""

node = lineage("total", sql, schema=schema, dialect="postgres")

def leaves(n):
    """Recurse .downstream to collect leaf source columns."""
    if not n.downstream:
        return [n.name]
    out = []
    for d in n.downstream:
        out.extend(leaves(d))
    return out

print(node.name)          # -> total
print(leaves(node))       # -> ['order_items.quantity', 'order_items.unit_price']

Step-by-step explanation.

1. lineage("total", ...) parses and qualifies the query, then locates the output column total in the final SELECT. It returns the root Node for total.
2. total in the outer query is r.total, which points at the CTE revenue. The node's .downstream therefore contains the CTE's total expression — an intermediate node, not yet a leaf.
3. Inside the CTE, total is SUM(quantity * unit_price). Its .downstream splits into the two base columns order_items.quantity and order_items.unit_price — these have no further downstream, so they are leaves.
4. The recursive leaves() walk collects exactly those two fully-qualified source columns. The intermediate CTE node is skipped in the leaf list but is available on the tree if you want to render the full path for provenance.
5. The SUM(...) node tells you the edge kind is aggregate — a signal that a change to either source column changes an aggregated value, which is a silent-corruption risk, not just a structural one.

Output.

Target	Path	Leaf source columns
total	outer r.total → CTE SUM(quantity*unit_price)	order_items.quantity, order_items.unit_price

Rule of thumb. Treat sqlglot.lineage's Node tree as the source of truth for one output column: recurse .downstream to leaves for the edge list, but keep the intermediate nodes for provenance narratives and for reading the transformation kind off each hop.

Worked example — ambiguous columns and SELECT * traps

Detailed explanation. The two failure modes that break naive lineage are ambiguous bare columns in a join and unexpanded SELECT *. Both are resolved by qualifying against the schema; both silently corrupt a regex-based approach. Walk through a query that has both problems and show how qualify fixes them.

• Ambiguity. Both orders and customers have an id — a bare SELECT id is ambiguous.
• Star. SELECT * over a join expands to columns from both tables; the lineage for each must point at the correct source.
• The fix. qualify with the schema disambiguates id (raising if truly ambiguous) and expands * to the right per-table columns.

Question. Show what qualify does with an ambiguous column and a SELECT * over a two-table join, and what a regex approach would get wrong.

Input.

Trap	Query fragment	Regex result	Qualified result
Ambiguous id	SELECT id FROM orders o JOIN customers c …	guesses / picks first	error or bound to the correct table
SELECT *	SELECT * FROM orders o JOIN customers c …	one opaque *	explicit per-table column list

Code.

import sqlglot
from sqlglot.optimizer.qualify import qualify
from sqlglot.errors import OptimizeError

schema = {
    "orders":    {"id": "BIGINT", "customer_id": "BIGINT", "status": "TEXT"},
    "customers": {"id": "BIGINT", "name": "TEXT"},
}

# 1. SELECT * expands to the union of both tables' columns
star_sql = "SELECT * FROM orders o JOIN customers c ON c.id = o.customer_id"
star_q = qualify(sqlglot.parse_one(star_sql), schema=schema)
print(star_q.sql())
# SELECT "o"."id" AS "id", "o"."customer_id" AS "customer_id",
#        "o"."status" AS "status", "c"."id" AS "id",
#        "c"."name" AS "name"
# ... (qualify also disambiguates duplicate output names per dialect rules)

# 2. A genuinely ambiguous bare column raises instead of guessing
amb_sql = "SELECT id FROM orders o JOIN customers c ON c.id = o.customer_id"
try:
    qualify(sqlglot.parse_one(amb_sql), schema=schema)
except OptimizeError as e:
    print("caught ambiguity:", str(e).splitlines()[0])
    # -> caught ambiguity: Ambiguous column: id ...

Step-by-step explanation.

1. For SELECT *, the qualifier reads both tables from the schema and expands the star into an explicit projection: three columns from orders, two from customers, each prefixed with its alias. Lineage can now attribute each expanded column to exactly one source.
2. A regex approach sees a single * and has nothing to expand; it either drops the projection entirely (missing edges) or invents column names (wrong edges). Both silently corrupt the graph.
3. For the ambiguous bare id, qualify refuses to guess: because id exists in both orders and customers, it raises OptimizeError. This is correct behaviour — a lineage tool must surface ambiguity rather than pick a table and be silently wrong.
4. In CI, that raised error becomes a loud "this model has an ambiguous column; qualify it in the SQL" message — pushing the fix upstream into the model, which is where it belongs.
5. The general lesson: qualify converts every implicit construct (*, bare columns, aliases) into explicit, resolved references, and turns the un-resolvable cases into errors instead of silent wrong edges.

Output.

Trap	Qualified behaviour	Effect on the graph
SELECT *	expanded to 5 explicit columns	5 precise edges instead of 0 or garbage
Ambiguous id	raises OptimizeError	loud CI failure, no silent wrong edge

Rule of thumb. Let the qualifier be strict: expand every * and raise on genuine ambiguity rather than guessing. A lineage tool that guesses is worse than one that fails, because a wrong edge is trusted until it causes an incident.

Senior interview question on SQL parsing for lineage

A senior interviewer might ask: "You're given a folder of 800 SQL models across Postgres and Snowflake dialects, some with SELECT *, CTEs, and window functions. Build the per-model column-source extractor: for each model, output every result column mapped to its leaf source columns, tag each edge with its transformation kind, and make the tool fail loudly on any model it cannot parse."

Solution Using a schema-aware sqlglot extractor with a coverage gate

# extract_edges.py — per-model column-source extraction with coverage tracking
from __future__ import annotations
import sqlglot
from sqlglot import exp
from sqlglot.lineage import lineage
from sqlglot.errors import ParseError, OptimizeError

def transform_kind(node) -> str:
    """Classify the edge from an sqlglot lineage node's expression."""
    e = node.expression
    if isinstance(e, exp.AggFunc):
        return "aggregate"
    if isinstance(e, (exp.Func, exp.Case, exp.Binary)):
        return "transform"
    return "identity"

def output_columns(sql: str, dialect: str) -> list[str]:
    ast = sqlglot.parse_one(sql, dialect=dialect)
    select = ast.find(exp.Select)
    return [proj.alias_or_name for proj in select.expressions]

def extract_model_edges(model: str, sql: str, schema: dict, dialect: str):
    """Yield (source_col, output_col, transform) edges for one model."""
    edges = []
    for out_col in output_columns(sql, dialect):
        node = lineage(out_col, sql, schema=schema, dialect=dialect)
        stack = list(node.downstream)
        while stack:
            n = stack.pop()
            if n.downstream:                       # intermediate (CTE/subquery)
                stack.extend(n.downstream)
                continue
            if "." in n.name:                      # leaf = source table column
                edges.append((n.name, f"{model}.{out_col}", transform_kind(node)))
    return edges

def build_all(models: dict[str, tuple[str, str]], schema: dict):
    all_edges, failures = [], []
    for model, (sql, dialect) in models.items():
        try:
            all_edges.extend(extract_model_edges(model, sql, schema, dialect))
        except (ParseError, OptimizeError) as e:
            failures.append((model, str(e).splitlines()[0]))
    return all_edges, failures

if __name__ == "__main__":
    edges, failures = build_all(MODELS, SCHEMA)
    print(f"edges={len(edges)} parsed_ok={len(MODELS)-len(failures)} failed={len(failures)}")
    if failures:
        for m, err in failures:
            print(f"  PARSE FAIL {m}: {err}")
        raise SystemExit(1)          # coverage gate — fail the build

Step-by-step trace.

Step	What it does	Why it matters
output_columns	reads the final SELECT's projection names	the set of output columns to trace
lineage(out_col, …)	walks the qualified AST for one column	attributes it to leaf sources
stack walk	recurses .downstream past CTEs/subqueries	reaches real source-table columns
leaf test "." in name	keeps only fully-qualified source columns	drops literals and intermediates
transform_kind	classifies the AST expression	tags each edge identity/transform/aggregate
try/except + SystemExit(1)	catches parse/optimize errors	coverage gate: unparseable SQL fails loudly

Run over 800 models, the extractor emits an edge list like ("orders.total_cents", "revenue.gross", "aggregate"), prints edges=4120 parsed_ok=796 failed=4, and exits non-zero listing the four models that need a qualify fix — so the graph never silently omits a model.

Output:

Field	Example value
Edge source	order_items.unit_price
Edge target	revenue.gross
Transform	aggregate
Parse success	796 / 800
Build result	exit 1 (4 unparseable models listed)

Why this works — concept by concept:

• parse_one + Select projection — reading the final projection names gives the exact set of output columns to trace; nothing is inferred, it comes straight off the AST.
• sqlglot.lineage per output column — delegating the hard scope/CTE resolution to lineage means the extractor never re-implements alias binding or star expansion; it consumes a correct Node tree.
• downstream stack walk to leaves — recursing past intermediate CTE/subquery nodes yields only real source-table columns, which are the endpoints that belong in the graph.
• transform classification — reading exp.AggFunc / exp.Func / exp.Case off the node's expression tags each edge so downstream impact analysis can tell a silent aggregate change from a loud structural one.
• Cost — O(total SQL size) to parse plus O(output columns × path length) to trace; a few thousand models parse in seconds. The coverage gate's SystemExit(1) is the cheapest possible insurance against an invisible hole in the graph.

SQL
Topic — sql
SQL parsing and query-structure problems

Practice →

Transform Topic — data-transformation Data transformation and CTE problems

Practice →

---

3. Building the column-level lineage graph

Every column is a node, every derivation is an edge, and the whole thing must stay a DAG

The mental model in one line: the column-level lineage graph is a single directed acyclic graph (DAG) whose nodes are fully-qualified schema.table.column identifiers and whose edges point from a source column to a derived column — you build it by extracting per-model edges (section 2), unioning them into one networkx.DiGraph, tagging each edge with its transformation kind, and then asserting acyclicity, because a genuine cycle means either a parse error or a real circular dependency you must break before the graph is usable. The per-query extractor gives you edges one model at a time; the graph is what lets you ask global questions — reachability, topological order, blast radius — that no single query can answer.

What the nodes and edges are.

• Node = fully-qualified column. prod.orders.total_cents. The three-part (or more, with database/schema) name is the primary key of the graph; consistency here is everything, because a typo makes a column silently disconnected.
• Edge = derivation. A directed edge source_col → derived_col means "the derived column's value is computed from the source column." Direction is data-flow direction: upstream sources point to downstream derivations.
• Edge attributes. At minimum a transform kind (identity / transform / aggregate); optionally the model that created the edge, the expression text, and the run/commit that produced it. These attributes are what make impact analysis actionable rather than structural-only.
• Table nodes are derived, not primary. You can collapse the column graph to a table graph by projecting schema.table.column → schema.table; you can never go the other way. Build columns first; derive tables when you need the coarse view.

Composing per-model edges into one graph.

• Union, don't rebuild. Each model contributes edges from its source columns to its own output columns. Adding every model's edges to one DiGraph composes them: model B's source columns are model A's output columns, so the graphs stitch at shared nodes automatically.
• The stitch happens at shared node names. If model A outputs revenue.gross and model B reads revenue.gross, the edge into revenue.gross (from A) and the edge out of it (to B) share the exact node — which is why fully-qualified, canonical names matter so much.
• Missing intermediate models = broken chains. If B reads revenue.gross but you never parsed the model that builds revenue, the chain is severed and downstream reachability under-reports. Coverage (section 2) directly determines graph correctness here.
• One graph per environment. Build the graph from the SQL on the branch you are validating; a graph mixing main and a feature branch answers no question cleanly.

Keeping it acyclic — and what a cycle means.

• A DAG is the correctness invariant. Column lineage should never cycle: a value cannot derive from itself. networkx.is_directed_acyclic_graph(G) must return True.
• Real cycles are bugs. A cycle usually means a recursive CTE modelled naively, a mutual dependency between two models, or a self-reference through a view — surface it, don't silently collapse it.
• Topological order is the payoff. A DAG has a topological sort, which is the correct build order for the models and the order to walk when propagating a change forward. nx.topological_sort(G) gives it in O(V+E).
• Self-loops from UPDATE/MERGE. A column updated in place (SET x = x + 1) can look like a self-edge; model it as a versioned node or drop the self-loop, but decide deliberately — a stray self-loop breaks topological sort.

Storing and querying the graph.

• In-memory networkx for build-time checks. For a few thousand models, a DiGraph in memory answers descendants/ancestors/topo-sort in milliseconds — ideal for a CI gate.
• A graph database for the served catalog. For interactive exploration across a large org, persist nodes and edges into a property graph (Neo4j-style) or a catalog backend, and query reachability with graph traversals.
• Adjacency lists for portability. Serialise the DAG as an edge list (source, target, transform, model) — trivially diffable, versionable in git, and re-loadable into any graph library.
• Node metadata. Attach type, nullability, and owner to each node so impact analysis can reason about semantic breaks (a type change), not just structural ones.

Common interview probes on the lineage graph.

• "What are the nodes and edges?" — columns and derivations; edges carry a transform kind.
• "How do per-model results become one graph?" — union edges; they stitch at shared fully-qualified node names.
• "Why must it be a DAG?" — a value can't derive from itself; a cycle is a bug or a parse error.
• "How do you get the build order?" — topological sort of the DAG.

Worked example — extracting edges from one model

Detailed explanation. Start with the atomic unit: turning one model's extracted (source, target, transform) triples into graph edges with attributes. Walk through adding a single model's edges to a DiGraph and reading them back.

• The model. revenue selects customer_id (identity) and SUM(quantity*unit_price) AS gross (aggregate).
• The edges. order_items.customer_id → revenue.customer_id (identity); order_items.quantity → revenue.gross and order_items.unit_price → revenue.gross (aggregate).
• The attributes. Each edge stores transform and the owning model.

Question. Add the revenue model's edges to a DiGraph with attributes and read back the in-edges of revenue.gross.

Input.

Source column	Target column	Transform
order_items.customer_id	revenue.customer_id	identity
order_items.quantity	revenue.gross	aggregate
order_items.unit_price	revenue.gross	aggregate

Code.

import networkx as nx

def add_model_edges(G: nx.DiGraph, model: str, edges: list[tuple[str, str, str]]):
    for src, tgt, transform in edges:
        G.add_edge(src, tgt, transform=transform, model=model)

G = nx.DiGraph()
add_model_edges(G, "revenue", [
    ("order_items.customer_id", "revenue.customer_id", "identity"),
    ("order_items.quantity",    "revenue.gross",       "aggregate"),
    ("order_items.unit_price",  "revenue.gross",       "aggregate"),
])

# Read back the sources of revenue.gross
for src in G.predecessors("revenue.gross"):
    print(src, G.edges[src, "revenue.gross"])
# order_items.quantity   {'transform': 'aggregate', 'model': 'revenue'}
# order_items.unit_price {'transform': 'aggregate', 'model': 'revenue'}

Step-by-step explanation.

1. add_edge(src, tgt, ...) creates both nodes if they don't exist and the directed edge between them, attaching transform and model as edge attributes. No separate add_node call is needed.
2. revenue.gross ends up with two in-edges — one per source column of the SUM — both tagged aggregate. This is exactly the multi-source shape aggregates produce.
3. revenue.customer_id has a single identity in-edge, which impact analysis will later treat as a pass-through (a source drop hard-breaks it, but there is no value transformation to hide a semantic bug).
4. G.predecessors(node) reads upstream sources (the backward direction); G.successors(node) would read downstream derivations (the forward direction). Both are O(degree).
5. Storing model on the edge lets impact analysis report which file to edit for each affected edge — the actionability payoff of edge attributes.

Output.

Node	In-edges (sources)	Transform
revenue.customer_id	order_items.customer_id	identity
revenue.gross	order_items.quantity, order_items.unit_price	aggregate

Rule of thumb. Attach transform and model to every edge as you add it. The marginal cost is zero and it is the difference between "column X is affected" and "column X is affected via a SUM in models/revenue.sql, go edit that file."

Worked example — composing a multi-model DAG

Detailed explanation. The graph's power appears when models compose: revenue reads order_items, and customer_360 reads revenue. Walk through unioning two models' edges and confirming the chain stitches so a source column reaches a two-hops-downstream mart column.

• Model 1. revenue.gross from order_items (aggregate).
• Model 2. customer_360.lifetime_value from revenue.gross (identity pass-through).
• The stitch. revenue.gross is an output of model 1 and an input of model 2 — the shared node.

Question. Compose both models and confirm order_items.unit_price reaches customer_360.lifetime_value through revenue.gross.

Input.

Model	Edge
revenue	order_items.unit_price → revenue.gross (aggregate)
revenue	order_items.quantity → revenue.gross (aggregate)
customer_360	revenue.gross → customer_360.lifetime_value (identity)

Code.

import networkx as nx

G = nx.DiGraph()
add_model_edges(G, "revenue", [
    ("order_items.quantity",   "revenue.gross", "aggregate"),
    ("order_items.unit_price", "revenue.gross", "aggregate"),
])
add_model_edges(G, "customer_360", [
    ("revenue.gross", "customer_360.lifetime_value", "identity"),
])

# The chain stitches at the shared node revenue.gross
print(nx.has_path(G, "order_items.unit_price", "customer_360.lifetime_value"))
# -> True
print(nx.shortest_path(G, "order_items.unit_price", "customer_360.lifetime_value"))
# -> ['order_items.unit_price', 'revenue.gross', 'customer_360.lifetime_value']
print(nx.is_directed_acyclic_graph(G))
# -> True

Step-by-step explanation.

1. Adding model 1's edges creates revenue.gross with two aggregate in-edges. Adding model 2's edge reuses that exact same revenue.gross node as its source — the union stitches automatically because the node name matches.
2. has_path confirms order_items.unit_price reaches customer_360.lifetime_value: the transitive relationship spans two models even though neither model's SQL mentions the other's source.
3. shortest_path shows the actual chain through revenue.gross, which is the provenance path you would render to explain where lifetime_value comes from.
4. is_directed_acyclic_graph returns True — the composition introduced no cycle, so topological order and reachability queries are well-defined.
5. This is the whole reason to build one graph rather than reason per-query: the two-hop dependency is invisible in either model alone but obvious in the composed DAG.

Output.

Query	Result
has_path(unit_price → lifetime_value)	True
Path	order_items.unit_price → revenue.gross → customer_360.lifetime_value
is_directed_acyclic_graph	True

Rule of thumb. Compose by union and let shared fully-qualified node names do the stitching. The transitive dependencies that span models — the ones that cause surprise breakages — only become visible once every model's edges live in one DAG.

Worked example — cycle detection and topological build order

Detailed explanation. A correct lineage graph is acyclic; a cycle signals a real circular dependency or a modelling mistake you must surface. Walk through detecting a cycle, reporting it usefully, and — on a clean DAG — producing the topological build order.

• The clean case. Three models chain linearly → one valid topological order.
• The broken case. Two models mutually depend (A reads B, B reads A) → a 2-cycle that must be reported, not swallowed.
• The report. nx.find_cycle names the offending edges so an engineer can break the loop.

Question. Detect whether the graph is a DAG; if so, print the topological order; if not, print the cycle.

Input.

Case	Edges
Clean	a.x→b.x, b.x→c.x
Broken	a.x→b.x, b.x→a.x

Code.

import networkx as nx

def report(G: nx.DiGraph):
    if nx.is_directed_acyclic_graph(G):
        order = list(nx.topological_sort(G))
        print("DAG ok — build order:", order)
        return order
    cycle = nx.find_cycle(G, orientation="original")
    print("CYCLE detected:", [(u, v) for u, v, *_ in cycle])
    raise ValueError("lineage graph is not acyclic; break the cycle")

clean = nx.DiGraph([("a.x", "b.x"), ("b.x", "c.x")])
report(clean)
# DAG ok — build order: ['a.x', 'b.x', 'c.x']

broken = nx.DiGraph([("a.x", "b.x"), ("b.x", "a.x")])
try:
    report(broken)
except ValueError as e:
    print(e)
# CYCLE detected: [('a.x', 'b.x'), ('b.x', 'a.x')]
# lineage graph is not acyclic; break the cycle

Step-by-step explanation.

1. is_directed_acyclic_graph is the O(V+E) gate: it must pass before any topological or ordered-propagation logic runs.
2. On a clean graph, topological_sort yields nodes so that every edge points from an earlier node to a later one — this is simultaneously the correct model build order and the order to walk when propagating a forward change.
3. On the broken graph, find_cycle returns the exact edges forming the loop (a.x→b.x→a.x), which tells the engineer which two models mutually depend so they can break it.
4. Raising on a cycle (rather than picking an arbitrary order) is the same "fail loudly" discipline as the parse-coverage gate: a cycle is a real problem the graph must not paper over.
5. In practice most cycles trace to a view that references a table that references the view, or a manual edge added out of the parser's sight — the report points straight at it.

Output.

Case	Result
Clean	build order a.x → b.x → c.x
Broken	cycle a.x → b.x → a.x, build fails

Rule of thumb. Assert is_directed_acyclic_graph as the first thing you do after composing the graph, and report find_cycle's edges when it fails. A silent cycle corrupts topological order and turns forward propagation into an infinite loop.

Senior interview question on the lineage graph

A senior interviewer might ask: "You have per-model column edges for a 2,000-model project. Assemble the project-wide column DAG: union the edges, validate acyclicity, expose descendants and ancestors for any column, and produce a topological build order — and explain how you'd catch a model whose upstream was never parsed so the graph doesn't silently under-report."

Solution Using a networkx DiGraph with acyclicity and orphan checks

# build_graph.py — assemble and validate the project-wide column DAG
from __future__ import annotations
import networkx as nx

class LineageGraph:
    def __init__(self):
        self.G = nx.DiGraph()

    def add_model(self, model: str, edges: list[tuple[str, str, str]]):
        for src, tgt, transform in edges:
            self.G.add_edge(src, tgt, transform=transform, model=model)

    def validate(self) -> None:
        if not nx.is_directed_acyclic_graph(self.G):
            cyc = nx.find_cycle(self.G, orientation="original")
            raise ValueError(f"cycle: {[(u, v) for u, v, *_ in cyc]}")

    def orphans(self, known_sources: set[str]) -> list[str]:
        """Source-side nodes with no in-edges that are NOT known base tables.
        These are columns read from a model whose SQL was never parsed."""
        roots = [n for n in self.G if self.G.in_degree(n) == 0]
        return sorted(n for n in roots
                      if n.rsplit(".", 1)[0] not in known_sources)

    def descendants(self, col: str) -> set[str]:
        return nx.descendants(self.G, col)

    def ancestors(self, col: str) -> set[str]:
        return nx.ancestors(self.G, col)

    def build_order(self) -> list[str]:
        return list(nx.topological_sort(self.G))


if __name__ == "__main__":
    lg = LineageGraph()
    for model, edges in MODEL_EDGES.items():
        lg.add_model(model, edges)
    lg.validate()

    missing = lg.orphans(known_sources=BASE_TABLES)   # e.g. {'raw.orders', ...}
    if missing:
        print("ORPHAN roots (unparsed upstream?):", missing)
        raise SystemExit(1)

    print("nodes:", lg.G.number_of_nodes(), "edges:", lg.G.number_of_edges())
    print("build order (first 5):", lg.build_order()[:5])

Step-by-step trace.

Step	Operation	Result
add_model × 2000	union all per-model edges	one composed DiGraph
validate	is_directed_acyclic_graph	passes, else reports cycle edges
orphans	roots not in BASE_TABLES	flags columns whose upstream model was never parsed
descendants(col)	forward reachability	the impact set (section 4)
ancestors(col)	backward reachability	the provenance set (section 4)
build_order	topological sort	correct model build + propagation order

Run over 2,000 models, this assembles a DAG with ~30k column nodes and ~45k edges, validates acyclicity in milliseconds, and — critically — the orphans check flags any column that has no in-edge and is not a declared base table, which is exactly the fingerprint of an upstream model whose SQL never got parsed.

Output:

Metric	Value
Column nodes	~30,000
Edges	~45,000
Acyclic	True (validated)
Orphan roots flagged	0 (clean) or list + exit 1
Topo-sort time	milliseconds (O(V+E))

Why this works — concept by concept:

• DiGraph union — adding every model's edges into one directed graph stitches the project together at shared node names, turning per-query facts into global reachability.
• Acyclicity validation — is_directed_acyclic_graph guarantees topological order and finite forward propagation exist; find_cycle localises the bug when it doesn't.
• Orphan-root check — a root that isn't a declared base table can only mean its upstream model was never parsed; flagging it converts a silent coverage hole into a loud build failure.
• descendants / ancestors — nx.descendants and nx.ancestors are the two O(V+E) traversals that power impact analysis and provenance respectively — the whole reason the graph exists.
• Cost — construction is O(E); validation, topo-sort, and each reachability query are O(V+E). On a 30k-node sparse DAG every operation is sub-second, so the entire graph can be rebuilt and queried inside a PR check.

Design
Topic — design
Design problems on DAGs and dependency graphs

Practice →

Transform Topic — data-transformation Data transformation and model-composition problems

Practice →

---

4. Impact analysis and downstream dependencies

Walk the graph forward to see what breaks, backward to see where the value came from

The mental model in one line: impact analysis is forward reachability on the column DAG — the descendants of a changed column are exactly its downstream dependencies, the set that breaks if you change it — while provenance is backward reachability — the ancestors of a column are the source columns whose values flow into it — and both are single graph traversals (nx.descendants / nx.ancestors) that become genuinely useful once you filter them by transformation kind and consumer tier. Forward answers "what will this change hurt?"; backward answers "why is this number what it is?" A senior engineer reaches for the first before every schema change and the second during every data-quality incident.

Forward = impact, backward = provenance.

• Descendants = impact set. nx.descendants(G, col) returns every column reachable by following edges forward from col. That set is the precise list of downstream dependencies — the columns, and by projection the tables and dashboards, that a change to col can affect.
• Ancestors = provenance set. nx.ancestors(G, col) returns every column reachable backward. That is the full derivation history: every source column that feeds the value, useful for "why did this metric change?" and for audit/compliance evidence.
• The changed node itself. Impact analysis reports descendants excluding the changed node; provenance reports ancestors excluding it. Be explicit about inclusivity so counts are comparable across tools.
• Reachability, not adjacency. The naive mistake is reporting only direct successors (one hop). Real impact is transitive — a change three hops away still breaks — so you need reachability (descendants), not just G.successors.

Structural vs semantic breaks — filtering by transform kind.

• Structural break (loud). Drop or rename a column and every descendant reached through any edge fails at compile/build time — the query references a column that no longer exists. These are caught by the build; the risk is churn, not silent corruption.
• Semantic break (silent). Change a column's type, units, or nullability and nothing errors — but every descendant reached through a transform or aggregate edge may now compute a wrong value. These are the dangerous ones; the compiler never catches them.
• Filter the impact set by edge kind. For a semantic change, the descendants that need human review are those reached through non-identity edges. descendants gives the set; the edge attributes tell you which subset can silently corrupt.
• Identity chains are safer to re-point. A pure identity chain (rename passthrough) can often be fixed mechanically; a chain through a CASE or arithmetic expression needs a human to confirm the semantics still hold.

Making impact analysis actionable.

• Name the files. Because every edge carries its model, the impact set maps to a concrete list of model files a change forces you to touch — turn the column set into a file set for the PR description.
• Project to tables and dashboards. Collapse the descendant columns to their tables (col.rsplit(".",1)[0]) for a table-level summary, and intersect the leaf descendants with the set of columns that back dashboards/exports for a consumer summary.
• Diff, don't dump. On a PR, compute the impact only for the columns the PR changes, not the whole graph — a focused report ("these 3 changes touch these 7 downstream columns") beats a 30k-node dump.
• Attach severity. Combine reachability with tier tags: a 2-column impact that reaches a regulatory export outranks a 40-column impact confined to scratch models.

Provenance — the backward story.

• Root-cause a bad number. When a metric looks wrong, ancestors gives the exact upstream columns to inspect, ordered by the topological path — you check sources nearest the leaf first.
• Audit and compliance. Regulators ask "where does this reported figure come from?" Provenance is the machine-generated answer: the full chain from report column back to raw ingested columns.
• Path, not just set. For a readable explanation, render nx.shortest_path (or all simple paths, bounded) from a raw source to the metric so a human sees the hops, not an unordered set.
• Provenance validates impact. If a column's ancestors don't include the source you thought you were changing, your mental model of the dependency was wrong — provenance is the sanity check on impact.

Common interview probes on impact analysis.

• "How do you find everything a column affects?" — forward reachability: nx.descendants.
• "Direct dependents or transitive?" — transitive; a break three hops away still breaks.
• "How is a type change different from a drop?" — semantic (silent) vs structural (loud); filter the impact set by transform kind.
• "How do you trace a bad metric to its source?" — backward reachability: nx.ancestors, rendered as a path.

Worked example — what breaks if I drop this column

Detailed explanation. The canonical impact query: given a column about to be dropped, list the downstream columns, the tables they live in, and whether any tier-1 consumer is reached. Walk through it on a small graph where orders.status feeds two models and one dashboard.

• The change. DROP COLUMN orders.status.
• The graph. orders.status → revenue.is_settled (transform, CASE) → finance_dash.settled_rate (identity).
• The report. 2 downstream columns, 2 tables, tier-1 reached (finance).

Question. Compute the impact of dropping orders.status: the descendant columns, their tables, and any tier-1 hit.

Input.

Edge	Transform
orders.status → revenue.is_settled	transform (CASE)
revenue.is_settled → finance_dash.settled_rate	identity

Code.

import networkx as nx

TIER1 = {"finance_dash", "regulatory_export"}

G = nx.DiGraph()
G.add_edge("orders.status", "revenue.is_settled", transform="transform")
G.add_edge("revenue.is_settled", "finance_dash.settled_rate", transform="identity")

def impact(G, col):
    downstream = nx.descendants(G, col)
    tables = {c.rsplit(".", 1)[0] for c in downstream}
    tier1  = sorted({t for t in tables} & TIER1)
    return {
        "column": col,
        "downstream_columns": sorted(downstream),
        "downstream_tables": sorted(tables),
        "tier1_hit": tier1,
    }

from pprint import pprint
pprint(impact(G, "orders.status"))
# {'column': 'orders.status',
#  'downstream_columns': ['finance_dash.settled_rate', 'revenue.is_settled'],
#  'downstream_tables': ['finance_dash', 'revenue'],
#  'tier1_hit': ['finance_dash']}

Step-by-step explanation.

1. nx.descendants(G, "orders.status") walks forward and returns both revenue.is_settled and finance_dash.settled_rate — the transitive set, not just the one-hop successor.
2. Projecting each descendant column to its table (rsplit(".",1)[0]) yields the affected tables revenue and finance_dash — the coarse summary a reviewer skims first.
3. Intersecting the affected tables with the TIER1 allowlist finds finance_dash, so the report flags a tier-1 hit — this is what escalates the change from "notify" to "block."
4. The two-hop reach is the point: dropping orders.status breaks a dashboard that never mentions orders in its own SQL, because it reads revenue.is_settled, which reads orders.status. Only the composed graph reveals this.
5. Because the first edge is a transform (CASE), a reviewer knows the break is not a trivial rename — revenue.is_settled encodes logic over status, so re-pointing it requires understanding that logic.

Output.

Field	Value
Downstream columns	revenue.is_settled, finance_dash.settled_rate
Downstream tables	revenue, finance_dash
Tier-1 hit	finance_dash
Verdict	block pending finance sign-off

Rule of thumb. Impact = descendants, then project to tables and intersect with the tier-1 set. Report the transform kind of the first hop so the reviewer knows whether the break is a mechanical rename or encoded business logic.

Worked example — provenance of a dashboard metric

Detailed explanation. The backward query answers "where does this number come from?" Walk through tracing a dashboard metric back to its raw source columns and rendering the path a human can read during an incident.

• The metric. finance_dash.settled_rate looks wrong after a deploy.
• The graph. raw.orders.state → stage.orders.status → revenue.is_settled → finance_dash.settled_rate.
• The output. The ancestor set plus the ordered path from raw source to metric.

Question. Compute the provenance of finance_dash.settled_rate: its ancestor columns and the readable path from the raw source.

Input.

Edge	Transform
raw.orders.state → stage.orders.status	identity
stage.orders.status → revenue.is_settled	transform
revenue.is_settled → finance_dash.settled_rate	identity

Code.

import networkx as nx

G = nx.DiGraph()
G.add_edge("raw.orders.state",   "stage.orders.status",       transform="identity")
G.add_edge("stage.orders.status","revenue.is_settled",        transform="transform")
G.add_edge("revenue.is_settled", "finance_dash.settled_rate", transform="identity")

metric = "finance_dash.settled_rate"

ancestors = nx.ancestors(G, metric)
print(sorted(ancestors))
# ['raw.orders.state', 'revenue.is_settled', 'stage.orders.status']

# Ordered, readable path from the raw root to the metric
root = "raw.orders.state"
path = nx.shortest_path(G, root, metric)
print(" -> ".join(path))
# raw.orders.state -> stage.orders.status -> revenue.is_settled -> finance_dash.settled_rate

Step-by-step explanation.

1. nx.ancestors(G, metric) walks backward and returns every upstream column that feeds the metric — three columns spanning raw, stage, and mart layers.
2. The ancestor set is unordered; for an incident you want the path, so nx.shortest_path from the raw root renders the hops in derivation order.
3. Reading the path left-to-right, an on-call engineer inspects the transform hop (stage.orders.status → revenue.is_settled) first, because identity hops can't introduce a value change — only the transform can.
4. This is provenance as a debugging tool: instead of guessing which of dozens of upstream models changed, the engineer has the exact three-column, three-hop chain to audit.
5. The same path is the compliance answer to "trace this reported figure to its ingested source," generated mechanically from the graph rather than maintained by hand.

Output.

Field	Value
Ancestors	raw.orders.state, stage.orders.status, revenue.is_settled
Path	raw.orders.state → stage.orders.status → revenue.is_settled → finance_dash.settled_rate
First place to look	the transform hop into revenue.is_settled

Rule of thumb. Provenance = ancestors for the set, shortest_path for the readable story. During an incident, walk the path and inspect the transform hops first — identity hops cannot change a value, so they are rarely the culprit.

Worked example — a type-change ripple (the silent one)

Detailed explanation. The most dangerous change is semantic: retype orders.total_cents (integer cents) to orders.total (decimal dollars). Nothing errors; every downstream aggregate is now 100× off. Walk through isolating the silent subset of the impact — descendants reached through transform/aggregate edges — which is exactly the set a human must verify.

• The change. total_cents (int, cents) → total (numeric, dollars).
• The graph. orders.total_cents feeds an identity copy and two aggregates.
• The silent subset. The two aggregate descendants — a SUM and an AVG — now silently wrong.

Question. For the retype, separate the impact set into identity (mechanical) vs transform/aggregate (needs human review).

Input.

Edge	Transform
orders.total_cents → stage.total_cents	identity
orders.total_cents → revenue.gross	aggregate (SUM)
orders.total_cents → metrics.avg_order	aggregate (AVG)

Code.

import networkx as nx

G = nx.DiGraph()
G.add_edge("orders.total_cents", "stage.total_cents",  transform="identity")
G.add_edge("orders.total_cents", "revenue.gross",      transform="aggregate")
G.add_edge("orders.total_cents", "metrics.avg_order",  transform="aggregate")

def semantic_review_set(G, col):
    """Descendants reachable through at least one non-identity edge."""
    needs_review, mechanical = set(), set()
    for d in nx.descendants(G, col):
        # inspect every simple path's edge kinds (bounded graphs)
        risky = False
        for path in nx.all_simple_paths(G, col, d):
            kinds = [G.edges[u, v]["transform"] for u, v in zip(path, path[1:])]
            if any(k != "identity" for k in kinds):
                risky = True
                break
        (needs_review if risky else mechanical).add(d)
    return needs_review, mechanical

review, mechanical = semantic_review_set(G, "orders.total_cents")
print("needs human review:", sorted(review))
# ['metrics.avg_order', 'revenue.gross']
print("mechanical / identity:", sorted(mechanical))
# ['stage.total_cents']

Step-by-step explanation.

1. All three descendants are in the impact set, but they are not equally dangerous. stage.total_cents is a pure identity copy — a units change flows through unchanged and is easy to reason about.
2. revenue.gross (a SUM) and metrics.avg_order (an AVG) are reached through aggregate edges. After the cents→dollars retype, SUM is now summing dollars where downstream consumers expect cents — silently 100× off with no error.
3. semantic_review_set walks the edge kinds along each path and classifies a descendant as "needs review" if any hop is non-identity. This isolates the exact columns a human must re-verify for a semantic change.
4. Splitting the impact this way is what makes a type change tractable: instead of "12 downstream columns, good luck," the report says "1 mechanical, 2 need human review — here they are."
5. all_simple_paths is fine on the small, sparse neighbourhoods around a single column; for very high-degree nodes, cap path length or use edge-kind propagation to stay linear.

Output.

Descendant	Reached via	Verdict
stage.total_cents	identity	mechanical
revenue.gross	aggregate (SUM)	human review — silent 100× risk
metrics.avg_order	aggregate (AVG)	human review — silent scale risk

Rule of thumb. For semantic changes, don't just count descendants — partition them by whether any path hop is non-identity. The transform/aggregate subset is where a units or precision change silently corrupts numbers, and it is the only subset that truly needs human eyes.

Senior interview question on impact analysis

A senior interviewer might ask: "Wire column lineage into your PR pipeline. For every column a PR changes, report the downstream impact — columns, tables, tier-1 consumers — distinguish structural from semantic changes, and fail the build when a semantic change silently reaches a tier-1 aggregate. Walk me through the CLI and how a reviewer reads its output."

Solution Using a PR-time impact CLI with structural/semantic classification

# impact_cli.py — PR-time column impact gate
from __future__ import annotations
import sys
import networkx as nx

TIER1 = {"finance_dash", "regulatory_export", "exec_kpi"}

def load_graph() -> nx.DiGraph:               # from build_graph.py (section 3)
    ...

def changed(col_change: dict) -> str:         # {"column": ..., "kind": "structural"|"semantic"}
    return col_change["kind"]

def path_is_risky(G, src, dst) -> bool:
    for path in nx.all_simple_paths(G, src, dst, cutoff=12):
        if any(G.edges[u, v]["transform"] != "identity" for u, v in zip(path, path[1:])):
            return True
    return False

def analyze(G, change: dict) -> dict:
    col, kind = change["column"], change["kind"]
    if col not in G:
        return {"column": col, "downstream_columns": 0, "block": False}
    downstream = nx.descendants(G, col)
    tables = {c.rsplit(".", 1)[0] for c in downstream}
    tier1  = sorted(tables & TIER1)
    silent = [d for d in downstream
              if kind == "semantic" and d.rsplit(".",1)[0] in TIER1
              and path_is_risky(G, col, d)]
    return {
        "column": col, "kind": kind,
        "downstream_columns": len(downstream),
        "downstream_tables": sorted(tables),
        "tier1_hit": tier1,
        "silent_tier1": silent,
        "block": bool(silent) or (kind == "structural" and bool(tier1)),
    }

def main(changes: list[dict]) -> int:
    G = load_graph()
    blocked = False
    for ch in changes:
        r = analyze(G, ch)
        print(r)
        blocked |= r["block"]
    if blocked:
        print("\nBLOCKED: a change reaches a tier-1 consumer.")
        return 1
    return 0

if __name__ == "__main__":
    sys.exit(main(CHANGES_FROM_PR_DIFF))

Step-by-step trace.

Step	Operation	Result
load graph	rebuild DAG from branch SQL	fresh graph, never stale
descendants(col)	forward reachability	full impact set per changed column
project to tables	rsplit(".",1)[0]	table-level summary + tier-1 intersect
path_is_risky	any non-identity hop on a path	isolates silent-corruption reach
classify	structural vs semantic	loud vs silent break
gate	block if semantic+silent+tier-1, or structural+tier-1	non-zero exit fails the PR

For a PR that retypes orders.total_cents, the CLI prints downstream_columns=7, tier1_hit=['finance_dash'], silent_tier1=['finance_dash.net_revenue'] and exits 1 — the reviewer sees the exact silently-corrupting column and knows finance must sign off; a PR that renames a scratch column prints its impact and exits 0.

Output:

Change	Report	Gate
retype total_cents (semantic)	7 downstream, silent tier-1 hit	BLOCK (exit 1)
rename scratch.tmp (structural)	2 downstream, no tier-1	PASS (exit 0)
drop unused orders.note	0 downstream	PASS (exit 0)

Why this works — concept by concept:

• descendants for impact — forward reachability returns the complete transitive dependency set, so a break three hops away is counted, not missed.
• structural vs semantic classification — separating loud (compiler-caught) from silent (value-corrupting) changes focuses the gate on the failures CI would otherwise never see.
• path_is_risky on non-identity hops — checking whether any path hop is a transform/aggregate isolates exactly the descendants a semantic change can silently corrupt, with a cutoff to bound path enumeration.
• tier-1 intersection — gating on reach into a tier-1 allowlist makes the check block the changes that matter and wave through the ones that don't.
• Cost — one descendants call is O(V+E); bounded all_simple_paths is the only super-linear piece and is capped by cutoff and confined to the changed columns' neighbourhoods, keeping the PR check within seconds.

Validation
Topic — data-validation
Data validation and impact-check problems

Practice →

Design Topic — design Design problems on dependency and impact systems

Practice →

---

5. Blast-radius mapping and OpenLineage

Turn "this feels risky" into a ranked number, then emit the graph as a standard facet

The mental model in one line: blast radius is the size and shape of a change's descendant set — how many columns, tables, and consumers it reaches, how deep the ripple goes, and how much of that reach lands on critical (tier-1) systems — reduced to a comparable score you can rank changes by and gate CI on; and OpenLineage is the open standard that lets you emit the column graph (via the columnLineage dataset facet) so it composes with runtime lineage instead of living in a bespoke silo. Blast radius is what makes lineage a prioritisation tool: given ten proposed migrations, it tells you which one to do first and which one needs the most review. OpenLineage is what makes your graph interoperable: a lingua franca that Marquez, DataHub, dbt, Airflow, and Spark all speak.

What blast radius measures.

• Count. The cardinality of the descendant set: how many downstream columns depend on the changed column. The first-order size of the ripple.
• Shape. The projection of that set onto tables, dashboards, and consumer tiers — a 20-column blast confined to one scratch model is smaller in shape than a 3-column blast that reaches a regulatory export.
• Depth. The longest path from the changed column to a leaf — deep ripples are harder to reason about and more likely to hide a silent transform.
• Criticality. How much of the reach lands on tier-1 systems. This is the dimension that should dominate the score, because one tier-1 hit outweighs dozens of scratch columns.

Scoring and ranking changes.

• A single comparable score. Reduce the descendant set to a weighted score: Σ weight(consumer_tier) over reached leaves, plus small terms for column count and depth. A scalar lets you sort candidate changes.
• Rank migrations by blast radius. Given a backlog of schema changes, do the low-blast-radius ones first (fast, safe wins) and reserve review budget for the high-blast-radius ones. This is portfolio management for schema evolution.
• Rank models by fan-out. The columns with the largest descendant sets are your load-bearing columns; they deserve the most tests, the clearest contracts, and the loudest change alarms.
• Gate on the score, not the count. A CI gate keyed on weighted blast radius (rather than raw column count) blocks the changes that actually matter and lets high-count-but-low-criticality changes through.

OpenLineage — the interchange standard.

• What it is. An open spec for lineage metadata: RunEvents carry a job, a run, input datasets, and output datasets; datasets carry facets — typed, versioned metadata blocks.
• The columnLineage facet. An output-dataset facet whose fields map each output column name to a list of inputFields (namespace + dataset + field), each annotated with a transformationType / transformations describing how the input contributed (identity/direct vs aggregation/indirect).
• Emit from your parse-time graph. Your sqlglot-derived edges map one-to-one onto the facet: output column → its source columns + transform kind. Emitting the facet publishes your static lineage into any OpenLineage backend.
• Consume for runtime lineage. Airflow, Spark, dbt, and Flink integrations emit OpenLineage events at run time; ingesting them into Marquez/DataHub plus your parse-time facets gives both the "what the code says" and "what actually ran" views.

Static parse-time vs runtime lineage — you want both.

• Parse-time (static). Built from SQL by sqlglot before anything runs. Complete for everything you can parse; available in CI; blind to dynamic SQL and to what actually executed.
• Runtime (dynamic). Emitted by orchestrators/engines as jobs run. Captures dynamic SQL and real execution; incomplete for paths that didn't run in the observed window.
• They complement. Static lineage gates the PR (before merge); runtime lineage verifies production reality and catches the dynamic tail. OpenLineage is the shared format that lets you merge them.
• Reconcile the two. An edge present in static but never seen at runtime may be dead code; an edge seen at runtime but absent from static is a parser coverage gap — both are actionable signals.

Common interview probes on blast radius and OpenLineage.

• "How do you prioritise which schema change to do first?" — rank by weighted blast radius.
• "What makes one change riskier than another with the same column count?" — shape and criticality: tier-1 reach, depth.
• "What is OpenLineage and its columnLineage facet?" — the standard event format; output field → input fields with transform type.
• "Static vs runtime lineage?" — parse-time gates PRs; runtime verifies production; OpenLineage merges them.

Worked example — compute and rank blast radius

Detailed explanation. Given a set of candidate column changes, compute each one's blast radius and rank them so the team knows what to schedule first and what to scrutinise. Walk through scoring three candidate changes on one graph.

• The candidates. Changes to orders.status, orders.note, and orders.total_cents.
• The metric. Descendant count, table count, and tier-1 reach per candidate.
• The ranking. Highest tier-1 reach first (most review), empty descendants last (safest).

Question. Compute blast radius for the three candidate columns and rank them from most to least risky.

Input.

Candidate	Descendants	Tier-1 reached
orders.total_cents	7	finance_dash
orders.status	3	finance_dash
orders.note	0	none

Code.

import networkx as nx

TIER1 = {"finance_dash", "regulatory_export", "exec_kpi"}

def blast_radius(G, col):
    d = nx.descendants(G, col) if col in G else set()
    tables = {c.rsplit(".", 1)[0] for c in d}
    depth = max((len(nx.shortest_path(G, col, x)) - 1 for x in d), default=0)
    return {
        "column": col,
        "columns": len(d),
        "tables": len(tables),
        "tier1": sorted(tables & TIER1),
        "depth": depth,
    }

def rank(G, candidates):
    scored = [blast_radius(G, c) for c in candidates]
    # sort key: tier-1 reach first, then column count, then depth
    scored.sort(key=lambda r: (len(r["tier1"]), r["columns"], r["depth"]),
                reverse=True)
    return scored

for r in rank(G, ["orders.total_cents", "orders.status", "orders.note"]):
    print(r)
# {'column': 'orders.total_cents', 'columns': 7, 'tables': 4, 'tier1': ['finance_dash'], 'depth': 3}
# {'column': 'orders.status',      'columns': 3, 'tables': 2, 'tier1': ['finance_dash'], 'depth': 2}
# {'column': 'orders.note',        'columns': 0, 'tables': 0, 'tier1': [],               'depth': 0}

Step-by-step explanation.

1. blast_radius reduces a column's descendant set to four comparable numbers: column count, table count, tier-1 reach, and depth. Each is a cheap graph query.
2. depth is the longest shortest-path to any descendant — a proxy for how far the ripple travels and how many transform hops could hide a silent bug.
3. rank sorts candidates by a tuple key: tier-1 reach dominates, then column count, then depth. This encodes the policy "criticality first, size second."
4. orders.note scores zero on everything — a free change, schedule it whenever. orders.total_cents tops the list with 7 columns and a tier-1 hit — the one that needs the most review and the most careful rollout.
5. The ranking turns an unordered backlog into a schedule: safe changes ship immediately; risky ones get review budget proportional to their blast radius.

Output.

Rank	Column	Columns	Tier-1	Verdict
1	orders.total_cents	7	finance_dash	most review
2	orders.status	3	finance_dash	review
3	orders.note	0	—	free

Rule of thumb. Reduce blast radius to a sortable tuple with tier-1 reach first, then rank the backlog. The point of a number is to order work — ship the zero-blast changes now and spend review budget where the ranking says the risk actually is.

Worked example — weighted blast radius by consumer tier

Detailed explanation. A raw column count treats a scratch model and a regulatory export as equal — they are not. Weight each reached leaf by its consumer tier so the score reflects real risk. Walk through a weighted score where tier-1 leaves count for far more than scratch leaves.

• The weights. tier-1 = 100, tier-2 = 10, scratch = 1.
• The change. A column reaching one tier-1 leaf and five scratch leaves.
• The insight. The single tier-1 leaf dominates the score, as it should.

Question. Compute a weighted blast-radius score that makes one tier-1 leaf outweigh many scratch leaves.

Input.

Reached leaf	Tier	Weight
regulatory_export.figure	tier-1	100
scratch.a … scratch.e (×5)	scratch	1 each

Code.

import networkx as nx

WEIGHT = {"tier1": 100, "tier2": 10, "scratch": 1}

def tier_of(column: str, tiers: dict[str, str]) -> str:
    table = column.rsplit(".", 1)[0]
    return tiers.get(table, "scratch")

def weighted_blast(G, col, tiers) -> dict:
    leaves = [d for d in nx.descendants(G, col) if G.out_degree(d) == 0]
    score = sum(WEIGHT[tier_of(l, tiers)] for l in leaves)
    breakdown = {}
    for l in leaves:
        breakdown[tier_of(l, tiers)] = breakdown.get(tier_of(l, tiers), 0) + 1
    return {"column": col, "score": score, "leaf_tiers": breakdown}

tiers = {"regulatory_export": "tier1"}   # everything else defaults to scratch
print(weighted_blast(G, "orders.amount", tiers))
# {'column': 'orders.amount', 'score': 105, 'leaf_tiers': {'tier1': 1, 'scratch': 5}}

Step-by-step explanation.

1. weighted_blast looks only at leaf descendants (out_degree == 0) — the terminal consumers (dashboards, exports), not the intermediate models — because those are the things people actually read.
2. Each leaf's tier is resolved from a tiers map; anything unlisted defaults to scratch. The weight table makes tier-1 worth 100× a scratch leaf.
3. The score of 105 = one tier-1 leaf (100) + five scratch leaves (5). A change touching a hundred scratch columns would score 100 — less than this one, correctly, because it touches nothing critical.
4. The leaf_tiers breakdown explains the score: one tier-1, five scratch. Transparency matters so a reviewer trusts the number rather than treating it as a black box.
5. This weighting is what lets a CI gate say "block above score 100": it blocks anything reaching a tier-1 leaf while ignoring large-but-harmless scratch churn.

Output.

Metric	Value
Weighted score	105
Tier-1 leaves	1 (regulatory_export.figure)
Scratch leaves	5
Interpretation	one critical hit dominates; gate should block

Rule of thumb. Weight blast radius by consumer tier and score only the leaf consumers. A single regulatory or finance leaf must outweigh a hundred scratch columns, or your gate will wave through the one change that can actually hurt you.

Worked example — emit an OpenLineage columnLineage facet

Detailed explanation. Your sqlglot-derived edges map directly onto the OpenLineage columnLineage facet: each output column lists its input fields and the transformation kind. Walk through building the facet for one output dataset and emitting it in a RunEvent.

• The output dataset. prod.revenue with column gross.
• The inputs. prod.order_items.quantity and prod.order_items.unit_price, both via an aggregation.
• The facet. fields.gross.inputFields = [ {quantity, AGGREGATION}, {unit_price, AGGREGATION} ].

Question. Build the OpenLineage columnLineage facet for revenue.gross from the graph edges and attach it to an output dataset.

Input.

Output field	Input dataset	Input field	Transform
gross	prod.order_items	quantity	aggregate
gross	prod.order_items	unit_price	aggregate

Code.

# Build the OpenLineage columnLineage facet from graph edges
NAMESPACE = "warehouse://prod"
TRANSFORM_MAP = {"identity": "IDENTITY", "transform": "TRANSFORMATION", "aggregate": "AGGREGATION"}

def column_lineage_facet(G, output_dataset: str) -> dict:
    """Emit the OpenLineage ColumnLineageDatasetFacet for one output table."""
    fields = {}
    out_cols = [n for n in G if n.rsplit(".", 1)[0] == output_dataset]
    for col in out_cols:
        field = col.rsplit(".", 1)[1]
        input_fields = []
        for src in G.predecessors(col):
            src_ds, src_field = src.rsplit(".", 1)
            kind = G.edges[src, col]["transform"]
            input_fields.append({
                "namespace": NAMESPACE,
                "name": src_ds,
                "field": src_field,
                "transformations": [{
                    "type": "DIRECT" if kind != "aggregate" else "INDIRECT",
                    "subtype": TRANSFORM_MAP[kind],
                }],
            })
        if input_fields:
            fields[field] = {"inputFields": input_fields}

    return {
        "_producer": "https://github.com/OpenLineage/OpenLineage",
        "_schemaURL": "https://openlineage.io/spec/facets/1-2-0/ColumnLineageDatasetFacet.json",
        "fields": fields,
    }

facet = column_lineage_facet(G, "prod.revenue")
import json
print(json.dumps(facet, indent=2))

{
  "_producer": "https://github.com/OpenLineage/OpenLineage",
  "_schemaURL": "https://openlineage.io/spec/facets/1-2-0/ColumnLineageDatasetFacet.json",
  "fields": {
    "gross": {
      "inputFields": [
        {"namespace": "warehouse://prod", "name": "prod.order_items", "field": "quantity",
         "transformations": [{"type": "INDIRECT", "subtype": "AGGREGATION"}]},
        {"namespace": "warehouse://prod", "name": "prod.order_items", "field": "unit_price",
         "transformations": [{"type": "INDIRECT", "subtype": "AGGREGATION"}]}
      ]
    }
  }
}

Step-by-step explanation.

1. The facet's fields object is keyed by output column name; for prod.revenue that is gross. Each entry lists the inputFields that produced it.
2. Each input field carries namespace + dataset name + field — the OpenLineage way of naming a source column — plus a transformations block describing how it contributed.
3. The transform kind on the graph edge maps onto OpenLineage's vocabulary: identity/transform become DIRECT transformations, aggregates become INDIRECT (the input influences the output but not row-for-row). This is exactly the distinction impact analysis needed.
4. Because the facet is derived mechanically from the same edges your PR gate uses, the emitted lineage and the enforced lineage can never drift — one source of truth.
5. Attach this facet to the output dataset inside an OpenLineage RunEvent and post it to a backend (Marquez, DataHub); the graph is now visible to every OpenLineage-aware tool in the org.

Output.

Facet field	Value
Output column	gross
Input fields	order_items.quantity, order_items.unit_price
Transformation	INDIRECT / AGGREGATION
Schema URL	ColumnLineageDatasetFacet 1-2-0

Rule of thumb. Generate the OpenLineage columnLineage facet from the same edge list that drives your PR gate. Deriving both from one graph guarantees your published lineage and your enforced lineage are identical — no drift between what you show and what you check.

Senior interview question on blast radius and OpenLineage

A senior interviewer might ask: "Ship the blast-radius layer on top of your column DAG. For a PR, compute a weighted blast-radius score per changed column, gate the merge on a threshold, and also emit the affected outputs' OpenLineage columnLineage facets so the org catalog stays in sync. Walk me through the report, the gate, and how static parse-time lineage reconciles with runtime lineage from Airflow."

Solution Using a weighted blast-radius gate plus an OpenLineage emitter

# blast_gate.py — weighted blast-radius gate + OpenLineage emission
from __future__ import annotations
import sys, json
import networkx as nx

WEIGHT = {"tier1": 100, "tier2": 10, "scratch": 1}
THRESHOLD = 100      # block at or above one tier-1 leaf

def load_graph() -> nx.DiGraph: ...          # section 3
def tiers_map() -> dict[str, str]: ...       # {table: tier}
def changed_columns() -> list[str]: ...      # PR diff

def score(G, col, tiers) -> dict:
    if col not in G:
        return {"column": col, "score": 0, "outputs": []}
    leaves = [d for d in nx.descendants(G, col) if G.out_degree(d) == 0]
    s = sum(WEIGHT[tiers.get(l.rsplit('.',1)[0], "scratch")] for l in leaves)
    affected_datasets = sorted({d.rsplit(".", 1)[0] for d in nx.descendants(G, col)})
    return {"column": col, "score": s, "outputs": affected_datasets}

def main() -> int:
    G, tiers = load_graph(), tiers_map()
    blocked, facets = False, {}
    for col in changed_columns():
        r = score(G, col, tiers)
        print(r)
        blocked |= r["score"] >= THRESHOLD
        for ds in r["outputs"]:
            facets[ds] = column_lineage_facet(G, ds)   # from previous example
    # publish facets to the OpenLineage backend (Marquez/DataHub)
    emit_openlineage(facets)                            # POST RunEvent(s)
    if blocked:
        print(f"\nBLOCKED: blast-radius score >= {THRESHOLD} (tier-1 reached).")
        return 1
    return 0

if __name__ == "__main__":
    sys.exit(main())

Step-by-step trace.

Step	Operation	Result
load graph	rebuild DAG from branch SQL	fresh, per-PR
score per changed col	weighted leaf sum	comparable blast-radius number
threshold gate	score >= 100	blocks any tier-1 reach
collect outputs	affected downstream datasets	which tables to re-emit
column_lineage_facet	build facet per output	OpenLineage payload
emit_openlineage	POST RunEvent(s)	catalog stays in sync with code

For a PR touching orders.total_cents, the gate prints score=100+, outputs=['prod.revenue', 'prod.finance_dash', ...], blocks the merge, and simultaneously publishes refreshed columnLineage facets for every affected output — so the org catalog reflects the change the moment it is proposed, and the reviewer sees a number, not a hunch.

Output:

Concern	Mechanism	Result
Prioritise / gate	weighted blast-radius score vs threshold	risky changes blocked; safe ones pass
Catalog freshness	emit columnLineage facets on every run	catalog never lags code
Static vs runtime	parse-time facets + Airflow OpenLineage events	dead-code and coverage gaps surfaced
Interop	OpenLineage standard	Marquez / DataHub consume it directly

Why this works — concept by concept:

• Weighted leaf scoring — summing tier weights over leaf consumers produces a number where one tier-1 export dominates hundreds of scratch columns, so the gate blocks what actually matters.
• Threshold gate — a single comparable score reduces "is this risky?" to one comparison, which is enforceable in CI and explainable to a reviewer.
• Facet emission from the same graph — building OpenLineage facets from the identical edge list that drives the gate means published and enforced lineage can never diverge.
• Static + runtime reconciliation — parse-time facets gate the PR while Airflow/Spark OpenLineage events verify production; an edge in one but not the other flags dead code or a coverage gap.
• Cost — one descendants per changed column (O(V+E)) plus O(affected outputs × degree) to build facets; both are seconds on a sparse DAG, and emission is a handful of HTTP POSTs — cheap enough to run on every PR.

Design
Topic — design
Design problems on blast-radius and catalog systems

Practice →

Optimization
Topic — optimization
Optimization problems on graph traversal and ranking

Practice →

---

Cheat sheet — column-level lineage recipes

• Table vs column lineage. Table lineage (edge = "A reads B") is commodity, over-reports for change safety, and is derivable from schedulers without parsing. Column lineage (edge = "A.col reads B.col") is the moat: it needs real SQL parsing, and it collapses a false "22 tables depend on this" into the real "2 columns depend on this." Always add a transform kind (identity / transform / aggregate) to each edge so you know not just what breaks but how.
• Parse, don't regex. sqlglot.parse_one(sql, dialect=...) → AST; then qualify(ast, schema=schema) to expand SELECT *, bind aliases, and resolve every column to schema.table.column. Qualify requires the schema ({table: {col: type}}, built from INFORMATION_SCHEMA). Regex cannot expand stars, resolve aliases, handle nested scopes, or read dialects — it silently corrupts the graph.
• Attribute output columns. sqlglot.lineage(output_col, sql, schema=...) returns a Node tree; recurse .downstream to the leaves ("." in name) for the source columns, keep intermediate CTE/subquery nodes for provenance, and read the transform kind off node.expression (exp.AggFunc → aggregate, exp.Func/exp.Case/exp.Binary → transform, else identity).
• Coverage gate. Wrap every parse in try/except, count failures, and raise SystemExit(1) on any unparseable model (or an explicit allowlist). A graph built while silently dropping 5% of models has invisible holes — the worst failure mode because it looks complete. Track dialect coverage as a metric.
• Build the DAG. Union per-model edges into one networkx.DiGraph with add_edge(src, tgt, transform=..., model=...); graphs stitch automatically at shared fully-qualified node names. Assert nx.is_directed_acyclic_graph(G) and report nx.find_cycle(G) on failure. Flag orphan roots (in-degree 0 that aren't declared base tables) — they mean an upstream model was never parsed.
• Impact = forward, provenance = backward. nx.descendants(G, col) = the transitive downstream dependency set (what breaks). nx.ancestors(G, col) = the provenance set (where the value came from). Use nx.shortest_path to render a readable provenance chain. Reachability, not one-hop adjacency — a break three hops away still breaks.
• Structural vs semantic. Structural changes (drop / rename) fail loudly at build time. Semantic changes (retype, units, nullability) fail silently — every descendant reached through a transform/aggregate edge can compute wrong values with no error. For semantic changes, partition the impact set by whether any path hop is non-identity; that subset needs human review.
• Blast radius. Reduce a descendant set to a sortable tuple: tier-1 reach first, then column count, then depth (len(shortest_path)-1). Weight leaf consumers by tier (tier1=100, tier2=10, scratch=1) so one regulatory export outweighs a hundred scratch columns. Rank the backlog by score; gate CI on the score, not the raw count.
• PR gate recipe. On every PR: rebuild the DAG from branch SQL (never stale), diff to the changed columns, compute impact + weighted blast radius, and exit 1 when a semantic change silently reaches a tier-1 aggregate or the score crosses the threshold. Report the affected files (from each edge's model attribute), not just column names.
• OpenLineage columnLineage facet. Output-dataset facet: fields.<out_col>.inputFields = [{namespace, name, field, transformations:[{type: DIRECT|INDIRECT, subtype: IDENTITY|TRANSFORMATION|AGGREGATION}]}], with _schemaURL pointing at ColumnLineageDatasetFacet. Derive it from the same edge list that drives the gate so published and enforced lineage never drift; POST it in a RunEvent to Marquez / DataHub.
• Static + runtime. Parse-time (static) lineage from sqlglot is complete-for-parseable and gates the PR before merge; runtime lineage (Airflow / Spark / dbt OpenLineage events) captures dynamic SQL and real execution. Reconcile them: static-only edges may be dead code; runtime-only edges are parser coverage gaps.
• Pitfalls. Bare/ambiguous columns (qualify raises — fix it in the model, don't guess); SELECT * (expand via schema or lose edges); UDFs / dynamic SQL / stored procs (record as coverage gaps, not silent drops); cents→dollars retypes (the classic silent 100× aggregate bug); node-name typos (silently disconnect a column). Canonical, fully-qualified node names are the whole game.

Frequently asked questions

What is column-level lineage and how is it different from table-level lineage?

Column-level lineage is a directed graph whose nodes are individual columns (schema.table.column) and whose edges are the derivations that produce one column from others — so it can answer "exactly which columns depend on orders.total_cents?" with column precision. Table-level lineage draws a single edge from orders to any table that reads any of its columns; it is cheap (schedulers already know table reads) but over-reports wildly for change safety, telling you 22 tables depend on orders when the column you are changing feeds only 2 of them. The practical difference is trust: because column lineage names the exact dependents, reviewers act on it, whereas coarse table lineage trains people to ignore it. Column lineage is the granularity you need to reason about a drop, a rename, or a type change safely.

How do you build column-level lineage from SQL?

You parse each model's SQL into an abstract syntax tree (AST) rather than pattern-matching text, qualify that tree against the warehouse schema so every SELECT * is expanded and every alias and bare column resolves to a fully-qualified schema.table.column, and then attribute each output column to its leaf source columns. In practice sqlglot.parse_one builds the AST, qualify(ast, schema=...) resolves it, and sqlglot.lineage(output_column, sql, schema=...) walks the tree from an output column to its sources — each output → source pair becomes one edge, tagged with the transformation kind read off the AST. Union every model's edges into one networkx DAG and the per-query facts compose into a graph you can traverse globally. The single most important discipline is failing loudly on SQL you cannot parse, so the graph never has invisible holes.

What is sqlglot and why use it for lineage?

sqlglot is a pure-Python SQL parser and transpiler that reads roughly twenty dialects into one common AST and ships a built-in lineage helper plus a schema-aware qualify optimizer. It is the default building block for column lineage because the hard parts — expanding SELECT * from a schema, binding aliases, resolving columns across nested CTE and subquery scopes, and handling dialect quirks like Snowflake QUALIFY or BigQuery STRUCT — are exactly what a real grammar solves and a regex cannot. Using sqlglot means you consume a correct Node tree for each output column instead of re-implementing alias resolution and star expansion yourself. It runs entirely in-process with no database connection, which is what lets you compute lineage at parse time inside a CI check.

What are impact analysis and blast radius in data lineage?

Impact analysis is forward reachability on the lineage graph: the descendants of a column (nx.descendants) are its downstream dependencies — the precise set of columns, and by projection tables and dashboards, that a change to it can affect. Blast radius is the size and shape of that descendant set reduced to a comparable score: how many columns and tables it reaches, how deep the ripple goes, and — most importantly — how much of the reach lands on critical tier-1 consumers like finance or regulatory exports. Weighting leaf consumers by tier lets one regulatory export outweigh a hundred scratch columns, so you can rank a backlog of schema changes and gate a PR on the score. The complementary backward query, provenance (nx.ancestors), answers "where did this number come from?" during an incident or an audit.

How does column-level lineage relate to OpenLineage?

OpenLineage is the open standard for lineage metadata: runs emit events carrying input and output datasets, and datasets carry typed facets. The columnLineage dataset facet is where column lineage lives on the wire — it maps each output field to a list of input fields (namespace + dataset + field) annotated with a transformation type (direct/identity vs indirect/aggregation). Your sqlglot-derived edges map one-to-one onto that facet, so you can emit your parse-time graph into any OpenLineage backend (Marquez, DataHub) and have it compose with runtime lineage that Airflow, Spark, and dbt emit as jobs run. The win is interoperability plus completeness: static parse-time lineage gates the PR before merge, runtime lineage verifies what actually executed, and OpenLineage is the shared format that lets you merge and reconcile the two.

Do dbt or data catalogs give you column-level lineage for free?

Partly, and it is worth knowing the limits. dbt exposes ref()/source() dependencies, which give solid model-level (table-level) lineage cheaply, and dbt plus some catalogs can infer column lineage — but that inference still comes from parsing the model SQL, so it inherits the same coverage caveats: unparseable models, dynamic SQL, and macros that expand at compile time can leave gaps. Catalogs like DataHub, OpenMetadata, and Unity Catalog ship table lineage almost for free from run metadata and increasingly offer column lineage, often powered by a SQL parser under the hood. The senior takeaway is that column lineage is only as good as its parse coverage and its freshness — so whether you build it with sqlglot yourself or consume it from a catalog, insist that unparseable SQL fails loudly, that the graph is rebuilt from current code, and that it is actionable in a PR gate rather than a static diagram.

Practice on PipeCode

• Drill the SQL practice library → for the dependency-tracing, SELECT *, CTE, and window-function query problems that column-level lineage has to parse correctly.
• Rehearse on the data transformation practice library → for the model-composition, aggregate, and CASE-logic rewrites where identity, transform, and aggregate edges come from.
• Sharpen the graph-design axis on the design practice library → for the DAG construction, reachability, and dependency-system scenarios behind impact analysis and blast-radius mapping.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the parse → graph → impact → blast-radius ladder against real graded inputs — and add the data validation practice library → for the PR-gate and impact-check drills.

Lock in column-level lineage muscle memory

Docs explain lineage diagrams. PipeCode drills explain the decision — when table lineage over-reports, when a regex misses a `SELECT *`, when a cents-to-dollars retype silently corrupts a downstream `SUM`, and when a change's blast radius has earned a hard PR block. Pipecode.ai is Leetcode for Data Engineering — parse-to-graph practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice design problems →