Setting Up a Text-to-SQL Assistant Against Your Warehouse Without Exposing Raw Tables

The fastest way to get a text-to-SQL assistant into production is also the worst: point an LLM at your warehouse's information_schema, hand it read credentials, and let it write whatever query the user asks for. It demos beautifully. Then someone asks "who are our highest-value churned accounts" and the model happily joins stripe_raw.charges to hubspot_sync.contacts on an email column that isn't hashed, returns 4,000 rows of names and card BINs, and you've created a data-exfiltration path that reports to no one.

The interesting engineering problem in 2026 isn't getting a model to write SQL. Claude, GPT-5.x, and the warehouse-native copilots are all competent at that. The problem is building a boundary so the model operates on a curated, governed surface and never touches raw tables. Here is the architecture I've settled on after shipping this pattern at two companies.

Never let the model see the physical schema

The single most important decision: the LLM's context should contain your semantic layer, not your physical tables. If you're on dbt, that means the model reads from a compiled semantic_manifest.json — the metrics, dimensions, and entities you've explicitly defined — not from the 900 columns across your staging and intermediate models.

This flips the usual mental model. You are not asking the LLM to "understand your database." You are giving it a small, deliberately narrow menu of things it is allowed to talk about. A well-built semantic layer for a mid-size company might expose 30 metrics and 40 dimensions. That is a context window the model can actually reason over reliably, and it is one you can review by hand.

Concretely, the flow looks like this:

1. User asks a question in natural language.
2. The model is given the semantic layer definitions (metric names, descriptions, valid dimensions, allowed filters) — not DDL.
3. The model emits a structured query against the semantic layer, not raw SQL. For dbt that's a MetricFlow query; for Cube it's a Cube query object.
4. The semantic layer compiles that into governed SQL and executes it.

The model never writes a JOIN. It never picks a grain. It can't accidentally fan out a fact table because the join paths are pre-defined in the semantic layer. This is the difference between "the AI writes SQL" and "the AI fills in a query template you already validated."

When you genuinely need raw SQL generation

Semantic layers don't cover everything. Ad hoc exploration, one-off investigations, and long-tail questions will exceed your metric definitions. For those cases you do want the model writing SQL — but against a hardened surface.

Build a dedicated schema, call it llm_safe, containing views only. No base tables. Each view:

• Excludes PII by construction. No raw email, no names, no payment identifiers, no free-text notes fields. If a downstream question needs to segment by customer, expose a surrogate account_id and a segment column, never the identity.
• Pre-applies row-level security. The view filters to the requesting analyst's allowed scope. In Snowflake this is a row access policy on the underlying tables that the view inherits; in BigQuery, authorized views plus row-level access policies.
• Carries column descriptions in the view metadata, which becomes the model's documentation. The model reads llm_safe's schema and comments — and only that.

The service account the assistant uses has SELECT on llm_safe and nothing else. Not on raw, not on staging, not on analytics. If the model hallucinates a table name from your real warehouse, the query fails with a permissions error instead of returning data it shouldn't.

The three-layer guard: generate, validate, execute

Between the model's output and your warehouse, insert a validation layer that runs before any query executes. This is not optional, and it's where most home-grown assistants fall down.

Parse, don't regex

Parse the generated SQL into an AST with something like sqlglot. Reject anything that isn't a single SELECT. No DDL, no DML, no multiple statements, no INTO, no session variables. A string search for "DROP" is not a security control; an AST walk that allowlists node types is.

Enforce table and column allowlists

Walk the parsed tree and confirm every referenced relation lives in llm_safe. Confirm every column exists in your allowlist. If the model references anything outside the surface, you reject before execution and feed the error back to the model to retry. This catches the failure mode where a model, primed on generic training data, invents a plausible users table that happens to exist in your warehouse.

Force resource limits

Inject a LIMIT if none exists. Set a statement timeout (30 seconds is generous for exploratory work). On Snowflake, run against a dedicated XS warehouse with a strict credit quota. On BigQuery, set a maximum-bytes-billed cap per query. An analyst's typo in English should never trigger a 40-terabyte scan.

Make it observable, because it will be wrong

Log every interaction as a triple: the natural-language question, the generated query, and the result row count. Two reasons. First, you need an audit trail — when someone asks "did the assistant ever return revenue by named customer," you need to answer definitively. Second, the logs are your evaluation set. After a week you'll have a corpus of real questions, and you'll see exactly which ones the model gets wrong and why.

Build a small regression suite from those logs. When you upgrade the model or change a metric definition, replay 50 known-good questions and diff the results. Text-to-SQL quality is not stable across model versions — a prompt that worked on one Claude release can subtly change behavior on the next, and you only catch it if you test.

What to tell the model, and what to withhold

Prompt design matters more than people expect. A few things that measurably help:

• Provide worked examples. Three or four question-to-query pairs from your actual semantic layer, in the system prompt, outperform pages of instructions. Models are strong few-shot learners for this task.
• Give it the metric definitions in prose. "active_users counts distinct users with at least one session in the period; it excludes internal accounts flagged is_internal" prevents the model from inventing its own definition of active.
• Tell it to refuse. Instruct the model that when a question can't be answered from the available metrics and dimensions, it should say so rather than improvise a join. A model that says "I can't answer that from the governed data" is doing its job.

A realistic rollout

Start read-only, semantic-layer-only, with a handful of trusted analysts. Watch the logs for two weeks. Add the raw-SQL llm_safe path only once the guardrails are proven. Resist the pressure to open it to the whole company on day one — the value is real, but the failure modes are the kind that end up in a postmortem, not a Slack apology.

The mental shift that makes all of this work: you are not building an assistant that can query your warehouse. You are building a governed surface, and letting a model drive it. The model is the easy part. The surface is the product.