DEV Community: Arif Ismailov

I gave an LLM the keys to a multi-tenant database

Arif Ismailov — Thu, 27 Aug 2026 07:27:32 +0000

Five things that had to be true before I would point it at data belonging to
more than one customer.

An agent that writes good SQL is a solved problem, near enough. The
first half of this project was about that: pulling
descriptions, join keys, real filter values and signed-off metric definitions out
of dbt and handing them to the model, so the query it writes is the query a
competent analyst would have written.

None of it makes the query safe. A perfectly grounded query against exactly the
right table will return every tenant's rows if nothing stops it, and it will do
so without an error, because from the warehouse's point of view nothing went
wrong.

So this half is the opposite discipline. The model gets no say in any of it.

The model writes the query. The server enforces the rules. Different jobs,
and only one of them is safe to delegate. Whatever SQL comes back goes through a
pipeline the model cannot influence:

validate → guard → qualify → limit → offset → mask → govern → assert → estimate → execute

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. Prove the filter, don't trust the injector

When a query reads a governed table, one with a tenant_id column, the
server parses the SQL into a syntax tree with
sqlglot, walks every SELECT scope,
and AND-injects a predicate.

The model writes this:

SELECT region, sum(amount) FROM customer_orders GROUP BY region

The warehouse executes this:

SELECT region, SUM(amount)
FROM "analytics"."customer_orders"
WHERE customer_orders.tenant_id = ANY(%(qg_tenant_scopes)s)
GROUP BY region
LIMIT 100

Tenant IDs are bound parameters; they never appear in the SQL text. The walk
covers joins, subqueries and CTEs, so a governed table three levels deep inside
a WITH still gets filtered, while a public table in the same query is left
alone.

Then comes the part I'd argue is the actual design decision. Having injected the
filter, the server re-parses the finished SQL and independently proves the
filter is there:

def assert_tenant_filter_present(sql, catalog):
    tree = _parse(sql)                          # fresh parse, no shared state
    for select in tree.find_all(exp.Select):
        for handle, model in _governed_refs(select, catalog, ctes):
            if not _scope_has_tenant_filter(select, handle, model.tenant_column):
                raise GovernanceError(...)      # before execution, always

Why bother, when the injector ran forty microseconds ago?

Because the injector could have a bug. Because a refactor in eight months could
introduce one. Because I am the sort of person who writes an injector and then
lies awake wondering whether it handles LATERAL. The assert shares no state
with the injector: it re-derives the proof from the text itself and demands the
exact predicate shape: right column, right parameter, top-level AND
conjunct. A filter that's missing, OR-ed with a tautology, or bound to the
wrong parameter gets rejected.

And it's fail-closed. A governed table with an empty tenant scope doesn't return
zero rows; it refuses to run. Absence of permission is not permission.

Does it hold? Here are the numbers

Three bearer tokens, three tenant scopes, one question:

tok_acme    →  $55,952.59
tok_globex  →  $62,841.34
tok_multi   →  $118,793.93     ← exactly the sum

No bleed, no double-counting. There is no prompt you can hand tok_acme that
returns Globex's revenue, because the filter is added after the model is done
and re-checked before execution. The model is never in the loop for that call.

For fun, I spent an afternoon being the attacker. Asking as tok_acme for
WHERE tenant_id = 'globex' returns NULL, because the injected predicate ANDs with
yours and the intersection is empty. Hiding the table in a CTE returns acme's
own total. OR 1=1 returns acme's own total. Selecting the masked customer name
returns a column of MD5 hashes. Asking WHERE customer_name = 'Alice Smith' is
refused outright, and I'll explain why in a moment. SELECT 1; DROP TABLE customer_orders gets told, politely, that two statements is one too many.

This runs as an integration test against real Postgres in CI, not a mock.

2. The cache is part of the security perimeter

This one nearly got me, and I think it's the most transferable lesson here.

Caching query results looks like a pure optimisation. In a multi-tenant system
it is not. Two callers can send byte-identical SQL and legitimately be owed
different rows, and the only thing separating them is the tenant scope the server
binds. A cache keyed on the query alone hands one tenant another's data
without ever running their query, elegantly bypassing every guarantee above.

The fix isn't a code-review checklist item, because code-review checklist items
are things humans do on Friday afternoons. It's a type:

@dataclass(frozen=True)
class CacheKey:
    namespace: str
    tenant_scopes: frozenset[str]   # required, no default
    parts: tuple[str, ...]

