Many researchers know the question they want to ask but do not write SQL. The prototype explored whether they could ask in plain language and receive a query against the underlying tables.

The data was sensitive patient data, and the organization was appropriately cautious about AI. That constraint shaped the prototype: semantic metadata had to ground the generated SQL, and every candidate query needed review before it could run.

Semantic metadata made useful queries possible

An LLM cannot infer a domain reliably from names such as dx_cd, enc_dt, pt_id, or a table called enc. Those abbreviations mean something to the people who built the database, but the raw schema gives the model little basis for choosing tables, joins, or filters.

I added metadata describing each table and column: the purpose of the encounter table, the meaning of enc_dt, relationship keys, and code-set definitions. Placed beside the schema in the prompt, that layer gave the model enough domain information to choose relevant tables and joins.

The generated SQL improved because the model received meaning rather than only identifiers. Model quality could not substitute for definitions that existed only in the organization and its data documentation.

The model still produced plausible wrong SQL

Even with that context, the model sometimes invented a plausible column, joined on the wrong key, or misread a definition. The resulting SQL looked no less confident than a correct query.

Here is why that’s not a small problem. A wrong query usually doesn’t error. It runs, and it returns a number. On a research question over patient data, a wrong number that looks right is the worst possible output there is — worse than a crash, worse than “I don’t know” — because it’s an answer, and someone will believe it, and they may act on it. Plausibility, the model’s great strength, is precisely the thing you cannot afford to trust when the answer is about patients.

Every candidate query needed a review gate

The architecture followed the suggest-versus-decide split. The model drafted SQL from the question and semantic layer. A separate review step owned correctness, permission to execute, and whether the returned number could be used.

The interface therefore surfaced the SQL, definitions, and columns used. A person—and deterministic rules where possible—could inspect them before execution and before accepting the result. The generated query was an inspectable draft, not an autonomous answer.

The model drafts. It never gets to conclude — no query runs, and no number is believed, until it’s checked.

Caution was part of the design

The organization’s caution was not separate from product usefulness. Because the model could be helpful and unreliable in the same session, the prototype needed an accountable person and a review gate to be suitable for patient data.

A system that can hand a researcher an unchecked number about patients is not finished, regardless of how fluent the interface appears.

Text-to-SQL became a product category

Text-to-SQL, semantic layers, and “chat with your warehouse” later became a product category. The requirements from this early prototype still apply: models need organization-specific definitions, and users need to see the query and evidence behind an answer.

For sensitive analytical work, returning only an answer is insufficient. The query, definitions, referenced columns, and review status belong with it so the researcher can judge where the number came from.

---

Companion code: ramwise-examples/semantic-sql — a semantic layer plus a verification gate that blocks a hallucinated query before it runs (synthetic data).

More on AI-native systems A Web Interface for the NHTSA Evidence System →