Understanding Data Lineage: Column-Level Tracing in Practice

Data lineage is the record of where each piece of data came from and what happened to it on the way. At its most useful it operates at column level: this figure in this report was produced from those specific columns in those specific source systems, through these transformations, in this order.

The reason it matters is narrow and practical. Sooner or later someone with authority — an auditor, a supervisor, a chief risk officer — points at a number and asks how it was calculated. In most organisations the honest answer is a chain of four systems, two manual reconciliation steps and a spreadsheet a specific analyst maintains. Reconstructing that chain takes days, and the reconstruction is a narrative rather than evidence.

Lineage converts that narrative into a traced path generated from the systems themselves. In a supervised institution, that conversion is the single most defensible return on a governance programme, and it is what to lead with when building the business case.

The three levels, and why only one of them settles arguments

Lineage is sold at three granularities, and vendors are not always precise about which one a demo is showing.

System-level lineage says data moves from the core banking system to the warehouse to the reporting layer. It is easy to produce, easy to draw, and answers almost nothing. Everyone already knew this.

Table-level lineage says this report table is built from those five source tables. Useful for a rough impact assessment, insufficient for an audit question, because a table with two hundred columns does not tell you which of them fed the figure in dispute.

Column-level lineage says the provision_amount field in this submission derives from loan.principal_outstanding and risk_grade.pd_value through a specific join and a specific case expression. This is the only level that answers the question an auditor actually asks, and it is the only level worth paying for.

The distinction shows up sharply in vendor evaluations. A product that produces beautiful system-level diagrams and shallow column-level coverage will demo better than one with the reverse profile, and be worth considerably less.

What lineage is actually used for

Four use cases, in descending order of how often they justify the spend.

Regulatory evidence. Show how a submitted figure was produced, on demand, without a week of preparation. In a bank under Central Bank supervision this is the primary driver, and it is the use case that makes the programme fundable.

Impact analysis before change. An engineer is about to alter a column type or drop a field. Lineage tells them which twelve downstream reports break. Without it, the answer is discovered in production by whoever consumes report number twelve.

Root cause analysis after breakage. A number is wrong. Lineage narrows the search from the whole estate to the specific chain that produced it, which turns a two-day investigation into a two-hour one.

Trust. An analyst who can see where a figure came from will use it. One who cannot will rebuild it themselves, which is how organisations end up with four versions of the same metric.

How lineage is captured, and the trade-offs

Three mechanisms, and most real deployments use a combination.

Code parsing reads SQL, stored procedures, ETL job definitions and view logic, and derives the column mappings statically. This is the most complete method where it works, because it captures the intended logic including paths that rarely execute. It is also the method that breaks on dynamic SQL, string-concatenated queries and proprietary transformation languages.

Query log analysis watches what actually ran. This captures what code parsing misses — ad hoc transformations, undocumented jobs, the analyst's Tuesday script — and it reflects reality rather than intent. Its weakness is that it only knows about paths that executed during the observation window, so a quarterly job is invisible for three months.

Manual declaration is someone drawing the connection in a user interface. Necessary for the gaps the first two cannot reach — a file emailed between departments, a mainframe extract nobody can parse. It is also the part that decays: manually drawn lineage is accurate the week it is drawn and progressively less accurate every week after.

A workable programme automates the first two, keeps manual declaration for genuine gaps, and marks manually declared edges visibly in the interface so that nobody mistakes an assertion for evidence.

Where automated lineage breaks

Being specific about this matters, because vendor demos never show it and every real deployment meets it.

Dynamic SQL. A query assembled from strings at runtime cannot be parsed statically. Query log analysis catches the executions; the logic connecting them stays opaque.

Stored procedures with control flow. Branching, loops and temporary tables defeat naive parsers. Good products handle common patterns; none handle everything.

Spreadsheets. A figure leaves a system, is manipulated in Excel, and returns as an upload. This breaks every lineage chain in every organisation, and the only honest treatment is to mark the gap explicitly rather than to draw an edge across it.

Cross-system moves without shared metadata. A nightly file transfer between two systems that share no naming convention. Lineage across that boundary is inferred at best.

Legacy and mainframe. COBOL copybooks, fixed-width extracts and job control language are outside the coverage of most modern catalogs. In Azerbaijani banks and state institutions these systems frequently hold the regulated data, which means the lineage question is hardest exactly where it matters most.

The correct response to all five is the same: represent the gap in the lineage graph rather than papering over it. A chain with an honest break at the spreadsheet is more useful to an auditor than a continuous chain that quietly guesses.

How to test lineage during an evaluation

This is the capability where demos are least informative and where the difference between products is largest, so it is worth being systematic.

Take your three most complicated real transformations — the ones with nested views, dynamic SQL and a stored procedure someone wrote in 2015. Require each vendor to produce column-level lineage for them during the proof of concept, on your own code, in your own environment. Do not accept lineage on sample data; sample data is chosen by the vendor precisely because it parses.

Then ask three questions about the result. Is it column-level all the way through, or does it degrade to table-level partway? Was it harvested or drawn? And can it be regenerated automatically on a schedule, so it stays true after next quarter's schema change?

One additional test that separates products quickly: change a column in a development environment, re-harvest, and see whether the lineage updates without human intervention. Lineage that requires a person to maintain it is documentation, and documentation drifts.

Lineage and AI systems

A newer requirement, and one that catches organisations unprepared.

When a language model answers a question using retrieved internal data, the same audit question arrives in a different form: which documents and which records produced this answer. That is lineage applied to a retrieval pipeline, and it needs the same treatment — captured at request time, stored, and reconstructable months later.

It also runs the other way. High-risk AI systems under the EU AI Act carry obligations on training data quality, provenance and documentation that are unmeetable without knowing where the training data came from. An organisation that cannot trace its reporting figures will not be able to trace its training sets either, because it is the same missing capability.

For the orchestration side of this, see what is AI orchestration.

Practical sequencing

Lineage across an entire estate is a multi-year project that demonstrates nothing along the way. The sequence that produces value early is narrower.

Start with one regulatory report. Harvest lineage for every asset that feeds it, end to end, including the gaps. This is four to six weeks of work in most organisations rather than a programme, and it produces the artefact that funds everything after it.

Demonstrate it in front of the people who have to answer for that figure — the CFO, the head of risk, whoever signs the submission. The demonstration is the deliverable, not the graph.

Then extend by following the reports, not by covering systems alphabetically. Assets that appear in supervisory submissions and executive decisions are where being wrong has consequences; the other thirty thousand tables can wait.

Throughout, resist the temptation to complete the picture by hand. A lineage graph that is 70% harvested and honest about the rest is worth more than one that is 100% complete and 30% asserted, because the second cannot survive a serious question.

Key points

• Only column-level lineage answers the question an auditor asks. System- and table-level lineage demo well and settle nothing.
• Capture through code parsing plus query log analysis; keep manual declaration for genuine gaps and mark it visibly as an assertion.
• Automated lineage reliably breaks on dynamic SQL, spreadsheets, cross-system file transfers and mainframe extracts. Represent the break rather than guessing across it.
• Test on your own worst transformation code, in your own environment, never on vendor sample data. Then confirm it regenerates on a schedule without human intervention.
• The same capability is now required for AI systems — which retrieved records produced this answer, and where did the training data come from.
• Start with one regulatory report, prove it end to end, and extend by following the reports rather than the schema.

We implement OvalEdge, whose automated column-level lineage is its strongest capability relative to price; the comparison against alternatives is in OvalEdge vs Collibra vs Alation. For the wider programme, see what is data governance.