Key takeaways
- With text-to-SQL, the AI writes the query and the database does the calculating; the model does not need to see any rows of data to do this.
- Clear table and column names, a data dictionary and agreed definitions largely determine the quality of the answers.
- Only run AI-generated SQL read-only, with a timeout, a row limit and an upfront check.
- A query can be technically correct and still answer a different question, so always show the query next to the result.
How text-to-SQL works
Text-to-SQL, also called AI SQL or natural language to SQL, makes a database accessible to anyone who can phrase a question. For this task, the model only needs the structure of the database, not its contents. That is an important difference from pasting an export of your data into a chatbot: with text-to-SQL, the rows of data can stay in the database.
A typical chain looks like this:
- A user asks a question, for example "What was revenue per region in the third quarter?".
- The system gathers context: tables, columns, data types and relationships, plus descriptions from the data dictionary.
- The language model writes an SQL query in the right dialect, such as PostgreSQL or T-SQL.
- The system checks the query: is it valid, read-only and limited to permitted tables?
- The database runs the query within fixed limits.
- The user sees the result together with the query and can adjust or save it.
Why metadata and a data dictionary make the difference
A language model knows nothing about your organisation. It only sees what you give it. If a column is called amt2 and nothing says what it contains, the model has to guess. With tables that come straight from an ERP system, full of abbreviations and technical codes, this often goes wrong.
A data dictionary describes in plain language, for each table and column, what it contains, in which unit, which values are valid and how tables relate to each other. Business definitions are at least as important: is revenue invoiced or ordered, with or without credit notes? What counts as an active customer?
In practice, a tidy layer in your data warehouse, with understandable names and views per subject, pays off more than endlessly tweaking the prompt. The difference between weak and strong metadata:
| Element | Weak | Strong |
|---|---|---|
| Column name | amt2 | amount_excl_vat |
| Description | Empty | Invoice amount excluding VAT, in euros |
| Definition | "Revenue" means something different to everyone | Revenue is the invoiced amount minus credit notes |
| Relationships | Not documented | orders.customer_id refers to customers.id |
| Values | status = 1, 2 or 3 | status: 1 = open, 2 = shipped, 3 = cancelled |
Safeguards for AI on a database
Anyone who lets AI loose on a database must assume that a generated query will sometimes be wrong, heavy or unwanted. Good text-to-SQL solutions therefore add several layers of protection. In ENABLE's SQL Explorer, these are fixed limits: read-only transactions, a 45-second timeout, paginated results and a limit on the number of queries per minute. The AI receives only metadata and the question, ENABLE checks the query with a read-only query plan, and the generated query only runs when you run it yourself.
Whichever tool you use, these are the safeguards to look for:
- Read-only: a database user with read permissions only, and a read-only transaction on top of that.
- Timeouts: a query that runs too long, for example because of a wrong join, is stopped.
- Row limits: a result is capped, so that nobody accidentally retrieves an entire table.
- Concurrency limits: a limited number of simultaneous queries per organisation or user.
- Scoped access: only the schemas and views that are needed, preferably on a data warehouse or copy rather than on the production system.
- Upfront check: the query is validated, for example with a query plan, and only run after confirmation.
- Minimal data to the model: only metadata and the question, no rows of data or passwords.
Typical mistakes in AI-generated SQL
The most dangerous mistakes produce no error message. The query runs and the result looks plausible, but it answers a slightly different question. That is why it is important that the query is always visible next to the result. Common mistakes and how to prevent them:
| Mistake | What happens | How to prevent it |
|---|---|---|
| Wrong join | Orders are counted twice after a join with order lines; totals come out too high | Document the level of detail of each table and offer views at the right level |
| Wrong definition | Revenue including VAT, or without deducting credit notes | Describe key figures in the data dictionary |
| Date errors | Calendar year instead of financial year, or a different interpretation of "last month" | Use a date table and state the financial year |
| Invented columns | The model uses a column or table that does not exist | Validate against the schema and let the model fix the error |
| Forgotten filters | Cancelled orders or test customers are included | Document status values and default filters |
| Wrong dialect | Functions from another SQL dialect, such as TOP instead of LIMIT | Pass the database type as context |
| Vague question | "Best customer" by revenue, when margin was meant | Show assumptions and let the user refine the question |
When text-to-SQL works well, and when it does not
Text-to-SQL works best with structured data in a well-designed model, and for questions that are not covered by an existing dashboard. Controllers, analysts and data teams use it to answer an ad hoc question faster, have an existing query explained or resolve an error message. For people who know SQL, AI SQL is mainly an accelerator; for people who do not, it is a way in.
It works less well for questions about documents and policies (RAG is better suited to those), for messy source tables without descriptions and for figures on which you base important decisions without checking. Recurring KPIs belong in a fixed dashboard with agreed definitions, not in a new question to the AI every month.
Introducing text-to-SQL: a practical approach
A good introduction does not start with the AI model, but with your data. This order works in practice:
- Start with a data warehouse or a set of views with understandable names, not with the raw tables of your source system.
- Write a data dictionary for the most-used tables, including definitions of key figures.
- Set up a read-only database user, with timeouts, row limits and restricted permissions.
- Start with a small group of users who know the data and can spot mistakes.
- Collect questions that went wrong and use them to improve your metadata, not just the prompt.
- Turn questions that keep coming back into a saved query or a dashboard.