You cannot construct a key without answering "on whose behalf?". Scopes are
sorted, so a multi-tenant caller maps to one key, and hashed with a separator,
so a crafted column name can't collide with somebody else's scope.

The end-to-end test makes the subtlety visible. Both tenants happen to have the
same region values, ['APAC', 'EU', 'NA']. The second tenant still gets a
miss:

acme  1st: ['APAC','EU','NA']  cached=False
acme  2nd: ['APAC','EU','NA']  cached=True    ← served from cache
globex 1st:['APAC','EU','NA']  cached=False   ← miss, not acme's entry

The cache never assumes what another tenant is entitled to see, even when the
answer would coincidentally be identical.

I also cache deliberately little: get_filter_values, which is repetitive and
stable, but not run_query. Analytical SQL is phrased differently on every
attempt, so the hit rate is poor while the blast radius of a wrong key is a
cross-tenant leak. Bad trade.

Worth knowing before you build any of this: Postgres does not cache query
results. It caches pages. A repeated identical query still re-plans and
re-executes, which I measured at 6-7 ms warm on demo-sized data: noise next to
a multi-second LLM round trip. BigQuery and Snowflake do cache identical
results for 24 hours, which makes an app-level query cache largely redundant
there. Caching earns its keep on large scans and repetitive lookups, not on
principle.

3. Fail open, fail closed, and know which is which

QueryGate has two guards that both protect the system and have opposite
failure modes. The asymmetry is deliberate and worth saying out loud.

Governance fails closed. Can't prove the tenant filter? Refuse. Empty tenant
scope on governed data? Refuse. The cost of being wrong is a leak, so the
default has to be no.

Rate limiting fails open. If Redis is unreachable, requests go through and
the server complains loudly in the logs. Rate limiting protects availability,
not confidentiality; refusing every query because the limiter is down trades a
small risk for a certain outage.

The limiter itself is a token bucket living entirely inside a Lua script, so the
read-refill-write cycle is atomic across replicas. Fifty concurrent calls
against a three-token bucket allow exactly three.

Token bucket, not fixed window. A fixed window lets a caller spend a full quota at the end of one window and again at the start of the next, briefly doubling the intended rate.
Per-caller buckets. A global counter lets one enthusiastic tenant starve everyone else, which is the same failure governance exists to prevent, just on a different axis.
Per-tool costs. run_query reaches the warehouse and costs 5 tokens; search_catalog answers from memory and costs 1. Charging them equally either throttles cheap discovery or waves through expensive scans.

I can report that the limiter works, because it fired at me during the attack
run above and refused four of my six attempts. Genuinely annoying. Correct.

4. A second adapter is a test for the first

The project claimed to be engine-agnostic. It shipped one adapter. That claim
was therefore untested, and I knew better than to believe my own README.

So I added DuckDB, chosen because it's in-process: the portability tests need no
container and no credentials, and run in plain CI.

It found two defects on the first run.

The first was structural. The SQL dialect was a module-level constant, imported
by value, so no deployment could actually change it. The "swappable warehouse"
was swappable in the documentation only. It moved to config.

The second was a real latent bug. bind_params() always bound the tenant list,
including for public-table queries containing no tenant predicate at all.
Postgres silently ignores an unused named parameter. DuckDB does not:

Invalid Input Error: Parameter argument/count mismatch,
identifiers of the excess parameters: qg_tenant_scopes

That bug had been sitting there the entire time, invisible because exactly one
engine was polite about it.

BigQuery and Snowflake came later and made the same point louder. The canonical
predicate the governance layer injects and re-proves is col = ANY(:param),
which is Postgres syntax and nothing else's. BigQuery wants IN UNNEST(@param).
Snowflake wants ARRAY_CONTAINS(col::VARIANT, PARSE_JSON(:param)). So there is
now a rewrite at the last hop before execution, and because a rewrite that
quietly drops a predicate is the single worst bug this codebase could have, it
re-proves its own output: the count of engine-form predicates must equal the
canonical count it replaced, and no canonical form may survive. Same trick as
section 1, one layer down. Four adapters in, the pattern has stopped feeling
like paranoia and started feeling like the only way to sleep.

The general lesson: an abstraction with one implementation is a guess. If
your architecture diagram has a box labelled "pluggable" and only one thing has
ever been plugged in, that box is decoration.

5. Sometimes the right move is not to be the one enforcing it

Everything above assumes a tenant column exists to filter on. Then I described
the project to someone running a mid-sized company's warehouse and got the
obvious question back: what if it doesn't?

