Series -- From Ad-Hoc SQL to a Semantic Layer: 0: Overview · 1: Ten Answers · 2: Why Not Indexes · 3: Metrics Model · 4: View Pipeline · 5: Schema Design · 6: Authorization · 7: Migration Sequence · 8: Stakeholder Alignment
A domain owner pulled me into a call in early 2024 with two reports open side
by side. Both claimed to show billable hours for the same firm over the same
period. The numbers were different, and the question was simple: which one is
right. I couldn't answer it. Nobody on the call could. Each report had its
own SQL, written by someone else at some other time, and each had quietly
decided for itself what "billable" meant.
I have opened this series with that scene once already, because it is the
moment the whole project became undeniable. But I want to come back to it
now, at the end, because the scene contains a trap. It looks like an
engineering problem, and the engineering answer is obvious: build one place
where "billable hours" is defined, and point every report at it. That is what
a semantic layer is. Seven posts of this series are about how we built it.
This post is about the part that the layer did not solve. Building one
definition does not settle a disagreement. It forces you to put exactly one
definition in the model, and the moment you do, every domain that had been
running a different implicit definition is now wrong by decree. The layer
does not end the argument. It makes the argument mandatory.
The easy conflict and the hard one
The billable-hours discrepancy had a root cause you could find. I sat down
with both queries and read them. One of them joined through invoices, so it
was counting only hours that had actually been invoiced. The other queried
time entries directly, so it counted every hour logged with a billable
status, invoiced or not. Same firm, same period, two honest numbers
answering two slightly different questions that both got called "billable
hours." Once you see the join, you understand the whole thing. The
disagreement was an accident of implementation, and the fix was to decide
which question we meant and write it down.
Trust balance was not like that. Under IOLTA, the rules governing how law
firms hold client money in trust, there were three definitions of "trust
balance" in play, and all three were correct. The bank statement definition
was the actual cash position, what the bank said was in the account right
now. The firm ledger definition was what the firm's own books showed it owed
across all clients. The per-client sub-ledger definition was what one
specific client had in trust at that moment. These are not three
implementations of one number. They are three different numbers that answer
three different questions, and a firm needs all three, for reconciliation,
for accounting, and for client-level compliance respectively.
There was no SQL to read that would resolve it. Nobody had made a mistake.
The disagreement was legitimate, and the only way through it was to decide
which question the analytics layer was going to answer, and to be explicit
that it was not answering the other two. That is not a debugging task. That
is a governance decision, and it needed someone with the authority to make
it and the willingness to be held to it.
Who decides what "billable" means
I drove the alignment across the four domains, but I was not the one who got
to decide what the metrics meant. The decisions belonged to the domain
product managers, one each for billing, trust, case, and financial. They
were the people who owned the consequences of getting a definition wrong,
and so they were the ones who had to own the definition.
What I built was the process that forced the decision to happen and made it
stick. Every contested metric got a written definition doc. The doc named
the competing definitions explicitly, laid out which report or which domain
had been using each one, and stated the canonical resolution: this is what
"billable hours" means in the semantic model, this is the question it
answers, and here is what it deliberately excludes. The relevant domain PM
signed off on the doc before the metric was committed to the model.
That sign-off was the whole point. Not a verbal agreement on a call, which
evaporates the moment someone's report shows a number they do not like. Not
an escalation to a VP, which turns a definition question into a political
one and teaches everyone to escalate next time. A written doc, with a named
decision, and a name on the approval. When a report later showed a number
someone disputed, the answer was not "let me check the query." The answer
was "here is the definition your domain signed off on, and here is the doc."
The disagreement had already been had. We were just looking up the result.
The weeks that were not engineering time
The honest cost of this shows up in the trust domain. The trust accounting
reports were built. The materialized views were ready, the GraphQL schema
could express them, the caching worked. The reports could have shipped. They
did not ship, for weeks, because the three-ledger trust balance question was
still live and nobody had signed a doc.
Those weeks were not engineering time. Nothing was being coded. The
infrastructure sat finished and waiting while the billing PM, the trust PM,
and the financial PM worked out which trust balance the platform would
report and how to scope the other two so the reports that genuinely needed
them could still get them. When people talk about how long a migration took,
they usually mean how long the code took. On this project, a real fraction
of the calendar was alignment time, and no amount of engineering velocity
would have shortened it, because the bottleneck was agreement, not
implementation.
I found that clarifying rather than frustrating, eventually. The reports were
blocked by exactly the thing they should have been blocked by. Shipping them
faster would have meant shipping them on a definition nobody had agreed to,
which is how you end up with two reports showing different billable-hours
numbers in the first place.
What never resolved
Not every definition got settled. Some metrics stayed domain-specific,
permanently. In the previous post I mentioned the 5 to 10 reports out of the
original 30-plus that never migrated onto the new platform. A subset of those
were not stuck on traffic or effort. They were stuck on definitions that
never got sign-off, because the domains could not agree and the use case was
too narrow to force the question to a conclusion.
Those reports stayed on their legacy queries indefinitely. Each one kept its
own private definition of whatever it measured, the same way all 30 reports
used to. From an engineering standpoint that is unfinished work. From a
different standpoint it is the most honest output of the project. You migrate
most of a thing like this, and the parts you cannot migrate tell you
something true about your organization's actual ability to agree on shared
meaning. Some questions did not have one right answer that four teams could
live with, and the platform was better for admitting that than for forcing a
false consensus into the model.
Every migration was a governance event
Here is the insight I did not have going in. Forcing reports onto shared
definitions was progress, and it was the entire point of the semantic layer.
But it also meant that every single report that migrated required a decision.
Someone had to say, officially and in writing, what "billable" or "active"
or "trust balance" meant for the platform, and then be accountable for that
decision when a number moved.
That is a fundamentally different kind of work than building infrastructure.
Materialized views and GraphQL schema evolution are hard in a way that yields
to engineering: you can think harder, test more, refactor. Getting four
teams to agree that they had been measuring the same thing differently, and
that the platform was going to pick one definition and hold it against
everyone's objections, does not yield to any of that. It yields to a written
doc, a named owner, and the patience to let the argument happen instead of
routing around it.
The day the domain owner checked two reports on the unified platform and the
billable-hours numbers matched, the project felt done to me, well before the
migration actually was. Not because the code was finished. Because the two
numbers were finally answering the same question, and there was a doc, with a
name on it, that said why.
Top comments (0)