Asking your database in plain language: where text-to-SQL works.

The demo is always convincing. Someone types “top ten customers by revenue” into a chat box, a query appears, a table follows, and the room imagines never waiting for a report again. Then the same tool is pointed at the real ERP, and the first answer is off by an amount nobody can explain.

Text-to-SQL works, but on narrower ground than the demo suggests: a small, documented set of curated views, a glossary of business definitions, read-only access and an evaluation set of real questions. Pointed at raw ERP tables, it produces confident, wrong numbers. And every answer should show its query and its definition, so the person reading it can check.

What is text-to-SQL, and why is it tempting?

Text-to-SQL is a pattern in which a language model translates a question in plain language into a database query (SQL, the language most business databases understand), runs it and presents the result. The appeal is real: report requests queue up with a small analytics team, managers want to ask follow-up questions without filing a ticket, and a generated query takes seconds.

It is a different job from answering from documents. The numbers come from systems of record, and the model’s job is to write the query, not to know the answer. The note on management reports with AI makes the same point for reporting: numbers from systems, words from the model.

Why does it fail on raw ERP and warehouse tables?

Because a query can be valid and its number still wrong. The usual causes:

• Cryptic schemas. Tables named with abbreviations, and status columns whose codes are documented nowhere in the database. The model guesses from names.
• Definitions outside the database. “Net sales” might mean invoiced amounts minus returns, discounts and cancellations, excluding sales between group companies. Nothing in the tables says so.
• Joins that multiply rows. Joining orders to order lines to shipments duplicates rows, and a sum double-counts. The query runs; the total is plausible and wrong.
• Records that shouldn’t count. Canceled, reversed and test records sit in the same tables as real ones.
• Dates and currencies. Posting date or document date, calendar or fiscal year, and amounts in several currencies.
• Permissions in the application. Row-level rules enforced by the ERP’s screens don’t exist in the raw tables.

A SQL error is loud. A wrong definition is silent.

What makes it work: views, definitions and examples?

Narrow the ground the model stands on:

• Curated views. A dozen views built by the data team rather than hundreds of raw tables, with plain names such as “net sales by day and region”. Joins are done, canceled and test records are filtered out, and currency is explicit.
• A glossary of business definitions, agreed with finance and sales: what counts as net sales, an active customer or a region. The model reads it, and the answer quotes it.
• Column descriptions and allowed values, so a status code means “shipped” rather than a number.
• Example questions with approved queries, which show the model the house patterns.
• A semantic layer, where you have one: metrics and dimensions defined once, so the model picks “net sales by region for the fiscal year” instead of writing free SQL. It is the safer design when it’s available.

Start with the views behind your most requested reports. Each new view needs an owner and written definitions before the model is allowed to query it.

How do you keep it safe?

With controls the model can’t talk its way around:

• A read-only account that sees only the curated views. No writes and no schema changes, enforced by the database, not by the prompt.
• Row limits and query timeouts, plus cost limits on warehouse queries.
• Validation before execution: the generated SQL is parsed, checked against an allow-list of views and rejected if it holds more than one statement.
• Row-level security per user, so a regional manager’s question returns only their region.
• Minimal personal data: views leave out the columns questions don’t need.
• Logs of the question, the query, the result size and the user.

How should answers be shown to users?

As a number with its receipt: the result, the definition used, the filters applied, the query, and a link that opens the same data in the reporting tool.

Take an illustrative example: a sales director asks, “what were net sales in the north region?” The glossary defines net sales as invoiced amounts minus returns, discounts and cancellations, excluding group-internal sales, and the region table maps “north” to a set of sales territories. The period is missing, so the system asks one question back: fiscal year to date, or a specific month? The director picks fiscal year to date. The system queries the curated net-sales view, filtered to the north region and the current fiscal year, and answers with the total, a line quoting the definition, the filters, the query behind a “show query” link, and a button that opens the view in the dashboard. If the director disputes the number, the conversation is now about the definition, which is where it belongs.

That clarifying question is a feature. Ambiguous questions get a question back, not a guess.

How do you evaluate it?

On an evaluation set of real business questions from report requests and analyst tickets, each with an approved query and an approved result, owned jointly by the data team and the business.

• Score results, not SQL text. Two different queries can both be right, so run both and compare the numbers within an agreed rounding tolerance.
• Include ambiguous questions, where the right behavior is a clarifying question, and questions the views can’t answer, where the right behavior is to say so.
• Tag by difficulty: one view, an aggregation, a comparison over time, a ranking.
• Re-run on every change to views, glossary, prompts or model.
• Check live answers too. An analyst re-runs a weekly sample of production questions by hand.
• Keep the unanswered questions. They are the backlog for new views and definitions.

Build the views first

Take the twenty questions your analysts answer most often, write down the approved query and definition for each, and build the views they need. A question those views can’t answer isn’t ready for text-to-SQL. The views, glossary and evaluation set are data and AI foundations work; for questions answered inside the ERP itself, see ERP and enterprise workflows.