Their marts were modelled the way most marts are actually modelled, which is to
say by whoever needed them, at the time, for one dashboard. Team-by-team schemas,
no tenant column anywhere, and exactly one technical Snowflake user because
Snowflake seats cost money and nobody wanted to explain twelve of them to
finance.

Point QueryGate at that in inject mode and here is what happens. The catalog
loader marks a model governed if it has the tenant column. None of them do. So
nothing is governed, no predicate is injected anywhere, every query runs under
the one service identity, and each caller receives the union of everything that
identity can read. The query succeeds. No error is raised. Nothing anywhere
reports a problem.

Wrong rows, no error. Again. The exact failure this whole project exists to
prevent, arriving through the front door because the precondition quietly did not
hold.

The fix is not to inject harder. It is a second mode where QueryGate stops being
the thing that enforces row security and the warehouse does it, because the
warehouse is frankly better at it and their CDO already owns the grants. Set
QG_GOVERNANCE_MODE=warehouse and the query runs as the caller's own warehouse
role: their IdP role maps to exactly one Snowflake role, the session opens
under it, Snowflake's grants decide what is readable.

flowchart LR
    subgraph inject["QG_GOVERNANCE_MODE=inject"]
        direction TB
        A1[Agent writes SQL] --> B1[QueryGate injects<br/>tenant predicate]
        B1 --> C1[Independent re-parse<br/>proves it is there]
        C1 --> D1[(Warehouse)]
        D1 -.->|one service identity,<br/>rows already filtered| E1[Caller's rows]
    end

    subgraph wh["QG_GOVERNANCE_MODE=warehouse"]
        direction TB
        A2[Agent writes SQL] --> B2[QueryGate resolves<br/>caller to ONE role]
        B2 --> C2[Session opens<br/>under that role]
        C2 --> D2[(Warehouse)]
        D2 -.->|engine's own grants<br/>decide the rows| E2[Caller's rows]
    end

Alternatives, not layers. The left column needs a tenant column on every
governed model; the right needs a warehouse role per audience.

Three things make it hold up rather than just sound good.

The mapping is an allowlist, not a claim passed through. It would be one line
of code to read the role out of the caller's token and hand it to Snowflake. It
would also mean a loose IdP configuration lets somebody name any role in the
warehouse. So the map lives in operator config, and a role that is not in it does
not exist as far as this server is concerned.

flowchart LR
    KC["Keycloak / OIDC"] -->|"JWT"| T

    subgraph T["Token claims"]
        direction TB
        R["realm_access.roles<br/><b>analyst</b>"]
        TN["tenant_ids<br/><b>acme</b>"]
    end

    R --> MAP{"QG_WAREHOUSE_ROLE_MAP<br/>operator config"}
    MAP -->|"analyst maps to QG_ANALYST"| ROLE["Warehouse role<br/><b>QG_ANALYST</b>"]
    MAP -->|"unmapped, none,<br/>or several"| REF["REFUSED"]

    TN -.->|"used in inject mode only"| PRED["Tenant predicate"]
    ROLE --> SF[("Snowflake<br/>session")]

    style MAP fill:#fff4e6,stroke:#c98a3a,stroke-width:2px
    style REF fill:#ffe0e0,stroke:#c0392b

The token's roles are lookup keys, never values. A forged claim of
QG_FINANCE matches no key and is refused.

Refusal, never a fallback. No mapped role, no roles at all, anonymous caller,
or several mapped roles all refuse the query. That last one took me a minute to
land on: picking one silently would grant or withhold access by accident, and I
would rather an analyst with two hats be told to get a third role created than
guess which of their hats I meant.

It fails at startup, not at request time. warehouse mode with no role map
refuses to boot. A deployment that would serve every caller as one identity
should not accept traffic while somebody notices.

And then the part that actually cost me an evening.

Every surface you would think to check looks correct.

flowchart TB
    START["Service user connects<br/>role=QG_ANALYST"]
    START --> CHECK{"DEFAULT_SECONDARY_ROLES<br/>on the user?"}

    CHECK -->|"('ALL'), the default<br/>for several creation paths"| BAD1["Session activates<br/>EVERY granted role"]
    BAD1 --> BAD2["USE ROLE narrows nothing"]
    BAD2 --> BAD3["Reads every audience's views"]
    BAD3 --> BAD4["Connection looks correct<br/>Logs look correct<br/>No error anywhere"]

    CHECK -->|"( ), set explicitly"| OK1["Only the primary role<br/>is active"]
    OK1 --> OK2["USE ROLE QG_ANALYST"]
    OK2 --> OK3["SELECT CURRENT_ROLE()<br/>read back and compared"]
    OK3 --> OK4["Wrong role, refuse<br/>Right role, execute"]

    style BAD4 fill:#ffe0e0,stroke:#c0392b,stroke-width:2px
    style OK4 fill:#e0f5e0,stroke:#27795b,stroke-width:2px

