Building an AI Analytics Assistant: Text-to-SQL with Guardrails

"What was revenue by region last quarter?" Large language models can now turn a question like that into SQL, which makes an AI analytics assistant one of the most requested features on data teams' roadmaps. They are also easy to build badly: an assistant that runs whatever SQL the model writes, on raw tables, with a powerful database user, will eventually give a confident wrong answer or touch data it should not. This article shows the architecture that makes text-to-SQL safe enough for real use, with a runnable guardrail example.

The architecture

1. Question from the user, in plain language.
2. LLM with context: the model gets the question plus a compact description of the allowed tables, columns and metric definitions, ideally generated from your semantic layer.
3. SQL validator: code, not the model, checks the generated SQL: one statement, read-only, only approved tables, row limit enforced.
4. Warehouse: the query runs under a dedicated read-only role that can only see approved, modelled tables, never raw personal data.
5. Answer: the result is shown together with the SQL that produced it, so the user (or an analyst) can check the logic.

The key principle: the model proposes, deterministic code and database permissions decide. Prompt instructions alone are not a security boundary.

Step 1: give the model a small, governed context

Accuracy depends far more on context than on model choice. Describe only the tables the assistant needs, with column types and business definitions, and tell it how to refuse. A sketch:

SYSTEM PROMPT (sketch)
You translate business questions into a single DuckDB SQL SELECT statement.
Use only these tables and columns:
  fct_orders(order_id, order_date DATE, region TEXT, net_amount DOUBLE)
Metric definitions (always use these exactly):
  revenue = SUM(net_amount)
  orders  = COUNT(DISTINCT order_id)
If the question cannot be answered from these tables, reply CANNOT_ANSWER.
Return only SQL, no explanation.

Feeding in metric definitions from the semantic layer means "revenue" is computed the same way the finance dashboard computes it. Including a handful of example questions with correct SQL (few-shot examples) typically improves results further.

Step 2: validate the SQL with code

The example below uses sqlglot to parse the model's SQL and reject anything that is not a single SELECT on approved tables, then adds a row limit. The four candidate queries stand in for model output: one good answer and three things you never want executed.

import duckdb, sqlglot
from sqlglot import exp

con = duckdb.connect()
con.execute("""CREATE TABLE fct_orders AS SELECT * FROM (VALUES
  (1, DATE '2026-07-03', 'EMEA', 120.0), (2, DATE '2026-07-15', 'AMER', 80.0),
  (3, DATE '2026-08-02', 'EMEA', 200.0), (4, DATE '2026-08-20', 'AMER', 50.0)
) t(order_id, order_date, region, net_amount)""")
con.execute("CREATE TABLE customers_pii AS SELECT 1 AS id, 'a@x.com' AS email")

ALLOWED_TABLES = {"fct_orders"}
MAX_ROWS = 1000

def validate_sql(sql: str) -> str:
    """Accept only a single read-only SELECT over approved tables; enforce a row limit."""
    statements = sqlglot.parse(sql, read="duckdb")
    if len(statements) != 1:
        raise ValueError("exactly one statement allowed")
    tree = statements[0]
    if not isinstance(tree, exp.Select):
        raise ValueError(f"only SELECT allowed, got {tree.key.upper()}")
    tables = {t.name for t in tree.find_all(exp.Table)}
    if not tables <= ALLOWED_TABLES:
        raise ValueError(f"table(s) not allowed: {sorted(tables - ALLOWED_TABLES)}")
    if not tree.args.get("limit"):
        tree = tree.limit(MAX_ROWS)
    return tree.sql(dialect="duckdb")

# Pretend these came back from the LLM
candidates = [
    "SELECT region, SUM(net_amount) AS revenue FROM fct_orders GROUP BY region ORDER BY revenue DESC",
    "DELETE FROM fct_orders",
    "SELECT email FROM customers_pii",
    "SELECT 1; DROP TABLE fct_orders",
]
for sql in candidates:
    try:
        safe = validate_sql(sql)
        print("OK  ", safe)
        print(con.sql(safe).df().to_string(index=False))
    except Exception as e:
        print("BLOCK", type(e).__name__, "-", e)

OK   SELECT region, SUM(net_amount) AS revenue FROM fct_orders GROUP BY region ORDER BY revenue DESC LIMIT 1000
region  revenue
  EMEA    320.0
  AMER    130.0
BLOCK ValueError - only SELECT allowed, got DELETE
BLOCK ValueError - table(s) not allowed: ['customers_pii']
BLOCK ValueError - exactly one statement allowed

The legitimate query runs (with a limit added); the delete, the attempt to read a personal-data table, and the stacked DROP are all blocked before reaching the database. Run it with pip install duckdb sqlglot pandas.

This validator is deliberately simple. A production version should also handle CTEs (their names appear as tables), UNION queries, column-level allow-lists and query cost limits. And it is a second line of defence: the first is a database role that physically cannot write or see restricted tables.

Step 3: make answers checkable

• Show the SQL (or a plain-language summary of it) with every answer.
• State assumptions, such as which date range "last quarter" was interpreted as.
• Prefer refusing to guessing: an explicit "I can't answer that from the available data" builds more trust than a plausible wrong number.
• Log every question, query and result so analysts can review failures and add them as examples.

Measure accuracy before launch

Build an evaluation set of 50 to 100 real business questions with verified answers, run the assistant against it, and track the share answered correctly, the share refused, and, most importantly, the share answered wrongly. Re-run the set whenever you change the model, the prompt or the semantic layer. Without this, you only find out about errors when someone quotes one in a meeting.

Checklist

Area	Minimum for production
Data access	Dedicated read-only role; only modelled tables; no raw personal data
Context	Table and metric descriptions from the semantic layer; refusal instruction
Validation	Single SELECT, allow-listed tables, row and cost limits
Transparency	SQL and assumptions shown with each answer
Evaluation	Fixed question set with verified answers, re-run on every change
Monitoring	Logged questions, failures reviewed weekly

Summary

A useful AI analytics assistant is mostly good data engineering: clean marts, a semantic layer with clear definitions, a read-only role, and deterministic validation around the model. The LLM translates questions; your platform guarantees what it is allowed to do. It is the final layer of the modern data engineering architecture.