A case study in semantic layers over a data platform
Private client work. Everything below is method and architecture — no client, table, pipeline, or metric names appear. Figures are structural counts, not business data.
Modern data platforms fail at a specific, repeatable point: not ingestion, not storage, not compute. It's the moment someone asks a question that requires knowing what the numbers mean.
The warehouse had roughly 118 modelled tables fed by 65 scheduled pipelines. Every new engineer joining inherited that context socially — by asking a colleague, or by being told. The platform was well-built and effectively undocumented.
Three failure modes followed, all of them expensive:
amount might be gross, net, or already reconciled. The model had no way to know.Don't document the warehouse. Teach it its own vocabulary, then let it be queried in that vocabulary.
The work separates cleanly into what a machine can derive and what only a human can assert — and that boundary drives the entire design.
A script walks the platform and emits one structured file per table: columns, types, engine, owning pipeline, and lineage. No interpretation, no judgement. This layer is fully regenerable — delete it and rebuild, and you get an identical result.
Its entire value is coverage. It knows about all 118 tables, including the ones nobody remembers existing. That completeness is what makes the next layers trustworthy: they are reviewing a complete inventory rather than a curated sample.
For each table, a written answer to: what is this for, who depends on it, what does "correct" mean here, and what happens downstream if it breaks.
This layer is deliberately not automatable, and treating it as automatable is the mistake that makes most "AI documentation" projects useless. It is authored with domain knowledge and reviewed by the people who own the data.
The principle: the machine may propose; the domain owner disposes. Anything the model writes is a proposal for review, never a direct edit to curated content. Every disputed item gets an owner decision recorded alongside it, and owner decisions always win — the machine's confidence is not evidence.
Tables are not the unit people think in. People think in concepts — orders, customers, settlements, targets — and those concepts cut across tables and pipelines.
I authored a graph of 35 business concepts with 70 typed relations, mapping concept → table → producing pipeline. This is what turns "here are 118 tables" into "here are 35 things the business actually cares about, and here is where each one lives."
A separate tree expresses the metrics themselves, hierarchical:
L0 ultimate business outcomes
L1 business pillars
L2 measurable drivers
L3 atomic, source-backed metrics135 metrics across those four levels, each carrying its definition, unit, direction of goodness, grain, and a pointer to the SQL that produces it.
Crucially, every metric is machine-validated before it is trusted — a validator checks that each metric resolves up to a root, that its cited evidence actually exists, and that it is traceable to real source columns. Of 135 proposed metrics, 128 were approved, 6 failed validation, and 1 was explicitly rejected by its owner despite being technically valid. The system is designed to produce failures — a metric tree with a 100% approval rate is a sign nobody is checking.
Per-concept notes written for an assistant to read, plus a 24-term bilingual glossary (the source team worked in two languages, and a metric name that means one thing in each language is a defect waiting to happen).
The artifact had to be usable by someone with no access to the platform — a reviewer, an auditor, a future hire — and it had to survive being emailed around.
So the entire thing compiles into a single HTML file that opens from
file:// with no server, no network, and no runtime dependencies. Roughly 4 MB.
No package.json, no install step, no CDN.
All parsing happens at build time. The browser only reads a pre-built bundle. This was a deliberate trade: slightly larger file in exchange for an artifact that cannot break, cannot phone home, and cannot be denied by a firewall.
The same bundle is served behind a PIN-gated edge worker for team access — same bytes, two audiences, one source.
The value wasn't the documentation. It was what the documentation made visible.
Once metrics were defined once and validated against source, they could be reconciled against the numbers the business had been reporting manually. That comparison surfaced defects nobody had known existed:
| Class of defect | Scale | Disposition |
|---|---|---|
| Records double-counted across two source systems | 12 records, 0.06% overstatement | fixed |
| Partially-paid records counted at full value | 2.1% overstatement | fixed (owner rule) |
| Cancelled records with payment retained | 87 records | escalated to the owning team |
| Shipping cost counted as revenue | 926 records | escalated |
| A "trusted" headline figure that swung 92% in a single day | flagship metric | escalated, methodology documented |
That last one is the clearest argument for the whole approach. A widely-relied-upon number was unstable, and no one knew it was unstable because the definition lived in someone's head rather than in a definition that could be checked against source. Once the metric had a formal grain and a traceable definition, the instability became visible — and the disagreement about which definition was correct became a decision rather than an argument.
Each defect ships with a runnable verification query, so any claim can be re-tested rather than taken on trust.
Scope of the reconciliation: 18,537 orders matched across three separate systems (98.5% of those in scope), exposing 5 classes of systematic defect in a process that had been run manually.
It is easy to frame documentation as a writing problem. It isn't. The hard parts are all modeling:
That is dimensional modeling applied to meaning rather than to rows — and meaning, not storage, is what actually degrades an analytics platform over time.