Data Lineage

Numbers used for analysis are never made in one place. A record written by the payment provider gets copied into the company's store, duplicates and failed charges are filtered out, account details are joined on, the result is grouped by month, and only then does it reach the revenue field on a dashboard. Five or six intermediate tables is normal.

When that path isn't written down anywhere, two directions close off. You can't trace backward when a number looks wrong, and you can't tell what will break downstream when you change something upstream. Tracing backward means opening the query behind the dashboard's table, finding the table that one reads from and opening that, then tracking down whoever built it. Each step costs half a day.

Pagedeck got hit from the other direction first. Theo renamed a column in the payment records table that he had judged unused, and that morning every revenue figure in Grace's weekly report came up blank. A table three steps downstream was referencing it. Finding the cause took two days, and most of those two days went into not knowing where to look.

Lineage is the idea of storing that path as a record rather than in people's heads.

Every processing step records which tables it reads and which tables it writes, and those reads and writes get joined with arrows. The result is one picture where tables and dashboards are nodes and processing steps are edges.

There are three ways to collect the path.

1. Parse the queries. Statements executed in the store already say which tables were read and which one received the result, so collecting that history builds the path without anyone writing it by hand.
2. Use the dependencies declared by the transformation tool. Data pipeline tools that keep table-building rules in one place require each rule to name the tables it derives from, and those declarations are the path.
3. Write the rest by hand. Stretches where a person downloads a file, calculates something, and uploads it again — anything routed through a spreadsheet — never gets captured automatically.

Lineage is read at two grains. Table-level lineage shows the large flow: payment source table into the cleaned table in the staging layer, from there into the monthly revenue table in the mart layer, and on to the dashboard. Column-level lineage narrows that to which source field the revenue column specifically derives from. The finer grain matters because two tables being connected doesn't mean every column in them is affected.

The two directions have names. What flows into the table you're looking at is upstream; what reads from it is downstream. You follow upstream when a number is wrong, and count downstream before you change anything.

Four situations.

1. Tracing a suspicious number. If March revenue doubled, walk upstream and find the step where the value jumped. Already doubled at the source means a problem at the payment provider; doubled from the cleaning step onward means the deduplication rule broke.
2. Sizing the blast radius before a change. Before renaming a column or dropping a table, count what sits downstream. If the answer is three tables and two dashboards, you warn the owners of those five before making the change.
3. Cleaning up unused tables. A table with nothing downstream that nobody has queried in three months is a candidate for deletion. Without lineage nobody can be sure, so nobody touches anything and the table count only grows.
4. Checking how far sensitive values traveled. Knowing which downstream tables a column holding member email addresses was copied into is what lets you apply the same access restrictions to those tables.

Item 2 is the one that changes how a team works day to day. Engineers who can't see downstream tend to do one of two things, and both are bad: they leave old columns in place forever because removing anything feels risky, or they remove things and find out from a broken report. A downstream count turns that judgment call into a list of names to notify.

The same picture settles arguments about metric definitions. A metric definition pins down what gets counted and how, but identical definitions still produce different results when the counting happens in two different tables. Open the lineage and it's immediately visible that two columns both named revenue arrive by two separate routes.

"Can you look at the lineage for this number? I can't tell which step it went wrong at."

A request to narrow the problem down by walking the path.

"Did you check downstream before deleting that column? Three dashboards depend on it."

Count what a change affects before making it.

"The lineage breaks at the spreadsheet step."

A human copy-paste stage sits in the middle, so the path doesn't connect.

After the incident above, Pagedeck started collecting executed queries and drawing table-level paths from them. What the first picture revealed was that there were two revenue tables. The one Mia used came from payment provider records; the one Sofia watched came from values typed into the sales tool by hand. The two disagreed by about 3 percent every month, and until then everyone had put it down to rounding.

Pulling the downstream list and attaching it to the change request is now standard practice on the engineering team before any table gets dropped or column renamed. It exists to avoid repeating that two-day outage, and no failure of the same kind has happened since.

The easy assumption is that installing a tool produces complete lineage on its own. Extracting paths from queries really is automatic, which makes it look that way. But only what happens inside the store gets captured. A stretch where someone downloads a file, computes on it, and re-uploads it, calculations performed inside an external tool, formulas written into a dashboard — none of it appears. If the broken stretches aren't marked as broken, the picture gets trusted as complete, and someone deletes a table that looked unused but wasn't.

The other mistake is expecting lineage to name the cause. The path shows which step the value changed at; why that step behaves as it does still requires reading the rule. What lineage does is cut the number of places you have to look from six to one.