Skip to Content
Explainer

Follow the Data: What Lineage Is and How to Read It

Where a value came from, what breaks if you change it, and how to tell an arrow you can trust from one you cannot.

Share this case file← All articles

A partner writes to you on a Tuesday. The weekly file you send them has your customers’ bank account numbers in it.

You need two answers today. Where did that column come from? And what breaks if you take it out? Nobody in the room knows. The file comes from a table, which comes from a view, which comes from a database three teams away.

Those two questions have one name: lineage.

A partner's note and the weekly export on a black table, the iban column marked in lime, and two stamps: where from, what breaks

Upstream and downstream

Lineage is a record of where values came from. Think of a river: upstream is where the water came from, downstream is everything it reaches.

A good graph keeps one rule. Arrows point the way the data moves, so upstream stands on the left and downstream on the right. Walk inward and you learn where something came from. Walk outward and you learn what breaks if it changes.

Why lineage graphs turn into hairballs

Because everything gets flattened into one kind of link. An attachment, a foreign key, a duplicate file and a table built from another table are four different things, and only one of them answers what breaks?

A knot of street wires beside four cards: an attachment, a foreign key, a duplicate file, and a table built from a table, the only one in lime

So ask one thing of every link: did values move?

ClassMeansExampleIs it a step in the path?
Lineage (flow)The values in one came from the otherA view over its tablesYes. This is the lineage graph
ContainsOne is a part of the otherA chart in a dashboardNo. It folds the picture
Same asOne real thing, seen twiceOne file in two bucketsNo. It merges two nodes
ReferencesOne points at the otherA foreign keyNo. A hint that carries nothing
Used bySomebody touched itWho opened a fileNo. A weight, not a path

A foreign key is the one that surprises people. It is a good hint about where lineage might be, but no data moved, so it is never a step. Whatever a system is unsure about belongs in the same place: a link that carries nothing. That is how a graph stays readable. The five classes are in the docs, with what each one means for a traversal.

Five cards on a table under the question did values move: only lineage is lime

Which kind of move

Inside flow, the kind of move tells you what to expect at the far end.

  • Copy of. The same values, moved. If the source is sensitive, so is the copy.
  • View of. A saved query. It holds nothing of its own, so a change underneath reaches it at once.
  • Derived. Real logic in between. The values may or may not survive, so you have to look closer.
  • Written by. A job produced this.
  • Exported to, sent to. It left the system. That is where a privacy question stops being internal.

Six kinds of move along one arrow, with a dashed line before exported to and sent to: past this line it is not internal

Column lineage

Table lineage says a view is built from two tables. That is true, and it is not enough to act on. Before you drop a column you need to know if this column feeds anything.

Column lineage records, for every output column, what fed it and every function on the way. It reads as a sentence: customer is name, through LOWER. income is amount, through a CASE, then through a SUM.

The functions are the useful part, because they tell you if the sensitive thing survived the trip. A lowered name is still a name. A sum over many rows no longer holds one person’s amount, unless the group has one person in it. So each mapping says one of three things: copied as it is, transformed, or aggregated.

The same diagram with the three words beside it, copied as it is, transformed, aggregated, and a stamp: did the sensitive thing survive?

Where, and why

One older idea helps here. In 2001, database researchers split the question of where data came from in two.

  • Where is the place a value was copied from.
  • Why is the data that made this row appear at all.

An old ledger, the year 2001, and two cards: where, the place this value was copied from, and why, the data that made this row appear at all

Look at a filter. A WHERE country <> 'CH' feeds no output column, and yet it decides who is in the result. So does a join key. If you drew those as arrows into columns, every column would seem to depend on everything. So they are kept apart, in a separate list: columns that shaped which rows came out.

Tracing a value, you can skip that list. Dropping a column, you cannot: remove the filter column and the view breaks.

The diagram with a box round country, which has no arrow, a sheet listing the columns that shaped which rows came out, and two stamps: tracing a value, skip it; dropping a column, read it

How a tool knows

Most databases say which tables a view depends on, not which columns. So a tool reads the view’s own SQL and follows each output column back: into common table expressions, down each branch of a UNION, through views of views.

Some platforms report columns from their own catalog. They name the columns, not the functions between them, so the two sources are good at different things.

Two cards: the SQL names the columns and the functions, the catalog names the columns and is silent on the functions

And sometimes the honest answer is nothing. SELECT * names no columns. A bare column in a join could belong to either table. A missing answer sends you to go and look. A confident wrong one sends you to production.

Two queries: SELECT star, stamped no columns named, and a bare amount in a join, stamped which table? Under them: no answer beats a wrong one

Column-Level Lineage has every case, including how to read the panel row by row.

How far to trust an arrow

Not every arrow is equally certain, so each one should say how it was known.

Five ways an arrow can be known, from runtime observed to manual, and the SQL kept beside it

