The hard part of text-to-SQL is not the SQL

An LLM will write you a query in seconds. Getting it to write the correct one
means shipping it everything your analyst knows and never wrote down.

There is a genre of Slack message that arrives at 1:40pm and ends with "before
my 2pm."

It is always from someone who does not write SQL. Not because they couldn't
learn, they could in an afternoon, but the afternoon is committed to a boat.
And the question itself is entirely reasonable: how much did we make in Q3, by
region. One correct answer. Sitting in a table. Ninety seconds of work.

Ninety seconds if you are the analyst. The analyst is currently on item 38 of

1. The analyst has been on item 38 of 37 since March.

So the obvious 2026 move is to cut her out of the loop. Hand the warehouse to a
language model, let the exec ask in English, everyone leaves early.

This takes about ten minutes. Genuinely. An MCP server, a connection string, and
a system prompt that says you are a helpful analytics assistant, only return
rows belonging to the user's own company.

That last clause is doing a great deal of work in that sentence. It is, in fact,
doing all of the security.

This is the first of two write-ups of
QueryGate,
an open-source MCP server I built because that clause kept me up at night. Not
"look, the AI wrote SQL", which has been solved for a while now. The
interesting engineering is everything between the question and the rows.

I built it as a template rather than a product. Every DWH team I've worked with
has a different warehouse, a different identity provider, and a different
theory of what a tenant even is, so shipping one opinionated deployment would
have been useless to almost everybody. What generalises is the shape. The demo
stack exists so a team can watch the whole path work once, then start deleting
the parts that are mine.

Everything below runs locally with docker compose up.

A word in defence of the analyst

This article is otherwise going to read as though the goal is to replace her, so
let me be precise about what she actually does.

She is the only person in the building who knows that orders has had three
rows per order since the 2023 migration. That status = 'complete' matches
exactly zero records, because the data says 'completed'. That joining
customers to plans on plan_name works fine until you hit the twelve accounts
where someone typed it by hand.

That knowledge is the product. The model does not have it, cannot infer it from
column names, and (this is the important bit) will produce a confident number
without it. Not an error. A number. Formatted nicely, to two decimal places,
wrong by a factor of three.

A number arrives formatted, centred and confident. That tells you nothing at all about whether it is true.

So half of this project turned out to be about getting her knowledge to the
model without needing her to be awake. The other half is about the exec, and
what happens when a system that will answer any question meets a person who
cannot evaluate any answer.

The one idea

The model writes the query. The server does the other two jobs.

There are two, and conflating them is why most text-to-SQL demos are demos. The
first is making the query right: the model has to know that orders has three
rows per order, that the status column says completed and not complete, that
"revenue" in this company means one specific aggregation someone signed off. The
second is making the query safe: only the caller's rows, read-only, bounded
cost.

Neither can be delegated to the model, and they fail differently. An unsafe
query gets refused. A wrong query gets answered.

Writing SQL from a fuzzy question is genuinely what LLMs are good at, provided
they are told the things above. Nobody's model knows them from column names. So
half this project is a pipeline for getting human knowledge into the model, and
the other half is a pipeline for not trusting it afterwards.

This article is the first half. The second, on injecting a tenant filter
and then re-proving it, on the oracle that makes naive column masking useless,
and on a Snowflake parameter that silently undoes the entire thing, is
here.

flowchart TB
    subgraph client[MCP Client · Claude / Cursor]
        A[Analyst asks in natural language]
    end

    subgraph qg[QueryGate MCP Server]
        direction TB
        AUTH[auth · verify token → tenant scopes]
        RET[retrieval · BM25 + vectors over the catalog]
        subgraph pipe[query pipeline · pure, unit-tested]
            V[validate · one read-only SELECT] --> Q[qualify names]
            Q --> L[clamp limit + offset] --> G[inject tenant filter]
            G --> ASSERT[independent assert]
        end
        WH[warehouse adapter · estimate + execute]
    end

    subgraph data[Data plane]
        DBT[dbt docs<br/>manifest + catalog + metrics] -->|CI publishes| S3[(S3 / GCS)]
        PG[(Warehouse)]
    end

    A -->|search / describe / stats| RET
    A -->|run_query sql| AUTH --> pipe
    S3 -->|TTL pull + compile| RET
    RET -.slice.-> A
    ASSERT --> WH --> PG
    PG -->|rows| WH -.compact result.-> A

Left half of the server is the answer to "what should I ask?". Right half is
the answer to "may you have it?". The article follows the same order.

The pipeline on the right is pure functions over (sql, catalog, tenant_scopes).
No network, no model, no I/O of any kind, which is why the security-critical core
can be tested exhaustively. About a thousand lines, small enough to read on a
train.

1. The context already exists, and it is not in your prompt

The instinct when wiring an LLM to a warehouse is to write a big system prompt
describing the tables. This is a bad idea twice over: it goes stale the moment
someone ships a migration, and it does not scale past about fifteen models
before it eats the context window.

The better answer is that your data team already wrote all of it, in dbt, as
part of their normal job. Nobody has to author a new artefact:

