A text-to-SQL agent lets people ask business questions in plain language and get answers from your databases. To use one safely, ground it in agreed metric definitions, give it read-only access scoped to the user asking, check every query before it runs and show people the query behind each answer so they can verify it.
Why text-to-SQL looks easy and is not
Modern models write fluent SQL against a clean, well-documented schema. Enterprise data is rarely like that. Tables carry cryptic names, the same idea lives in several places, and the meaning of "revenue" or "active customer" depends on rules that sit in someone's head or a finance spreadsheet.
The dangerous failure is not a query that errors. It is a query that runs, returns a confident number and is wrong because it joined the wrong tables, missed a filter for test accounts or counted refunds as sales. A person reading a chart has no way to tell. Public benchmarks are run on tidier schemas than most businesses have, so a good benchmark score says little about how an agent will do on your warehouse.
Ground the agent in agreed definitions
The most reliable pattern is to stop the model inventing business logic. A semantic layer defines metrics, dimensions and approved joins once, and the agent asks for "net revenue by region last quarter" instead of writing the joins itself. The layer then generates the SQL deterministically.
The trade-off is coverage. A semantic layer only answers questions it has been modelled for. dbt Labs describes this split: a governed layer for questions where accuracy matters, free-form text-to-SQL for ad hoc exploration. A sensible design uses the layer first and falls back to generated SQL only when the question falls outside it, and says so in the answer.
If you have no semantic layer yet, start smaller. Give the agent a curated set of views with plain names, column descriptions, sample values and a short glossary. This is the same work we describe in getting data ready for an AI agent.
Limit what the agent can read
Connect the agent through a read-only database role. Never reuse an application or admin account. Where possible, point it at a reporting replica or warehouse so a heavy query cannot slow down a production system.
Access should follow the person asking, not the agent. If a regional manager cannot see another region's figures in the dashboard, the agent should not be able to show them either. Row-level security in the database, or passing the user's identity through to the query layer, keeps those rules in one place. Our guides to stopping agents reaching restricted data and authenticating an agent on behalf of a user cover the patterns.
Check every query before it runs
Treat generated SQL as untrusted input. Before execution:
- Parse the query and reject anything that is not a single read statement.
- Allow only approved schemas, tables and views.
- Add row limits and a query timeout, and cap the cost on warehouses that charge per query.
- Block or mask sensitive columns such as personal data unless the user is entitled to them.
These checks belong in code between the agent and the database, not in the prompt. A prompt asking the model to "only run SELECT statements" is a request, not a control. The same principle applies to any agent output, as we set out in validating AI agent output.
Show the working and test with real questions
Every answer should come with the query that produced it, the definitions used and the time the data was last refreshed. Analysts can then check the logic, and business users learn to notice when a filter looks wrong. Log each question, query, result and user, so you can audit what the agent did later.
Before launch, collect real questions from the people who will use the agent, with answers an analyst has confirmed. Run the agent against that set after every change to the model, prompt or schema. Include questions it should refuse or ask to clarify, such as ones that are ambiguous about date ranges. We cover how to build these tests in what AI agent evals catch.
Where CodeDTX fits
We build text-to-SQL and analytics agents into existing data estates: modelling the definitions, wiring access to your identity system, adding query checks and setting up evaluation. If you are weighing whether your data is ready for this, talk to us.
Frequently asked questions
Can a text-to-SQL agent change or delete data?
It should not be able to. Connect it with a read-only database role, and parse every generated query to reject anything that is not a single read statement before it reaches the database. Relying on the prompt to forbid writes is not enough, because models can be talked out of instructions. If you later need an agent that writes data, treat that as a separate tool with its own approval step.
Do we need a semantic layer before using a text-to-SQL agent?
Not always, but it helps a great deal for questions where the number must be right, such as revenue or headcount. A semantic layer fixes metric definitions and joins so the agent stops guessing them. Without one, start with a small set of curated views with clear names, column descriptions and a glossary, and widen the agent's reach only as testing shows it answers correctly.
How do we stop the agent showing data a user should not see?
Make access follow the person asking. Use row-level and column-level security in the database, or pass the user's identity through to the query layer, so the agent can only return what that user could already see in their reports. Avoid a single shared service account with broad access, because then every user effectively gets that account's permissions through the agent.
How do we know the answers are correct?
Build a test set of real business questions with answers confirmed by an analyst, and run the agent against it after every change to the model, prompt or schema. In production, show the generated query, the definitions used and the data refresh time with each answer, so people can check the logic. Log every question and query so mistakes can be traced and fixed.
