TL;DR
Text-to-SQL, also called NL2SQL, has a language model write SQL from a question. Grounded in a semantic layer, it handles descriptive, BI-shaped questions well. It breaks on methodology: funnels, retention and journeys are methods, not metrics, and an LLM writing that SQL freehand produces plausible, wrong numbers. An analytics agent plans the analysis and lets a deterministic engine write the SQL. Evaluate any tool on who writes the SQL, definitions, permissions, failure behavior and correction effort.
Every analytics vendor now has a chat box. Type a question, get a chart. The architecture behind that chat box varies a lot, and the differences only show up after the demo, on the questions your team actually asks. The most common architecture is text-to-SQL, also called NL2SQL or natural language to SQL: a language model writes a query against your warehouse, sometimes grounded in a semantic layer of metrics and dimensions.
Text-to-SQL is useful for a class of questions. It is also structurally unsuited to product analytics. This post explains where the line sits, what an analytics agent does differently, and closes with a checklist for evaluating NL-to-SQL tools in a pilot. Mitzu's analytics agent is built the second way: it composes the analysis, and a deterministic engine writes the SQL.
What text-to-SQL and BI chatbots do?
Text-to-SQL tools, such as Snowflake Cortex Analyst, Databricks Genie, Looker's agent, ThoughtSpot Spotter, or ChatGPT pointed at a warehouse, share one architecture: the AI generates SQL or a query DSL, executes it, and presents the result. A semantic layer of metrics, dimensions and joins can ground the generation, which clearly improves accuracy on metric lookups.
On descriptive questions this works well. What was MRR last quarter? How many weekly actives in Germany? Revenue by plan, last six months? These map onto a single SELECT over well-modeled tables, and a grounded LLM produces that query reliably. Data analysts and data scientists, the main users of these tools, can also read the output and catch mistakes.
Where generic SQL generation breaks: methodology?
Product analytics questions are different in kind, not just in difficulty. A funnel is not a SELECT. It is a sequence of per-user event matches with strict ordering and a conversion window. Retention needs cohort assignment, time bucketing and a return-event definition. A journey needs per-user path reconstruction. These are methods, not metrics.
When an LLM authors this SQL directly, the failure is not a syntax error. The query runs and returns rows. The failure is a methodology error that produces a plausible, wrong number:
- A funnel without a conversion window, or with steps counted out of order
- Retention that doesn't enforce cohort time bucketing, so cohorts bleed into each other
- A segmentation that double-counts users who triggered an event multiple times
- Filter values the model invented because it never saw the real ones in the data
These errors are hard to catch because the output looks right. A PM who receives a conversion rate of 4.2% has no way to know the window was wrong. An analyst can audit one query, but not every query an agent runs across dozens of tool calls. For the wider question of when AI answers can be trusted, see AI analytics hallucinations.
Why BI semantic layers don't close the gap?
The standard answer to LLM query errors is a semantic layer: define metrics and dimensions once, and let the AI query the definitions instead of raw tables (for the basics, see what is a semantic layer). That helps for BI-shaped questions. But the semantic layers these tools use (dbt MetricFlow, Cube, LookML) express metrics, dimensions, entities and joins. They have no native vocabulary for multi-step funnels, retention windows, cohort definitions or journey depth.
So even a well-grounded text-to-SQL agent falls back to freehand SQL exactly where the methodology risk is highest. Building that semantic layer is also data engineering work, typically weeks of YAML authoring before the first question gets answered. The role a semantic layer should play for an agent is covered in why an analytics agent needs a semantic layer.
What an analytics agent does instead?
An analytics agent built this way doesn't write SQL at all. It assembles an analysis specification: for a funnel, the first event, subsequent events, conversion window and breakdown; for retention, the cohort definition, return event and time granularity. A deterministic query engine turns that specification into SQL using analytics methodology encoded in code, not in a prompt.
The division of labor is the point. The LLM does what it is good at: interpreting the question, planning the investigation and deciding which analyses to run. The engine does what it is good at: generating correct, consistent SQL. Same specification, same SQL, same answer, every time.
The SQL is still shown for review, but it is a verification artifact, not the model's authored work.
| Question | Text-to-SQL / BI chatbot | Analytics agent with a deterministic engine |
|---|---|---|
| What was MRR this quarter? | Works well: single metric query | Works: a governed metric, the easy case |
| Signup-to-activation funnel by channel, 7-day window | Methodology risk: windows and step order left to the LLM | Funnel methodology enforced by the query engine |
| Why did week-2 retention drop in November? | Returns a chart, not an investigation | Agent fans out into breakdowns and cohort comparisons, then synthesizes |
| Show users in DACH who completed checkout this month | May invent region codes | Filters suggested from sampled values in the warehouse |
| Fit a model to predict 30-day churn | Not the tool | Not the tool either: that is notebook work |
When text-to-SQL is the right choice?
This is not an argument that text-to-SQL is bad technology. For arbitrary one-off queries over modeled tables, in the hands of an analyst who can read and correct the SQL, a grounded text-to-SQL agent is a real productivity gain. The same goes for notebook AI features used by data scientists for bespoke statistical work.
The mismatch appears when the audience includes people who cannot audit SQL, and when the questions are behavioral: funnels, retention, journeys, cohort comparisons, or a KPI drop that has to be traced to the users behind it. That is where the methodology needs to live in an engine rather than in a model's output.
A checklist for evaluating text-to-SQL and NL2SQL tools
Evaluate a natural-language-to-SQL tool like an operational system, not a chat demo. Score reliability before UX polish: teams that invert the order often move fast for a month and then stall when trust drops. A tool that answers beautifully but cannot explain its SQL is not an analytics solution at scale.
| Criterion | Why it matters | Minimum acceptable bar | Best-practice bar |
|---|---|---|---|
| Who writes the SQL | Decides whether the same question gives the same answer | LLM-written SQL grounded in a semantic layer | Deterministic engine generates SQL from an analysis specification |
| SQL transparency | Enables analyst verification | SQL available on request | SQL visible with every answer, in every channel |
| Semantic layer quality | Prevents metric drift | Core KPI mapping | Metrics, events, entities and sampled values, owned by the data team |
| Warehouse execution and permissions | Controls freshness and governance | Live query support | Runs in place; the same roles apply in the app, Slack and AI assistants |
| Failure behavior | Reduces confident wrong answers | Basic confidence cues | Asks, abstains or escalates instead of improvising |
| Auditability | Supports accountability | Query logs retained | Question-to-query lineage with user identity |
How to run a reliable pilot?
- Choose one KPI domain with stable definitions, usually activation or conversion.
- Test with one product team and one growth team before broader access.
- Require analyst review for executive, financial or externally shared metrics.
- Track answer acceptance, correction rate and time to decision weekly.
- Feed rejected answers back into the definitions, not only into the answer.
Bring your own question pack rather than vendor-selected examples. Include at least one retrieval question, one segmentation, one funnel, one retention question, one anomaly investigation and one deliberately ambiguous question. Ask every vendor to show the SQL, explain how it interpreted each question, and run the same funnel twice with a different conversion window. Then have analysts score correction effort: two tools can look equally accurate in a demo while one needs far less cleanup in production. How a tool handles uncertainty tells you more than its polished first answer.
"The fastest path to self-serve analytics is not fewer controls; it is better controls built into the workflow."
Common buyer mistakes
- Treating SQL visibility as an optional admin feature instead of the default.
- Skipping definition ownership because the model looks smart enough in the demo.
- Measuring adoption by query count instead of acceptance and correction rates.
A scoring rubric that keeps every team at the table
- Data team: definition integrity, who writes the SQL, permissions and auditability.
- Product team: question speed, depth on funnels and retention, and decision usability.
- Growth team: segmentation flexibility and reliable campaign readouts.
- Leadership: rollout risk, governance visibility and expected adoption.
Where Mitzu sits?
Mitzu is an analytics platform on your data warehouse for BI, product analytics and experiment analysis, where the data team governs the metrics and everyone uses them through dashboards, an AI agent, Slack and their own AI assistants. Its agent answers questions, explains why metrics moved and runs deep dives on those governed metrics; it builds analysis specifications that a deterministic engine turns into SQL, so the same question gets the same answer. The same engine has generated the SQL behind Mitzu's UI for years.
Underneath sits Mitzu's semantic layer: metrics on fact and dimension tables, plus events, entities, properties and sampled property values, drafted by Mitzu from your warehouse and dbt models and owned by your data team. That lets one agent answer a KPI question and the funnel question behind it on the same definitions.
If your data already lands in Snowflake, BigQuery, Databricks, Redshift or ClickHouse, you can see how this architecture behaves on your own questions. The AI analytics page walks through the agent, and warehouse-native vs first-generation product analytics covers the data-residency side of the comparison.
FAQ
What is NL2SQL?
NL2SQL, natural language to SQL, is another name for text-to-SQL: a user asks a question in plain language and a model translates it into a SQL query against the warehouse. Accuracy depends heavily on grounding, such as a semantic layer of governed definitions, and on whether the question is a simple lookup or an analysis method like a funnel.
Is a BI chatbot the same as an analytics agent?
No. A BI chatbot translates one question into one query. An analytics agent plans and runs a multi-step investigation, then synthesizes an answer. Many products labeled agents are chatbots underneath; the test is whether a diagnostic question triggers an investigation or just a chart.
Does a semantic layer fix text-to-SQL accuracy?
It improves accuracy on metric and dimension lookups, which is why every serious text-to-SQL tool uses one. It does not fix analysis methodology, because BI semantic layers cannot express funnels, retention windows or cohort logic, so the LLM still writes that SQL freehand.
Why does deterministic SQL generation matter?
It makes answers reproducible and auditable. When a deterministic engine generates SQL from an analysis specification, the same question yields the same SQL and the same number every time, and analysts can review the engine's output once instead of auditing every individual LLM generation.
What should a text-to-SQL evaluation checklist include?
Who writes the SQL, whether the SQL is visible with every answer, who owns the definitions, whether permissions hold in every channel, how the tool behaves on ambiguous questions, and what audit trail it keeps. In the pilot, score analyst correction effort, not only first-answer accuracy.
How many use cases should an NL-to-SQL pilot include?
Start with one KPI domain and a fixed question pack. Expand only after acceptance and correction rates show the answer quality holds.
Who should still use text-to-SQL tools?
Data analysts and data scientists running one-off exploratory queries, especially where they can read and correct the generated SQL. For behavioral and KPI questions consumed by non-technical teams, an analytics agent with the methodology in the engine is the safer architecture.
