20. Asking the data in natural language

From SQL to the question in words

Programming languages pointed out that SQL is the standard for querying databases and that any job posting in the field will probably list it as a requirement. That statement remains true, but today it coexists with another possibility: asking the question in natural language ("how much did we sell by region last quarter?") and letting a tool translate it into a query, run it and return the result, sometimes accompanied by a chart.

This task is known as text-to-SQL and, when it is built into a conversation, as conversational analytics. It promises something very appealing: that anyone in the organization can query the data without depending on someone who knows how to program. That promise is partly real. To understand which part, it is worth seeing how it works and where it tends to fail.

How it works

In general terms, the tool receives three things: the question, a description of the available data (which tables exist, which columns they have, how they relate to each other) and, ideally, the organization's own definitions and examples. With that, it generates a query, runs it against the database and presents the answer.

Each of those inputs shapes the result. If the description of the tables is poor (columns with cryptic names, no documentation), the tool will have to guess what each one means. If the question is ambiguous, it will choose an interpretation, not necessarily the one that was intended. And if it does not know the organization's definitions, it will use the usual ones, which may not match.

The underlying problem: ambiguity

Consider the question "how many active customers do we have?" Answering it requires deciding, at a minimum:

  • what a customer is (a person, an account, a company with several branches?);
  • what active means (bought in the last 30 days, in the last year, has a current subscription?);
  • which point in time it refers to (today, the close of last month?).

Each combination produces a different number, and all of them are technically correct. This is not a limitation of AI tools: it is exactly the problem raised in Data, units and variables of analysis. The definition of the unit of analysis and of the variables comes before any calculation, and what changes now is that this definition, previously implicit in the head of whoever wrote the query, must be made explicit for a tool to use it correctly.

Put another way: querying in natural language does not remove the need to define; it moves it. Whoever used to write SQL now writes definitions.

Definitions as infrastructure

For this reason, many organizations build what is called a semantic layer: a place where the metrics (sales, margin, active customers), the dimensions by which they can be broken down (region, product, month) and the correct way to compute them from the tables are defined once, in a shared way. Query tools, whether traditional dashboards or conversational assistants, take those definitions instead of reinventing them with each question.

Beyond the specific technology used, the underlying idea long predates AI: a well-maintained data dictionary. What is new is that its absence shows much faster, because a tool without definitions answers with the same confidence when it is right as when it invents an interpretation.

Verifying the query, not only the answer

When the answer is a number, it is tempting to take it at face value. Some simple practices reduce the risk:

  • Reading the generated query. Writing SQL fluently is not necessary to check which tables were used, which filters were applied and how the data were grouped. Many errors (a misplaced date filter, a join that duplicates records) can be spotted at a glance.
  • Checking against known totals. If total sales for the year are known, the sum by region should match. Frequency tables are, once again, a control tool: the absolute frequencies must add up to the total number of units.
  • Rephrasing the question. Asking the same thing in another way and comparing the results is a quick way to detect different interpretations.
  • Distrusting what is too clean. A result with no missing values or odd cases, in data known to have them, suggests that something was left out.

When it pays off and when it does not

Natural language querying works especially well for exploratory and recurring questions about well-documented data: getting a first overview, answering specific questions, quickly putting together a table for a meeting. It works less well when the question is new, depends on subtle definitions or the result will support an important decision. In those cases, the tool is still useful for writing a first draft of the query, but review by someone who understands the data is not optional.

Recap

Asking the data questions in words is a highly visible transformation of recent years, and it greatly broadens who can query information. But it does not solve the central problem of analysis, which is defining precisely what is to be measured. On the contrary, it brings that problem to the foreground: the easier it is to ask, the more important it is to have defined well what is being talked about.

Further reading