What is data lineage, and why does it decay?

Data lineage is the record of where data came from and what happens to it — which source produced a table, which transformations touched it, and which reports depend on it.

It exists to answer two questions that are otherwise unanswerable. Backwards: this number looks wrong, where did it come from? Forwards: we need to change this column, what breaks? Both are asked constantly and both are usually answered by asking whoever has been there longest.

What we'll cover

• The two questions it answers
• Table-level and column-level
• Where lineage comes from
• Why manual lineage decays
• The gaps automation cannot see
• Using it for impact analysis
• Who it is actually for
• Lineage and a data catalogue
• Keeping lineage current with CodeWords
• What to get right first
• Frequently asked questions

The two questions it answers

Backwards, for trust. A figure is disputed, and tracing it to its source is the difference between resolving the question in ten minutes and spending a day on it.

Forwards, for change. Somebody wants to rename a column, deprecate a source, or migrate a system, and the question is what depends on it.

The forwards question is the one that blocks work. Without an answer, changes either do not happen or happen and break something unexpected, and both are expensive.

A third use is compliance, where demonstrating where personal data flows is an obligation rather than a convenience.

And a fourth is debugging, where a downstream anomaly is traced up to the point where something changed.

Table-level and column-level

The distinction that determines how useful lineage actually is.

Table-level says this table feeds that table. Coarse, easy to produce, and usually enough for orientation.

Column-level says this column feeds that column, through which transformation.

Column-level is what impact analysis needs. "Which reports use this table" returns forty; "which reports use this column" returns three, and only the second is a usable answer.

It is considerably harder to produce, requiring parsing the transformation logic rather than only observing which tables were read and written.

Start at table level if that is what you can get, and know its limit — it tells you where to look rather than what will break.

Where lineage comes from

Parsing queries is the most common source: read the SQL that builds each table and derive the dependencies from it.

Transformation tools frequently produce it as a by-product, since they already know which model depends on which.

Runtime capture observes what actually ran and what it read and wrote, which catches things static parsing misses.

The warehouse's own logs record query history, from which a good deal can be reconstructed.

Manual documentation, which is where most organisations start and which is covered below.

A combination is normal. Automatic where it can be derived, manual for the gaps, with the manual portion kept as small as possible.

Why manual lineage decays

The predictable failure, and it is worth understanding rather than resolving to be more disciplined.

It is a separate artefact from the thing it describes. Changing a pipeline and updating the documentation are two actions, and only one is required for the change to work.

Nothing detects the divergence. A diagram that is wrong looks exactly like one that is right.

It is updated during the change and not afterwards. The clean-up that follows a migration is where documentation goes to not be written.

People trust it until it fails them once, at which point they stop trusting it entirely — and partially trusted lineage is worse than none, because somebody acts on it.

The only durable version is derived, generated from the actual transformations, so it cannot drift from what runs.

Accept manual entries only where nothing else is possible, and mark them as such so consumers know which parts to verify.

The gaps automation cannot see

Even derived lineage is incomplete, and knowing where is important.

Application code that writes to the warehouse outside your transformation tool.

Spreadsheets. Somebody exports a table, transforms it in a spreadsheet, and a decision is made from the result — invisible to everything.

Business intelligence tools' own transformations, which are frequently a second modelling layer with its own logic.

Manual uploads. A file somebody produces monthly and loads, which is genuinely a source and is documented nowhere.

External consumers. An extract sent to a partner is a dependency your lineage does not know about and which will break loudly.

Mark the boundaries explicitly, so lineage that stops does not read as lineage that ends. A graph that goes quiet at the edge of what it can see is misleading if it looks complete.

Using it for impact analysis

The workflow that makes lineage worth maintaining.

Before changing a column, list what depends on it — downstream tables, then reports, then external extracts.

Identify the owners, since the answer to "can we change this" is a set of conversations rather than a query result.

Distinguish depends-on from would-break. A report using a column may tolerate a rename with a small change; another may be embedded in something nobody can modify.

Check the edges. The extract to a partner and the spreadsheet somebody maintains are the dependencies most likely to be missed and most likely to complain.

Announce with a date, since consumers do not migrate in response to a notification alone.

Keep the old column briefly where you can, populated in parallel, which turns a coordinated change into two independent ones.

Who it is actually for

Worth asking, because lineage projects frequently build for the wrong audience.

Data engineers, for impact analysis, which is the primary use and the one that justifies the investment.

