Your agent is only as good as your joins
A dozen public registers about funds and banks, almost no shared keys, and why the agent sitting on top turned out to be the easy part.
Published: ·14 min read
I started this project thinking the hard part would be the agent. It was the joins.
The project is Institutional Entity Intelligence: an agent that answers questions about asset managers, funds, banks and the companies they hold. Ask it who controls a fund, what it holds and who owns more than five percent of it, and it answers with every number cited. Ask a plain chat model the same thing and you'll also get an answer, fluent and full of numbers, and there's a fair chance some of those numbers belong to a different fund.
That second failure is the one this post is about, and it has nothing to do with the model. Call it a data hallucination: the join underneath the answer is wrong, so the model faithfully narrates the wrong entity's data. No prompt fixes it, because the sentence comes out grammatical, cited and plausible. Almost everything that decides whether an answer is right happens before the model sees a token, which is why this is mostly a post about data engineering.
One question is four registers Take what an analyst actually asks about an institution: who is it, what does it hold, who owns a real stake in it, who controls it? It sounds like one lookup. It's four, against registers built by different regulators for different reasons.
GLEIF knows who the legal entity is and what it consolidates into. The SEC's Form 13F knows what a US manager held at quarter end. Schedules 13D and 13G know who crossed five percent of a listed company. Companies House knows who has significant control of a UK company. None of them was built to be joined to the others, and each names the same institution with its own number: an LEI, a CIK, a CUSIP, a company number. Get any one of those joins wrong and you've planted a data hallucination that the model will repeat with total confidence.
Almost nothing shares a key The project loads a dozen public sources, and the first useful diagram I drew wasn't a schema. It was a map of which identifier each source's records actually carry, because that alone tells you which joins are lookups and which are guesses.
GLEIF's Legal Entity Identifier sits in the middle because it's the only identifier here designed to be global: 3.4 million entities, any country, any legal form. The ISIN map and N-PORT attach cleanly because the LEI is written in the record itself. Companies House and the Fed's NIC register attach only partly (a UK company number matches a GLEIF registration ID for 101.8k of 5.7 million companies, which is under 2%). The SEC sources hang together on the CIK, the SEC's own number, through EDGAR's submissions file.
Then there are the two red lines. Form 13F, the richest holdings data in the whole map, carries no LEI at all: a filer is a CIK and a free-text name. Form ADV is keyed by a CRD number that appears in no other source I load. So everything the agent knows about what a manager holds has to cross a gap no key bridges.
The second view that paid for itself was the shape of one record from each source, with what it lacks written down as carefully as what it has.
- LEIglobal, ISO 17442
- Legal namethousands of near twins
- Accounting parentconsolidation, not control
- Exception reasonwhy a parent is missing
- No SEC CIK
- No holdings or stakes
- Filer CIKSEC-only number
- Filer namefree text, renamed over time
- CUSIPa security, not its issuer
- Valuethousands before 3 Jan 2023
- No LEI
- No group parent
- Registrant CIKjoins the SEC side
- Series LEI13,537 of 13,548 in GLEIF
- Holdingsdebt, derivatives, non-US
- Net assetsregistered funds only
- States its own LEIs
- Company numberUK-only
- Statusactive, dissolved
- Nature of controle.g. 25-50% of shares
- Controller IDpeople have none
- No LEI
Each record is quietly specific about what it isn't. GLEIF's parent is an accounting consolidation, which is not the same as control or ownership. A 13F filer name is free text that changes over time, one fund group files under many CIKs, and the CUSIP on a holding identifies a security, not the company that issued it. 13F values were in thousands of dollars before 3 January 2023 and in whole dollars after, and its put and call rows are options, not long positions. A UK controller who is a person has no identifier at all. Writing the gaps down is what tells you where entity resolution is needed, where it's impossible, and where it would be actively wrong.
Every source fills a line the others leave blank So why a dozen? Couldn't one good source do? No, and the quickest way to see it is to build one profile and watch what each source adds.
GLEIF is the spine: 3.4 million entities, 669k relationships between them and 6.2 million reporting exceptions that say why a parent is missing (a natural person, an entity that doesn't consolidate), so a missing row never gets read as "no parent". Its mapping files hang 9.3 million ISINs, plus BIC and company-register IDs, off those LEIs. EDGAR's submissions file covers 993k CIKs and adds almost nothing to a profile by itself; its value is better names and addresses for matching everything else on the SEC side.
On holdings, 13F gives the latest long US equity positions of managers with over $100M under discretion (10.7k filers and 3.8 million rows a quarter), and never the whole portfolio: no shorts, no derivatives, nothing non-US, nothing private. N-PORT fills that hole for registered funds, with debt, derivatives, non-US holdings and net assets across 14.4k fund reports. Better still, it states its own LEIs, and 13,537 of its 13,548 series LEIs are in GLEIF. The SEC's series and class register gives the structure inside a fund company, down to share classes and tickers, and OpenFIGI turns each CUSIP into a security with a type and a ticker, so a share is a thing of its own rather than an attribute of its issuer.
On ownership and control, Schedules 13D and 13G name anyone who crosses five percent of a listed company, with their voting power (13.5k of 21.7k reporting-person rows have no CIK at all). Forms 3, 4 and 5 cover officers, directors and ten percent owners, mostly people, who will never have an LEI. Companies House and its PSC register say who controls each of 5.7 million UK companies and how ("25 to 50 percent of shares", say), and the Fed's NIC register does the same for US banks, with equity percentages and mergers. Form ADV brochures are the one unstructured source: 996 PDFs where advisers describe fees, strategy and conflicts in their own words, searchable and cited to the page.
The overlap between all this is smaller than you'd expect, and the overlap is where the value is. A 13D/G owner, an insider or a fund's share classes only reach a profile once their CIK has been linked to an LEI. So the quality of that one link caps how much of every CIK-keyed source the agent can ever see.
A name match needs a decision attached To put 13F holdings on an LEI, something has to decide that "ACME GLOBAL OPP FUND 2 LP" is the legal entity "Acme Global Opportunities Fund II, L.P." and not its Fund III sibling, its feeder or its manager. GLEIF has thousands of near-identical fund names, so this is not a fuzzy-match one-liner.
The matcher runs in three separate steps. It retrieves about twenty candidates out of 3.4 million from OpenSearch, searching several versions of the name (full, with legal suffixes stripped, and squashed with no spaces for glued-together names). Country is a soft boost, never a filter, because a wrong country field is common in real data and a filter silently deletes the true entity before scoring even starts. Then it scores each candidate with one small function per signal (name similarity, jurisdiction, postcode, registration ID, fund-number and master/feeder conflicts), added up with weights from a config file. No learned model, so every point traces back to a named feature. Then it decides, and that last step is the one that matters.
The decision uses the gap to the runner-up as much as the score. A lone high score is trustworthy. Two close high scores mean the query doesn't contain enough information to choose, and the honest output is "I can't tell". A tie isn't a wrong answer. It's the right one.
Two bugs taught me how much the small details count. The first: I started with a Levenshtein-style ratio for name similarity, and "north rock capital" scored 66.7 against its own full legal name, North Rock Capital Management (UK) LLP, and 75.0 against the unrelated North Moor Capital Ltd. The wrong name won purely because it was closer in length. Comparing sets of words instead scored them 100 and 83.9.
The second was sneakier. The master/feeder check penalised the real master fund whenever a query simply didn't contain the word "master", because it treated "the query makes no claim" as "the query says this is not a master fund". In feature code, no signal and false have to be different values. Fixing that, plus a rule that refuses to auto-match when the query says neither master nor feeder and the top two candidates disagree on it, took the rate of confidently wrong matches on master/feeder pairs from 26.1% to zero.
How do you know any of it works? Not by eyeballing a few names. The test set is generated rather than hand-labelled: the correct answers come from the ISIN-to-LEI bridge (labels that never passed through my own name cleaning), and the traps are confusable pairs from inside GLEIF, with the same core name and a different fund number or structure. The number I watch isn't accuracy. It's how often the matcher is confidently wrong. On the current 6,000-query run, auto-matches are right 97.7% of the time, and on confusable pairs it picks the wrong twin with confidence 4.9% of the time. I'd rather push that down than trade it for coverage.
"I can't tell" is a feature Of 10,672 13F filers, the matcher auto-links 3,247 to an LEI and sends 853 to human review. The other 6,572 stay unmatched. That looks bad until you see what's left: brand names that map to many legal entities (Point72 alone is fifteen of them across seven jurisdictions) and near-identical funds with nothing but a name and a US state to go on. Guessing would raise coverage and quietly poison every answer built on the guess.
Two choices make the abstentions useful rather than just safe. First, decisions are kept as evidence, not state. Every match is stored with its score, the runner-up, each feature's contribution and a fingerprint of the config that produced it, and a human review is a separate dated record that overrides it. The automated decision is never edited, so when someone asks why the agent linked two records there's an answer, including the one a person overruled.
Second, a key the source states beats any matcher. N-PORT's own LEIs added 14.9k CIK and series links to the graph, four times what name matching on 13F resolves. The best entity resolution is the resolution you don't have to do, so load the sources that state their identifiers first and spend the matcher on whatever's left.
A parent is not an owner Once records land on the same entity there's a second trap, which is collapsing what they say about each other. It's tempting to have one "parent" edge and let every source write to it. But GLEIF's parent is an accounting consolidation. The NIC records bank control, with an equity percentage. A 13D/G filing records a beneficial owner of a voting class. PSC records significant control, often by a person, in bands like 25 to 50 percent. A 13F row is a holding of a security. Five different claims.
So the knowledge graph keeps them apart, with typed nodes (legal entity, fund series, share class, security, person, reporting owner) and one edge type per kind of claim. Node types matter as much as edge types: a CUSIP never becomes an identifier of its issuer, because a share of stock is not the company. That comes to 3.58 million nodes and 2.83 million edges before the UK's 16 million control records go in. Fold it all into "parent" and an agent asked "who controls Acme?" will cite an accounting parent, which is a data hallucination wearing a citation.
Bad data walks in through the front door A source's defects become the agent's defects, so the cheapest place to stop them is at ingest. The 13F bulk archive includes a summary page per filing with a row count and a total value, which my first version of the ingest never read. Checking every holdings table against its own summary sent 402 of 9,846 filings to a quarantine table with a typed reason, instead of into the holdings. Amendments follow the SEC's rules (a restatement replaces its original, a "new holdings" amendment adds to it), which turns 3.82 million raw rows into 3.50 million real ones.
Units are sneakier. Since 3 January 2023, 13F values have to be in whole dollars, and some filers still report in thousands. You don't need a price feed to catch them, because the 13F population is its own consensus: divide each row's value by its share count, compare that implied price with what every other filer implies for the same stock, and take each filing's median.
299 of 8,477 comparable filings sit at almost exactly a thousandth of everyone else. In one, a large manager's Apple position implied 25 cents a share when the real price was about $254. Those filings get flagged, not rescaled. Quietly correcting a source's mistake hides it behind a number that looks authoritative, so the agent shows the value as filed with the warning next to it.
By then the agent is the easy part It's an MCP server with seven tools: search for an entity, get its profile, walk its hierarchy, get a position's history, list beneficial owners, list typed connections, and search ADV brochures. Each tool is a thin wrapper over the same code the CLI and the web UI use. There's no retrieval over text chunks for anything structured; every tool returns typed evidence with its source, its criteria and a reproducible query hash.
The rule that made the answers trustworthy is short: the model never types a number. Tools return facts, each with an address (source, document, locator, field). Where a value should go, the model writes a placeholder like {{f:v1}}, and a gate renders the value only after re-running the tool behind it and getting the same fact back.
A literal number in the prose gets the answer rejected. So does an unknown fact ID, or a fact whose evidence the answer doesn't cite. A computed value, like the change in a position between two quarters, has to come from a registered formula and carry its inputs with it. Then a separate decision model checks the finished answer against the tool results and gives it a badge (verified, partially verified, not verified) in well under a second. The badge means the answer matches what was retrieved. It never claims the records are complete.
And none of that works without everything above it. An address only means something if the record it points at belongs to the right entity. A re-read only proves something if the data underneath was checked before it was stored. A typed edge only stops a wrong answer if the graph kept the types apart in the first place.
Six of the seven boxes are plumbing Here's the whole thing end to end.
Each source owns its own ingest, with a validation model between raw and cleaned rows, so a malformed record fails loudly instead of landing quietly. Parquet is the system of record for everything the build produces. OpenSearch holds copies shaped for each kind of read, with the joins done once at publish time rather than on every request, so you can drop the serving indexes, republish and lose nothing. The model is the box everyone asks about. I changed it least.