Someone points at a revenue number in a dashboard and asks where it comes from.

To answer, you open the warehouse. You find the mart. You read its SQL. It selects from another table, so you read that one too. You keep going until you reach a raw table that came from Stripe. That took twenty minutes, and it was one number.

Data lineage is a record that answers the same question in seconds.

The short version:

• Lineage records which table or column was built from which.
• There are three kinds: technical, business and operational.
• There are three ways to get it: parse the SQL, read warehouse metadata, or write it down yourself.
• Drawing it is easy. Keeping it correct after the pipeline changes is the hard part.

What is data lineage?

Data lineage is a record of where data came from and what happened to it on the way. For each table and column, it says which table or column it was built from, and what the transformation did.

You can draw it as a graph. Each box is a table. Each arrow means "this table was built from that one". The arrows point one way, from the older table to the newer one.

People often mix this up with foreign keys. They are not the same thing:

• orders.customer_id points at customers.id. That is a foreign key. It tells you how to join two tables. Both tables already exist.
• mart_revenue was built by running a query over fct_orders. That is lineage. If fct_orders had never run, mart_revenue would be empty.

A foreign key tells you how two tables fit together. Lineage tells you which table was created from which.

Concept	What it records
Data lineage	Which table or column was built from which
ER diagram	Foreign key relationships between tables
Data flow diagram (DFD)	Processes and data stores in an application
Data provenance	Where data originally came from, and who handled it
Data catalog	A list of what data exists; it often includes lineage

Types of data lineage

People use the word to mean three different things.

• Technical lineage. Which table or column was built from which, and by what query. This is what engineers use to debug, and what most of this article is about.
• Business lineage. The same path, but described without table names. For example, "Stripe charges become reported monthly revenue". Auditors and finance teams ask for this.
• Operational lineage. Which job built the table, when it last ran, and whether it worked. This tells you a number is out of date rather than wrong.

There is one more difference to know. You can write lineage down while designing a pipeline, or a tool can record it while the pipeline runs. If the two do not match, something is wrong, and that is useful to know.

Table-level or column-level?

Table-level lineage says stg_orders was built from raw_orders.

Column-level lineage says mart_revenue.revenue was built from fct_orders.net_revenue, which was built from raw_stripe.amount_cents.

Column-level lineage: clicking net_revenue hides every table that did not help produce it.

Why teams need it

• Debugging. A number looks wrong. With lineage you only check the tables that feed that column, not all 200 tables in the warehouse.
• Impact analysis. You want to rename a column or drop a table. Lineage shows you what will break before you make the change.
• Onboarding. A new engineer can look at one diagram instead of reading four hundred SQL files.

Banks have a fourth reason: regulators ask for it.

In the 2007 financial crisis, many large banks could not add up their own risk exposures correctly. The Basel Committee responded with BCBS 239, published on 9 January 2013.

The ECB's 2024 guide asks for more. It expects lineage to cover the full life of the data, from the moment it arrives to the moment it appears in a report.

How lineage gets captured

Parsing the SQL

A tool reads your queries or your dbt project, looks at each SELECT, and works out which tables feed which. You write nothing. When the SQL changes, the lineage changes with it.

This works well for ordinary queries. It works less well for SQL that is generated at runtime, for stored procedures, for SELECT *, and for steps written in Python instead of SQL. When a parser cannot work out the columns, it usually falls back to table-level only.

Reading warehouse metadata

Warehouses already track some of this. Snowflake has ACCOUNT_USAGE.OBJECT_DEPENDENCIES, Databricks records lineage in Unity Catalog, and BigQuery reports it through Dataplex.

This is accurate for views and for tables the warehouse built itself. It knows nothing about what happened to the data before it arrived, or what happens after it leaves. It also does not record why a transformation exists.

Writing it down yourself

You describe the pipeline by hand, as code, in the same repository as your models.

This is the only option that works before the pipeline exists, because there is no SQL yet for a tool to read. It is also the only option that records your reasons. A parser can see that status became state. It cannot tell you the team decided to rename it in March.

One more thing to know: OpenLineage is an open standard for passing lineage between tools. It gives Airflow, Spark and dbt a shared format, so their lineage ends up in one place instead of three. It graduated from the LF AI & Data Foundation in September 2023.

Choosing an approach

