The governed data layer

The state before

The client is a large enterprise, several operating companies and a workforce of several thousand, and the platform we were building for them needed to answer questions about people, projects, and work. Who is assigned where. Which subsidiary owns this cost. What has this project director actually got in front of them.

Every one of those questions crosses systems, and in an enterprise assembled over decades the systems disagree. Staff exist in the HR platform, in the ERP, in the project management tool, and in the spreadsheets that grew up wherever the systems never covered the case. Each holds a different spelling of a name, a different notion of who counts as an employee, and a different opinion about which project someone is on this month. What does not exist anywhere is a roster, or an org chart in the sense of a thing a query can join to. That is the normal condition at this size, not a failing peculiar to this client.

Why the obvious approach fails

The obvious approach is to query the sources directly and reconcile in the application. Here is why that wasn’t ideal.

Reconciliation belongs in one place or it happens everywhere. The moment two features both need “employees,” and each resolves the identity mismatch its own way, the platform starts disagreeing with itself. Two screens showing different headcounts is worse than one screen showing none, because the second is a gap and the first is a credibility problem you cannot argue your way out of in a room with executives.

We were not the only consumer. The platform is one of several things reading these sources, and the client’s own analytics work sits alongside it. A reconciliation that lives inside our application is a reconciliation nobody else can use, which means the same joins get written again somewhere else and the two answers drift. The layer had to be a shared asset or it would likely be duplicated, contributing to technical debt on both sides.

The work happens inside the client’s systems. This is their warehouse, their data engineering team, and their conventions, so the layer had to be built to fit an existing Snowflake estate rather than stood up beside it. It also meant the access model was subject to the client’s security organization. The intuitive design registers a scope per role, so the application knows what it is allowed to ask for. That does not survive an organization which adds roles continuously, and it leaves the security boundary sitting in application code, where a mistake ships on a deploy.

What I built

I stood up the database and schema for production and own nearly all of its migration history. The layer runs as dynamic tables: ingestion from each source, then conformed entities for employees, assignments, projects, and their links, then facts and rollups built only on those entities. Nothing downstream reads a source system directly, so identity reconciliation happens once and everything inherits it.

Dynamic tables were the right primitive because the result is materialized rather than a view that re-runs its whole graph every time somebody reads it, and because refresh is declared as a freshness target instead of scheduled by hand. Each table’s target is set by how fresh its upstream can honestly be, and the rest of the graph propagates off that, so nothing is recomputed to produce data that has not changed. On a warehouse the client pays for by the second, that is a cost decision as much as a design one.

The access model I designed with the client’s platform team moves the security boundary out of application code and into the warehouse. Sessions carry identity rather than a list of permissions the application assembled for itself, so admitting a new role is an administrative act instead of a code change, and the application has no path by which it can widen its own reach. Connections live for the length of a query rather than the length of a session, and the most sensitive fields sit behind a separate grant again. Working the identity provider question through with the client’s senior data engineers was part of getting that model accepted.

Single entry was designed in, not a side effect, and it does security work as well as consistency work. Nothing downstream reads a source system directly, so the access our database needs is not access to the original data, which is both more sensitive and far more expansive than anything the platform has a use for.

On top of the layer sit semantic views by domain: finance, safety, sales, preconstruction, recruiting, quality, and operations. They exist so the platform answers questions rather than serving tables, and they are the interface the analytics agents query, which means a new question usually costs a prompt instead of a pipeline.

What changed

The layer covers over a thousand employees, tens of thousands of assignments, and hundreds of projects. Every module built since reads those same conformed entities rather than its own interpretation of the sources, and the access model went through the client’s security review intact.

The semantic views on top are the part I can measure without asking anyone. In the trailing twelve months they served more than ten thousand queries from dozens of distinct users, none of them me.

What I take from it

What was asked for was a platform that could answer questions about people, projects, and work. What had to exist first was a roster assembled from systems that disagreed, and that turned out to be the durable asset rather than any one feature built on it.

The part that is more expensive to learn: a platform that cannot clear the client’s security review is a demo. The access model is not overhead on the real work. Inside an enterprise it is a substantial share of the real work, and designing it in the first month is the difference between shipping in weeks and negotiating for months.