QueryGate compiles those four artefacts into an in-memory catalog on boot and
refreshes on a TTL. Precedence is S3, then a local dbt target, then a bundled
sample so the server always starts. A failed refresh keeps serving the
last-known-good copy, because a stale description is a cosmetic problem and a
crashed server is not.

The consequence worth stating plainly: improving the agent is a pull request to
the dbt repo, not to this one. An analyst who writes a better column
description has improved the model's SQL, and never touched Python.

2. Finding the right table when the words don't match

Retrieval sounds like the boring part. It produced the most useful measurement in
the project, and it was not the one I expected.

The agent's first move is search_catalog, which returns a slice of the
catalog rather than a copy of the schema. Behind it is BM25 over a document per
model, assembled from name, description, column names and column descriptions.

Run the demo catalog's golden questions against that and one of them does this:

question: "how much money did we make"
BM25 returns: []

Not a wrong table. An empty list. The word "money" appears nowhere in a catalog
whose text says "revenue" throughout. Keyword search is lexical, the analyst is
not, and this is the characteristic way retrieval fails in a data context: not
ranking badly, but matching nothing at all.

Two mechanisms address it, and they are not interchangeable.

A glossary, written by a person, sitting next to the dbt project:

{
  "revenue":  ["sales", "income", "turnover", "money", "earnings"],
  "churn":    ["attrition", "cancelled", "inactive", "lost"],
  "customer": ["client", "account", "user"]
}

At index time, any document containing revenue is enriched with its synonyms.
A query for "money" now matches the model whose description only ever said
"revenue". On the demo catalog this takes retrieval from 75% to 100% hit@1, and
the fix costs four lines of JSON.

Embeddings, second. Local ONNX vectors via fastembed, cosine similarity over
a normalised matrix, fused with BM25 by Reciprocal Rank Fusion. RRF rather than a
weighted blend because the two scales are not comparable: a BM25 score of 8.2 and
a cosine of 0.71 mean nothing beside each other, but rank 1 and rank 1 do. No
calibration constant to tune, which is the whole appeal.

On the demo catalog, they also take it to 100%. Four models and four questions,
so both hit the ceiling and neither can distinguish itself. That result says
nothing, and I nearly published it as though it did.

The measurement, including the one I got wrong

So I built a catalog at a realistic size: 40 models across ten domains, from
sales and finance through support and ops to platform and legal, described in
schema language the way a dbt project reads. It is a fixture, not a warehouse:
retrieval only ever reads catalog metadata, so names, domains, descriptions and
column names are the whole of it, and they live as literals in the benchmark
file rather than as a second dbt project. Then 32 questions in the language an
executive actually uses, written before running anything: what is our top line,
who is about to leave us, how busy is the help desk, which bits of the app
does nobody touch.

The first glossary scored zero. Not "less". Zero.

There was no bug. I had written it backwards, and the direction is worth knowing.

Expansion fires on the key. A document containing the key gains its synonyms.
So the key has to be a word that is already in your catalog, and the values
have to be what people say instead. I had done the reverse: keys like revenue
and churn, which is how humans talk. Nothing in a catalog full of
gross_amount and fct_plan_changes ever contains the word "revenue", so no
document was ever enriched and six sensible entries did nothing at all.

A glossary anchored on the words people use, reaching for a word the catalog never contains. The thin arc is the embedding, which needs no anchor at either end.

Turned around, keys drawn from the schema, it jumped to 97% hit@3 and I nearly
published that.

It is not a real number. I wrote that glossary after looking at which questions
were failing, so entries like "tickets": ["help desk", "complaints", "busy"]
are lifted almost verbatim from the test set. That is not a measurement of a
glossary. It is a measurement of me holding the answer key.

So I wrote a third one blind: walk the 40 models, take the salient noun from
each, attach the business synonyms a data person would reach for knowing only
their schema, never look at the questions.

Reproduce it with make bench. The catalog, the questions and all three
glossaries are literals in
evals/retrieval_benchmark.py, so disagreeing
with any of this is a matter of editing one file and running it again.

Embeddings beat a fairly written glossary, 62 against 56. They also took no
maintenance, no vocabulary decisions and no knowledge of what anyone would ask.
Together the honest pair reaches 69, which is the best either of them manages
without somebody peeking at the answers.

That is the opposite of the conclusion I had drafted, and I am leaving the wrong
one visible above because the gap between the two glossary rows is the actual
finding here.

The same mechanism scores 56% or 97% depending on nothing but whether it was
written before or after somebody looked at what people were really asking. The
difference is not the technique. It is the feedback loop.

Which is a much more useful thing to know than "glossary versus vectors",
because it tells you where to spend. Embeddings are a one-time install that buys
you a solid floor. A glossary is a practice: you read the questions that returned
nothing, you add three lines, retrieval gets better this week and better again
next month. Neither replaces the other, and the one that compounds is the one
with a person attached.

Two caveats. The catalog is synthetic, so it reflects how I wrote it. And I could
not un-see the questions while writing the blind glossary, so treat 56% as
generous rather than conservative.

