Two Checks Between an LLM and Your Warehouse

A client wanted their own customers to ask questions of their marketing data in plain English. The obvious build is to hand a model the schema, let it write SQL and run whatever comes back. That works in a demo after an afternoon. The part that takes the time is making sure a question from one customer can never read another customer's rows.

A prompt is not a permission system

The first instinct is to put the rule in the prompt. Only query the tables for this client. That holds right up until someone asks a question the model reads creatively, or pastes in text telling it to do something else. A prompt is a request. I don't treat anything the model writes as trusted, however good the instructions were.

So the model can write whatever it likes, and everything it writes goes through two checks before it gets near the warehouse. If either one says no, the query is refused.

Check one: read the query

The first gate looks at the SQL text. It has to be a SELECT, with no insert, update, delete, merge or DDL anywhere in it. Every table it names has to be one of this client's modelled tables, the mart and dimension tables the dashboard itself reads. Not another client's dataset, not another project, and not the raw staging tables the marts are built from.

It sounds simple, and I still got it wrong the first time. The check strips comments and string literals before looking for table names, and it did that with two regular expressions, strings first and comments second. An apostrophe inside a comment, something as ordinary as -- it's fine, opened a string match that ran to the next quote and swallowed the SQL in between. A subquery sitting there was invisible to the check, and a query shaped that way read over half a million rows straight out of a raw export table. The model wasn't even trying. It just writes contractions in its comments like everyone else.

The fix was to stop using regex for it. Whether a quote opens a string depends on whether a comment opened first, and the other way round, so the only correct way is one pass from left to right. That's the kind of hole a text check always has somewhere, which is why it isn't the only one.

Check two: ask the warehouse what it would actually read

BigQuery has a dry run. You submit the query, it plans it without running it, and it tells you which tables it would read, resolved through every view underneath. The second gate dry-runs the query and checks that every one of those tables sits in this client's own dataset or in a source dataset the client is declared to own. If anything falls outside, the query never runs.

This one can't be talked round. It's BigQuery's own parser rather than something I wrote, and it sees through whatever the SQL does to hide a reference. The query also runs with a hard cap on bytes scanned, so a runaway scan is impossible rather than just expensive.

Why both, and not just the strong one

The dry run is blind in one specific way. Because it resolves through views, a mart disappears from the list and you only see what it's built from. A query that reads the raw source table directly looks exactly like a query that reads a mart built on it. The text check catches that, and the dry run catches whatever a view reads that the text can't see. They cover different gaps, so you genuinely need both, and both fail closed. A query that can't be proved safe is refused, never run.

One more detail that turned out to matter. When a query is refused for reaching outside the client, the assistant stops. It doesn't hand the error back to the model and let it have another go in a different shape.

The client is pinned outside the model

The declared table list comes from the session, not from anything in the request. A user signs in, the session says which company they belong to, and that decides whose marts are in scope. There's no client id in the URL to edit, and nothing in the question can change it. The model never gets told who it's serving in a way it could be talked out of. It just finds that the only tables it can reach belong to one company.

Each question costs a few cents, almost all of it the model call. The dry run itself is free.

None of this is BigQuery specific. In Postgres, EXPLAIN shows the plan with views expanded, and a role per tenant with row-level security gets you the same guarantee from the database itself. What I'd push back on is any setup where the only thing between a question and the wrong customer's data is a sentence in a system prompt.

If you're building something like this, it's the kind of work I do.