How we build column lineage from SQL without running your queries

Lineage is a map of which tables and columns feed which. There are two ways to draw it. You can watch data move at run time, or you can read the code that moves it. We chose to read the code, because it works on the first day and never touches your data.

This post explains how that works, what makes it hard and how we know the result is right.

Why parse instead of trace

Run time tracing needs an agent inside every job. It is accurate for the jobs it sees and blind to the rest, and it has to be installed before the first run you care about.

Parsing needs only the SQL, and warehouses keep a record of it. Snowflake, BigQuery and Redshift each store the text of recent queries along with who ran them and what they wrote. dbt writes the compiled SQL of every model into its manifest. Between them, almost every transformation in a typical warehouse is available as text.

The cost is that we must understand SQL well enough to resolve names, which turns out to be most of the work.

Step 1: parse into a tree

Each statement becomes a syntax tree. We use a grammar for each dialect, because QUALIFY, array syntax and quoting all differ. A statement that does not parse is recorded and counted, never guessed at.

Take a simple model:

create table analytics.daily_revenue as
select
  o.order_date,
  sum(o.amount_usd) as revenue
from analytics.orders o
join analytics.customers c on c.id = o.customer_id
where c.is_internal = false
group by 1;

Step 2: resolve names

Names in SQL are relative: aliases, common table expressions, unqualified columns and the current schema all change what a name means. We build a scope for each select and resolve every reference to a real table or to another scope.

For the model above the resolver finds that o is analytics.orders, c is analytics.customers, and o.amount_usd is a column of the first table.

Step 3: trace each output column

For each column the statement produces, we walk its expression and collect the source columns it depends on. The output is a list of edges:

[
  {
    "from": "analytics.orders.order_date",
    "to": "analytics.daily_revenue.order_date",
    "kind": "direct"
  },
  {
    "from": "analytics.orders.amount_usd",
    "to": "analytics.daily_revenue.revenue",
    "kind": "aggregate"
  },
  { "from": "analytics.customers.is_internal", "to": "analytics.daily_revenue.*", "kind": "filter" }
]

The third edge matters. A column used in a where or join does not appear in the output, but changing it changes every row. We record it as a filter edge, so impact analysis does not miss it.

The hard cases

Most statements are easy. The rest are where a lineage tool earns trust.

Star expansion. select * from a depends on the columns of a at the time the query ran. We resolve stars against the schema that the warehouse reported then, not the schema today.

Dynamic SQL. If a query is built from strings at run time, only the final text is in the query log. We use the log, not the template, so the graph reflects what actually ran.

Name collisions. A common table expression named like a real table shadows it inside its scope. The resolver tracks scopes explicitly, so a shadowed name never links to the wrong table.

Temporary tables and views. Views are expanded to the tables behind them, with the view kept as a named node, so you can see both.

Checking the result

A graph that looks plausible can still be wrong, so we test it three ways.

1. A corpus. We keep about 40,000 real statements, contributed by customers with identifiers replaced, and check edges against hand-reviewed results for a sample of them.
2. Warehouse metadata. Where a warehouse reports its own lineage, we compare. Disagreements are reviewed one at a time.
3. Coverage. For each workspace we report the share of statements that parsed and resolved. On the corpus that share is 97.4 percent, and the workspace page shows the figure for your own data, with the statements that failed.

Showing the failures is deliberate. A lineage graph with a visible gap is more useful than one that quietly fills it with a guess.

What we are working on

Two things are next. The first is better handling of procedural SQL, such as stored procedures that build statements in loops. The second is stable column identity across renames, so that a renamed column stays one node in the graph. The second shipped for dbt projects in October, and the changelog describes how it decides that a renamed column is the same column.