It usually starts with one sentence from leadership: "we want ChatGPT for our data." Then the vendor pitches arrive, each with its own acronym. One says text-to-SQL, another says semantic layer, a third says RAG, and a fourth says MCP. Most of the attention goes to text to SQL vs semantic layer, but the real decision covers all four, and they don't compete the way the pitches suggest.
They sit at different layers of the stack. Text-to-SQL, a semantic layer and RAG are three different ways to answer a question. MCP doesn't answer questions at all. It's a protocol for connecting AI applications to tools and data. Teams that get natural-language analytics working usually run several of these together, with clear rules for which question goes where.
This guide covers how each approach works, how each one fails, a decision table for your situation, and when a native Snowflake or BigQuery feature is enough. It's the same framing we use in our data analytics and BI engagements.
Four approaches, four different jobs
Before comparing accuracy, get clear on what each one takes in, what it gives back, and where it sits.
| Approach | Input | Output | Layer of the stack |
|---|---|---|---|
| Text-to-SQL | Question, schema, example queries | LLM-written SQL and its result | Answer engine over raw tables |
| Semantic layer | Question mapped to predefined metrics and dimensions | SQL generated by an engine, deterministically | Modeling layer between tables and answers |
| RAG | Question and a document index | Retrieved passages and a cited answer | Answer engine over documents |
| MCP | A tool call from an AI client | Whatever the server's tool returns | Connection and transport layer |
MCP belongs in a different column from the other three. An MCP server that exposes a raw execute_sql tool is just text-to-SQL with a new connector. One that exposes a semantic-layer query tool is a semantic layer with a new connector. The question "MCP or RAG?" makes about as much sense as "USB or a hard drive?"

