Text-to-SQL AI Agents: Let Your Team Query Data Safely
A text-to-SQL agent lets your team ask “what were last month’s overdue invoices by region?” and get an answer from your own database in seconds, without waiting for the data team. Done safely, it can only read data, only sees what each user is allowed to see, and always shows how it got the answer.
The short answer: never point a language model at your production database and hope. Give it read-only access to a curated semantic layer: approved tables and views, plain-English definitions of your business terms, and example questions with correct queries. Enforce permissions in the database, not in the prompt. Check every generated query before it runs, show the query and the assumptions behind every answer, test on your team’s real questions, and teach the agent to say “I don’t know” or ask a clarifying question.
Finance and operations teams ask the same kinds of data questions every week, and each one waits in a queue for an analyst. Text-to-SQL agents (SQL is the language databases are queried in) can answer many of them directly. This guide is for data leads and operations heads deciding how to do that safely. It sits in our Agent Engineering work.
Why naive text-to-SQL goes wrong
- The model doesn’t know your business. “Active customer”, “revenue” and “overdue” have specific meanings in your company. The model will guess.
- Real schemas are messy. Hundreds of tables, cryptic column names, old tables nobody uses. The model picks the wrong one.
- Wrong answers look right. A query that runs and returns a number is convincing even when it joined the wrong tables.
- Security gaps. Without guardrails, a query can read data the user shouldn’t see, or run long enough to slow down production.
The architecture
Read-only, always
- Connect through a database role that can only read (SELECT), never insert, update or delete.
- Point it at a read replica or warehouse, not the system your business runs on.
- Set a statement timeout and a row limit, so a heavy query can’t slow anything down.
- Reject any query that isn’t a single read statement before it reaches the database.
The semantic layer: your business, defined
The semantic layer is the most important part of the system and the most often skipped. It holds:
| Part | Example |
|---|---|
| Approved tables and views | A clean invoices view, not the raw ledger tables |
| Column descriptions | due_dt: the date payment is due, in local time |
| Business terms | Overdue: unpaid and due date before today |
| Metrics | Revenue: invoiced amount excluding tax and credit notes |
| Approved joins | How invoices link to customers and regions |
| Example questions | 30 to 50 real questions with checked queries |
The agent searches this layer for the definitions and examples relevant to each question, the same retrieval idea described in our hybrid RAG guide, and writes SQL only against what it finds there. If the data isn’t in the semantic layer, the agent can’t query it. That is a feature: your data team decides what is in scope.
Row-level permissions, enforced by the database
A sales manager for one region should see only that region’s numbers, whether they ask a person or an agent. Enforce this where it can’t be talked around:
- run every query as the user, or with their role, not as one shared super-user;
- use the database’s row-level security or per-role views, so filters apply whatever SQL the model writes;
- never rely on the prompt (“only show this user’s region”) for security. A prompt is an instruction, not a lock.
Show the query behind every answer
Every answer should come with:
- the result, as a table or a short sentence;
- the SQL that produced it, for anyone who wants to check;
- the assumptions in plain words: “Overdue means unpaid with due date before 13 November. Excludes credit notes.”
Showing the assumptions is what catches most errors. A user who reads “includes credit notes” when they didn’t mean that will ask again, before the wrong number reaches a board pack.
When to say “I don’t know”
A good agent refuses more than you expect. It should ask a question back or decline when:
- a term is ambiguous (“sales”: orders, invoices or payments?);
- the data isn’t in the semantic layer;
- the user lacks permission for the data needed;
- its confidence is low, for example when two quite different queries fit the question.
“I can’t answer that from the approved data; the finance team can help” is a good answer. A confident wrong number is the worst one.
Testing on real questions
Collect 100 to 200 real questions from the people who will use the agent, with answers checked by your data team. Compare results, not SQL text: two different queries can both be right. Track:
- execution accuracy: share of questions with the correct result;
- correct refusals: share of unanswerable questions it declined;
- clarification rate: how often it asks back, which should fall as the semantic layer improves;
- blocked queries: queries stopped by the checks, which should be investigated.
Re-run the set on every change to the model, prompts or semantic layer. Every “that was wrong” from a user becomes a new test. Our post How to Test AI Agents covers how to automate this.
Common mistakes
- Giving the agent the whole raw schema instead of a semantic layer.
- A shared database account with access to everything.
- Enforcing permissions in the prompt.
- Hiding the SQL and assumptions from users.
- Judging accuracy on a demo instead of real questions.
How SHVX can help
Data team buried in ad hoc questions? Send 20 questions your team asks of your data. We’ll test them against a text-to-SQL agent and show you what it answers, what it refuses and why.
Learn more about Agent Engineering.
FAQ
Can a text-to-SQL agent change or delete our data?
Not if it connects through a read-only role, with every query checked before it runs.
What is a semantic layer?
A curated description of your data: approved tables, business definitions, metrics and example queries the agent works from.
How accurate is text-to-SQL?
It depends mostly on how good your semantic layer is. Measure it on your own team’s real questions before rollout.
Do we need a data warehouse first?
Not always, but a read replica or warehouse with clean views makes the agent safer and far more accurate.