Text-to-SQL: Letting Business Teams Ask Data Questions in Plain Language, Reliably

Plants.work · · upskilling
Text-to-SQL: Letting Business Teams Ask Data Questions in Plain Language, Reliably

How text-to-SQL works, why it needs a semantic layer and verified answers, and how to set it up so non-technical staff can query data safely.

The revenue manager of a hotel group wants to know "How did occupancy at our Phuket hotels last month compare with the previous year?" but has to send a request to the data team and wait two days. Many organisations dream of letting people like this type a question and get an answer immediately. The technology that makes it possible is text-to-SQL, but if the system is poorly designed, it will answer wrongly with confidence, which is more dangerous than not answering.

What is text-to-SQL?

Text-to-SQL means AI converts a plain-language question into SQL, runs it against a database, and returns numbers or charts. Unlike letting AI "guess the answer", the result comes from real data. But accuracy depends on whether the AI translates the question into a query that matches its meaning.

The mechanism: what AI needs to know to translate correctly

1. A semantic layer

A semantic layer defines the business meaning of data. For example, "occupancy rate" is rooms sold divided by rooms available for sale, not all rooms. Without this layer, AI has to guess the formula from column names, and may guess differently each time.

2. Curated datasets

Do not expose every table in the database to AI. Create views or datasets that are clean and clearly named. The fewer the options, the more likely AI picks the right one.

3. Instructions and verified answers

Many BI tools offer this mechanism. Microsoft Learn documentation says Copilot in Power BI apps supports "verified answers" prepared by authors. Google's documentation says Conversational Analytics in Data Studio lets you create data agents that add context and instructions. Both share the same idea: put human knowledge in for the AI to use.

Text-to-SQL does not replace dashboards

Dashboards suit questions asked every week that the whole organisation must see the same way. Text-to-SQL suits one-off questions that are not worth a new report, such as "How many nights on average did guests who booked directly on our website stay over Songkran?" An approach that works well:

Example: a hotel group lets sales teams query data themselves

The data team builds a booking dataset with only 15 columns, writes KPI definitions such as occupancy, average daily rate and revenue per available room, adds 30 sample questions with verified answers, and sets permissions so each hotel manager sees only their own hotel. When users ask a new question, the system shows the answer along with an explanation of the filters used, so users can see whether the AI understood the question.

Good text-to-SQL does not start with AI. It starts with definitions everyone has agreed on.

Watch out for

How to get started

  1. Collect the 20 to 30 questions the data team is asked most often.
  2. Build a dataset that answers them, with written KPI definitions.
  3. Record verified answers to these questions and use them as a test set.
  4. Pilot with a small group and use the questions AI gets wrong to refine definitions and instructions.
  5. Expand to more users group by group and review the test set monthly.

If you are the analyst who maintains this system, read Using AI to Write SQL for Analysts, or learn natural-language querying with the course Power BI Q&A: ask anything with AI on Plants.work Learn.