Embeddings are strictly additive in the code, which matters more than the
scoreboard: a missing extra, a failed model load or an unreachable vector store
each degrade to keyword-only and log it. A question is never left unanswered
because the semantic half is down.

Vectors sit behind a VectorBackend protocol with two implementations. The
default is a normalised numpy matrix in memory, and for most catalogs that is the
right answer: three thousand models at 384 dimensions is about 4.5 MB, so an
external service would be pure operational overhead. Qdrant earns its place
once the catalog outgrows the process, or replicas would otherwise re-embed the
same data on every sync, or you want metadata-filtered search pushed down. BM25
and the fusion always stay in-process, which is what makes the swap invisible to
everything above it.

3. Removing the rest of the guesses

A human analyst explores by looking: scrolling a table, eyeballing a column,
noticing half of it is NULL. An agent cannot do any of that, so it guesses. And
the guesses fail quietly, which is the worst available way to fail.

Declared joins. The most common way generated SQL goes wrong is a wrong join
key, because a plausible-but-incorrect join doesn't error. It silently fans out
row counts and returns a confidently wrong total. Your dbt project already
declares its foreign keys, in relationships tests:

- name: plan_name
  tests:
    - relationships:
        to: ref('plan_catalog')
        field: plan_name

Those exist to catch broken data in CI. QueryGate reads them for a second
purpose their author never intended: they are the only place in a normal project
where join keys are written down as fact rather than convention. Parsed out of
the manifest, handed to the agent through describe_model. It stops guessing
because it no longer has to.

Data profiling. get_table_stats returns row count, null fraction,
cardinality and ranges per column, plus a few sample rows. The agent learns that
a column is 33% NULL, or that a GROUP BY would produce 50,000 groups, for a few
hundred tokens instead of a few thousand rows. It runs through the governed
pipeline, which gives a nice property: a profile only ever describes rows the
caller may see. One tenant's maximum order is $100 and another's is $300;
neither profile leaks the other's range.

Real filter values. get_filter_values returns the actual distinct values of
a column, so the agent filters on 'completed' instead of inventing
'complete' and reporting, with total composure, that Q3 revenue was zero.

Pagination. A truncated result returns next_cursor. The cursor is opaque
and stateless, with no server-side registry to size, expire, or leak between
tenants, and carries a fingerprint of the query it belongs to. Replay it against
different SQL and it's refused, because silently returning the wrong page is
worse than an error.

None of this is glamorous. All of it is the difference between a demo and
something you'd let a colleague use unsupervised.

4. Don't let the model decide what "revenue" means

Everything so far helps the model write SQL that runs. This one is about SQL that
agrees with the board deck.

"Completed revenue" is not a fact about the schema. It is a decision somebody
made: which statuses count, whether refunds net off, what grain it is measured
at. An agent handed a table and the word "revenue" will produce a number, and
it will be defensible, and it will disagree with finance by four percent for a
reason nobody can reconstruct three weeks later.

So definitions live in dbt's semantic layer, owned by a person:

- name: completed_revenue
  label: Completed Revenue
  description: Total USD from completed orders only. The canonical revenue number.
  type: simple
  type_params:
    measure: order_amount
  filter: "{{ Dimension('order__status') }} = 'completed'"

dbt parse compiles that to semantic_manifest.json, which QueryGate ingests
alongside the rest of the catalog.

Surfacing the definition to the agent is not enough on its own, and this took me
a while to accept. Hand a model an expr and a filter and ask it to assemble
the SQL, and most of the time it will, and occasionally it will drop the filter
or use sum where the definition says count(distinct). The definition was
never the hard part. So get_metric compiles it:

get_metric("completed_revenue", dimensions=["region"])

-- the server writes:
SELECT "region", sum(amount) AS completed_revenue
FROM "analytics"."customer_orders"
WHERE status = 'completed'                       -- from the definition
  AND customer_orders.tenant_id = ANY(...)       -- from governance
GROUP BY "region"

The agent decides what to ask for. A person decided what it means.

The agent decides what to ask for. A person decided what it means. The server
is the only thing that turns the second into SQL, and it still runs the full
governed pipeline, so a metric cannot be used to sidestep row or column security.

There is a stricter mode on top, for the audience that will quote the number as
official. Principals holding a role in QG_CERTIFIED_ONLY_ROLES may read metrics
the data team certified in dbt, and nothing else. No ad-hoc SQL, no uncertified
metric. The refusal is a redirect rather than a wall: it names the certified
metrics that do exist, so the agent asks an answerable question instead of
guessing an aggregation.

What this half buys you

None of the above is a security control. A perfectly grounded query against the
right table with the right join keys and the correct metric definition will
still happily return another tenant's rows if nothing stops it.

That is the other half, and it is a completely different kind of engineering:
where this half is about giving the model more, that half is about trusting it
with nothing. It is the second article.

QueryGate is MIT-licensed, Python 3.12, built on FastMCP, sqlglot and dbt, with
Postgres, DuckDB, BigQuery and Snowflake adapters. Clone it, docker compose up,
and ask it something in plain English.

Arif Ismailov. If you
work on LLM data access, MCP servers, or multi-tenant analytics, I'd like to
compare notes.