I gave my text-to-SQL agent a business glossary. One version helped a lot, one did nothing.

#agents #ai #llm #sql

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_invoice is 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?

DEV Community