A text-to-SQL agent sees table and column names. It does not know that "revenue" in your company means billing_invoice.total_net, only for invoices with status = 'issued'. So it guesses, and the guess runs without errors and returns the wrong number.
I added business terms to schemagate 1.2.0 (open source, Apache-2.0) and measured whether they help. Short answer: a complete glossary helps a lot. A glossary learned from other people's questions did not help at all. Here are both results.
What a term looks like
cat.concept(
"revenue",
synonyms=["turnover", "sales"],
maps=["billing_invoice.total_net"],
filter="billing_invoice.status = 'issued'",
)
When a question uses "turnover", two things happen:
-
billing_invoiceis pulled into the few tables the model sees, even if no table name looks like "turnover". - A line goes into the prompt, above the DDL:
-- What the business terms in this question mean:
-- revenue (turnover): billing_invoice.total_net; rule: billing_invoice.status = 'issued'
Terms can also have broader and narrower terms ("net revenue" is a kind of "revenue"), which pulls in related tables at half weight.
You don't have to type them by hand. You can import them from a dbt semantic manifest, a Snowflake semantic model, or a CSV export from Collibra or Purview:
schemagate ontology import semantic_manifest.json --from dbt --url $DB --config catalog.json --save
Or learn them from past (question, SQL) pairs: if "refunds" keeps showing up in questions whose SQL reads billing_credit_note, that becomes a suggested term.
The part I care about: access control
schemagate's main job is hiding tables and columns a caller may not read, before the model sees anything. A glossary could leak them right back: "pay means hr_compensation.annual_amount" tells a payroll clerk that the column exists.
So a meaning line is dropped for any caller who can't see every table and column it names. The term still exists. That caller just never gets its line.
Results
BIRD dev, 201 questions, Claude Sonnet 5, execution accuracy, the hint text withheld:
| Setup | Accuracy |
|---|---|
| No terms | 44.8% |
| No terms, run again (noise check) | 46.8% |
| Terms learned from other questions' hints | 46.8% |
| Complete glossary for this database | 54.7% |
| The question's own hint pasted in (ceiling) | 61.2% |
The complete glossary won 28 questions and lost 8 against no terms (McNemar p = 0.001). That's real.
Terms learned from other questions did nothing: 46.8%, the same as running the baseline twice. BIRD's hints are mostly one-off formulas ("percentage = count where x / count"), and a formula from one question rarely fits the next. I'm reporting this because it's the result you would hit if you expected a glossary to build itself.
Retrieval, Spider, 876 tables pooled: terms learned from half the questions raised recall@5 on the other half from 73.3% to 82.8% (49 questions better, 0 worse). Finding the right tables is where learned terms do help.
What I'd take from it
- Write down the 20-50 terms people really argue about (revenue, active customer, churn). That's where the 10 points came from.
- Learned terms are good for finding tables, not for teaching formulas.
- Whatever you add to the prompt, check it against the caller's permissions too.
Try it
pip install "schemagate[mcp]"
SCHEMAGATE_DATABASE_URL=demo python -m schemagate.mcp_server
Works with Postgres, Oracle, MySQL, SQL Server and SQLite, as a Python library, a LangChain retriever, or an MCP server for Claude and Cursor.
GitHub: https://github.com/ashishsinha1602/schemagate · In-browser demo: https://ashishsinha1602.github.io/schemagate/
If you run an agent over a real warehouse: how many business terms would you need to write down before it stopped guessing?
Top comments (9)
Nice design, especially the repeat-baseline run as a noise check. I suspect the 'learned terms did nothing' result says as much about BIRD as about glossaries, since its hints are mostly one-off formulas. Have you tried a schema where you control the definitions (say revenue counts completed orders net of refunds) and compute the expected answers from that definition? I build Misata, an synthetic data generator that ships each dataset with an answer key (the KPIs and the SQL), partly for this kind of test, though a hand-built SQLite with planted definitions would show it too.
Agreed on BIRD: its evidence is mostly one-off formulas that rarely recur, so concepts learned from other questions had little to reuse. The same mechanism on Spider (learned from half of each database's questions, scored on the other half) took table recall from 73.3% to 82.8%, so that result says more about the benchmark than about glossaries.
On "did the glossary make the answer correct": that part is scored on execution, not table picking. A complete glossary (each question's own definition supplied as a concept) took BIRD execution accuracy from 44.8% to 54.7% (+28 / −8, p=0.001), against a noise floor of about two points.
What that doesn't cover is your version: definitions I write myself, with the expected answers computed from them rather than from the benchmark's gold SQL. I've built it. It's a small seeded SQLite (orders, refunds, shipments, subscriptions) with eight planted terms where the obvious reading gives the wrong answer: revenue as completed orders net of discounts and refunds with internal SKUs excluded, "active" from recent orders rather than a stale flag, on-time excluding unshipped, churn with a 30-day grace window, and so on. Each question is kept only if the naive SQL disagrees with the defined answer on that data. I'll post the numbers here once it has run.
Ran it. A seeded SQLite (orders, order lines, refunds, shipments, subscriptions) with eight planted terms whose obvious reading is wrong, and 21 questions whose expected answers are computed from the definitions. A question is kept only if the naive SQL gets a different answer on that data. Claude Sonnet 5, execution accuracy:
Without the glossary the model does the reasonable thing: gross revenue,
is_active = 1, every ended subscription as churn. For "big accounts" and "partner sales" it declines to guess at all.The six misses that remain are all the same pattern, and they're a real gap in my retrieval. A term defined through another ("partner sales is revenue on partner orders") retrieves its own definition but not the one it leans on, so the model gets the channel filter right and then guesses what revenue means (gross, or
qty*price - discount). Pulling in the definitions a definition cites is the next fix.Two things I had to fix in my own harness before trusting the numbers, in case they're useful to anyone building one: exact row matching marked right answers wrong when the model added a helper column (the rate it ranked by), and one question said "the last 90 days" without an as-of date, so the model correctly used today. Expected answers were also checked against a plain-Python computation, not just the gold SQL.
Code and numbers: github.com/ashishsinha1602/schemag...
That's a cleaner result than I expected: The repeat arm not moving a single answer makes it hard to argue with. The 'big accounts' refusals are interesting too. In production a model that declines is cheaper than one that confidently returns gross revenue, so it might be worth scoring abstentions separately from wrong answers.
On the six misses, before adding closure retrieval I'd try one more arm: compile each term once into a view (
net_revenue,active_customers) and have partner sales, AOV and big account point at the view instead of the prose. If views and closure retrieval score the same, it's a retrieval gap. If views win, the model is re-deriving revenue even when it has the definition.The reason why I am interested in your work is that I have been building a synthetic data generator as a side project after I face exhaustion working on one in my last organisation. But then building just another AI powered data generator is a graveyard. So I decided a outcome-declarable synthetic data generator would be valuable for testing data agents, for text-to-SQL scripts and also for story telling business demos. The development is not completed but I am exploring this field in depth.
Splitting abstentions out turned up something I'd missed. Per arm, out of 21:
The 4 abstentions are both "big accounts" questions and both "partner sales" questions, and all 4 are among the 6 the glossary arm still gets wrong. So the glossary didn't just fail to fix them, it made them worse by your cost measure: the model went from declining to answering confidently, because it now had a definition (partner sales = revenue on partner orders) without the one underneath it. A half-delivered definition reads to the model as permission. That's an argument for scoring abstention as its own outcome, and for treating "definition retrieved but its dependency wasn't" as a failure the retriever should report rather than pass along silently.
The views arm is the right next test, and cheaper to read than closure retrieval: if
net_revenueas a view and closure retrieval both land at 21/21, it was retrieval; if the view wins, the model is re-deriving revenue even with the rule in hand. I'll run both and post the split here, abstentions included.On your generator: declaring the outcome first is what made this benchmark possible at all. I could only keep a question if the naive SQL gave a different answer on the data, so I had to know the answers before writing the questions. A generator that lets you say "revenue must differ from gross by refunds, 15% of orders have one" and builds data where that holds is exactly what this needed, and I hand-built it badly. When yours is usable I'd like to try the planted-definitions test on it.
That split matters more than the headline numbers. Four abstentions turning into four confident wrong answers is the worst trade you can make, and "a half-delivered definition reads as permission" is the clearest way I've seen it put. I'd score it three ways (correct, abstained, confidently wrong) and make a wrong answer cost more than an abstention. Otherwise the glossary arm gets credit for removing the model's caution.
One thing on the views arm: keep the gross column queryable next to the net_revenue view. If the view is the only way to get revenue, 21/21 tells you the wrong path was removed, not that the model chose the right one. For closure retrieval, it might be worth a variant that fetches partner sales but deliberately drops net revenue and reports the gap. That would show whether flagging the missing dependency brings the abstentions back.
On the generator, it's actually usable now as an opensource library named misata. But for your kind of usecases I am building a more powerful and accurate generator which is now in the workshop. What you describe is what it's built for: you declare that 15% of orders have a refund, that net revenue is gross minus refunds, the share of partner orders and a big-accounts threshold. The data is generated so those hold exactly, and it comes with an answer key: the KPIs and the SQL that computes them. Because you set the definitions, you can also choose values so the naive version of each question gives a different number, which is the filter you had to apply by hand.
Right now there is a bit of things in my plate, but if you send me the definitions behind the 21 questions (especially what "big accounts" means), I'll build the dataset once I have the capacity and send you the files so you can rerun all three arms on it. Happy to move this to email if that's easier.
Agreed on the three-way score, and on making a wrong answer cost more than an abstention. As the numbers stand, the glossary arm is credited for removing exactly the caution that was protecting it on those four questions. I'll report correct / abstained / confidently wrong per arm from now on, with a cost table rather than one accuracy figure.
Both of your arm designs are better than what I had in mind. Keeping gross queryable beside a
net_revenueview makes 21/21 mean the model chose the view instead of having no alternative. The dropped-dependency variant answers the more useful question: whether "you were given partner sales but not the revenue it depends on" is enough to bring the abstentions back. If it is, the retriever can make that call itself, and flagging is cheap compared with closure.On misata: yes please, when you have the capacity. All eight definitions are in code, in the same words the model sees: github.com/ashishsinha1602/schemag... (
GLOSSARYnear the top). "Big account" is a customer whose lifetime revenue, by that file's revenue definition (completed orders, lines net of discount, internal SKUs excluded, minus refunds), exceeds 5,000. Each question's gold SQL is beside it, and the naive version used to keep or drop it. A generated dataset with an answer key would replace the most fragile part of my setup, the hand-built seed. I'll drop you a message about moving this to email.Ran both, scored your three ways (plus "failed", for SQL that errors visibly). Same 21 questions, claude-sonnet-5:
"Cited definitions" means a matched term now brings in every term its definition or filter names, up to three steps, with the same visibility rules. Partner sales arrives with revenue's definition attached instead of alone. That fixed all six, including the four that had flipped from abstaining to wrong.
The views arm, built as you said with gross still queryable beside
net_revenue, didn't do better, and neither of its two misses involved revenue. One counted unshipped orders in the on-time denominator. The other returned the top customer's id where the gold wanted the name, because the view has no name column. By your test that makes it a retrieval gap, not the model re-deriving revenue. So it's fixed where it should be, in retrieval: github.com/ashishsinha1602/schemag...Still to try is your dropped-dependency variant: remove revenue from the glossary and flag the gap, to see whether the abstentions come back. That's the next thing I'll run.
Ran the dropped-dependency variant. Revenue's entry removed from the glossary, so partner sales, big account and AOV all cite a term with no definition. Same 21 questions, claude-sonnet-5. On the 6 that depend on revenue:
So your hunch holds: one neutral line about the gap was enough to bring every abstention back, and the instruction not to guess added nothing on top. But it buys honesty, not answers. Only the definition itself made them right. That makes a flag a sensible fallback for when closure can't find the cited term, rather than a substitute for it.
Arms D, F and FS are in benchmarks/planted_definitions.py, and the table is in the business-definitions doc: github.com/ashishsinha1602/schemagate/pull/142