Text-to-SQL, also called NL2SQL or text2SQL, lets someone ask a database a question in plain English and get a query result back. It demos well. The trouble starts when the question is about a business metric: the SQL runs, the number looks reasonable, and it isn't the number finance reports.
Mactores has built data platforms and AI agents on AWS since 2008, and this guide draws on that delivery work: conversational analytics over 1.82 million calls a month at Hibu, the move of The New York Times' subscription engine off Oracle, and the supply chain ontology we're building for a global aerospace manufacturer. The benchmark figures come from the researchers' own papers and leaderboards, checked on November 16, 2026.
Text-to-SQL is the use of a language model to translate a natural-language question into a SQL query, run it against a database and return the result. Most systems do it in four steps, and each step has its own way of getting a business question wrong.
|
Step |
What the system does |
Where a metric question goes wrong |
|---|---|---|
|
Schema linking |
Picks the tables and columns that look relevant to the question |
"Revenue" matches columns in the CRM, billing and the finance mart, and nothing says which one finance uses |
|
SQL generation |
Writes the joins, filters and aggregations |
A join from accounts to workspaces repeats rows, and the total grows without an error |
|
Execution |
Runs the query on the warehouse under some identity |
The query runs with broader access than the person asking has |
|
Checking and repair |
Catches syntax and runtime errors and retries |
A query that runs cleanly and returns a plausible number passes every check |
On AWS, the managed route is an Amazon Bedrock knowledge base connected to a structured data store. It uses Amazon Redshift as the query engine and lets you add table and column descriptions and curated example queries to steer generation. AWS is candid about the limits: its documentation says the query engine makes "a best effort attempt" to apply that context, calls the context "non-deterministic" and tells builders to verify the generated SQL queries.
None of this makes SQL less important. In Stack Overflow's 2026 Developer Survey, 58% of respondents use SQL, behind only JavaScript at 62%, and an agent that answers from a warehouse still ends in a SQL query. The open question is whether that query means what the person asking meant.
High on public benchmarks and low on private warehouses. As of November 2026, the top system on the BIRD text-to-SQL leaderboard scores 83.4% execution accuracy, against 92.96% for human data engineers. On a benchmark built from private enterprise warehouses, the best published result is about 11%.
That second figure comes from BEAVER, which researchers at MIT and their collaborators built from 9,128 question-and-SQL pairs taken from the real query logs of three private data warehouses. In its May 2026 revision, the best agent framework scored 10.8% running GPT-5.2, and no model took it above 11.4%. The queries explain most of the gap. A BEAVER query averages 5.7 joins against 0.9 on BIRD, the databases average about 870 columns against about 73, and the paper notes that in these warehouses "join relationships are frequently implicit rather than explicitly declared."
|
Benchmark |
What it tests |
Latest result |
What it tells you |
|---|---|---|---|
|
BIRD |
12,751 question-and-SQL pairs over 95 databases |
Top system 83.4% (October 2026); humans 92.96% |
Strong SQL on documented, mid-size schemas |
|
Spider 2.0 |
632 enterprise workflow tasks on databases that often exceed 1,000 columns |
17.1% for the best model at launch; top scores now 96.7% (Snowflake track) and 76.2% (Lite) |
Fast gains from agent frameworks tuned to the benchmark |
|
BIRD-Interact |
600 tasks with ambiguous and follow-up requests that need a conversation |
GPT-5 completed 8.67% and 17% in its two settings |
Resolving ambiguity by asking is still unsolved |
|
BEAVER |
9,128 questions from private warehouse query logs |
10.8% with GPT-5.2; 30.1% with expert hints |
Private schemas and implicit joins are the hard part |
The public scores deserve some caution too. A University of Illinois audit published in January 2026 found annotation errors in 52.8% of the BIRD Mini-Dev examples it reviewed and in 62.8% of the Spider 2.0-Snow examples. When the researchers corrected a sample and re-scored 16 agents, some moved as many as nine places on the leaderboard. In 78 of the 498 BIRD examples, the problem was ambiguity: the question could be read more than one way or didn't say what shape the answer should take. An unqualified ARR question is in the same position.
BEAVER is also a useful check on where the effort should go. Across seven models, the same agent framework scored between 7.7% and 11.4%, a spread of fewer than four points. When the researchers handed it the benchmark's own annotations, such as the right tables, join keys and column mappings, the headline result rose from 10.8% to 30.1%. What the system was told about the warehouse moved the result far more than which model read it.
Because the schema records where the data is, and the meaning of ARR, a seat or churn lives somewhere else. In a software business, each system that touches revenue defines it its own way. The CRM counts bookings, billing counts recognized run-rate and product analytics counts active accounts, with discounts, credits and one-time services landing in different places. An agent that queries whichever table it reaches first inherits that disagreement.
|
Term |
How the systems define it |
The decision an owner has to sign |
|---|---|---|
|
ARR |
Bookings in the CRM, recognized run-rate in billing, active accounts in product analytics |
Which figure finance reports, and whether usage, credits and one-time services count |
|
Seat |
A license in billing, an invited user in the identity system, a weekly active user in analytics |
Which of the three a "seats" question means by default |
|
Customer |
An account in the CRM, a tenant in the platform, a billing entity with subsidiaries |
One identity across all three |
|
Churn |
Canceled accounts in one report, lost revenue in another |
Whether a downgrade counts as churn or contraction |
None of those choices is wrong, and none of them is in the column names. It's the split our context ontology for software work is scoped around: one governed definition per metric, owned by the team that reports it.
The definitions do exist, usually in places a text-to-SQL agent never reads: finance's spreadsheet, a dbt model, a view someone wrote for a board report, a stored procedure. During the legacy modernization with AI that moved The New York Times' PLATO subscription engine off Oracle in 12 weeks, a knowledge graph built from the code surfaced rules the team didn't know existed. Rules like those decide what a metric means, and reading a schema more carefully doesn't recover them.
Gartner made the same argument in a May 2026 release from its Data & Analytics Summit in London: "Traditional schema-based data models alone no longer suffice for agentic AI because they lack business context and data meaning." It predicts that by 2027, organizations that prioritize semantics in AI-ready data "will increase their agentic AI accuracy by up to 80%."
Almost every wrong metric answer traces back to one of three guesses. The agent picks a join path, picks a definition or picks a scope, and the SQL runs either way. Each one can be tested on your own agent before anyone commits to a fix.
|
The guess |
What it looks like |
A test you can run this week |
The rule the agent needs |
|---|---|---|---|
|
Joins |
A total grows when one account has several workspaces or subscriptions, and no error appears |
Ask for ARR, then ARR by workspace, and compare the sum with the total |
The governed join path, with child records rolled up to the account first |
|
Definitions |
The same question worded three ways returns three numbers |
Ask for the metric in the words sales, finance and product each use |
One signed formula per metric, with an owner and a version |
|
Tenants |
A filter the model wrote decides whose rows come back |
Sign in as a test customer and ask about another customer by name |
Scope taken from the caller's identity and enforced outside the model |
Joins are the quietest of the three. A fan-out from one account to several workspaces doesn't throw an error; it makes a sum bigger, and nothing in the result looks wrong. Of the 275 wrong queries the BEAVER researchers examined, 35.2% were schema linking errors, meaning the wrong table, column or join.
Definitions are the loudest, because people notice when two numbers disagree, and they're the one guess engineering can't fix alone. In our delivery method, the definition sessions sit with finance, RevOps and product, because only the owner of a metric can sign what it means. That's also why the working session we open with asks for one question and three answers the agent gave, since the three answers show where the definitions split.
Tenants are where a wrong answer becomes a security incident. A multi-tenant table holds every customer's rows, and an agent that writes its own WHERE clause is one rewording away from another customer's data. AWS draws the same line for its own text-to-SQL feature, noting that table and column inclusions and exclusions "aren't a substitute for guardrails."
For a metric question, RAG finds the text of a definition, a knowledge graph supplies the relationships, a semantic layer compiles the metrics someone has already defined, and an ontology holds all of it with the rules and permissions attached. Each one builds on the one before it.
|
Approach |
What it gives the agent |
What the agent still guesses |
Where it fits |
|---|---|---|---|
|
RAG over documents |
The text of a definition, when retrieval finds it |
How to turn that text into joins and filters |
Questions a document answers, such as plan or contract terms |
|
Knowledge graph RAG |
Entities and the links between them |
Which metric definition and which rules apply |
Questions about how things connect |
|
Semantic layer |
Governed metrics compiled to SQL |
Anything outside the defined metrics, plus identity and tenancy |
Metrics that already have one agreed formula |
|
Context ontology |
Definitions, relationships, rules and permissions together |
Little, and it can decline what isn't covered |
Agents inside and outside the product answering from one model |
Retrieval-augmented generation puts the definition in front of the model, and nothing obliges the model to follow it. We'd also keep numbers out of the document path altogether, because tables lose their meaning once they're flattened into prose.
A knowledge graph helps with the join problem, because it states how accounts, workspaces, subscriptions and invoices connect and doesn't leave the model to infer it from foreign keys. The first job is usually identity. For a global aerospace manufacturer, we're reconciling warehousing, shipping and material identifiers into one supply chain ontology, and that work starts with which records in which systems describe the same thing. A graph still doesn't say which formula is ARR.
A semantic layer is the right home for a formula. dbt's MetricFlow, Snowflake semantic views and Databricks metric views all compile a governed metric into SQL, and Apache Ossie, the open specification formerly called Open Semantic Interchange, entered the Apache Incubator in July 2026 to describe semantic layers and ontologies in one format. What a semantic layer doesn't hold is who a customer is across the CRM, billing and the platform, or which tenant is asking. A context layer includes those metric definitions and adds the entities, rules and authority an agent needs.
An ontology in AI ties those together: the classes the business runs on, such as account, workspace, subscription and seat, the relationships between them, the governed metrics defined on top and the rules about who may see what. If you already run dbt models and a metrics layer, keep them. The ontology reads those definitions and doesn't duplicate them.
It resolves the question to a governed metric, a time frame, dimensions and a tenant before any SQL exists, then runs the query stored with that metric. Generated SQL is the fallback for questions the stored query can't cover, and the agent is allowed to decline.
The open-source AWS Context Ontology Accelerator, released on July 31, 2026, is built this way, and it's what we build context ontologies on today. It answers in three tiers, tried in order. The first serves a governed metric from pre-compiled SQL with no model call. The second generates SQL over a virtual knowledge graph from the governed formula, and a firewall that allows only SELECT statements checks the result against the caller's table allowlist and column denylist. The third searches documents.
The first tier deliberately declines a question that carries a filter or a time window it can't apply, and hands the governed formula down to the second. A narrower question never gets the unfiltered total back as if it were the answer.
|
The question |
Where it resolves |
What runs |
|---|---|---|
|
"What was ARR at the end of last quarter?" |
Governed metric |
The stored query for finance's signed definition, with no model call |
|
"ARR by region for enterprise customers in EMEA" |
Constrained SQL |
Generated filters and grouping around the governed formula, checked by the SQL firewall |
|
"What does the contract say about extra seats?" |
Document retrieval |
A search over contract and plan documents, under the same access policies |
|
"ARR including usage" |
Declined |
Nothing, until an owner defines it. We'd have the agent show ARR and usage as two figures and name who could define a combined metric |
Each answer should carry its own evidence: the metric and version, the owner, the as-of date, the scope, the path it took and the sources it read. Recording that at the moment of the answer is what a context graph is for, and it's what lets a second person get the same number and see why.
Before any agent answers from the ontology, we hold it to a release gate. A first release usually covers 10 to 20 business questions, each with an owner and an agreed answer, and takes 8 to 12 weeks. Four of the checks matter most for metric questions:
Narrow what the model is allowed to decide. The gains that hold up in production come from fewer free choices: a fixed list of questions, governed definitions, tools in place of raw queries and access taken from identity.
The clearest production evidence we have is Hibu, a digital marketing provider whose roughly 1.82 million inbound lead calls a month had no BI or SQL path, so every question about call content waited 2 to 3 days in an analyst queue. We built it a conversational analytics platform on AWS. Business users now ask in plain language and get an answer in under 5 minutes, and 17 of the first 20 users were active every week by week six, asking about 23 questions each a week.
It isn't a text-to-SQL system, since the answers come from call transcripts through a purpose-built MCP connector. The controls that took it through a three-week security review and on to company-wide approval within four months are the ones a text-to-SQL agent needs.
|
What we did at Hibu |
What we'd carry over to a text-to-SQL agent |
|---|---|
|
Gave the agent a purpose-built connector with no write path and no raw queries |
Expose governed metrics and parameterized queries as tools, and keep free-form SQL as a checked fallback |
|
Mapped Microsoft Entra ID app roles to read-only roles scoped by call type, so access ends the day someone leaves |
Take the tenant and role from the signed-in identity, never from the prompt |
|
Launched with prompt patterns for the 12 questions users asked most, then gave each team its own templates when broad questions like "what do customers think?" produced vague answers |
Start from 10 to 20 real questions, each with an owner and an agreed answer |
|
Kept improving schema descriptions and data curation from user feedback and domain knowledge |
Treat descriptions and definitions as maintained content with named owners |
For a team starting from a text-to-SQL pilot that finance doesn't trust, this is the order we'd work in.
|
Step |
What we'd do |
|---|---|
|
1 |
Write down the 10 to 20 questions that matter, each with an owner and the answer that owner expects |
|
2 |
Have each metric's owner sign one formula, and store the query with it |
|
3 |
Model how customers, tenants, subscriptions and usage join, starting with one customer identity across the CRM, billing and the platform |
|
4 |
Route each question to the governed metric first, and let generated SQL handle only what's left, behind a SELECT-only firewall |
|
5 |
Take the tenant and role from the caller's identity, and run cross-tenant probes before every release |
|
6 |
Run the known-answer questions on every change, and keep the definition, version and sources with each answer |
Customers' agents should call your governed metrics as tools, and they shouldn't write SQL against your tables. A tool takes a few typed parameters, such as a month and a workspace, and everything else, the tenant included, comes from the caller's verified identity.
The MCP authorization specification suits a multi-tenant product well. It makes a protected MCP server an OAuth 2.1 resource server that must "validate that access tokens were issued specifically for them" and "MUST NOT accept or transit any other tokens," so the tenant can come from the verified token and never from anything the agent writes.
We've shipped both halves of that pattern. Hibu's connector already works this way for its own staff, and the multi-agent customer service we built for Safaricom lets each agent change only what its guardrails permit. Before a customer's agent gets access to a product's context, we run cross-tenant probes and prompt-injection tests against every exposed tool until each refusal is confirmed, and anything that would change a customer's account, billing or data waits for a person.
Our context ontology services start from one metric your agents get wrong, which for a software company is usually ARR, seats or churn. Bring the question and three answers your agent gave to a working session, and within a week you get a diagnosis, a draft first-release scope and a target production date.