Chat With Your Data: How Text-to-SQL, RAG and Agents Work

Compare text-to-SQL, RAG and tool-using agents for chat with your data, covering how each pipeline runs, where it breaks, and the data foundation each needs.

|
Nov 16, 2026

Summarize with AI:

Chat with your data succeeds or fails on the data foundation underneath it, not on the model. Of the three approaches to chat with your data, text-to-SQL retrieves schema, generates SQL and runs it against warehouse tables, breaking on wrong joins unless a semantic layer and clean schemas define metrics. RAG chunks, embeds and retrieves documents, and it quotes stale passages unless the index is refreshed and filtered by document ACLs. Agents call both as tools in a loop, and they compound errors unless tool calls are capped and every tool enforces the user’s permissions. That makes the semantic layer, index freshness and permissions the first engineering investment, before any model comparison.

Summary

  • Match the approach to the data: text-to-SQL for warehouse tables, RAG for documents, and an agent when later tool calls depend on earlier results.
  • Define metrics, joins and synonyms in a semantic layer before you put text-to-SQL in front of business users.
  • Treat chunking choices and index freshness as the main levers on RAG quality, and set reindexing by how fast each source changes.
  • Cap agent iterations, tool calls and run time explicitly instead of trusting framework defaults.
  • Enforce permissions in the warehouse and the retrieval index, never in the prompt.
  • Score every change against a versioned golden question set that stores expected SQL, result rows and source documents.

Learn more about Hybrid Deployment

What Is Chat With Your Data?

Chat with your data means answering natural-language questions from your company’s warehouse tables and documents, such as how many enterprise trials converted last month. An LLM’s training does not provide a current view of your pipeline, contracts or trials. Your application grounds its answer by fetching relevant rows or passages, answering in plain English, and attaching its SQL or source documents for review.

A chat answer is only as reliable as the data and definitions behind it: if the warehouse copy of the CRM lags a day, the answer lags a day. If two teams define conversion differently, the system picks one definition without saying which. Citations can reinforce that misplaced confidence, since a visible source makes an answer look trustworthy even when the cited link is wrong.

How Does Chat With Your Data Work?

Three approaches do the work: text-to-SQL queries structured warehouse tables, RAG retrieves passages from documents, and agents call either of those as tools inside a planning loop. Each one ends by reading from a data store that someone has to keep correct and current.

Text-to-SQL Translates Questions Into Warehouse Queries

Text-to-SQL retrieves the relevant schema, generates SQL, executes it against the warehouse, and turns result rows into an answer. When the warehouse schema exceeds the prompt’s capacity, the system retrieves indexed table and column descriptions for the model to query. Miss the refunds table, and the generated query never subtracts refunds.

Tidy-schema benchmarks hide retrieval risk. On Spider 2.0’s enterprise workflows, GPT-4o solved 10.1% of tasks, compared with 86.6% on Spider 1.0, so a strong score on a clean benchmark says little about how the model will perform on your warehouse.

ReFoRCE scores 62.9% on Spider 2.0 but 11.4% on an enterprise warehouse benchmark built from private query logs, with databases averaging 101.5 tables and 869.4 columns. Test retrieval against your warehouse’s complexity before using benchmark scores to approve launch.

Many columns leave the model guessing which one finance means by “revenue.” A semantic layer records governed metric definitions, join paths and synonyms. Across three frontier models, adding a 4 KB semantic-layer document raised accuracy by 17 to 23 points, from 45.5 to 50.5 percent to 67.7 to 68.7 percent. Definitions improved accuracy without changing models.

RAG Retrieves Document Chunks to Ground the Answer

RAG answers are only as good as the passages retrieved for the model to cite. Loaders pull files from wikis, ticket systems and shared drives; chunkers split passages, embedding models convert them into vectors, and a vector index stores them. The embedded question retrieves chunks by semantic similarity, which determines the available evidence. Hybrid search merges these results with BM25 keyword matches, then can rerank them before generation, so every stage from loading to reranking shapes what the model can cite.

Hybrid retrieval tends to help on documents full of exact identifiers, though not on every corpus. On a financial text-and-table benchmark, hybrid fusion lifted Recall@5 to 0.695 from 0.587 for dense retrieval alone, and adding a reranker took it to 0.816.

On BEIR corpora, reciprocal rank fusion scored below dense-only retrieval on SciFact, FiQA and ArguAna. Measure retrieval settings on your own documents, because a default that wins on one corpus loses on another.

Agents Call Tools to Combine Queries, Retrieval and Actions

