A number nobody could explain
The organisation is an investment manager running portfolios worth tens of billions across institutional mandates and funds. Its reporting estate had grown the way most have: a portfolio management system at the core, market data arriving from two vendors, custodian files landing overnight, all of it flowing through a staging area and several hundred transformation jobs into a data warehouse, and from there into the regulatory returns, client statements and internal risk packs the business runs on.
The engagement started with a single number. A valuation figure on a client report was challenged, and it took three people the better part of four days to establish where the number had come from, which systems had touched it, and which of two plausible transformation rules had actually been applied. The answer, when it arrived, was reassuring; the four days were not. Shortly afterwards an internal audit review made the point formally: for the firm's most important reports, provenance — where each critical figure originates and what happens to it on the way — was documented nowhere and depended on the availability of specific individuals.
The firm already used Sparx EA for its application architecture, and asked us a precise question: could the repository they already trusted for systems and interfaces also carry data lineage for the reports that matter, without turning into a second data warehouse project? The honest answer was yes, within limits — and the limits turned out to be the most useful part of the design.
Lineage lived in two developers' heads
Discovery took three weeks and confirmed what the audit had suspected. The transformation layer consisted of a few hundred SSIS packages and stored procedures, written over a decade, with naming conventions from at least three eras. Two senior developers could navigate all of it from memory; nobody else could navigate more than their own corner. There was an inventory spreadsheet, last touched about two years earlier, which described a pipeline that had since been substantially rebuilt.
The warehouse itself was documented by its table and column names. Reports were built on views, which were built on other views, some of which existed purely to patch column renames from an old migration. None of this was unusual, and none of it was blameworthy — every shortcut had been rational at the time. But stacked together, the shortcuts meant the firm's most regulated outputs rested on a structure only two people could explain, and both of them were near the top of every project's resourcing list.
One earlier attempt had been made to fix this with a specialised lineage-scanning tool. It had parsed the SQL estate and produced column-level lineage graphs of spectacular completeness and no discernible audience: thousands of nodes, correct in detail, unreadable in practice, and orphaned the moment the licence lapsed. The lesson we took was about altitude, and it shaped everything that followed: the firm did not need every column traced; it needed its critical numbers explained.
Critical, not complete
With risk and compliance we drew up the scope in a single workshop: twelve reports that carry regulatory or client commitments, and the critical data elements on each — the figures whose wrongness would constitute an incident. The list came to just over forty elements: valuations, exposures, performance figures, fee calculations, a handful of client-specific restrictions. Everything else was explicitly out of scope, written down as such, and revisited only when someone made a case.
Scoping this narrowly felt uncomfortable in the room, and we defended it firmly. Lineage documentation fails by ambition: a model that tries to trace everything is obsolete before it is finished and maintained by nobody. Forty elements across twelve reports is a model two architects can keep truthful in the margins of their week. The previous tool had proven the opposite approach empirically.
Choosing the level of detail
The modelling vocabulary in Sparx EA stayed deliberately small. Systems and stores that already existed in the application architecture — the portfolio system, the staging area, the warehouse, the reporting platform — remained application components, exactly the elements the architects already maintained. The datasets that matter became data objects: not one per table, but one per meaningful set, so "custodian positions file", "cleaned positions", "position fact" and "client valuation extract" are four objects, whatever the physical table count beneath them.
Transformations became application functions owned by the component that runs them, and the movement of data between components became flow relationships labelled with, and linked to, the data object that moves. Where a transformation applies a rule someone might one day dispute — a pricing hierarchy, a rounding convention, an exclusion — the rule is named in the function's notes in plain language, with a pointer to the code that implements it.
The decision we were most often asked to defend was staying above column level. A modelling tool can hold column-level lineage; it cannot keep it true, because every schema change in a maintained estate would demand a model edit nobody will make at five on a Friday. Dataset-level lineage, with the forty critical elements called out explicitly as their own data objects where they needed individual treatment, is the altitude at which a repository model stays honest. Below that line, the code itself is the documentation, and the model's job is to tell you which code to read.
Where lineage lives in the repository
A decision that looks administrative and is actually architectural: the lineage work reused the existing application architecture rather than sitting beside it. The portfolio system, the warehouse, the reporting platform — those components already existed in the repository, maintained by the architects, connected to capabilities and infrastructure. The lineage model added data objects, functions and flows to those same elements instead of creating a parallel set, which is the difference between one model with a new aspect and two models that will disagree within a year. The earlier CSV-era attempt at documentation elsewhere in the firm had made the parallel-set mistake, and its remains were still visible in the repository as a package of orphans nobody dared delete.
Structurally, each of the twelve reports owns a package holding its critical elements and its lineage views; the shared plumbing — feeds, staging, warehouse stores — lives in a common package the report packages reference. One overview diagram per report, one estate-wide diagram, and (added later) two timing views — nothing else: completeness questions go to the Relationship Matrix and model searches, not to ever-larger diagrams. Package security limits editing of the lineage packages to the two maintaining architects, so the model's truthfulness has a short list of accountable names attached.
Quarterly baselines — taken across the report packages and the shared plumbing package together, since a report's lineage crosses both — close the loop. When the regulator or an auditor asks what the firm understood its lineage to be at year-end, the answer is a baseline comparison away, not a reconstruction. For a model whose job is provenance, its own provenance had better be in order.
Building the model alongside the people who built the pipelines
We built the model in working sessions with the two senior developers, reading the actual SSIS packages and stored procedures together rather than interviewing anyone about them. Memory summarises; code does not. The sessions ran twice a week for about two months, each one walking a report backwards: start from the number on the page, find the view behind it, the tables behind the view, the jobs that load them, and so on back to a source system or an external feed. Each walk became elements and relationships in Sparx EA before the session ended — modelling live, on the shared screen, so the developers corrected the model in the room instead of reviewing a document afterwards.
Everything captured carries the same small set of tagged values: an owner, a refresh schedule, the upstream contract where one exists (which custodian, which vendor feed, which cut-off), and the quality checks applied on the way in. The tagged values are what turn a diagram into an operational answer — "when should this number have been refreshed, and who do I call if it was not" is a lookup, not an investigation.
Reading the code together had a side effect we now consider part of the method's value: three times, the code did something different from what everyone in the room believed it did. The sharpest case was a filter, added years earlier for a since-resolved data quality problem, that was still quietly excluding a small asset class from one risk figure. Nobody had decided that; the pipeline had just kept a temporary decision permanent. Two of the three discrepancies became change requests and one became a defect ticket, and all three went into the model's notes as worked examples of why the rule descriptions point at code rather than paraphrase it.
Coverage was checked with tooling rather than by eye. The Relationship Matrix reviews each adjacent hop — elements against the flows that feed them — but a critical element sits several hops from its sources, so the end-to-end check is a scripted search that walks the chain of flows backwards from each critical element. Any element whose walk reached no source system or external feed was unfinished work. By the end of the two months every walk terminated at a source, which became our definition of done — and re-running the same search today is how the client checks the model has stayed whole.
Following one number through the estate
The test we applied to the finished model was the four-day question, re-asked. Take the challenged valuation figure: the model now shows it as a critical data element on the client report, fed by the client valuation extract, which is produced by a valuation assembly function in the warehouse, which reads the position fact and the price history, which are loaded by named jobs from cleaned positions and vendor prices, which arrive respectively from the custodian's overnight file and the market data platform — each hop a relationship in the repository, each store and function carrying its owner, schedule and checks.
Walking that thread in Sparx EA's traceability view takes about two minutes, and the two minutes are repeatable by anyone with read access — not just by the two developers. That is the entire value proposition of the engagement in one sentence: the answer existed before; now the answer exists somewhere.
A lineage model at the right altitude does not replace reading the code — it tells you which code to read, who owns it, and what was supposed to happen there. In an incident, that is the difference between four days and an afternoon.
What the model answers
Once the twelve reports were traced, the model started answering questions beyond the one it was built for. Impact analysis became the daily use: when one of the custodians announced a file format change, the flows fed by that feed, the functions reading them and the reports at the end of the chain came out of a model search in minutes, and the change landed with a checklist instead of a scramble. The same search pattern — everything downstream of this, everything upstream of that — runs either through the traceability window or as saved searches we built for the recurring cases.
For audit, we configured document generation to produce a lineage annex per report: the diagram, the element definitions, owners, schedules and quality checks, generated from the repository on demand. The annex answered the original audit observation, and because it is generated rather than written, it does not rot the way the old inventory spreadsheet did. The scripting behind the saved searches and the generation templates is standard automation-API work of the kind we cover in our guide to the Sparx EA automation API.
The custodian episode is worth a moment more, because it shows the model at working temperature. The format change notice arrived with ninety days' lead time. The search for everything downstream of that feed produced two staging objects, five transformation functions and four of the twelve reports; the owners on those elements became the working group by lookup rather than by meeting; and the two reports that everyone had assumed were affected but were not — they draw positions from the other custodian — were struck off the plan on day one, with the model as the evidence. The change landed on schedule, and the post-change reconciliation confirmed the model's map of the blast radius had been exact. Ninety days earlier, that map would have been assembled by interview.
There was one unplanned finding. Tracing the reports backwards revealed a vendor feed that fed nothing on any critical path — a data subscription that had outlived every report built on it. A wider usage check confirmed nothing outside our scope consumed it either, and cancelling it paid a measurable slice of the engagement fee, which is not why you document lineage, but nobody complained.
What we deliberately left out
Scope discipline is easier to praise than to hold, so it is worth recording what was asked for and declined. Column-level lineage, as discussed, stayed out — the model names the dataset and points at the code. Data quality metrics stayed out: the model records which checks are supposed to run at each hop, but the pass rates and threshold breaches belong to the quality tooling that produces them, and copying numbers into a modelling repository manufactures staleness. The privacy team asked whether the same structure could carry personal-data mapping for their processing register; the honest answer was that the pattern transfers but the scope should not — mixing two registers with two owners and two update rhythms into one model tends to produce a model that serves neither. They took the pattern and built their own.
The most persistent pressure was for more reports. Once the twelve traced reports proved useful, every team wanted theirs added, which is flattering and fatal: the maintenance budget that keeps forty elements true does not keep four hundred true. Additions now require the same test the original scope used — would a wrong number here constitute an incident? — plus a named maintainer for the new paths. Four reports have passed that bar since; a great many have not, and declining them is what protects the ones inside the fence.
Keeping it current without heroics
A lineage model is only worth keeping if it stays true, so the maintenance design got as much attention as the model. Two mechanisms carry it. The first is procedural: the release checklist for the warehouse now includes one question — does this change touch any dataset in the critical scope? If yes, the model update is part of the change, walked through by the developer with one of the two maintaining architects — who holds the edit rights and applies it — in the same working style as the original sessions, minutes rather than meetings. Because the scope is forty elements and not four thousand, this stays within minutes per change.
The second mechanism is a reconciliation script, run quarterly through the automation API. It reads the warehouse catalogue and the SSIS package inventory, compares what exists against what the model claims, and reports drift both ways: physical objects on critical paths that the model does not mention, and modelled objects that no longer exist. The script does not fix anything — deciding what a difference means is architectural work — but it means structural drift — objects renamed, added or removed on critical paths — is normally noticed within a quarter rather than during the next incident; logic that changes quietly inside an existing package remains the release checklist's job to catch. It is the same pattern we apply to model validation generally: scripts find the discrepancies, people judge them.
What changed
The measurable change is response time. Provenance questions on the twelve reports — from clients, from the regulator, from the risk committee — are now answered in hours, usually by whoever received the question, with the trail exported straight from the repository. The audit observation was closed at the next cycle with the generated annexes as evidence, and the follow-up review specifically noted that the documentation was being maintained, which for lineage documentation is the rarer achievement.
The structural change is that the two senior developers stopped being a single point of failure for the reporting estate. Their knowledge of the critical paths is in the repository, examples, warnings and rule descriptions included. One of them has since moved to another firm — the event everyone had quietly feared for years — and the handover for the critical reports took an afternoon of walking the model with his successor.
And the number that started it all has not been challenged again; but when a different figure was, some months later, the trace took twenty minutes and the response went out the same day. The four-day question is now a twenty-minute question, and that ratio has held.
What we would do differently
We would capture refresh timing more richly from the start. Our tagged values recorded each dataset's schedule, but the questions that actually arrived were about alignment — which cut-off feeds which report, what happens when the custodian file is late, where the timing budget for a reporting day actually goes. We added a simple timing view for the two most time-critical reports in the final weeks; it should have been there from the second month, and it is now part of our standard pattern for this kind of engagement.
The limitation to be honest about is that a repository model documents intent, not behaviour. Sparx EA can say what should flow, who owns it and which checks are supposed to apply; it cannot observe what flowed last night, profile the values, or catch the job that silently loaded twice. Those are catalogue and observability concerns, and the model's reconciliation script narrows the gap without closing it. The model tells you which questions to ask of the runtime; treating it as the runtime's witness would be a category error, and we say so in the model's own front page.
Engagements like this sit at the intersection of our Sparx EA consulting and data architecture work: scoping, repository design, and the scripts that keep the result true after we leave. If your organisation has a number it cannot explain — or two developers it cannot afford to lose — you can reach us through our contact page.
This case study describes a representative engagement pattern. Organisational details are illustrative and do not identify a specific client.