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
Ad-hoc SQL is the fastest way to ship a report and the slowest way to ship the tenth one. The first few feel like free wins. Someone needs a number, you write a query against the read replica, you hand back a table, everyone moves on. Nobody is thinking about the tenth report while writing the first. But by the time you have thirty, each one has quietly decided for itself what the business words mean, and no two of them decided quite the same way.
I told the front of this story in the overview post: a domain owner pulled me into a call in early 2024 with two reports open side by side, both claiming to show billable hours for the same firm over the same period, and the numbers didn't match. The question on the call was which one was right. I couldn't answer it, and neither could anyone else in the room. This post is about why that call was inevitable, and why the thing that looked like a performance problem turned out to be something worse underneath.
The two queries that measured different things
When I finally pulled up the SQL behind those two billable-hours reports, the divergence was almost boring. One query joined through the billing and invoice tables. It counted hours that had actually been invoiced. The other query hit time_entries directly and counted every entry with a billable status, regardless of whether it had ever made it onto an invoice.
Neither query was wrong. That is the part that took a moment to sit with. The first report was answering "how many billable hours have we invoiced," which is exactly the number you want when you are looking at collections. The second was answering "how many billable hours have our attorneys logged," which is exactly the number you want when you are looking at productivity or work in progress. Two legitimate questions, two correct answers, two reports that both put the label "billable hours" at the top of a column and left the reader to assume they meant the same thing.
The divergence wasn't a bug anyone introduced. It was the natural result of two engineers, at two different times, each making a locally reasonable choice about what "billable" meant and encoding it inline. There was no shared definition to conform to, so each query became its own definition. Multiply that across thirty reports and you don't have thirty queries, you have thirty competing opinions about the vocabulary of the business.
It wasn't just billing
I want to be honest that if this had been a billing-only quirk, we might have papered over it with a footnote and moved on. It wasn't. Once I started auditing, the same pattern showed up everywhere the reports touched a business concept with any nuance.
Active case count was its own small argument. Which case statuses count as "active" is a genuine business question, and different reports had drawn the line in different places, so a firm could see one active-case number on a dashboard and a different one on a summary export. Trust balances, which carry real regulatory weight in legal accounting, could differ depending on which ledger a report queried. Financial summaries produced discrepancies that users noticed and asked about. None of these were exotic edge cases. They were the core numbers firms use to run their practices, and they disagreed with each other in ways that were visible to the people who depended on them.
Three ways this actually hurt
The damage came in three forms, and the order matters because the last one is the one that scared me.
The first was wrong decisions. Firms make real calls on billing and collections data. When the number a firm is looking at depends on which report they happened to open, some of those calls are being made on the wrong figure without anyone knowing it.
The second was operational drag. Because you couldn't trust any single report, the workaround was to cross-check. Domain owners started manually reconciling conflicting reports before they'd share a number with a stakeholder, which is exactly the kind of invisible tax that never shows up in a metrics dashboard but eats hours every week.
The third, and the worst, was the credibility loss. Once a domain owner catches two authoritative reports disagreeing, they stop trusting the reports. They stop trusting all of them, not just the two that disagreed. People began asking for manual verification of numbers the system was supposed to be the source of truth for. That is a slow erosion, not a crash, and it is much harder to recover from than an outage. An outage ends. A reputation for unreliable numbers follows every report you ship afterward.
The index-tuning detour
Here is the part I find most instructive in hindsight, because we got the diagnosis wrong first.
The presenting symptom was latency. The reports were slow, load on the read replica was climbing with every new report, and the obvious move was to make the slow queries faster. So that is what we did. We took the worst-offending queries, added indexes, and tuned them one at a time. It worked. Latency came down, the replica breathed easier, and for about two weeks it felt like we had solved the problem.
Then the next report landed, written from scratch against the production schema like all the others, and the latency and load problems came right back. That was the tell. We were treating the symptoms of one query while the actual disease, the fact that every report re-derived its own definitions and its own access pattern from nothing, was completely untouched. You cannot index your way out of thirty definitions of the same concept. Indexing makes a wrong-but-slow answer into a wrong-but-fast one.
That detour taught me that performance and consistency are different failure modes that happen to look identical from the outside. Both present as slow, unreliable reports. But performance is a downstream problem you fix with faster queries and better caching, and consistency is an upstream problem you fix by moving the definition to a single place before anyone queries it. Optimizing performance downstream of thirty competing definitions doesn't just fail to fix the consistency problem, it locks the mess in by making it cheaper to keep adding to.
What the audit really found
We went into this project to fix performance. Latency was the ticket, the replica load was the pressure, and the two-week index reprieve was the false summit that told us we hadn't found the real thing yet.
What we actually found was definition drift. The reports weren't just slow, they were quietly answering thirty different questions while presenting the answers under the same names. That reframe is the whole reason the rest of this series exists. If the problem had really been latency, a faster database or a caching proxy would have been the end of it. But the meaning of the data lived in thirty places instead of one, and no amount of tuning consolidates meaning.
In the next post I'll get into why we didn't just keep optimizing, and why "add more indexes" and even "add a data warehouse" were the wrong shape of fix for a problem that was really about where a definition is allowed to live.
Top comments (0)