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
The first thing we tried was the obvious thing. We took the worst-offending
report queries, looked at their execution plans, and added indexes. We tuned
them one at a time, the way you would tune any slow query against a
production replica. It worked. For about two weeks the reports were fast
again and the load on the replica settled down, and it felt like we had
handled it.
Then the next report landed, and the same latency and load problems came
back. That was the tell. When a fix buys you two weeks and then the problem
returns wearing slightly different clothes, you are not looking at a
performance problem. As I covered in Part 1,
what actually broke at MyCase was that thirty-plus reports had each quietly
decided for themselves what "billable hours" meant, and no two of them agreed.
Index tuning treats the symptom of a query we would re-derive again next time.
It does nothing about the fact that the same definitions kept getting
re-derived at all. You cannot index your way out of a governance problem.
So we stopped tuning queries and started asking a harder question: what is
the right level of abstraction to put underneath thirty reports so that
"billable hours" gets defined once. That question had several plausible
answers, and most of this post is about the ones we ruled out, because the
reasons we ruled them out are what pointed us at the semantic layer.
The BI tool that solved the wrong three problems
The most tempting off-ramp was a business-intelligence tool. Something in the
Metabase or Looker family, where analysts build reports against a modeled
data source and you never write raw SQL for a report again. On paper it
addresses exactly what we were complaining about.
We evaluated one and rejected it on three blockers that all applied at the
same time. First, it could not embed natively in the MyCase product UI. These
reports are a product feature that law firms use inside the application, not
an internal dashboard for our own analysts, and a bolted-on iframe to a
separate BI product is not that. Second, it could not enforce firm-scoped,
field-level authorization. MyCase is a multi-tenant legal SaaS, and a trust
accounting figure that firm A can see is a figure firm B must never see, down
to the individual field. General-purpose BI tools are not built to enforce
that kind of per-tenant, per-field rule as a hard invariant. Third, it could
not express our business logic. What counts as billable, how a write-off is
categorized, how trust balances reconcile across three ledgers: that is domain
logic, and encoding it inside a BI tool's modeling layer would have just
moved the thirty-definitions problem into a tool that was worse at owning it
than our own codebase was.
Any one of those might have been workable on its own. All three at once meant
the BI tool was solving a problem we did not have while failing at the three
we did.
The data warehouse that was a later step, not an alternative
The other reflexive answer was a data warehouse. Move the analytics workload
off the transactional replica entirely, into a system built for it. I want to
be precise here, because a warehouse is a genuinely good idea and we did not
reject it on its merits. We rejected it as an answer to this problem.
A warehouse moves data. It does not decide what the data means. If you take
thirty ad-hoc queries and point them at a warehouse, you have thirty ad-hoc
warehouse queries, and each one still gets to invent its own definition of
billable hours on the way through. The consistency problem survives the move
completely intact, now with a bigger bill attached. The semantic layer is the
prerequisite, not the alternative. Once the definitions are canonical and live
in one place, a warehouse can absolutely sit underneath as a performance and
scale decision. But it has to sit under the definitions, not instead of them.
That reframing mattered for sequencing too. Treating the warehouse as a future
step meant we did not block the fix that actually addressed trust erosion on a
much larger infrastructure migration.
The faster database that would have preserved the problem
The last one is the subtlest, and it is the one I have to talk teams out of
most often. If reports are slow and hammering the replica, why not just make
the reads faster: a beefier database, or a caching proxy in front of the
existing queries.
Both of those preserve the actual problem exactly. The meaning of the data
would still live in thirty different places. You would have made thirty
inconsistent answers arrive more quickly. A cache in front of divergent
queries caches divergent results. Part 0 documents this reasoning directly,
and it is the load-bearing insight of the whole project: performance tooling
downstream of competing definitions locks in the mess instead of clearing it.
The fix had to move the definition upstream, and no amount of read throughput
does that.
Why the semantic layer, and why not just materialized views
Here is the distinction that took me the longest to articulate to
stakeholders, because materialized views were part of the eventual design and
it is easy to conflate the two.
The materialized views are a performance optimization. They pre-compute the
shape of the data so a report is not reverse-engineering it out of OLTP tables
on every request. What they do not do is decide what "billable" means. A
report querying a view directly still has to make that decision at query time,
which means we would be right back to thirty query authors making thirty local
choices, just against a faster source.
The semantic layer is what moves that decision upstream and locks it down. In
our case that layer was a Ruby module namespace, one class per named metric,
with the canonical definition living in the class and versioned by convention.
module Analytics
module Metrics
class BillableHours < Base
VERSION = 2
DESCRIPTION = "Billable-status time entries, write-offs excluded"
def self.view_name = :analytics_billable_hours_v2
def self.query
TimeEntry.joins(:matter).where(billable: true, written_off: false)
end
end
end
end
The class is the single source of truth. Its query method defines the metric,
its view_name points at the materialized view that pre-computes it, and its
VERSION lets a definition change without silently rewriting history under
reports that depended on the old meaning. Reports never write SQL. They call
the metric class. When the business redefines billable hours, we change one
class, bump the version, and every downstream report inherits the new
definition at once. That is the property none of the alternatives could give
us, because none of them had a single place for the definition to live.
The authorization insight that picked the API
One decision was left over. Reports could have queried the semantic model by
running SQL against its materialized views. We put a GraphQL API in front
instead, and the reason was authorization.
Field-level, firm-scoped authorization in a multi-tenant legal SaaS is not a
thing you want thirty report authors each implementing against raw tables.
Get it wrong in one place and a firm sees another firm's trust balance. If
every report hand-rolls its own permission checks, you have thirty chances to
leak data and no single place to audit. The API layer was the one place to
enforce those rules once, for every consumer, as an invariant rather than a
convention. That is the same logic that ruled out the BI tool, arriving from
the other direction: authorization was non-trivial enough that it wanted to
own an interface, and that interface was the API.
Each layer in the eventual stack earned its place by solving a specific
constraint the alternatives could not. The next post gets concrete about the
metrics model itself: how a named, versioned metric actually behaves, and what
changed the day reports stopped writing SQL.
Top comments (0)