Wrong rows, no error. The red path is what you get by default, and every
surface you would think to check looks healthy.

For any of this to work, the service user has to be granted every mapped role.
That is unavoidable: it is the account doing the switching. Now, Snowflake users
carry a property called DEFAULT_SECONDARY_ROLES, and for users created through
several perfectly normal paths it defaults to ('ALL'). What that means is the
session activates every role the user has been granted, regardless of which one
you connected with.

A property, not a session parameter, which matters when you go looking for it:

DESC USER QUERYGATE_SVC;   -- the DEFAULT_SECONDARY_ROLES row

SHOW PARAMETERS will not find it and will not complain either, it just returns
nothing, which is a memorable ten minutes. Reading another user's properties also
needs MONITOR on that user or a role like USERADMIN, so an analyst checking a
service account gets a privilege error. Correct behaviour, and a reminder that
the person who can answer this is the person who created the account.

Read that again with the previous paragraph in mind. USE ROLE QG_ANALYST
narrows nothing. The connection is configured correctly. The logs look right. The
analyst reads every audience's views. Wrong rows, no error, one layer further
down, and this time wearing the costume of the fix.

So the adapter does three things in order, before any query runs: USE SECONDARY ROLES NONE, which is what actually narrows the session; then USE ROLE; then
SELECT CURRENT_ROLE() and compare, so a silent fallback to a default role fails
closed instead of answering as somebody else.

What I would say to anyone building this: the general shape of the bug is a
control whose precondition is invisible. Predicate injection needs a tenant
column. Role pinning needs secondary roles off. Both degrade to success when
the precondition is absent, which is why both now assert their own precondition
rather than assuming it.

You hand over one key. The ring comes with it.

One thing this mode does not fix, and I would rather say so than let someone find
out during an audit: the service user still holds the union of every mapped role.
Role pinning is a control QueryGate applies at runtime, not a property of the
account. Whoever holds those connection details holds the warehouse. The honest
end state is External OAuth, where the analyst's own identity reaches Snowflake
and no union principal exists at all, but that needs every analyst to have a
Snowflake identity, which is precisely what a company with one technical user
does not have. That is an org problem wearing an engineering costume, and it is
usually what decides the timeline.

The bug I'd rather show than hide

Column masking worked. PII columns marked in the dbt catalog got wrapped in a
hash on the way out, equal values hashed equal so count(distinct) still
returned the right answer, and I was pleased with myself for roughly a day.

Then I tested SELECT *.

A wildcard is a Star node, not a Column node. My rewrite walked columns.
It never saw the star. The single laziest query available to any user on earth
walked straight through the entire control.

Wildcards are now expanded from the catalog before masking runs. A test caught
it, which is the argument for writing the paranoid ones. The tests you write
while assuming you're an idiot are the tests that find out you were right.

The second half of that control is less obvious and, I think, more interesting.
Masking only the SELECT list is theatre. If a masked column can still appear in
a predicate, the caller has an oracle:

SELECT count(*) FROM customers WHERE customer_name = 'Alice Smith'

Run that a few hundred times and you have read the column one guess at a time,
while every returned value stayed dutifully masked. So masked columns are
confined to the projection: WHERE, JOIN, GROUP BY, ORDER BY and HAVING
are all refused, with an error explaining why.

How I tested it

A governance matrix. Every case that could leak a tenant is a test: single
governed table, aliased table, join of two governed tables, join of governed and
public where only one side gets filtered, subquery, CTE body, a schema-qualified
table shadowing a same-named CTE, empty scope, and the adversarial asserts:
filter missing, filter OR-ed with 1=1, right column but wrong parameter.
This is the suite that lets me sleep.

Ground-truth evals. Tests prove the code does what I said. They cannot tell
me whether the agent found the right table or computed the right number. So
there's a golden set of plain analyst questions, each pinned to a ground-truth
answer computed from a deterministic seed, with retrieval quality measured
separately as hit@k. Two independent signals: did it find the right table, and
did it compute the right number. When something's off, you know which layer to
fix, which saves you from tuning retrieval to solve what was actually a prompt
problem.

