How to Build Data Lineage: Manual, dbt, and Automated Approaches

There are three ways to build data lineage: maintain it manually by hand, infer it from dbt metadata, or generate it automatically by parsing warehouse query logs and model SQL. Manual lineage is accurate the day you draw it and stale within a sprint; dbt lineage is reliable but stops at the dbt boundary; automated query-log parsing is the only approach that stays current, spans both ends of the pipeline, and resolves down to the column level. Most teams progress through all three as their warehouse grows.

Approach 1: manual lineage

The simplest starting point is to draw the graph yourself, in a diagramming tool, a spreadsheet, or a wiki page. You list each table, note what it depends on, and connect the dots.

Manual lineage has exactly one virtue: it requires no tooling and forces you to actually think about how your warehouse is wired. For a tiny project with a dozen models and one engineer, it can be enough for a while.

Its weakness is fatal at scale: it goes stale immediately. Every new model, every renamed column, every split mart, and every repointed dashboard requires a human to remember to update the diagram, and nobody ever does. Within a sprint the diagram is wrong, and a confidently wrong lineage diagram is more dangerous than none, because people trust it. The maintenance cost scales with warehouse churn, and manual effort always loses that race.

Approach 2: dbt lineage

If you use dbt, you already have lineage for free inside the project. Every ref() and source() call declares a dependency, and dbt compiles those into a graph in manifest.json. The dbt docs site renders this as a clickable DAG. Because the lineage is derived from the same code that builds the tables, it cannot drift from the models the way a hand-drawn diagram does.

This is a real step up. dbt lineage is accurate, automatic, and updates every time you run or compile. For understanding model-to-model dependencies, it is excellent and you should use it.

The limitation is the dbt boundary. dbt only knows about what is inside the dbt project:

• It does not see upstream of your sources. The raw loads, the connectors, and the API that feeds raw.stripe.charges are invisible, so you cannot trace a metric to its true origin.
• It does not see downstream of your marts. The BI dashboards, reverse-ETL syncs, and ad-hoc queries that consume fct_orders are invisible, so "which dashboard breaks if this model changes" is unanswerable from dbt alone.
• It is primarily table-level. The DAG shows model-to-model edges, not that fct_orders.revenue specifically comes from stg_stripe_charges.amount_usd.

So dbt lineage answers a lot of questions, but not the two that stakeholders ask most: where did this number originate, and what downstream thing breaks if I change it. For dbt-specific lineage that extends past those edges, see how dbt lineage works end to end.

Approach 3: automated lineage from query logs

The third approach reconstructs lineage from what actually ran in the warehouse, by parsing the query log and model SQL rather than relying on a human or on dbt's declarations alone.

Every warehouse logs the SQL it executes:

• Snowflake exposes QUERY_HISTORY for every statement and ACCESS_HISTORY for column-resolved read and write information, which is the key to column-level lineage. See Snowflake lineage.
• BigQuery exposes INFORMATION_SCHEMA.JOBS and audit logs with the queries and referenced tables.
• Redshift and Postgres expose their own statement logs and system views.

By parsing these statements, an automated system reconstructs lineage that reflects reality, including ad-hoc queries and BI-tool queries that live past the dbt boundary. Crucially, it sees both ends: the raw loads upstream of sources and the dashboard queries downstream of marts.

Column-level lineage through SQL parsing

Table-level edges come from the FROM and JOIN clauses. Column-level edges require parsing the select list and expressions: recognizing that SUM(c.amount_usd) AS revenue creates an edge from amount_usd to revenue, and that amount / 100.0 AS amount_usd carries the value forward with a transformation. This is what lets you trace orders.revenue back to raw.stripe.charges.amount field by field, and what lets you answer "if I rename customers.email, which exact columns reference it?" with column-level precision instead of flagging whole tables.

The staleness problem, solved

Because automated lineage is rebuilt from the live query log and current SQL, it cannot drift. Add a model and its edges appear. Rename a column and the trace updates. Repoint a dashboard and the downstream edge moves. The diagram reflects today's code, which is exactly what the manual approach cannot promise.

Comparing the three approaches

How to choose, and how to combine

The approaches are not mutually exclusive; the strongest setup combines them. Use dbt metadata for accurate, declarative model-to-model edges inside the project. Layer query-log parsing on top to capture the raw loads, the BI consumers, and the ad-hoc queries that dbt cannot see, and to resolve everything to the column level. The result is a single graph that runs from the connector that loads raw.stripe.charges all the way to the revenue tile on an executive dashboard.

That combined lineage is also what makes monitoring useful. A freshness or schema alert is just noise without a blast radius; paired with end-to-end lineage it arrives with the downstream impact already mapped, which is the difference between an alert you can act on and one you triage blind.

Building lineage without touching your data

All three signals, dbt metadata, query logs, and parsed SQL, are gathered by reading. None of them require writing to or mutating your warehouse. A lineage tool should connect read-only by design and reconstruct the graph from history and metadata alone.

Datatrail builds lineage by combining dbt metadata with parsed query history from Snowflake, BigQuery, Redshift, or Postgres, resolving both table and column-level edges across the full pipeline from raw load to dashboard. If you would rather not maintain a diagram by hand or live inside the dbt boundary, you can see how the automated approach connects and what it surfaces against your own warehouse.