NL2SQL

What is NL2SQL?

NL2SQL, short for natural language to SQL, turns a plain-language question into a SQL query that runs against a known database schema. You type "What was revenue by product group last month in Belgium?" and the system writes the SELECT, the joins, and the filters, runs the query, and hands back the answer.

It is the engine behind most "ask your data a question" features, including the natural-language experiences in Power BI Copilot. A large language model, or LLM, reads your question together with a description of the tables and columns, and produces a query. The appeal is obvious: fewer tickets for the data team, and more people who can ask their own questions.

The catch is that a question in English is vague and a SQL query is exact.

The hard part: joins, meaning, and unspoken filters

To turn one sentence into a correct query, the model needs four things, and only the first is easy.

• The schema. Which tables and columns exist. Matching words in the question to the right tables is called schema linking, and it is where many systems already trip up when two tables both have a column named country.

• The join paths. How the tables connect. Revenue lives in one table, customers in another, products in a third, and there is usually more than one way to join them, only one of which is right for this question.

• The business meaning of columns. "Revenue" might mean net invoiced amount without VAT, stored in a column that is not even called revenue. "Last month" has to land on the order date, not the record-created date.

• The filters nobody writes down. Every team quietly excludes test orders, cancelled lines, or internal accounts. A human analyst applies these without thinking. The model has no way to know they exist unless something tells it.

These are not hypothetical. Microsoft's own Copilot guidance shows a model answering "How many units were sold in Australia?" by filtering the country column on the customer table instead of the sales-region table, and answering a question about a given year by filtering a birthday column rather than the marked date table. Same question, confidently wrong number.

Worked example: revenue last month

Ask for "revenue last month" and a naive generator will happily sum an amount column for June. The query your finance team actually trusts looks more like this:

SELECT SUM(amount) AS revenue
FROM orders
WHERE order_date >= '2026-06-01'
  AND order_date < '2026-07-01'
  AND status = 'completed'
  AND is_test = false;

The naive version misses three things: it groups on the wrong date column, it counts cancelled and pending orders, and it keeps the test orders your team uses to check the checkout. None of that was in the question, yet every one of them moves the number.

Why a semantic model is the practical answer

The most reliable fix is to stop pointing the model at raw tables. Point it at a semantic model or a metrics layer instead, where revenue, active customer, and margin are already defined once as named measures, with the joins, the date logic, and the standard filters built in.

Now the model has a smaller, cleaner target. Instead of guessing which of five amount columns to sum, it asks for the measure called revenue. This is how Copilot works in Power BI: it answers against the measures and relationships in a well-built semantic model rather than inventing SQL against the warehouse. A tidy star schema, human-readable field names, and synonyms all improve the answers it returns.

Run the query under the user's permissions

A generated query is still a query, so it has to obey the same access rules as everything else. The person asking in plain language must not see more than they would see in a normal report.

That means the query runs under the asking user's identity, and row-level security is enforced by the database or the semantic model engine, never by the prompt. In Power BI, row-level security filters are applied automatically to every query, and a user with read access sees only the rows their role allows. You never want the LLM to be the thing deciding who sees what. An instruction in the prompt like "only show this user's own accounts" is a suggestion to a text generator, not a security control, and anyone who can rephrase the question can try to talk their way around it, a form of prompt injection.

What to watch out for with NL2SQL

Expect real accuracy below the demo. On public benchmarks built from real databases, such as BIRD, the best systems still trail human experts, and a private schema with local names and undocumented rules is harder than any benchmark. Treat a slick demo as the best case and plan for worse.

Keep it read-only and scoped. Start with one well-documented domain rather than every table in the company, and restrict generated queries to reading. Writing back to the database through plain language needs far stricter controls.

Show the SQL and the definitions. A good system displays the query it ran and the measures it used, so someone can check that the question was understood before anyone acts on the number.

Constrain the output without trusting it blindly. Grounding the model in your schema and forcing structured output, so it returns a parseable query rather than prose, both cut down mistakes. Neither removes the need to curate definitions and test with your own column names.