7 things to check before trusting public data apis data

Fifty-odd American registers and a handful of national ones do not disagree about facts. They disagree about vocabulary. Companies House in the UK lists "Active - Proposal to Strike off" beside plain "Active", and the two mean different things.

California's Secretary of State reports "FTB Suspended" for an entity that is still on the register. Merge that string with a neighbor's "Active" and the column stops answering anything.

These are the seven checks to run before you trust what a public data API hands back.

What to take away

1. The verbatim status string

Every value that arrives from a source gets two homes: the office's own string exactly as published, and your derived code. The verbatim column answers "what did the office actually say", which is the only question anyone asks when a decision is challenged.

Companies House returns "Active - Proposal to Strike off" for a company heading for dissolution. Flatten it to "Active" and the warning is gone. California's "FTB Suspended" names the Franchise Tax Board as the cause; "Suspended" alone does not.

This matters most for registration status, the field most often flattened and the one where flattening costs the most.

2. The mapping version

A mapping is your interpretation, not a fact. Version it and date each row's application. When the mapping changes, old derived values were produced by the old mapping, and knowing which one is the difference between a repairable history and a rebuild.

Derive your controlled vocabulary from the questions you need to ask, not from the union of what the offices say. A short list you can defend beats a long one that mirrors every source.

ISO 20275, the Entity Legal Form standard that GLEIF maintains, assigns four-character codes to legal forms. An ELF code lets you compare a German GmbH with a Delaware LLC without claiming the two are the same.

3. The unknown bucket

An unfamiliar source string maps to "unknown" and raises a flag. It never maps to the nearest-looking neighbor. Every silent guess becomes a permanent, invisible error that affects only the records that hit that string, which is exactly why nobody catches it.

GLEIF publishes a short, closed list of LEI registration status values, and it includes ISSUED, LAPSED and RETIRED. A string from a company register that matches none of your values is a signal to review, not a synonym to assume.

4. The identifier

Identifiers do not normalize across jurisdictions because they were never meant to. Jurisdiction plus identifier is the unit, as entity number lookup sets out. A single-column identifier is a collision waiting to happen.

Watch the stripped zeros and dropped prefixes in particular. A file number that loses a leading zero still looks like a file number.

EDGAR stores a CIK as ten digits with leading zeros, so Apple's is 0000320193. A Companies House number runs to eight characters, and a Scottish company's begins with SC. BRIS on the EU e-Justice Portal searches by national number, because the EU has no single company number.

5. The name fields

Legal name, former name, and trade name are three different things, each answering a different question. A name change orphans the old record unless you keep both. Keeping them in one column loses the question along with the answer.

EDGAR lists former names on a company's filing page, which is where a rename becomes visible. In the US, a trade name often sits in a county fictitious business name filing rather than in the state entity register.

6. The date kind

Filed, effective, and retrieved are three different dates, and the third belongs to you rather than to the source. Merging any two produces a number that looks more exact than anything anybody knows.

EDGAR separates the filing date from the period of report, and the two can sit months apart on the same document. Keep them in separate columns.

7. The channel

Record whether the value came from a web page, a documented interface, a bulk file, or something else. That column earns its place the first time an interface and a web page disagree and somebody has to explain which one your system read.

Companies House publishes the same register through a free REST API, a bulk product and web pages, with the API capped at 600 requests per five minutes. EDGAR offers the same filings through full-text search, daily index files and the data.sec.gov submissions API, which asks callers to stay at or below 10 requests per second.

Fields that resist normalization entirely

Addresses. A registered agent's address, a principal address, and a mailing address describe different relationships to a place. One of them frequently belongs to a registered agent service such as CSC or CT Corporation, which puts the same street address on thousands of filings.

Officer rosters. These are snapshots dated to a report on some registers and dated events on others. Companies House records an appointment date and a resignation date for each officer; a snapshot register gives you a current list with no event dates at all. There is no common shape, and forcing one manufactures precision on the snapshot side.

Reconcile, do not merge

A company on two registers has two records, two identifiers, and two vocabularies. Store them as two sets of rows with the jurisdiction visible on each. Where a combined view is genuinely needed, build it as a query rather than as a table, ordered by date, with the source on every row.

A merged table loses which office said what, and the offices do not agree about what a record contains in the first place, which the filing history material shows in detail. The link between two records is an assertion you made from a document, and it belongs in its own row with the document named.

OpenCorporates follows that pattern: one record per register, each showing its source register, joined by a link rather than replaced by a merge.

A schema that holds all seven

Every question about a value is answerable from the row.

Where the vocabularies come from

These words were not chosen to be interoperable. Each office wrote a vocabulary for its own law and procedures, and open data publication came later.

Catalogs on how public data is made available, such as the federal open government pages or the Government of Canada's open data guidance, cover the publication layer, not the legal vocabulary. No publication standard reconciles two different bodies of law.

W3C's DCAT vocabulary describes how to publish a catalog of datasets; it does not say whether two states mean the same thing by "Active". data.gov, run by GSA, catalogs federal datasets under that model.

There is no authoritative crosswalk to fetch, so the mapping is yours. Hold it as a documented interpretation rather than a lookup table somebody else validated.

What this buys, and what it does not

It buys the ability to query across jurisdictions while retaining the ability to answer honestly about any single one. It does not buy a common truth.

Two offices reporting different things about one company are usually both correct about their own filings. A schema that makes that impossible to express is not a cleaner schema, only a quieter one.

The licensing and access questions that sit above all of this are the subject of public data APIs, and they are settled before any of these columns are worth creating.

Common questions

Can I skip the verbatim column if I trust my mapping?

No. The mapping will change, an office will introduce a word you have not seen, and somebody will eventually ask what the source actually said. Without the verbatim column, that question has no answer.

How many derived status codes are enough?

Fewer than most teams start with. Begin with the distinctions your decisions actually turn on, add one only when a real use appears, and keep everything else recoverable from the verbatim column.

What do I do when an office changes its own vocabulary?

Add the new string to the mapping as a new version, leave old rows tagged with the old version, and do not backfill. The old rows correctly record what was published at the time.

Should the derived code ever be null?

Yes, for any source string you have not deliberately mapped. A null that raises a flag is far safer than a plausible code produced by a guess.