Text to SQL vs Semantic Layer vs RAG vs MCP: Which Architecture Fits Your Data Questions
Data Analytics|Natural language
Natural language

Text to SQL vs Semantic Layer vs RAG vs MCP: Which Architecture Fits Your Data Questions

Four acronyms, four different jobs. How text-to-SQL, semantic layers, RAG and MCP work, where each one fails, and how they fit into a single architecture.

Sahil Kadivar
Sahil KadivarHead of AI Solutions
Nov 14, 2026|16 min read

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.

ApproachInputOutputLayer of the stack
Text-to-SQLQuestion, schema, example queriesLLM-written SQL and its resultAnswer engine over raw tables
Semantic layerQuestion mapped to predefined metrics and dimensionsSQL generated by an engine, deterministicallyModeling layer between tables and answers
RAGQuestion and a document indexRetrieved passages and a cited answerAnswer engine over documents
MCPA tool call from an AI clientWhatever the server's tool returnsConnection 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?"

Stack diagram with warehouse tables and documents at the bottom, a semantic layer and vector index above them, text-to-SQL, semantic query and RAG as answer engines, and an MCP connector band at the top linking to AI clients.
Three answer engines sit on top of your data. MCP is the connector band above them, not a fourth engine.

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 modeMost likely inWhat the user seesHow you catch itMitigation
Confident wrong numberText-to-SQLA plausible figureOnly an eval set, or an analyst reading the SQLShow the SQL, limit open-ended SQL to analysts, test against known answers
RefusalSemantic layer"Can't answer that"Visible and loggableModel the missing metric, or fall back to text-to-SQL
Stale documentRAGA cited answer from an old policyCitation review, document datesRe-index on change, version metadata, filter superseded documents
Misapplied ruleRAGRight passage, wrong conclusionHard to catch automaticallyHuman review for high-stakes answers
Over-permissioned toolMCPData the user shouldn't seeAudit logs, after the factPer-tool grants, read-only defaults, row-level security in the warehouse
Schema driftText-to-SQL and semantic layerBroken query, or a silently wrong joinEval set running in CIAssign 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:

  1. A retrieval call to find the policy change, what it said and when it took effect.
  2. A semantic-layer call for the governed refund metric, split before and after that date.
  3. 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.

Flowchart routing a user question through a classifier to the semantic layer, a text-to-SQL fallback for analysts, RAG with citations, or both for mixed questions, with lock icons marking where permissions are enforced.
Route by question type, and decide where permissions are enforced on each branch before you build it.

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 situationData typeQuestion typeGovernance needStart with
Board KPIs, finance reportingStructured, modeledDefined metricsHigh: one number for everyoneSemantic layer
Analysts exploring new tablesStructured, unmodeledAd hoc, long tailMedium: the user can read the SQLText-to-SQL with the SQL shown
Business users self-servingStructuredMixedHigh: row-level security, auditSemantic layer first, with a text-to-SQL fallback limited to analysts
Policies, contracts, SOPsUnstructured"What does X say?"Medium: citations requiredRAG
"Why did this metric move after that event?"BothMixedHighRouter: semantic layer plus RAG
Several AI clients (Claude, Copilot, an internal app) need the same dataAnyAnyPer-tool grantsAn MCP server in front of the above
Already all-in on Snowflake or BigQueryStructuredMetrics and ad hocInherit platform governanceTest 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:

DateChange
Summit 2025 (June 2–5, 2025)Defining semantic views became GA. Querying them was still in preview.
March 2, 2026Querying semantic views with standard SQL clauses became GA, so you can name a semantic view in the FROM clause.
April 5, 2026Support for verified queries in semantic views was added.
June 26, 2026SQL queries as logical tables in semantic views became GA.
August 7, 2026Cortex Agents and MCP servers in Snowflake Native Apps became GA.
August 18, 2026Power 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:

  1. 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.
  2. 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.
  3. 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.
  4. 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:

ComponentTypical ownerUpdate trigger
Semantic layer modelsAnalytics engineeringNew metric requests, schema changes
Eval setAnalytics lead and domain expertsNew question types, refusal log review
Document indexDocument owners and the platform teamPolicy or contract revisions
MCP server and tool grantsPlatform or security teamNew clients, role changes, access reviews
Router and promptsML or application engineeringEval 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.

Get a Routing Plan for Your Data Questions

Send us 30 real questions your team asks. We'll sort them by type, tell you what to model first, and whether a native Snowflake or BigQuery feature covers them before you build anything custom.

Plan My Architecture
CONTACT US

Got a Project For Us?

Skip the sales deck. Speak directly to an AI systems engineer about your data, models, and timeline.

The Engineering Guarantee, We do not route inputs through generic filters. A technical architect will review your project parameters and respond within 1 business day.

Call Us
+91 9537290206
+1 (215) 602-7044
Email Us
info@atlasml.ai

Let's Build Something Smart Together

Tell us about your data infrastructure and project goals. Our engineering team will review your requirements and provide a preliminary technical scoping framework within 24 hours.

+91
Upload Document