How each approach works mechanically
Text-to-SQL: the LLM writes the query
The prompt contains the schema (tables, columns, sometimes sample values), a few example question-and-SQL pairs, and the user's question. The LLM writes a SQL statement, the warehouse runs it, and the result goes back to the user, often with a short summary. Better systems add schema retrieval, so only relevant tables go into the prompt, and they validate the query before it runs. For the full walkthrough of schema prompting and query validation, see our guide to how text-to-SQL turns business questions into database queries.
The failure mode is SQL that looks right but isn't. Omni, a semantic-layer vendor, lists the usual causes: misreading what a metric means, picking the wrong join, giving different answers to the same question, and ignoring permissions. Omni also cites an analysis of 4,602 incorrect SQL queries in which schema-level errors caused 81.2% of failures and syntax errors only 18.8%. In practice, the query runs and returns a number, and nothing in the pipeline flags it as wrong.
Semantic layer: the LLM picks metrics, the engine writes the SQL
A semantic layer defines metrics, dimensions and join paths once, in code. "Active policy", "net written premium" and "region" each get one definition that everyone shares. When a question comes in, the LLM doesn't write SQL. In the setup dbt Labs describes, the LLM only breaks the question into metrics and dimensions, and MetricFlow generates the SQL deterministically.
When it fails, it usually says it can't answer. If a metric or join path isn't modeled, the request can't be expressed, so the system refuses. dbt puts it this way: "The Semantic Layer tells you it can't answer. It never returns invalid data. Text-to-SQL will cheerfully give you a wrong number." The price is effort. Someone has to model the metrics, review changes, and keep the model in step with the warehouse as schemas change.
RAG: retrieve passages, then answer
Retrieval-augmented generation splits documents into chunks, embeds them, and stores them in a vector index. At question time it retrieves the most relevant passages and asks the LLM to answer from those passages, with citations. It works well for policies, contracts, SOPs and product documentation, where the answer is written down somewhere.
It's poor at arithmetic over rows. Retrieval returns text passages, so it can't sum ten million transactions. It also has two quieter failures. The index may hold an outdated version of a policy, or the model may retrieve the right clause and still apply it wrongly. Retrieved text guides the model, but it doesn't stop the model from reasoning badly about it. We cover how RAG grounds chatbot answers and limits hallucination in our chatbot FAQ.
MCP: a standard socket for tools and data
The Model Context Protocol specification (version 2025-11-25) defines hosts, clients and servers that communicate over JSON-RPC 2.0. Servers expose three kinds of things: Resources ("context and data"), Prompts ("templated messages and workflows") and Tools ("functions for the AI model to execute"). Build one MCP server in front of your data, and Claude, an IDE assistant and your internal app can all use it without separate integrations.
Adoption is broad. When the Linux Foundation announced the Agentic AI Foundation on December 9, 2025, with MCP as a founding project alongside goose and AGENTS.md, it cited "more than 10,000 published MCP servers."
For governance, the key line in the spec is this: "MCP itself cannot enforce these security principles at the protocol level." It also warns that tools "represent arbitrary code execution and must be treated with appropriate caution." So permissions live in the MCP server and in the systems behind it, not in the protocol. Our article on protecting sensitive business data in generative AI applications covers the access-control patterns.
Where each one breaks
Each approach fails in a different way. For an analytics product, how visible the failure is matters as much as how often it happens.
| Failure mode | Most likely in | What the user sees | How you catch it | Mitigation |
|---|---|---|---|---|
| Confident wrong number | Text-to-SQL | A plausible figure | Only an eval set, or an analyst reading the SQL | Show the SQL, limit open-ended SQL to analysts, test against known answers |
| Refusal | Semantic layer | "Can't answer that" | Visible and loggable | Model the missing metric, or fall back to text-to-SQL |
| Stale document | RAG | A cited answer from an old policy | Citation review, document dates | Re-index on change, version metadata, filter superseded documents |
| Misapplied rule | RAG | Right passage, wrong conclusion | Hard to catch automatically | Human review for high-stakes answers |
| Over-permissioned tool | MCP | Data the user shouldn't see | Audit logs, after the fact | Per-tool grants, read-only defaults, row-level security in the warehouse |
| Schema drift | Text-to-SQL and semantic layer | Broken query, or a silently wrong join | Eval set running in CI | Assign an owner for the model, test on every schema change |
The schema-drift row deserves extra attention. When a column is renamed or a table is split, a semantic layer usually breaks loudly the next time the model is built and tested. Text-to-SQL adapts to whatever schema is in the prompt, so it may join through the new structure in a way that runs fine and means something different.
Text to SQL vs semantic layer: what the 2026 benchmarks say, and don't
The benchmark cited most often is from dbt Labs, published April 7, 2026 by Jason Ganz and Benoit Perigaud. They ran 11 questions on an ACME Insurance dataset, 20 times each, and compared 2023 results with 2026 models.
- On the original schema: text-to-SQL accuracy across all questions rose from 32.7% (GPT-4, 2023) to 64.5% (both 2026 models). The semantic layer rose from 60.5% to 72.7%.
- After additional modeling: Claude Sonnet 4.6 scored 90.0% with text-to-SQL and 98.2% with the semantic layer. GPT-5.3-Codex scored 84.1% and 100.0%.
Read those numbers with the caveats attached:
- dbt sells the semantic layer it tested.
- The test used 11 questions on one dataset.
- Text-to-SQL had the entire schema in context, which the authors say "isn't practical for larger datasets." On a warehouse with hundreds of tables, text-to-SQL would also need schema retrieval, which adds another place to fail.
- On questions that needed too many entity hops, the semantic layer scored 0% in both 2023 and 2026. That gap closed only after three new dbt models, generated by an LLM, were added. The semantic layer's accuracy depends on modeling work, and that work is never finished.
For harder conditions, look at Spider 2.0, an academic benchmark of 632 enterprise text-to-SQL workflow problems. Its Spider 2.0-Snow subset has 547 examples, all on Snowflake. Spider 2.0-Lite has 547 across BigQuery (214), Snowflake (198) and SQLite (135). The original Spider 2.0 paper reported that a code-agent framework using o1-preview solved 17.0% of tasks, compared with 91.2% on Spider 1.0 and 73.0% on BIRD. Enterprise schemas are much harder than the older academic benchmarks.
The leaderboard has climbed since then. At the time of writing it shows Genloop's Sentinel Agent v2 Pro at 96.70 on Spider 2.0-Snow, and Tencent's Tianqiong Data Agent + GLM 5.2 at 76.23 on Spider 2.0-Lite. Teams submit these entries themselves, and the entries are undated. Treat them as snapshots of what heavily engineered agents can do on a public benchmark, not as the accuracy you'll get on your own warehouse.
What matters more than any single score is how each approach fails. Text-to-SQL accuracy has improved a lot, but when it's wrong it gives no warning. A semantic layer fails by refusing, which you can see. If several people need to report the same number for the same metric, that difference outweighs a few points of accuracy.
How they combine: a reference architecture
Most teams end up with the same arrangement: a semantic layer first, text-to-SQL as a controlled fallback, RAG for documents, and MCP as the plumbing that exposes all of it to AI clients.
Route by question type
A lightweight classifier, usually an LLM call with a few examples, decides what kind of question it's looking at:
- Metric questions ("What was net premium by region last quarter?") go to the semantic layer.
- Unmodeled structured questions fall back to text-to-SQL. The generated SQL is shown to the user, and the fallback is limited to people who can read SQL.
- Document questions ("What does our refund policy say about partial shipments?") go to RAG and come back with citations.
Log every refusal from the semantic layer. That log becomes the modeling backlog, and it shows you which metrics people actually ask for.
MCP as the plumbing
Expose each capability as its own MCP tool with its own permissions: a semantic-layer query tool, a document search tool, and, for analysts only, a read-only SQL tool. Snowflake makes the same recommendation: its managed MCP server documentation "generally recommends exposing a Cortex Agent as the only client-facing tool" for governed business questions. Put one governed entry point in front of business users and keep the raw tools behind it.
If answers also need live operational data, such as an open ticket or a customer's order status, add those systems as further tools. The integration work then looks much like connecting AI to existing ERP and CRM systems.
Mixed questions
Take "Did refunds rise after the March policy change?" No single approach answers it. It needs:
- A retrieval call to find the policy change, what it said and when it took effect.
- A semantic-layer call for the governed refund metric, split before and after that date.
- A final step that combines the two, cites the policy document and shows the metric definition used.
If the router sends this question only to text-to-SQL, the LLM has to guess the date of the policy change. If it sends it only to RAG, you get a description of the policy and no number. Most of the value of the combined architecture comes from handling questions like this one.

