Reference Data: The Boring Tables That Break Everything Downstream

Nobody gets promoted for fixing reference data. It's the unglamorous layer underneath everything else: class codes, peril codes, currency rates, territory mappings, product and coverage codes, the little lookup tables that give raw values their meaning. It's also, quietly, one of the most common root causes when two reports disagree, a migration produces nonsense, or a model behaves oddly on a subset of the book. The tables are boring. The failures they cause are not.

Why reference data goes wrong

Reference data tends to be copied rather than shared. Each system gets its own version of the class-code list, its own territory mapping, its own currency table — and they drift. A code is retired in one place and kept in another. A mapping is extended locally to handle an edge case. A rate table is updated on a different schedule. Nothing breaks loudly; the systems just gradually stop agreeing about what the same code means, and every downstream join inherits the disagreement.

Where it breaks down

  • Multiple copies, no source of truth. Each system holds its own version, so "the same" code can mean different things in different places.
  • Ungoverned change. Codes get added, retired or redefined without a process, so history becomes inconsistent.
  • No versioning. A mapping changes and old data is silently reinterpreted, which quietly rewrites the past.
  • Local hacks. Edge cases get handled by extending a lookup in one system, which then disagrees with everywhere else.

Why it's a data-foundation problem

Reference data is the vocabulary the rest of your data uses. If the vocabulary isn't governed, every analysis built on it inherits the ambiguity, and no amount of downstream cleverness fixes it. Treat it as a first-class managed asset: one source of truth, controlled change, versioned history so past data keeps its original meaning, and distribution to consuming systems rather than copies maintained independently. It's unglamorous work with unusually high leverage, because it improves everything built on top at once.

What good looks like

  1. A single governed source of truth for each reference set, distributed rather than duplicated.
  2. Controlled change — additions, retirements and redefinitions follow a process, not a ticket to one system.
  3. Versioned history so historical data retains the meaning it had when it was written.
  4. No local extensions, so edge cases are solved centrally instead of creating divergence.

Reference data is invisible until it causes a problem, and then the problem looks like something else entirely — a broken report, a bad migration, a model quirk. Getting that layer governed is exactly the kind of unglamorous, high-leverage work we do with insurers at IntelliBooks.

Your data can only be as consistent as the code tables that give it meaning. Those tables deserve more attention than they get.

Comments

Post a Comment

Popular posts from this blog

Why Your Insurance Data Warehouse Didn't Fix Anything

You built the data warehouse. It took eighteen months and cost more than planned. Policy data, claims data, billing — it's all in there. The project was declared a success and everyone moved on. And yet: underwriting still can't see the whole customer. The AI initiative is still stalled. Someone still asks for a report and waits three weeks. Nobody says it out loud, but the question hangs there — what exactly did we buy? You're not alone, and it isn't because you built it badly. It's because a warehouse solves a problem you didn't have. The problem a warehouse solves Warehouses solve location . Before: your data was in eight systems. After: it's in one place. That's genuinely valuable — you can run a query across policy and claims without begging two teams for extracts. But notice what that fixes: it fixes where the data is . It doesn't touch what the data means , whether it's trustworthy, or whether the records refer to who you think t...
Read more

Embedded Insurance: Why the API Is the Easy Part

Embedded insurance is the idea that coverage should be offered at the moment of need, inside someone else's product — travel insurance at checkout, device protection when you buy the phone, cover for the rental car in the booking flow. It's one of the fastest-growing distribution models in the industry, and every insurer has a strategy slide about it. Most of those strategies underestimate the same thing, and it isn't the partnerships or the product. It's the data plumbing. Everyone assumes the API is the hard part. The API is the easy part. What embedded insurance really demands The promise of embedded is speed and invisibility: a customer buys a flight and, in the same breath, is offered exactly the right travel cover at exactly the right price, and accepts with one tap. No forms, no friction. Deliver that and you've won. But "exactly the right cover at exactly the right price in one tap" makes brutal demands on your back end: You must price i...
Read more

Insurance Knowledge Graphs: The Foundation AI Needs Before It Can Think

Every insurer wants enterprise AI. Few realise they're missing something much more fundamental. Knowledge. Not data. Knowledge. Insurance companies already possess enormous amounts of information. Policy systems contain customer records. Claims platforms hold loss histories. CRM systems track interactions. Document repositories store underwriting reports. Data warehouses consolidate analytics. Yet AI still struggles to answer surprisingly simple questions. Not because the information doesn't exist. Because the relationships between that information remain invisible. Data tells you what exists A customer database tells you who someone is. A policy database tells you what they bought. A claims system tells you what happened. An accounting platform tells you what was paid. Each system is internally consistent. Together they rarely behave like a connected enterprise. Humans mentally assemble those relationships every day. Computers usually canno...
Read more