290 tests. 213 of them pass on a bare uv sync with no optional dependency and
no infrastructure at all; the rest need DuckDB, Redis or a real Postgres, and CI
runs every one except the Qdrant case.

Making it yours

Because the point was a template, the boundaries got drawn where a team would
actually need to cut.

The warehouse is two coroutines, estimate and execute. Postgres, DuckDB,
BigQuery and Snowflake ship; Trino is one module more plus a line in a dict. The catalog comes from whatever your dbt
CI already publishes, or a JSON bundle in a documented shape if you don't run
dbt. Auth is a TokenVerifier returning a principal, so an in-house SSO gateway
drops in beside the OIDC one. Tenancy is a column name in config, and if you'd
rather let Snowflake row access policies do that job, QG_GOVERNANCE_MODE=warehouse
turns injection off and keeps the pipeline for cost control, masking, grounding
and retrieval. A snow CLI script provisions the role model from the same map
the server reads, so the grants and the mapping cannot drift.

The part I'd ask a team to read before changing anything is query/: pure, no
I/O, the whole safety argument in about 1,200 lines. Its companion is
test_governance.py, where that argument is written down as assertions. Change
the test first and the code second.

What deliberately doesn't generalise is the dbt project in dbt/. Three tenants
and a few hundred orders, small enough that the diffs stay readable. It is meant
to be deleted.

The honest backlog

Things that are missing, stated plainly, because a template that oversells
itself wastes somebody's sprint:

No connection pooling. Every call opens a fresh psycopg connection, and
run_query opens two: one for the EXPLAIN, one to execute. Fine for a demo
and for a handful of analysts. It is unambiguously the first thing that falls
over under real concurrency.

The masking hash is MD5. It's a pseudonymiser, not a secret. Analytical
value survives; a low-cardinality column with a guessable domain, like customer
names, does not survive a determined dictionary attack by somebody who can see
the hashes. For regulated data, swap in a keyed HMAC whose secret the query
layer never returns.

The cost ceiling is off unless you set it. QG_MAX_PLAN_COST defaults to
zero, which disables it. Compose sets a real number. A bare install does not.

The guarantee is only as strong as sqlglot's parse. The assert is
independent of the injector but not of the parser, since both read the SQL through
the same library. If sqlglot resolves a scope differently from your engine, the
predicate can land in the wrong place and re-parsing won't catch it. This is the
honest boundary of the threat model, and it's why inject-and-assert belongs
over native row access policies rather than instead of them.

The takeaway

If you're wiring an LLM to a database, the question worth asking on day one is:
what is the model allowed to decide?

Let it write the query. That's its strength and it's genuinely good at it now.
Do not let it decide who may see what, whether the query is read-only, or how
much it's allowed to scan. Put those behind a deterministic layer, inject them
yourself, then re-verify them independently before you execute.

Trust the model to be clever. Don't trust it to be safe.

The exec, for the record, still doesn't know what SQL is. He gets his number
before the 2pm, it's correct, and it's only ever his company's number. That was
always the entire brief.

The analyst is still on item 38 of 37. But she's no longer on it because
somebody wanted a sum.

The code is on GitHub. Clone it, docker compose up, and try to make one tenant
read another's data. I'd genuinely like to know if you can, and if you lift it
into your own stack, I'd like to hear which seam turned out to be in the wrong
place.

QueryGate is MIT-licensed. Built with FastMCP, sqlglot and dbt, running on
Postgres, DuckDB, BigQuery or Snowflake.

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

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

Arif Ismailov — Thu, 27 Aug 2026 07:25:25 +0000

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

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:

What the agent needs	Where it already lives
What a table means, and its grain	`description` in `_models.yml`
Which columns exist, and their real warehouse types	`catalog.json`, from `dbt docs generate`
Which columns are PII	`meta: {mask: hash}` on the column
How tables join	`relationships` tests
What "completed revenue" means	MetricFlow semantic models
That "money" means "revenue" here	a glossary file next to the project

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.

Configuration	hit@1	hit@3
BM25 alone	22%	38%
Glossary written backwards	25%	38%
Glossary written blind, from the schema	31%	56%
Embeddings (RRF), no glossary	38%	62%
Blind glossary + embeddings	47%	69%
Tuned glossary (leaked the test set)	81%	97%

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.