Where an arrow came from SQL, keep the SQL beside it, so you can read the derivation instead of trusting a label.

Across systems

The most valuable lineage crosses a boundary: two systems, two scans, sometimes months apart. The trick is a name. Not an ID from the scan, but what the platform itself calls the object: host, database, schema, table. Two scanners that never met write the same string.

Two sheets, one from March and one from October, carrying the same address for one table, with tags for host, database, schema and table

So a scan can point at a table nobody has scanned yet. The arrow is kept, and it ends on an outlined node: a name with nothing inside. That is a finding in itself, because it shows where your data goes that you are not watching.

A dashed node named customers, not yet scanned, with an arrow to a warehouse table, and a stamp: where your data goes that you are not watching

Connect that system later and the two halves recognise each other. The lineage completes itself, backwards in time. Here is that happening for real, in a test we ran before adding a schema to the scan, and straight after:

The canvas before: vendor_feed in Postgres as a dashed node, with nothing to open, and an arrow labelled derived into supplier_costs

The canvas after the schema was scanned: the same arrow, now from the real lab_ext.vendor_feed asset

The name includes the host and port, so both scanners have to reach the database the same way. We nearly tripped on this ourselves: one connection used a pooler on port 6543 and the other port 5432, so the two would have named one table twice and the arrow would never have joined. Pointing both at the same port fixed it.

A second job: duplicates

Two tables look nearly identical. The question is not how similar, but why.

Three rows, each with two tables: a path joins them, shared upstream, low priority; no path, the alarm; one side unknown, judge the values

Contents and lineage come from different evidence, which is why putting them together tells you something. Duplicate review is built on this.

One real column

Back to the file. This is a test workspace with three real systems: Postgres, MySQL and a Databricks warehouse. Everything below is open: you can click through it yourself.

The export carries five bank account numbers, and each of them turns up in ten tables, across two systems.

Ten suspects. The obvious one is a CRM contact list. A value search cannot settle it, because the value is the same in all ten. Lineage can.

Walk upstream. The export reads a statement, the statement reads a staged table, and then the path leaves the warehouse. The warehouse reads the database through a foreign catalog and reports the read, and two scans that never met wrote the same name.

The lineage canvas walked upstream from the export: a warehouse on the right, Postgres in the middle, MySQL on the left, each node labelled with its source

Pick a single column. The IBAN comes from customers, in three steps, and not from the CRM list. That idea is closed.

The column lineage panel for iban: three rows, back through partner_statement and staged.customers to postgres.lab_core.customers

value_band goes further: five steps, across the boundary, through two Postgres views, down to quantity times price. Read the rows. Inside the warehouse they say catalog, 95%, mapped: it knows the link, not the logic. Inside Postgres they say SQL parsed, 80%, with the functions.

The column lineage panel for value_band: five rows, three reading catalog 95% mapped, two reading SQL parsed 80% with SUM, CAST and the multiplication

Now walk down from that staged table. One statement, one export, nothing else. The fix is one column in one SELECT.

The canvas walked downstream from staged.customers: one statement, one export, nothing else

One arrow nobody reported. A nightly job copies account managers from MySQL into Postgres, and neither database knows. So it is drawn by hand, in amber, so that nobody mistakes it for something a platform said.

The same canvas closer in: an amber arrow from lab_hr.employees in MySQL to account_managers in Postgres, labelled nightly copy

One path, three kinds of evidence. The case keeps the verdicts: the CRM idea refuted, customers supported, the fix sized.

The case board: the CRM hypothesis refuted in red, the customers hypothesis and the size of the fix supported in green, the amber link along the bottom

What it will not tell you

  • Only what a platform says. Stored procedures, triggers and outside jobs leave no arrow unless someone declares one.
  • How data can flow, not that a given row did. An arrow is a statement about structure.
  • Names have to match. Reach one database by two host names and the halves never meet.

A missing arrow means nobody reported one. It does not mean nothing moved.

Three stamps, procedures, triggers and outside jobs; how it can flow, not which row did; two host names, two tables. Under them: no arrow is not no movement

If you know how data moves and no system will say, a custom connector can declare it, column detail included.

Ask the two questions of a column, not a table, and check how each arrow was known to be there.

Open it yourself

Everything in the case is live in the all-sources-test workspace.

Start hereWhat to look at
partner_exportConnections tab, View, Upstream, ∞: the whole path. Pick value_band in the column panel
lab_core.customersWalk downstream: everything that reads it, in both systems
lab_hr.employeesThe hand-drawn arrow out of MySQL
supplier_costsThe arrow that waited for a schema, and bound itself
The caseThe hypotheses, their verdicts and the evidence

The full guide is in the docs: Lineage & Relationships, Column-Level Lineage and the source pages for Databricks, PostgreSQL and MySQL. Cases are covered in Links on the board.

More from the blog

Last updated on