Most employees cannot write SQL, so every new question queues behind a small analytics team. Text-to-SQL and conversational BI let a user type "revenue by region last quarter versus plan" and receive a checked query, a table and a chart. Success depends less on the model than on a governed semantic layer, good metadata and permission-aware execution.
Problem & value
- Today: dashboards answer predefined questions; anything new becomes a ticket for the BI team. Business users export to spreadsheets, producing inconsistent numbers ("which revenue?").
- AI value: self-service for ad-hoc questions, faster time to insight, analysts freed for modelling, and a single set of metric definitions when answers are grounded in a semantic layer.
- Reality check: academic benchmarks show a large gap between clean-schema demos and real enterprise databases. Spider 2.0 (enterprise workflows, large schemas, multiple dialects) was designed to expose this gap and models scored far lower on it than on the older Spider 1.0 [1]; BIRD tests dirty values, external knowledge and efficiency on large databases [2]. Treat vendor accuracy claims as valid only on your own question set.
Variants
- Embedded BI assistant: chat inside an existing BI tool (Power BI, Tableau, Looker, Qlik).
- Warehouse-native analyst: assistant inside Snowflake, Databricks, BigQuery.
- Developer/analyst copilot: SQL autocomplete and explanation in the IDE (see CODE-022).
- Operational data chat: questions on ERP/CRM data for sales, finance (FP&A: DATA-024).
- Public sector / open data: natural-language access to statistical portals.
- Private use: chat over personal finance exports or a local SQLite file; see DATA-006.
How it works
- question
- intent + entity resolution (map terms to metrics, dimensions, tables via glossary)
- context retrieval (schema subset, column descriptions, example queries, metric definitions)
- generation (semantic-layer query OR SQL in the target dialect)
- validation (parse, read-only check, allow-list of tables, cost estimate / LIMIT)
- execution under the *user's* permissions (row/column-level security)
- answer (table, chart, text summary + visible query + assumptions)
- feedback (thumbs, corrections)
- curated example store
- Schema linking is the hard part on wide schemas: retrieve only relevant tables and columns with embeddings plus keyword search, then let a reasoning model plan joins.
- Semantic layer first: generating queries against governed metrics (dbt Semantic Layer / MetricFlow, Cube, LookML, Power BI semantic models) is markedly more reliable than raw SQL over raw tables, and guarantees consistent definitions.
- Agentic loop: the model runs the query, inspects errors or empty results, repairs and retries.
- Human-in-the-loop: show the SQL and assumptions; analysts certify frequently asked questions into "trusted" answers; high-impact numbers (board, regulatory) are never taken from unreviewed chat.
Data preparation
Text-to-SQL depends on metadata more than on data volume. The relevant data lives in the ERP database, a data warehouse or Excel files on file shares; the knowledge of what columns mean sits in analysts' heads. Typical gaps are cryptic column names, undocumented metric definitions (what counts as "revenue"?), duplicates across systems, missing row-level access rules and spreadsheets used as shadow databases.
Preparation steps: (1) pick 10-20 real business questions and the tables they need, see PREP-002; (2) document tables and columns in plain language and build a small business glossary and metric layer, see PREP-012 and DATA-007; (3) restructure key spreadsheets into tidy tables, see PREP-011 and PREP-003; (4) fix master data duplicates with DATA-003 and monitor quality with DATA-008; (5) define roles and row-level permissions, see PREP-023; (6) collect 30-50 example questions with correct SQL as an evaluation set, see PREP-018 and PREP-016.
Tools: dbt or a BI semantic model for definitions, a read-only replica as query target, DuckDB for spreadsheet extracts. Rough share of effort: about 40 to 50 percent of the project, mostly spent on definitions and permissions, not on technology. Use the GUIDE-003 readiness check before the pilot.
Implementation paths
Which path for whom
- smb: Path A, because BI platforms already in use (Power BI, Qlik, Metabase) ship mature conversational features at seat prices an SMB can afford.
- enterprise: Path B, because the semantic layer and access rights are company-specific and API costs stay manageable while retaining control of governance.
- developer: Path C, because open text-to-SQL stacks and local models allow full control over schema, prompts and evaluation.
- public: Path C on sovereign EU/German infrastructure, because citizen and administrative data and procurement rules favour hosting under own control.
Path A — Buy (recommended for most organisations)
- Inside the BI/data platform: Microsoft Fabric / Power BI Copilot, Tableau (Pulse, Agent), Looker Conversational Analytics (Gemini), Qlik, ThoughtSpot (Spotter), Snowflake Cortex Analyst, Databricks AI/BI Genie, Amazon Q in QuickSight, Domo.
- Specialists: Omni, Hex, Julius, Vanna (open core), plus EU vendors such as SAP Analytics Cloud with Joule (Germany), Mistral-based offerings and local BI vendors (e.g. Pyramid Analytics ships EU hosting; check the specific product).
- Choose A when: you already run one of these platforms; the feature is a licence option and inherits existing security. Verify EU data residency and whether prompts/results leave the region.
Path B — Build on cloud APIs
- Models: frontier LLMs with tool use and structured output (Claude, Gemini, GPT, Mistral Large/Codestral as EU provider) via EU regions (Azure OpenAI EU data zone, Vertex AI EU, Amazon Bedrock Frankfurt/Paris).
- Building blocks: semantic layer (dbt MetricFlow, Cube), MCP servers for databases and warehouses (so any MCP-capable assistant can query governed sources), schema/metadata store with a vector index, a SQL parser (sqlglot) for validation, a read-only role per user.
- Choose B when: you need custom UX, several sources, or embedding in your own product.
Path C — Self-host (open source)
- Models: open-weight code/reasoning models, e.g. Qwen3-Coder / Qwen3, DeepSeek, Mistral Codestral, OmniSQL (open text-to-SQL models), served via vLLM or Ollama.
- Frameworks: Vanna, Wren AI, LangChain/LlamaIndex SQL agents, Metabase and Apache Superset as BI front ends, Cube or dbt Core as semantic layer, sqlglot for validation.
- Hardware: HW2 for ~30B-class models on modest schemas; HW3 for larger reasoning models and concurrent users. Operations: model serving, evaluation harness, metadata upkeep.
Path D — Custom training
Fine-tune a mid-size open model on your own question/SQL pairs (hundreds to a few thousand, taken from analyst query logs) when schema, dialect or jargon are unusual or when cost/latency requires a small model. Published open text-to-SQL models show that small specialised models can approach larger general ones on benchmarks [2]; verify on your data.
State of the art (2026)
- Reasoning models with execution feedback lead public text-to-SQL leaderboards; gains come from schema linking, candidate generation with selection by execution, and self-repair [1][2].
- Semantic-layer grounding and MCP have become the standard route to enterprise-grade accuracy and consistent metrics; vendors position agents that combine SQL, Python and visualisation.
- Limits and failure modes: ambiguous questions (silent wrong interpretation), wrong joins and fan-out double counting, misapplied filters, hallucinated columns, dialect quirks, stale metadata, questions that need causal reasoning instead of lookup. Plausible but wrong numbers are the main risk, because users trust charts. Prompt injection via data values in text columns is a further threat.
Shared resources & combinations
- Same stack (llm-code + embedding-text + vector-db): KNOW-002 RAG assistants, CODE-022 data pipeline code generation, DATA-006 spreadsheet assistant.
- Metadata: DATA-007 catalogue enrichment supplies the column descriptions that drive accuracy; DATA-008 data-quality monitoring flags unreliable tables.
- Downstream: DATA-002 automated reporting narratives, DATA-024 FP&A copilot.
- One LLM gateway with logging and PII guardrails can serve all of these.
Effort, cost & KPIs
- Path A: days to weeks to enable; licence uplift per user or capacity unit; effort goes into metadata and permissions.
- Path B: 2–4 months for a governed multi-source assistant with evaluation harness.
- Path C: 3–6 months incl. serving and evaluation; GPU plus ops staff.
- Cost drivers: warehouse compute from agent-generated queries (set limits), metadata curation, evaluation set maintenance.
- KPIs: execution accuracy on your own golden question set, share of questions answered without analyst, user adoption, ticket volume to BI team, query cost per answer, rate of corrected answers.
People & change
Involve management and the department lead first to choose a few questions worth answering and to accept that answers are drafts, not facts. The data owners and IT must agree on a read-only access path and on definitions; IT security reviews the connection and prompt-injection exposure. End users should co-create the question list, because they know what they actually ask.
Works council: depends, but likely yes. The system logs who asked what and may reveal individual performance data (sales per person), so §87(1) no. 6 BetrVG applies if there is an elected works council; also inform it under §90(1) no. 3 and consider an expert under §80(3). Public bodies involve the Personalrat. Data protection: involve the DPO early; a DPIA is likely only if personal data (HR, customers) is queried at scale; sign an AVV with every cloud provider.
AI literacy: support staff with short training (what the tool can get wrong, how to read the shown SQL), see GUIDE-006. Start with a pilot group of 5-10 power users and an AI champion, communicate that humans verify any number used in decisions, and address fears of replacing analysts by repositioning them as owners of definitions. Further reading: GUIDE-004, GUIDE-008, GUIDE-009.
Risks, regulation & compliance
- EU AI Act: typically minimal risk; AI literacy duty (Art. 4). If answers feed decisions on individuals in Annex III areas (e.g. staff or credit assessments), the downstream use may be high-risk.
- GDPR: warehouses often hold personal data; enforce row/column security and masking under the asking user's identity, not a service account; log queries (purpose limitation, data minimisation); data processing agreement and EU region for any cloud model; avoid sending raw rows to the model when schema and aggregates suffice.
- Security: read-only roles, query allow-lists, timeouts and cost caps, injection defences, audit trail of question, SQL and result.
- Mitigations: show SQL and assumptions, certified metrics, golden-set regression tests on every model or schema change, escalate low-confidence answers to analysts.
Getting started
- Pick one domain (e.g. sales) and 50–100 real questions with analyst-verified SQL as a golden set.
- Document tables and metrics: descriptions, synonyms, join paths; build a small semantic layer.
- Trial the native assistant of your current BI/warehouse platform against the golden set.
- If it falls short, build a thin agent with read-only per-user access and SQL validation.
- Roll out to a pilot group, collect corrections, and expand domain by domain.
Sources
- Spider 2.0 benchmark — https://spider2-sql.github.io/
- BIRD benchmark (text-to-SQL) — https://bird-bench.github.io/
- Snowflake — Cortex Analyst documentation — https://docs.snowflake.com/en/user-guide/snowflake-cortex/cortex-analyst
- Databricks — AI/BI Genie — https://docs.databricks.com/aws/en/genie/
- dbt Labs — Semantic Layer / MetricFlow — https://docs.getdbt.com/docs/use-dbt-semantic-layer/dbt-sl
- Vanna (open source text-to-SQL) — https://github.com/vanna-ai/vanna
- Model Context Protocol — https://modelcontextprotocol.io/
- Regulation (EU) 2024/1689 (AI Act) — https://eur-lex.europa.eu/eli/reg/2024/1689/oj