Teaching the engine your vocabulary

Every text-to-SQL demo works on the demo schema, because the demo schema uses the words the demo questions use. Real warehouses do not. The column is size and the values are S, M, L, XL; the question says flagship stores. The column is status and the question says active accounts, which in that business means three of the eleven codes. The model can guess. It will sometimes guess right, and when it guesses wrong the number is plausible and nobody notices.

When we ran the engine at a client site, this was where the time went: not writing SQL, but writing it, having it critiqued, and writing it again until the vocabulary landed on the right column. Three or four loops on a question that should have taken one. The fix is not a better prompt. It is to tell the engine, once, what the words mean — and most businesses have already written that down.

The glossary already exists

Organisations that take data seriously have a business glossary. It lives in a catalog, a data governance tool, or — if they use another product of ours — in the extraction platform that reads their documents, where every standardised value had to be defined so that documents could be read consistently. Those glossaries hold three shapes of vocabulary, and each one maps to something a SQL engine can use:

InsightCompose pulls those glossaries in as they are — from the platform's shared glossary store or straight from a deployed extraction system — and then does the part nobody wants to do by hand: aligns each term to the indexed schema.

Alignment is evidence, weighed

The aligner has three kinds of evidence, and it is deliberately unwilling to act on any one of them alone.

flowchart LR
  G["Glossary term
'flagship' = XL"] --> N["Name
does the glossary's name
match a column?"]
  G --> V["Values
are the list's values
values of that column?"]
  G --> E["Meaning
embedding of the field
and its description"]
  N --> D{Confirmed only when
name AND data agree}
  V --> D
  E --> D
  D -->|yes| C["Confirmed binding
enforced on every query"]
  D -->|not quite| R["Review
a hint in the prompt,
a person decides"]
  style C fill:#e6f7f2,stroke:#9fd8c9
  style R fill:#fdf6ec,stroke:#e6c79c
                

For a standardisation list, the strongest evidence is the data itself: if ten of the twelve payment methods in the glossary are literally values of transactions.payment_method, that is the column, and the aligner will say so without asking. For an entity field, the strongest evidence is a column of exactly that name in the entity's own table. Embeddings recover the cases neither of those catch ("years in business" → tenure_years), but on their own they only ever produce a proposal.

SQL Engine → Terms → Glossary → schema

What a machine is not allowed to decide

A confirmed binding is enforced: a generated query that ignores it is sent back before it runs, and a cached query that contradicts it is not reused. That is a strong power, so the rules for who gets it are strict, and they are the part of this we would defend hardest.

• One-word terms that name several columns are never machine-confirmed. "State" is in ship_state, billing_state and store_state. "Name", "status", "date" — the same. A person picks the reading; the aligner only offers it.
• Ambiguous words are never enforced as values. "Open", "new", "active", "other" mean something in every table. They land as hints.
• Near-misses are hints, not rules. A question that says "order state" must not be held to a binding for "order date" because the two are three letters apart. Only a phrase the question actually contains is enforced; a fuzzy match is shown to the model as possibly relevant and nothing more. (We found this one the hard way — it cost three failed attempts on a question that had been fine the day before.)
• A person's verdict outranks the aligner, permanently. Re-aligning after the glossary changes re-scores only what the machine decided. Anything a curator confirmed, rejected or wrote by hand is left alone.

Everything that does not clear that bar is still useful. It goes into the prompt as a learned hint — "store → franchise, learned, 3 uses" — so the model starts from the right neighbourhood without the engine betting the answer on it.

Two ways bindings arrive, one place to review them

The glossary is the fast way in. The engine also learns on its own: when it writes franchise AS store and the query runs, that alias is evidence; when the warehouse rejects a column and the repair replaces it with the one that works, that is stronger evidence. Both arrive on the same Terms tab, with the same statuses, and the same rule — hints until a person confirms. The advice we give every new site is simple: watch that tab for the first week.

Applying an alignment also writes the chosen column back onto the term in the glossary, so the curator's view and the engine's view are the same rows. When the glossary changes at its origin, you pull again; the words update, the links a person made survive.

What it is worth

On a 35-table, 637-column warehouse, 88 imported glossaries produced 52 confirmed bindings — 34 columns and 18 value sets — and 561 hints for review, in about a minute. The questions that used to loop ("open orders by reason, with the amount outstanding", "returns by region") answered on the first attempt afterwards, because the filter the business meant was already in the prompt as a rule rather than a guess.