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 engineering problem was solved. The semantic model worked, the
materialized views were refreshing on schedule, and the authorization-aware
GraphQL API was live and returning correct results. I remember the specific
relief of watching a query come back through the new layer and matching it
by hand against the old read replica. The numbers agreed.
And then I looked at the list. Thirty-plus reports were still pointing at
the old read replica, none of them aware that a better path existed. The
migration problem was just starting, and it opened with a question that had
nothing to do with materialized views or schema design: in what order do you
move them?
Two axes, not one
It is tempting to sequence a migration by traffic alone. Move the heaviest
queries first, get the biggest latency win, declare progress. That instinct
is half right, and the missing half is what made this migration interesting.
There were two axes that mattered, and the Part 0 overview names both of
them: how load-bearing a report was, and how contested its metric
definitions were.
"Load-bearing" was not just query volume. It was a combination of replica
traffic and business consequence. A report that firms used to make billing
or collections decisions carried more weight than a case-summary report that
someone glanced at occasionally, even if the two had similar query counts. A
report could be low-traffic and still high-stakes, because the cost of it
being wrong was measured in a firm's money rather than in database load.
Load-bearing meant "how much breaks, and how badly, if this migration goes
wrong."
"Contested" was a different thing entirely. It measured whether the metric
definitions behind a report required stakeholder alignment before migration
was even technically possible. A report built on a disputed definition could
not move, because there was nothing for it to move onto. The semantic model
demanded exactly one canonical version of each metric, and until the
stakeholders agreed on what "trust balance" meant, the model had no version
of it to offer. Contested meant "how much agreement has to happen before the
new layer can accept this at all."
How the two axes interacted
Plot every report on those two axes and the sequence writes itself, mostly.
Low-load, low-contested reports went first. These were the proof-of-concept
migrations, the ones where the goal was not the latency win but the
demonstration: prove the pattern works, validate that the new layer returns
correct results, build confidence before touching anything that a firm would
notice. If something went wrong here, the blast radius was small.
High-load, low-contested reports were the compelling early wins. Once the
pattern was proven, these were where the latency numbers came from. Their
definitions were settled, so there was nothing blocking them, and their
traffic meant moving them mattered. This is the group that made the project
look successful to anyone watching the dashboards.
High-contested reports went last, and here is the part that traffic-first
sequencing gets wrong: they went last regardless of load. A high-traffic,
high-contested report could not jump the queue on the strength of its
traffic, because the semantic model physically could not accept it until its
definitions were settled. Contention was a hard gate, not a soft
preference. You can defer a low-priority migration; you cannot migrate a
report whose central metric has three competing definitions and no agreed
winner.
Starting with case analytics
The first domain we migrated was case analytics: active case count, case
status, and the reports built on them. On both axes, these scored lowest.
"Active case" is close to an enum-level concept. A case is open or it is
not, and while there are edge cases, they are the kind you can resolve by
reading a status column rather than by convening four teams. There were no
IOLTA implications, no invoicing dependencies, no money riding on the exact
boundary of the definition. And these reports were lower-traffic than the
billing reports that firms hit constantly.
That made case analytics the right proof-of-concept. We migrated these
reports first not because they were urgent but because they were safe. They
let us exercise the whole pipeline -- semantic model, materialized view,
GraphQL query, cache, frontend shell -- against real reports without
gambling on a definition that might still be under dispute. By the time we
moved to anything financial, the pattern had already survived contact with
production.
Why trust accounting was last
At the far corner of the grid sat trust accounting, and it stayed there
until the end.
Trust balance has three legitimate answers under IOLTA, the rules governing
how law firms hold client money. There is the bank statement figure, the
firm ledger figure, and the per-client sub-ledger figure, and each is
legally valid in a different reporting context. This is not a bug or a
disagreement to be resolved by picking the smartest engineer's preference.
All three are simultaneously correct, and choosing which one a given report
should show is a compliance decision, not a technical one.
No trust report could migrate until that question was resolved and the
domain PM signed off on a canonical definition, or on an explicit scoping of
which definition belonged to which report. So the trust reports sat in the
queue while the alignment work ran alongside the migration. That alignment
work is the subject of Part 8; for sequencing purposes, the relevant fact is
that trust was gated on a conversation, not on engineering effort, and the
conversation took time.
Parallel running and shadow comparison
Each report migrated one at a time, and each one went through a shadow
comparison period before cutover. The legacy query and the new layer ran
simultaneously, both producing output, and we compared the two before
decommissioning the legacy path. A migration was not "done" when the new
query returned results; it was done when the new query had agreed with the
old one across a validation window.
For billing and trust reports, that window was longer. When a report drives
a firm's collections decisions, or touches client trust money, the bar for
"we are confident these match" is higher than it is for a case-count report.
More confidence required a longer comparison period, which is another way
the load-bearing axis expressed itself: it did not just influence order, it
influenced how carefully each cutover happened.
What stayed behind
The honest version of "we migrated the system" is that we migrated most of
it. Twenty to twenty-five of the thirty-plus reports moved. The remaining
five to ten did not, and they fell into two groups.
The first group was low-traffic, edge-case reports: one-firm customizations,
rarely-accessed summaries, reports where the cost of migration plainly
exceeded the benefit. Nobody was going to reap a latency win on a report run
twice a quarter, and rebuilding it on the new layer was effort spent for
almost no return.
The second group was blocked on metric definitions that were never fully
resolved. Some disagreements did not converge. Where a definition fight
stalled and the report was not important enough to force the issue, the
report stayed on the legacy query path permanently. Those reports are still
there, as far as I know, quietly running their own SQL against the replica,
each with its own private answer.
Every migrated report was a governance artifact
Here is what the sequencing revealed that the design work had obscured.
Every report that successfully migrated was also proof that a definition had
been agreed on. You could not move a report onto the semantic model without
first settling what its metrics meant, so a migrated report was a
governance artifact as much as an engineering one.
The write-off case from Part 0 makes this concrete. A write-off was excluded
from one billing report and included in another, and that was not a bug. It
was a definition that had never been made explicit, and migration forced it
into the open. Someone had to decide, on the record, which treatment was
correct, and that decision became part of the model.
This is why sequencing was not a purely mechanical exercise. The order in
which reports migrated determined which definitions got forced into the open
in which order, and that in turn determined which stakeholder conversations
were live at any given moment. Migrating the billing reports meant the
write-off conversation had to happen then; deferring trust meant the IOLTA
conversation could wait. The migration schedule was, in effect, a schedule
for the arguments.
That is also why the sequencing decisions were made jointly with the program
PM, not purely by engineering. Which reports moved first was not only a
technical call. The PM had visibility I did not into which firm-facing
reports were politically sensitive or tied to external stakeholder
commitments, and that visibility fed directly into the order. Co-owning the
sequence with someone who understood the business stakes was not a courtesy.
It was the only way to sequence a migration whose real constraint was
agreement rather than code.
Top comments (0)