Text-to-SQL for Production Analytics Assistants in October 2026: Schema Linking, Read-Only Roles, and Query Validation Before Anything Runs

"How many customers upgraded last month?" sounds like the perfect job for an LLM. The model knows SQL, your warehouse holds the answer, and a chat box beats waiting two days for an analyst. Then the first real week happens: a query joins the wrong table and doubles revenue, another scans three years of events and blows the warehouse budget, and someone asks the assistant to "clean up the test accounts" and it writes a DELETE.

Text-to-SQL works in production, but only when the model is the smallest part of the system. This guide walks through the pieces that make it safe and accurate: giving the model the right slice of the schema, running it under a role that cannot hurt anything, validating every query before it runs, and showing users enough of the reasoning that they can catch a wrong answer.

Image: Vasek Frolik via Wikimedia Commons (CC BY 4.0)

Why naive text-to-SQL fails

The common first version pastes the whole schema into the prompt, asks for a query, and runs whatever comes back. It fails in predictable ways:

• Wrong table, plausible answer. Real warehouses have orders, orders_v2, orders_clean, and a staging copy nobody deleted. The model picks one by name, and the number it returns looks reasonable, so nobody notices.
• Business definitions are not in the schema. "Active customer" might mean "logged in within 30 days" to product and "has a paid invoice" to finance. Column names cannot tell the model which one you mean.
• Join fan-out. Joining customers to orders to line items and then summing order totals counts each order once per line item. The SQL is valid; the answer is wrong.
• Cost and safety. A missing date filter on an events table can scan terabytes. A writable connection means one bad generation can change data.

None of these are fixed by a better model alone. They are fixed by context, permissions, and checks around the model.

Step 1: Schema linking instead of schema dumping

Schema linking means choosing which tables and columns are relevant to a question before you ask for SQL. It shrinks the prompt and, more importantly, removes the decoy tables the model would otherwise be tempted by.

A practical approach:

1. Curate an allowlist. Start with the 10 to 40 tables analysts actually trust. Mark deprecated and staging tables as excluded. This one decision removes a large share of wrong-table errors.
2. Write table and column descriptions. One sentence per table and per non-obvious column: what a row represents, the grain ("one row per invoice line"), units ("amount in cents"), and gotchas ("includes refunds as negative rows").
3. Retrieve, then expand. Embed the descriptions and retrieve the top tables for each question. Then add any tables connected to those by foreign keys, so the model sees the join path rather than guessing it.
4. Include a few sample values. For low-cardinality columns like status or plan, list the actual values ('trialing', 'active', 'canceled'). Models otherwise invent values like 'Active' that match nothing.

Step 2: Put business logic in a semantic layer, not the prompt

If "monthly recurring revenue" has one correct definition, do not ask the model to rederive it each time. Define it once as a view, a metric in your semantic layer, or a documented SQL snippet, and expose that to the model as the thing to query.

-- Exposed to the assistant as the only source for MRR
CREATE VIEW analytics.mrr_by_month AS
SELECT date_trunc('month', period_start) AS month,
       SUM(amount_cents) / 100.0          AS mrr_usd
FROM billing.subscription_periods
WHERE status = 'active'
  AND is_test_account = false
GROUP BY 1;

Now "what was MRR in August?" becomes a one-line query against a view that finance already agreed on. The model's job shrinks from inventing business logic to picking the right metric and filter, which it does far more reliably. Keep a small glossary in the prompt that maps common phrases ("revenue", "active user", "churn") to these views.

Step 3: Run under a role that cannot do damage

Treat every generated query as untrusted input. Validation (next step) catches most problems, but the database role is the backstop that holds even when validation has a bug.

• Read-only role. Grant SELECT on the allowlisted schemas and nothing else. No INSERT, UPDATE, DELETE, DDL, or access to system schemas beyond what is needed.
• Row-level and column-level security. If users should only see their own region or tenant, enforce it in the database with row-level policies, not with a WHERE clause you hope the model remembers. Mask or exclude columns with personal data the assistant has no reason to return.
• Resource limits. Set a statement timeout and, where your warehouse supports it, a cap on bytes scanned or credits per query. In PostgreSQL, SET statement_timeout = '15s' on the session is a cheap start.
• Read replica or separate warehouse. Point the assistant at a replica or a dedicated compute pool so a heavy query slows down the assistant, not your production app.

Image: SqlPac via Wikimedia Commons (CC BY-SA 3.0)

Step 4: Validate the query before it runs

Parse the generated SQL with a real parser (for example, sqlglot in Python supports many dialects) rather than regex. Then apply checks on the parsed tree:

1. Single statement, read only. Reject anything that is not exactly one SELECT (or WITH ... SELECT). This blocks stacked statements like SELECT 1; DROP TABLE users.
2. Table allowlist. Every referenced table must be on the allowlist. Reject references to system catalogs or excluded schemas.
3. Required filters on big tables. For large fact tables, require a date predicate. If it is missing, either reject with a clear message or add a default window (say, the last 90 days) and tell the user you did.
4. Row limit. Inject or tighten a LIMIT on non-aggregated results so a question never streams a million rows into the chat.
5. Dry run or EXPLAIN. Many warehouses can estimate cost without executing. BigQuery dry runs report bytes processed; PostgreSQL's EXPLAIN gives an estimated cost and row count. Refuse or ask for confirmation above a threshold.

When a check fails, feed the specific error back to the model and let it try again, with a cap of two or three attempts. Error messages like "table orders_v2 is not available; use analytics.orders" fix most mistakes on the second try.

Step 5: Catch wrong answers, not just broken queries

A query that runs is not a query that is right. A few cheap guards help:

• Fan-out detection. When a query sums a column after joining a one-to-many relationship, flag it. You can compare COUNT(*) to COUNT(DISTINCT primary_key) on the joined set to see whether rows were duplicated.
• Sanity bounds. For known metrics, store expected ranges. If monthly revenue comes back ten times higher than last month, show a warning instead of a confident sentence.
• Show your work. Display the SQL (collapsed by default), the tables used, and the filters applied in plain language: "Counted paying customers, excluding test accounts, from August 1 to August 31." Users catch wrong assumptions quickly when they can read them.
• Empty results are a signal. Zero rows often means an invented filter value. Check filter literals against the known values from Step 1 and retry before telling the user "no results."

Step 6: Build an evaluation set from real questions

Collect 50 to 200 real questions from your analysts and logs, each paired with a trusted reference query. Score the assistant by executing both and comparing result sets, not by comparing SQL text, because many different queries produce the same correct answer. Run this set on every prompt change, schema description edit, and model upgrade. Add every production mistake users report as a new test case.

A minimal request flow

1. Receive the question and the user's identity.
2. Retrieve relevant tables, columns, sample values, and glossary entries.
3. Ask the model for one SQL query plus a one-line plain-English description of what it computes.
4. Parse and validate: read only, allowlisted tables, required filters, limit, cost estimate.
5. On failure, return the error to the model and retry, up to a small cap.
6. Execute under the read-only, row-secured role with a timeout.
7. Run sanity checks, then answer with the result, the description, and the SQL.
8. Log the question, query, cost, and any user feedback for the evaluation set.

Where to start

If you only do three things this week, do these: create a read-only role with a statement timeout, write one-sentence descriptions for your trusted tables and hide the rest, and add a parser-based check that rejects anything other than a single SELECT. Those steps alone turn text-to-SQL from a risky demo into something you can hand to a small group of users, and the evaluation set will tell you when it is ready for more.