Text-to-SQL is the task of turning a question written in plain language—“Which product lines lost margin last quarter in Mexico?”—into a SQL query that runs against a real database and returns the answer. Modern text-to-SQL systems use large language models (LLMs) to read the question, look at the database schema and business context, generate SQL, execute it, and often explain the result back in natural language.

Why it matters now: almost every data platform is shipping a “talk to your data” feature. Snowflake Cortex Analyst, Databricks AI/BI Genie, and Google’s Gemini in BigQuery all promise that business users can ask questions without writing SQL (Snowflake Cortex Analyst docs; Databricks AI/BI Genie docs). That promise only holds when the data underneath is modeled, documented, and governed—which is why text-to-SQL has become a data team problem as much as an AI problem.

How text-to-SQL works

A production text-to-SQL pipeline is rarely a single prompt. Most systems follow a sequence like this:

• Schema linking: find which tables and columns are relevant to the question. With hundreds of tables, the system cannot paste the whole schema into the prompt, so it retrieves the most likely candidates using table descriptions, column names, and sample values.

• Context assembly: add business definitions (“active customer means at least one order in the last 90 days”), join paths, and example question–SQL pairs.

• SQL generation: the LLM writes a query in the correct dialect (Snowflake, BigQuery, PostgreSQL, and so on).

• Validation and execution: the query is parsed, checked against permissions, sometimes dry-run, then executed. If it fails, the error goes back to the model for a repair attempt.

• Answer and explanation: the result is returned as a table or chart, ideally with the SQL visible so a human can audit it.

Google Cloud’s engineering team describes many of these techniques—schema retrieval, few-shot examples, self-correction, and disambiguation—as the difference between a demo and a reliable feature (Google Cloud: Techniques for improving text-to-SQL).

Why demos look easy and production is hard

Ask an LLM to write SQL for a toy schema with products and orders and it will usually get it right. Enterprise warehouses are different: cryptic column names, dozens of similar tables, multiple definitions of “revenue,” and dialect-specific functions.

Benchmarks show the gap clearly. The research team behind Spider 2.0, a benchmark built from real enterprise workflows, reported that an agent framework based on a frontier reasoning model solved only 21.3% of its tasks, compared with 91.2% on the original Spider benchmark and 73.0% on BIRD. The lesson is not that text-to-SQL fails; it is that accuracy depends heavily on how much real-world messiness the system has to absorb.

The most common failure modes

• Wrong table or column: the model picks orders_legacy instead of fct_orders.

• Wrong business logic: the SQL runs, but uses gross revenue when finance reports net revenue.

• Join explosions: a many-to-many join silently duplicates rows and inflates totals.

• Ambiguous questions: “last quarter” might mean calendar or fiscal quarter.

• Dialect errors: date functions and window syntax differ across engines.

The dangerous case is the second one: a query that executes successfully and returns a plausible number that is simply wrong.

What makes text-to-SQL reliable

1. A semantic layer or semantic model

Snowflake’s documentation is explicit that generic solutions struggle when given only a schema, because schemas lack business definitions and metric logic. Cortex Analyst relies on a semantic model—a YAML description of tables, metrics, and relationships—to close that gap. The same idea appears across vendors: give the model governed metric definitions instead of letting it guess. If this concept is new, start with our guide on what a semantic layer is and why AI agents need one.

2. Clean dimensional models

LLMs write better SQL against well-named fact and dimension tables than against raw operational tables. A clear star schema reduces join ambiguity and makes it obvious where each metric lives.

3. Verified example queries

Curated question–SQL pairs act as few-shot examples and as a regression test set. Several platforms let data teams save “verified queries” that the system reuses when a similar question arrives.

4. Guardrails and permissions

Text-to-SQL should run with the user’s own access rights, read-only, with row limits and cost controls. Never give a natural-language interface broader permissions than the person asking.

5. Evaluation on your own questions

Public benchmarks are useful signals, but the only accuracy that matters is on your company’s questions. Build a golden set of 50–200 real business questions with expected results, and compare result sets, not just SQL strings—two different queries can be equally correct.

Does text-to-SQL replace data analysts?

No. It changes where analysts spend time. Instead of writing the same ad hoc query for the tenth time, analysts curate metric definitions, document tables, review generated SQL, and investigate the questions where context matters. Strong SQL skills become more valuable, because someone has to judge whether the generated query is right.

A simple way to try it

1. Pick one well-modeled subject area (for example, sales orders).

2. Write short descriptions for every table and key column.

3. Define five to ten core metrics with exact formulas.

4. Collect 30 real questions and the correct SQL for each.

5. Test a text-to-SQL tool against that set, log failures, and improve the documentation—not just the prompt.

FAQ

Is text-to-SQL the same as natural language BI?

Text-to-SQL is the core engine. Natural language BI adds charts, conversation history, follow-up questions, and sharing on top of it.

Do I need to fine-tune a model for text-to-SQL?

Usually not at first. Better schema context, metric definitions, and verified examples tend to improve accuracy faster and more cheaply than fine-tuning.

Can business users trust the answers?

Only when the system shows its SQL, uses governed definitions, and has been evaluated on real questions. Treat early answers as drafts that an analyst can verify.

Which skills do I need to build text-to-SQL features?

Solid SQL and data modeling, an understanding of how LLMs use context and tools, and the habit of measuring accuracy with test sets.

If you want to master the SQL and modeling side or build the LLM applications on top of it, explore these Coderhouse courses:

• Data Analytics Course — build the SQL, modeling, and metrics skills that make natural-language queries trustworthy.

• AI Engineering Course — learn to design LLM applications, agents, and evaluation workflows.