Reference data is the quiet foundation of every data platform. State codes. Country codes. Funding types. Classification hierarchies. Without it, nothing joins. Without governance, everything breaks.
And yet, reference data is almost always the last thing teams design deliberately — and the first thing that causes production incidents.
I recently worked through a reference data architecture challenge on a large multi-project data platform built on Microsoft Fabric. The problem wasn't technical. It was structural. Multiple projects each needed the same reference tables. Each team had quietly started managing their own copy. The result: three slightly different versions of what should have been a single authoritative list, served from three disconnected endpoints to a downstream application that needed exactly one.
Here is the pattern we landed on — generalised here but directly applicable to any modern data platform on Fabric, Databricks, or Azure Synapse.
The Problem Worth Naming
When reference data is not deliberately architected, four failure modes emerge almost inevitably.
Duplication. Project A sources a country code table. Project B sources the same table independently. They diverge within weeks — different nullability, different default values, different historical records.
No clear owner. When a data quality issue is raised, no one knows whether the problem lives in the source system, the project pipeline, or the consuming application.
Unstable consumer endpoints. A downstream application — a business rules engine, a reporting layer, a third-party integration — has to connect to multiple project workspaces to get all the reference data it needs. Every new project onboarded means a new connection.
Schema drift without warning. A pipeline change in Project A silently alters a shared table. The downstream application breaks. No one was notified because there was no governance gate.
The Architecture Pattern
The solution is a four-layer federated reference data architecture. The core idea is simple: reference data is owned by the platform, not by projects. Projects are consumers and contributors — not custodians.
Layer 1 — Authoritative Sources
All reference data originates from exactly two places. A system-of-record (in our case, a CRM/ERP-class platform synced continuously into the data platform via a native connector — no custom pipelines). And a legacy source, migrated once as a bootstrap. After migration, the legacy system is frozen — no further updates come from it. The source of record owns all future corrections.
Both source workspaces are read-only. Nothing writes to them.
Layer 2 — Common Reference Data Workspace
This is the enterprise reference data store. It ingests from both sources into a Bronze layer, then a governed pipeline merges, deduplicates, and curates into a Silver layer. This Silver layer is the single authoritative version of every reference table in the platform.
This workspace is shared platform infrastructure. No individual project owns it. No project can modify it. It belongs to the platform team.
Layer 3 — Project Workspaces
Each project workspace shortcuts from the Common Reference Data Silver layer — zero-copy, no data movement, always reading the authoritative version.
Projects also generate a small number of write-back reference tables — derived from their own workload processing. For example, an employer classification derived from training activity data. These don't come from the system of record, but they are reusable across projects and need the same governance rigour. They live in a dedicated Ref Silver Lakehouse within each project workspace, completely separate from workload tables.
Layer 4 — Consolidated Reference Data Workspace (the Consumer Surface)
This is the layer that solves the third-party integration problem. A single, dedicated workspace — no pipelines, no compute, no raw data — that holds only shortcuts. It shortcuts from the Common Reference Data Silver (for system-of-record tables) and from each project's Ref Silver Lakehouse (for write-back tables).
It exposes everything through one SQL analytics endpoint.
The downstream application — whatever it is, a business rules engine, a reporting tool, a third-party SaaS — connects once. It never needs to know how many projects have onboarded, or where a table originated.
The Two Data Paths
Path B is largely automated — governed by a metadata registry that drives shortcut provisioning. Path C requires formal onboarding: schema review, data dictionary submission, and Platform Architect sign-off before any write-back table enters the shared surface.
Governance: Structural, Not Procedural
The key design decision that makes this work is that governance is enforced structurally, not through policies people have to remember.
- The Consumer Workspace is read-only at the Fabric permission layer. The downstream application cannot write to it regardless of how it is configured.
- The metadata registry drives automated shortcut provisioning. A table not in the registry does not exist in the consumer surface — there is no way to bypass this silently.
- The onboarding gate for Path C tables prevents write-back tables from entering the shared surface without review. Schema consistency and naming convention compliance are checked at the gate, not discovered post-incident.
SQL Views sit on top of the Delta shortcuts in the consumer workspace. They provide a stable, abstracted query surface — if an upstream schema change occurs, the view absorbs it before the downstream application is affected.
What This Delivers
For the downstream application: one connection string, one SQL endpoint, all reference data. Stable across project onboarding, stable across schema evolution.
For project teams: read access to authoritative reference tables via shortcuts. A clear, governed path to contribute write-back tables when needed. No responsibility for maintaining shared data.
For the platform: no duplication — structurally prevented. Clear lineage from source to consumer for every table. Ownership is unambiguous. Data quality accountability is explicitly assigned.
For governance leads and CDOs: a single point of control. Every reference table in the platform is registered, described, owned, and traceable. No shadow copies. No orphaned tables.
Closing Thought
Reference data architecture is not glamorous. But it is foundational. The pattern described here is not complex — it is disciplined. A federated shortcut model, a single consumer surface, two governed paths, and a metadata registry as the source of truth for what exists and who owns it.
If your platform has more than two projects sharing reference data and no deliberate architecture for managing it, the duplication is already happening. The question is whether you find out now or after a production incident.
Design it deliberately. Own it centrally. Serve it from one place.