Text-to-SQL

What is text-to-SQL?

With text-to-SQL you put a language model between a person and a database. You type "how much did we invoice in Belgium last quarter", the model writes the query for you, the database runs it, and you get a number back. You do not have to open a query editor and write code yourself.

The idea is a lot older than the current wave of language models. Researchers have been working on querying databases in ordinary language for decades, and Power BI shipped Q&A years before most people had heard of LLMs. What did change is the success rate of the technology. A model that has read an enormous amount of SQL now handles joins, date logic and aggregations that the older systems could not manage.

The difficulty has stayed in exactly the same place. Writing SQL was never the real bottleneck. The real work is in what you have to know about your own data. You have to know which table holds the revenue figure your management team actually uses. You have to know which lines the finance team leaves out. And you have to know which of the four date columns you mean when you say "last quarter". A model that only gets to see table names and column names has to guess at all three of those.

How a text-to-SQL system builds a query

If you look under the bonnet, almost every implementation does the same four things.

1. Collect the schema
   The system first gathers the tables, columns, data types and relationships that the question might need. On a large warehouse you cannot send all of that along, so the system first searches for the tables that look most relevant. If that choice is wrong, then everything that follows is wrong too.

2. Assemble the prompt
   The schema, the question, the conversation history and a set of examples of a question with its matching query all go to the model together. Those examples do more work than most teams expect. They are where you record that "turnover" means the net line at your company and not the gross line.

3. Generate the query
   The model writes a query. Some systems let the model produce several candidates and then pick one of them.

4. Validate and run
   The system checks the query for form, tests it against the schema, and usually forces it to read only. If it errors, the system sends that error message back to the model and the model tries again. Because of that repair loop, a system that looks shaky in a demo can still be genuinely usable in practice.

Notice what is missing from that list. There is no step that checks whether the query actually answers the question. You can verify the syntax of a query, but you cannot verify its meaning.

Text-to-SQL compared to a semantic layer

In both cases someone asks a question in plain language. The difference is in what happens between that question and your tables.

Text-to-SQL is an access mechanism. It translates a sentence into a query against whatever tables happen to be there, and it works out the meaning of a column from its name and its contents.

A semantic layer is a layer of meaning. Someone has written down there, once, what revenue is, which dimensions you are allowed to split it by, and how the tables connect. The model no longer writes SQL. It picks a metric and a few dimensions, and a query engine builds the query according to fixed rules.

The difference you feel in practice is what a mistake looks like. When text-to-SQL gets something wrong, you get a believable number with no warning attached. When a semantic layer gets something wrong, you get an error message saying that metric or that dimension does not exist. A wrong number that looks right costs you far more than a question you cannot get answered.

Independent testing keeps pointing the same way. Text-to-SQL has become a lot more accurate over the past few years, and it still trails a well modelled semantic layer. That gap shows up mostly on questions that turn on a business definition rather than on SQL syntax. What is interesting is that better modelling lifts both approaches at the same time. Cleaning up your model is therefore never wasted work.

Where you meet text-to-SQL in the Microsoft stack

Microsoft ships this in several places, each time under a different name.

Fabric data agent
It first picks a data source and then calls natural language to SQL for a lakehouse or a warehouse, natural language to DAX for a Power BI semantic model, and natural language to KQL for an eventhouse. Everything runs read only and under the permissions of the person asking, so your row level security keeps working.

Copilot in Power BI
It answers questions against a semantic model instead of against raw tables. That sits closer to a semantic layer than to real text-to-SQL, and that is why your descriptions, synonyms and hidden fields decide how well it works.

Copilot in the warehouse and in notebooks
This one is built for people who can read the generated SQL and correct it themselves. That is a safer setting for text-to-SQL, because a professional is looking at the result.

What to watch out for with text-to-SQL

The mistake makes no noise. A wrong join or a forgotten filter gives you a number, not an error message. So make sure people can call up the generated query, and that at least one person on your team can read it.

The real ambiguity is in your own business terms. Think of "active customer", "margin" or "open order". Each of those has a definition sitting in a spreadsheet somewhere or in someone's head. Write them down as an example query or as a measure before you blame the model.

Permissions do not sort themselves out. The system has to query the database as the user and not as a service account with read access to everything. Check that right at the start, because this is the mistake that gives you a data breach rather than a wrong figure.

More tables make it worse instead of better. If you point an agent at an entire warehouse, accuracy goes down, because the search step gets more chances to grab the wrong table. Start with a small set of well named tables and expand from there once that works.

The same question can give you a different answer twice. Model output is not fixed. For a figure that ends up in a board pack or in a report to a regulator, you are better off routing the question through a metric you have defined yourself.