An agent loops through reading the question, selecting a tool by its name and description, calling it, and using its output to choose another call or answer. Vague descriptions for query_data and search_docs can cause routing errors, so distinguish what each tool accepts and returns.

Agents gain flexibility at the cost of dependent steps: a long-horizon execution study models success over t steps as p to the power t. With independent step outcomes and 95% accuracy per step, about four runs in ten fail across ten steps. Accept that risk when you cannot predict the steps in advance; otherwise, fixed routing to SQL or retrieval reduces cost, latency and testing complexity.

In practice the three approaches nest. Frameworks such as LangChain and LlamaIndex, which build on data that replication tools like Airbyte deliver, let developers wrap a text-to-SQL engine and a document retriever as two tools under one agent.

When Should You Use Text-to-SQL, RAG or an Agent?

Choose the approach by the data type your question needs. If the answer lives in rows and columns, it needs SQL, because counting, summing and grouping are operations a database executes exactly and a language model reconstructs poorly from retrieved text. On a query-operator benchmark, SQL over extracted records scored 51.2 on counting and grouping questions, against 30.0 for RAG and 3.4 for a ReAct-style retrieval agent. Embedding warehouse rows into a vector store and asking for a total means summing whatever top-k rows happened to come back.

If the answer lives in prose, such as contracts, runbooks and policy pages, use retrieval to find the relevant termination clause or instruction. Adoption remains early, with natural language analytics use below 30% among organizations in Dresner’s 2025 research.

Mixed questions justify adaptive routing: “Which enterprise accounts that complained about the new pricing terms in support tickets also cut seats this quarter?” needs ticket retrieval followed by a warehouse query. If these questions are rare, route the bulk of traffic to one pipeline and add an agent for the remainder after both pipelines pass their tests.

Why Do Text-to-SQL, RAG and Agent Systems Break in Production?

Most production breakage traces to what the model received: missing definitions, outdated passages and ungoverned access. Each approach has a characteristic way of breaking:

  • Text-to-SQL picks the wrong join path: In 200 categorized GPT-4o failures on multi-hop joins, wrong join paths caused 38% and missing tables another 24%, and both trace to undocumented table relationships.
  • RAG chunking buries the answer: In a controlled study on Natural Questions, chunk overlap added indexing cost with no measurable quality benefit, and answer quality declined once retrieved context passed roughly 2,500 tokens.
  • Semantic chunking adds compute: On real documents, semantic chunking rarely justified its extra compute over fixed-size chunks.
  • RAG indexes go stale: The retrieval system returns old policy chunks until a sync reaches the vector index, and it can still retrieve a deleted document if the delete never propagates, even when the answer carries a real citation.
  • Agents loop: An analysis of 150 multi-agent traces identified 14 distinct ways these systems fail, including repeated steps and not recognizing when to stop, so an agent without caps on iterations, tool calls and run time keeps spending tokens until someone notices the bill.

Undefined metrics invite invented calculations in enterprise text-to-SQL. Across four models, governed semantic definitions cut metric hallucination from baseline rates of 0.48 to 0.70 to 0.075 or lower for the three models with reported results.

Citations do not cure RAG hallucination on their own, since up to 57% of citations in attributed answers were post-rationalized, matched to a source after the answer was already written. Check whether the cited passage supports the claim before treating the citation as evidence of correctness.

Permission leaks expose the same dependence on context and service-account access. In the EchoLeak exploit, a crafted email coerced Microsoft 365 Copilot into accessing internal files and sending their contents to an attacker-controlled server; Microsoft assigned CVE-2025-32711 and shipped emergency patches.

OWASP lists vector and embedding weaknesses among its 2025 LLM application risks. The controls belong in the data layer: row-level security and column masks in the warehouse, and document ACLs carried into the index and filtered at query time with the end user’s identity rather than a shared service account.

How Do You Prepare Data and Evaluate Chat With Your Data Answers?

Build the foundation and a golden question set before you compare models, because a model comparison run on undefined metrics and stale indexes mostly measures those defects. The foundation each approach needs follows from how it breaks:

ApproachData typeTypical breakageRequired foundation
Text-to-SQLWarehouse tables and viewsWrong joins, guessed metrics, invented columnsSemantic layer with metric definitions, join paths and synonyms; clean, current schemas; row-level security
RAGDocuments, tickets, wiki pagesChunks that split answers, stale or undeleted passagesStructure-aware chunking, hybrid retrieval tested on your corpus, incremental reindexing, document ACLs in the index
AgentMixed questions spanning bothWrong tool routing, loops, compounding errorsClear tool descriptions, governed read-only tools, explicit caps on iterations, tool calls and run time, plus everything the wrapped tools need