Approach	Best for	Main drawback
Drawing tools draw.io, Lucidchart, Visio	A slide or a quick sketch	The diagram becomes wrong and nothing tells you
Diagram as code dbdiagram, Mermaid	Planning, and docs you want to keep	You write it yourself, so you have to keep it updated
Automated platforms DataHub, Atlan, Collibra, Monte Carlo, Marquez	Large production warehouses, regulated work	Only covers pipelines that already run

dbt is a mix of the last two. It belongs to a category of tools, the data modeling layer, that run transformations inside your warehouse. Its diagram comes from the ref() calls you write by hand, so you are still describing the pipeline yourself. Column-level detail is a paid Cloud feature and is not in dbt Core.

Many teams use two: an automated platform for what is running in production, and lineage written by hand for pipelines they are still planning.

Why lineage projects fail

Two reasons, and neither is the tool.

The scope is too big. "Map the whole warehouse" never finishes, and a half-finished map is one nobody relies on. Teams that succeed pick one number people keep arguing about, map only the tables behind it, and add more later.

It goes out of date. Lineage that is wrong is worse than no lineage, because people believe it and look in the wrong place. So ask this about any method you pick. When someone changes the pipeline, does the lineage change at the same time? Or does it need a second step that people will forget?

Three smaller mistakes:

• Drawing arrows with no description. The arrow says a dependency exists. It does not say what the step does, and that is usually what people need to know.
• Using the same arrow for foreign keys and for lineage. They mean different things, so use different lines.
• Only drawing lineage after the pipeline is built. It is much cheaper to change a diagram than to change a pipeline that is already running.

Lineage as code

Writing lineage yourself does not fix the scope problem. You still have to decide what to leave out.

It does fix the second problem. If the lineage is a text file in the same repository as your SQL, one commit changes both, and the reviewer sees both.

Here is what it looks like in DBML, the schema language behind dbdiagram. One line records one step. Dep: A -> B means B was built from A.

Table raw_stripe {
  id integer [pk]
  amount decimal
  status varchar
}

Table stg_orders {
  order_id integer [pk]
  amount decimal
  status varchar
}

Table mart_revenue {
  month date
  revenue decimal
}

Dep: raw_stripe -> stg_orders [note: 'Cleans raw charges. Drops voided rows.']
Dep: stg_orders -> mart_revenue [note: 'Aggregates paid orders into monthly revenue.']

The pipeline is the last two lines. The note on each one says what that step does.

You can also go down to single columns. Add the source column to the field itself, like revenue decimal [dep: <- raw_orders.amount], and then you can click one column and see every table it came through. You can keep the SQL for a step next to the arrow too. The syntax reference covers both.

You do not have to type it

This is the real advantage of a text format, and it is easy to miss.

A diagram you draw in a GUI can only be drawn by a person. A text file can be written by a program. DBML is plain text with an open, published syntax, so the lineage can be generated instead of typed:

• sql2dbml turns SQL CREATE TABLE statements into DBML.
• @dbml/connector reads an existing database and writes out its schema.
• An AI agent can write DBML directly, and you check the diagram it produced.
• Reading lineage straight from a dbt project or a warehouse is what we are building next.

So "write it down yourself" does not have to mean typing it by hand. It means the lineage has a source file. Anything that can write a file can produce it, and you review the diagram that comes out.

Frequently asked questions

What is data lineage in simple terms? A record of where each piece of data came from and what was done to it.

What is the difference between data lineage and data provenance? Lineage is about the path data takes through transformations inside one system. Provenance is wider: where the data originally came from, who owned it, and how it was produced. In practice most data teams use the two words to mean the same thing.

Is a data flow diagram the same as a data lineage diagram? No. A data flow diagram shows the processes and data stores inside an application. Lineage shows which tables were built from which other tables.

Can data lineage be generated automatically? For pipelines that already run, yes. A tool can read your SQL or your warehouse metadata. Column-level results vary depending on the SQL dialect. For a pipeline you have not built yet, no tool can do it, because there is no code to read.

How detailed should data lineage be? Detailed enough to answer the question you built it for. Table-level shows you the shape of a pipeline. Column-level lets you trace one value back to its source.

Do you need a data catalog to have lineage? No. A catalog is one place to keep lineage, and it is usually the right place in a large regulated company. A small team can keep lineage in the repository next to the models.

---

Draw your own

You describe the pipeline in DBML, and dbdiagram draws it as you type. Start with two tables and one line. Add columns and the SQL for each step later, when you need them. The file lives in your repository next to your schema.

It is on every plan, with no limit on project size. The syntax reference has the full Dep syntax, column mapping and embedded queries.