Analysts, for tracing a figure they are being asked to defend.

Anyone answering a compliance question about where personal data goes.

Not executives. A lineage diagram is not a dashboard, and presenting it as one leads to a tool bought for a visualisation nobody uses.

Build for the people who will use it weekly, which is a small group, and resist the pull towards a broad catalogue nobody maintains.

Lineage and a data catalogue

The two get bought together and they answer different questions.

A catalogue says what exists — which tables, what each column means, who owns it, how fresh it is.

Lineage says how things relate, which is the graph between the catalogue's entries.

The catalogue's hard problem is descriptions, which are written by people and go stale in exactly the way manual lineage does.

Descriptions generated from nothing are worse than absent ones, because a plausible wrong definition is acted upon while a blank one prompts a question.

Ownership is the field worth maintaining above all others, since almost every question about a table ends with needing to ask somebody.

Start with ownership and freshness, both of which can be derived, and add descriptions only where somebody will maintain them — usually the tables that are actually used.

Keeping lineage current with CodeWords

Automations that move data are part of the lineage and are frequently the part that is invisible. CodeWords connects to more than 3,000 integrations, with connections made once and reused, which keeps what reads and writes each system visible in one place.

1. Open CodeWords and start a new automation.
2. Describe what should happen in plain language to Cody, the automation builder: which source, which destination, and how often.
3. Describe the exceptions: a schema change, an empty result, a figure outside the expected range.
4. Connect each system once and reuse the connection.

You describe the outcome; Cody builds it, connects it, and deploys it. The free plan covers light use, with Pro at $39 per month and Business at $100 per month as usage grows; details are on the pricing page.

What to get right first

Derived lineage for the transformations you control, even at table level. A generated graph that is slightly coarse is more useful than a detailed diagram that is eighteen months old, because it is trustworthy — and the whole value of lineage is that somebody can act on it without verifying it first.

Two habits make the difference. Mark the boundaries where lineage stops, so a graph that goes quiet at the edge is not mistaken for one that ends there. And check the edges before any change — the extract to a partner and the spreadsheet somebody maintains monthly are the dependencies nothing records and the ones that complain loudest.

Frequently asked questions

What is lineage for?

Two questions: where did this number come from, and what breaks if I change this. The second is the one that blocks work, because without an answer changes either do not happen or happen and break something nobody predicted.

Do I need column-level lineage?

For impact analysis, effectively yes. "Which reports use this table" returns forty and "which reports use this column" returns three — only the second is actionable. Table level is useful for orientation and does not answer the question.

Why does documented lineage go stale?

Because it is a separate artefact from the thing it describes, updating it is not required for a change to work, and nothing detects the divergence. Derived lineage cannot drift, which is why it is the only durable kind.

What can automated lineage not see?

Application code writing outside your transformation tool, spreadsheets, transformations inside business intelligence tools, manual uploads, and extracts sent to external parties. Mark those boundaries, or a graph that stops looks like one that ends.

Who actually uses lineage?

Data engineers doing impact analysis, analysts defending a figure, and anybody answering a compliance question. Not executives — a lineage graph is not a dashboard, and building for that audience produces a tool nobody uses weekly.

How do I change a column safely?

List what depends on it, identify owners, distinguish depends-on from would-break, check the edges, announce with a date, and where possible populate old and new in parallel so the change becomes two independent ones.

Is partial lineage worth having?

Yes, provided its boundaries are marked. What is dangerous is lineage that looks complete and is not, because somebody acts on it — and one bad experience ends trust in the whole thing.

Is a data catalogue the same as lineage?

No. A catalogue says what exists and what each thing means; lineage says how they relate. They are frequently sold together and the catalogue's hard problem is descriptions, which go stale in the same way manual lineage does.

Which catalogue field matters most?

Ownership, because almost every question about a table ends with needing to ask somebody. It can be derived and maintained, unlike descriptions, which need a person and go stale quickly.

What is the cheapest useful starting point?

Derived table-level lineage for the transformations you already control, plus ownership per table. Both come from things you have, neither requires a project, and together they answer most of the questions people currently ask a person.

How do I stop a change breaking an external consumer?

Populate the old and new in parallel for a period, so the change becomes two independent ones. External extracts are the dependency nothing records and the one that complains loudest, so they need the longest notice.

Related reading

• Data orchestration
• ETL pipelines explained
• Process mining