Decision table: data type × question type × governance need
Find the row closest to your situation. Most organisations match more than one row, and that's the reason for the router.
| Your situation | Data type | Question type | Governance need | Start with |
|---|---|---|---|---|
| Board KPIs, finance reporting | Structured, modeled | Defined metrics | High: one number for everyone | Semantic layer |
| Analysts exploring new tables | Structured, unmodeled | Ad hoc, long tail | Medium: the user can read the SQL | Text-to-SQL with the SQL shown |
| Business users self-serving | Structured | Mixed | High: row-level security, audit | Semantic layer first, with a text-to-SQL fallback limited to analysts |
| Policies, contracts, SOPs | Unstructured | "What does X say?" | Medium: citations required | RAG |
| "Why did this metric move after that event?" | Both | Mixed | High | Router: semantic layer plus RAG |
| Several AI clients (Claude, Copilot, an internal app) need the same data | Any | Any | Per-tool grants | An MCP server in front of the above |
| Already all-in on Snowflake or BigQuery | Structured | Metrics and ad hoc | Inherit platform governance | Test the native feature before building anything |
The last row is the one buyers skip most often. If your governed data lives in one warehouse, you should test the platform's own feature before paying anyone, us included, to build a custom stack.
The 2026 platform options: Snowflake Semantic Views and BigQuery Conversational Analytics
Snowflake Semantic Views and the managed MCP server
Snowflake released Semantic Views in stages, and third-party write-ups often get the dates wrong. From Snowflake's own release notes:
| Date | Change |
|---|---|
| Summit 2025 (June 2–5, 2025) | Defining semantic views became GA. Querying them was still in preview. |
| March 2, 2026 | Querying semantic views with standard SQL clauses became GA, so you can name a semantic view in the FROM clause. |
| April 5, 2026 | Support for verified queries in semantic views was added. |
| June 26, 2026 | SQL queries as logical tables in semantic views became GA. |
| August 7, 2026 | Cortex Agents and MCP servers in Snowflake Native Apps became GA. |
| August 18, 2026 | Power BI ingestion for Semantic View Autopilot became GA. |
The Snowflake-managed MCP server can expose Cortex Analyst, Cortex Search, Cortex Agents, custom tools and SQL execution. A few details matter for planning. According to the documentation:
- It "only supports using semantic views with Cortex Analyst. It does not support semantic models." If you built on the older semantic model files, plan a migration.
- Each server can have at most 50 tools.
- "Access to the MCP Server does not give access to the tools… Permission needs to be granted for each tool."
- SQL execution defaults to
read_only.
The per-tool grant model is what the MCP spec expects servers to provide, since the protocol can't enforce permissions itself.
BigQuery Conversational Analytics
Google announced general availability of Conversational Analytics in BigQuery on July 1, 2026. According to the announcement, it "inherits BigQuery's governance model, so users only query data they are authorized to see and every query is logged for auditing."
- Grounding: the Knowledge Catalog (glossaries and context bundles), verified queries, BigQuery Graph for multi-hop queries, and custom instructions.
- Transparency: the agent shows the generated SQL before the answer, cites the context it used, and asks clarifying questions.
- Controls: per-user or per-project cost controls, and a maximum query size in bytes for each agent.
- Distribution: agents can be published to Gemini Enterprise, Data Studio, or custom apps through the Conversational Analytics API.
Google quotes Mony Group's Head of Data as saying analysts save "around half a day each week." That's a vendor-published customer claim, not an independent measurement. The announcement gives no pricing, so model your costs in a pilot.
When native is enough and when it isn't
The native feature is probably enough if:
- Your governed data and metric definitions live in one warehouse.
- Your questions are mostly structured, not documents.
- You're happy to inherit the platform's permission model and audit logs.
Build custom, or extend the native feature, if:
- Your data spans several warehouses, or your metric definitions live in a BI tool.
- You need routing that combines document answers with metric answers in one response.
- You need your own evaluation and regression testing beyond what the platform offers.
- Several non-native AI clients need the same governed access.
What to build first: a 4-step plan for consulting buyers
We follow the same order on every engagement, whatever stack the client ends up with:
- Collect 30–50 real questions. Get them from the people who'll use the system, not from a workshop whiteboard. Sort each one as metric, ad hoc or document. The ratio between the three tells you which layer to build first.
- Write an eval set with known-correct answers. An analyst writes the correct SQL or cites the correct document for each question. Run it on every model, prompt or schema change. Treat it like any production ML test suite; our notebook-to-production MLOps playbook covers the pattern.
- Model the top metrics before adding any LLM. If the 15 most-asked metrics have no agreed definition, no architecture will fix that. Modeling them first is also when definition disputes come out, while they're still cheap to settle.
- Add RAG and MCP exposure only for the questions that need them. Build retrieval for the document questions you found in step 1. Add MCP when more than one client needs access. Our generative AI team builds the RAG pipelines and agents; for the architecture and feasibility decision itself, AI consulting is usually the right first engagement.
Decide who owns each piece after launch before you build it:
| Component | Typical owner | Update trigger |
|---|---|---|
| Semantic layer models | Analytics engineering | New metric requests, schema changes |
| Eval set | Analytics lead and domain experts | New question types, refusal log review |
| Document index | Document owners and the platform team | Policy or contract revisions |
| MCP server and tool grants | Platform or security team | New clients, role changes, access reviews |
| Router and prompts | ML or application engineering | Eval regressions, model upgrades |
If nobody owns the eval set, accuracy will drift without anyone noticing, and that's the exact failure the semantic layer was meant to prevent.
For a working example, our text-to-SQL business intelligence project converts natural-language questions into SQL queries, so business teams can query their databases and get insights faster.
Frequently asked questions
What is the difference between text-to-SQL and a semantic layer?
With text-to-SQL, an LLM writes SQL directly against your raw tables, using the schema and examples in its prompt. With a semantic layer, the LLM only picks predefined metrics and dimensions, and an engine generates the SQL deterministically. A semantic layer refuses questions it can't express. Text-to-SQL always returns an answer, and sometimes it's wrong.
Is MCP a replacement for RAG?
No. MCP (Model Context Protocol) is a standard for connecting AI applications to tools and data sources. RAG is a technique for retrieving document passages and answering from them. You can expose a RAG search as an MCP tool, so the two work together rather than compete.
Do I need a semantic layer if LLMs are good at SQL now?
You do if several people need to report the same number for the same metric. Text-to-SQL accuracy has improved a lot since 2023, but its errors don't announce themselves: the query runs and returns a plausible figure. A semantic layer gives you one governed definition per metric and refuses instead of guessing.
Can RAG answer questions about my database?
Poorly. RAG retrieves passages of text, and it can't aggregate rows, so it can't reliably sum, filter or group your transactions. Use a semantic layer or text-to-SQL for numbers, and RAG for policies, contracts and other documents. For mixed questions, route to both and combine the answers.
Are Snowflake Semantic Views or BigQuery Conversational Analytics enough on their own?
Often, if your governed data and metric definitions sit in that one warehouse and most questions are structured. They inherit the platform's permissions and audit logging. They're less suitable if your data spans several platforms, if your metrics live in a BI tool, or if you need answers that combine documents with metrics.
How do I keep an MCP data agent from leaking data?
Grant permissions per tool, default SQL tools to read-only, and enforce row-level security in the warehouse or semantic layer, not in the agent. The MCP specification itself says the protocol cannot enforce security principles, so the server and the systems behind it have to. Review audit logs and tool grants on a regular schedule.