A golden question set is a versioned list of real questions with verified answers that every change must pass. Keep it small enough to run on every change, roughly 50 to 200 cases covering each major question type plus the adversarial cases that broke you before. For text-to-SQL, store the verified SQL, the expected result rows and the schema snapshot version together, and score by execution accuracy, which compares result sets instead of SQL text.

Gold answers need the same scrutiny as model output: annotation errors in 52.8% of BIRD Mini-Dev problems and 66.1% of the checked Spider 2.0-Snow problems undermine those evaluation labels. Validate your reference answers before using them to gate releases.

Score RAG retrieval and generation separately: context precision and recall assess whether the right chunks came back and ranked first, while faithfulness assesses whether those chunks support every answer claim. Score agents on the final answer, tool trajectory and step count, so a correct answer reached in fifteen calls still counts against the design.

Review aids need testing too: users who could see an SQL assistant’s generated SQL were no more correct than users writing SQL by hand. Show the SQL, plain-language filters and joins, and source documents; route answers involving undefined columns or weak retrieval to an analyst instead of treating visibility as validation.

Small score movements can mislead: on a 1,534-question set, the 95% confidence interval spans about 2.5 points either way, so a two-point prompt or model improvement may be noise. Use the golden set as a release gate and add production misses to it.

How Does Airbyte Help With Chat With Your Data?

Airbyte supports chat with your data by replicating your sources on a schedule you set, so the freshness behind your answers is a sync-frequency decision. Airbyte’s 700+ connectors replicate databases, SaaS applications, APIs and files into Snowflake, BigQuery, Databricks, Redshift, Postgres, S3 and Iceberg. CDC replicates database changes without locking production tables; fixed intervals, cron and manual runs let you set freshness source by source. Schema-change detection and propagation expose changes your team must reconcile with metric definitions.

RBAC, audit logging and field hashing govern pipelines and let you hash sensitive fields before they reach destination tables. AI applications and dashboards can consume the same governed, replicated data, with MCP support providing an AI-client consumption path.

Airbyte Flex offers hybrid deployment when records must stay in your environment. Airbyte operates the control plane while the data plane, along with your records, credentials, keys and compute, runs in your environment, though some metadata such as cursor and primary-key values sits in the control plane. That means the records themselves stay with you, and the only residency question left is what that metadata exposes.

What Should Teams Fix Before Launching Chat With Your Data?

Assign owners to the definition, freshness and permission checks for each question type before launch. Give those owners authority to block expansion until additional sources and tools meet the same release criteria.

Support that launch scope with Airbyte replication through CDC and scheduled syncs, using Airbyte Cloud or Flex to match your deployment requirements. Use pipeline RBAC and audit logging alongside the warehouse and retrieval permissions your application enforces.

Get a demo to see how Airbyte keeps the data behind your AI answers current and governed.

Frequently Asked Questions

Do chat with your data tools send company data to model providers?

A hosted-model request can include your question, schema, retrieved passages and query results. Evaluate prompt and output handling, logging, retention, training opt-outs and feature-specific exceptions before approving that path. For questions that need no narrative synthesis, the model can generate SQL from the schema while your application executes it and renders the results without sending the rows to the provider.

Does chat with your data replace BI dashboards?

No, dashboards serve recurring, governed reporting, while chat handles ad hoc follow-up questions. Point both at the same semantic layer to reuse metric definitions, but compare their filters and data snapshots when values differ.

Do I need a vector database to chat with structured data?

You can query structured data through a read-only SQL connection without a vector database. Restrict the connection’s roles and sandbox execution before exposing generated queries. Embeddings can support free-text columns such as ticket bodies or product reviews, with SQL then filtering and counting the matches.

Can business users trust answers without knowing SQL?

Business users need a way to inspect the calculation without reading SQL. Pair the generated query with intermediate results and editable, plain-language explanations so users can identify an incorrect filter or grouping. Treat confidence indicators as prompts for review, not proof that the answer is correct.

How often should a RAG index be refreshed?

As often as each source changes. A ticket queue that changes hourly needs near-continuous updates, while a policy library that changes monthly can sync daily. Reindex incrementally by hashing each document’s content, re-embedding only what changed and propagating deletes so retired documents stop ranking. Track embedding lag, the time between a source update and its index update, alert when a source misses its window, and schedule a periodic full rebuild to catch drift the incremental runs miss.

Integrate with 700+ apps using Airbyte

Move data from 700+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.