My team was stuck behind an analyst queue. I spent a weekend building them a way around it, then spent a lot longer proving it could be trusted.
I manage five product managers. Last spring all five of them were stuck behind the same wall.
We had to audit data health across customer identity, rewards, and wallet balances before anything we wanted to build on top of that data could be trusted. Personalization, the ML work, all of it sat behind the audit. The audit itself meant a steady stream of exploratory queries. None of them were hard. There were just a lot of them, and each one depended on what the last one returned.
Our analysts could have written any single query in about four minutes. Getting to the front of their queue was the problem, and we never did. Engineering was worse. A data quality question does not survive sprint planning against anything customer-facing, and I would not have prioritized it either.
So my PMs waited. Some of them started guessing instead, which was worse, and I spent more than one one-on-one telling people not to do that.
THE DIAGNOSIS
The bottleneck was never analytical skill. It was queue position.
I had no engineering headcount to assign and no realistic path to more analyst capacity. Both had been asked for and turned down, for reasons I understood, because there were larger fires that quarter.
That left one resource I actually controlled, which was my own weekend. A group product manager spending a weekend writing specs for an internal tool is not an obviously correct use of the job. I went ahead because the downside was two days of my time and the upside was unblocking five people on work that everything else depended on.
Cheap to try, expensive to keep not trying. That is most of what the decision came down to.
There was no procurement process here and I want to be straightforward about that. I did not run a build-versus-buy analysis. I wanted to find out whether the problem was tractable at all before I asked anyone for anything.
I have since looked properly at Snowflake’s Cortex Analyst and Databricks’ Genie. Both generate better SQL than what I put together, which is unsurprising given that they have teams on it and I had two days.
Neither would have solved the part that turned out to matter. Our metric definitions did not exist anywhere a machine could read them. They lived in analysts’ heads, inside dashboard filters, and in whoever happened to remember which join was the right join. Every one of those vendor tools needs a semantic layer underneath it before you can trust its output, and assembling that layer was most of the work.
If we swapped my system for a vendor product tomorrow, the definitions would carry over intact. That is the piece worth owning.
I cannot read SQL. I have never written a query.
That ruled out the normal way of validating these systems, which is to put a generated query in front of somebody competent and have them judge it. I could not do that myself, and I was not going to build something that required an engineer’s attention on every output, since engineering attention was the scarce thing I was trying to stop consuming.
So the system proves that a query conforms to a declared definition of what a metric means, instead of asking a person to look at it. Verification by contract rather than by inspection.
It also scales better than the thing I could not do. A human reviewing queries becomes a bottleneck again the moment volume increases, and gets less careful late in the day. A contract check runs in milliseconds and does not have a late afternoon.
I would like to present this as foresight. It was a limitation I designed around.
A contract is a machine-readable statement of what a metric means: the aggregation, the filter it requires, the grain of the output, the source table, and which joins are permitted.
Take a synthetic example. Say net_revenue is the sum of an order-level amount, filtered to completed orders, at order grain. Ask for revenue on orders containing a product category and the obvious query joins orders to line items and sums the amount. It runs, it returns a number, and the number is wrong, because an order with three matching line items gets counted three times.
That query is syntactically perfect and semantically false, and it fails without announcing itself. It does not error. It hands you something plausible. The contract catches it because the grain is declared: the result has to be one row per order, so a query producing one row per line item violates it regardless of what it is aggregating.
Most systems in this category have two: it passed, or it did not. I added a third and went back and forth on it longer than anything else, because it means conceding the system has a boundary and then designing for what happens outside it.
A system that always produces an answer is one you can never verify by using it, since a confident wrong answer and a confident right answer look identical from the outside. My team was going to make real decisions off this thing. I would rather they get interrupted occasionally than misled quietly.
For several months my case for the system was that it had never handed me a wrong answer. I had checked a fair number of its queries against ones my engineers wrote, and the results agreed.
That is not a result. I picked which queries to check, there was no denominator, and nothing in the sample had been chosen because it was difficult. A perfect pass rate should have bothered me on sight, since vendors with dedicated teams report between ninety and ninety-six percent on the public benchmarks. I had been telling my PMs to stay skeptical of what the tool returned without holding myself to the same standard about the tool itself.
Sixty questions against a synthetic dataset small enough to check by hand, with ground truth computed by executing a reference query rather than written from memory. Thirty-seven have a correct answer. Twenty-three do not, because they are ambiguous or ask for metrics no contract covers, and refusal is the correct behavior on those. A system that returns a number for all sixty has scored thirty-seven at best and told you something worse, which is that it never abstains.
Eleven of the questions are adversarial, each built around a specific failure mode:
Each has a predictable wrong answer, so when the agent produces that number I know which trap it fell into rather than only that it failed. The metric I care about is the false-negative rate on LIKELY CORRECT: of the answers the system was confident in, how many were wrong. Those are the only errors that reach a person undetected.
False-negative rate
5.4%
down from 10.0%
Of the answers it was confident in, how many were wrong. The only errors that reach a person undetected.
Adversarial traps hit
0of 11
unchanged, before and after
Fan-out, fiscal boundaries, snapshots, identity collisions, expiry filters.
| Measure | Baseline | After fix |
|---|---|---|
| Wrong answers | 4 of 40 | 2 of 37 |
| Overall accuracy | 93.3% | 95.0% |
| Correct refusals | 19 of 23 | 21 of 23 |
Sixty questions. Ground truth computed by execution, not written by hand.
It hit none of the eleven traps, before or after. It told fiscal apart from calendar, caught both boundary dates, resolved one person across three accounts, and took the latest wallet snapshot rather than summing three of them.
The four baseline failures were all the same failure: it answered ambiguous or out-of-scope questions instead of asking. The worst asked for revenue grouped by product category, a metric no contract covered. It answered anyway, by fanning out. The figures summed to three times the true revenue and carried a high confidence label.
THE TELL
It handled the same join correctly minutes earlier, when the question was a filter rather than a group-by.
A scope check ahead of the semantic check, asking two things before any SQL is generated. Does a contract cover this metric at this grain? Does more than one contract plausibly match? It halved the false-negative rate and removed the dangerous case.
It also introduced a regression. The check now refuses a question it used to answer correctly, because it matches on the entities a question mentions rather than the measure it requests. That is a real bug and it is next on the list.
This is the whole design problem with a scope gate. Too loose and wrong numbers reach people who act on them. Too tight and the tool stops being useful, so people route around it, which is worse than never having built it. I would rather ship the tight version and tune it down, and I would rather say I made that trade than report the improved number without mentioning it.
My rough estimate, and I want it labelled as an estimate, is something close to a full FTE per week across the five teams. That is analyst hours we stopped requesting, engineering time we stopped spending on data quality questions, and PM hours that used to go into waiting.
The change I care about more is harder to count. My PMs stopped filing tickets to find things out. Someone wants to know whether rewards balances reconcile across two systems, so they ask, they get an answer or a refusal, and they keep moving. One of them ran an entire reconciliation analysis without involving me or anyone in data, and brought me a conclusion instead of a request.
The hours are real but they are the smaller half of the return. What matters is that a PM who can interrogate data directly asks more questions and better ones, because asking stopped being expensive.
The constraint on this system is coverage, not capability. It answers what the contracts describe and refuses everything else. Every refusal in the evaluation traced back to a missing contract or an undeclared join. None of them traced to the model failing to reason.
That makes the next phase a governance problem rather than an engineering one. Sixteen metrics have contracts today. Getting to a number that covers most of what the business actually asks means deciding who owns a definition, who approves changes to one, what happens when two contracts collide, and how often they get reviewed as the business shifts underneath them.
If I were staffing it properly I would want a named owner for the definitions, a standing review with analytics leadership, and a rule that no metric reaches the tool until somebody has signed off on what it means. Then I would take it past my own team.
For now it is a prototype with five users and a command line. It is not a product and I would rather say so than let the numbers above imply otherwise. What it demonstrates is that the definitions were the hard part, that the failure modes are measurable, and that a team can stop waiting on a queue when somebody is willing to spend a weekend on the thing nobody has time to fix.