A chatbot that can find a sales deck isn’t automatically qualified to answer a revenue question. Retrieval-augmented generation works with tabular data when the system treats a database as an executable source of record, not a pile of text to embed.
If you turn rows into plain-language chunks, exact filters, joins, currencies, and dates can disappear. I use retrieval to identify business meaning, then use governed queries to calculate the answer.
That distinction supports factual accuracy, helping an assistant produce a reproducible business answer instead of merely sounding informed.
Key Takeaways
- Treat databases as executable sources of record: use retrieval to identify business meaning, then use governed SQL for filters, joins, definitions, and arithmetic.
- Route each question through a document path, SQL path, or both; table augmented generation is useful for mixed questions that combine calculations with explanatory context.
- Build a business-aware schema catalog, combine dense and sparse retrieval, and use reranking to select approved tables and definitions—not to determine financial truth.
- Validate every generated query, enforce database-level permissions, and keep arithmetic, freshness, and access decisions inside governed systems.
- Evaluate structured-data assistants against real business failures, measuring schema selection, SQL validity, numeric accuracy, freshness, permissions, latency, and cost.
Why table retrieval needs a different plan
Document RAG can answer, “What does our travel policy say about hotel limits?” A table question is closer to, “What did the Midwest team spend on hotels last quarter, excluding canceled trips?”
The first needs a supporting passage. The second needs agreed definitions, filters, joins, and executed math.
A table contains relationships, not only words
A row gets meaning from its table, column names, primary key, foreign keys, and data type. A value of 125 means little without its currency, metric definition, date grain, and customer or product relationship.
In relational databases, another constraint applies. The answer may require a join across orders, refunds, accounts, territories, and a metrics table. Vector search can retrieve relevant descriptions or candidate records, but it can’t validate an aggregate.
Flattening every row into a sentence can help with record discovery. It is a poor default for aggregate reporting or questions with strict conditions.
Embeddings cannot perform audited calculations
Semantic search is useful when a user says “lost customers” and the schema calls the metric churned_accounts. It improves semantic precision when matching business language to formal fields, but it doesn’t prove the metric or join is correct.
After initial semantic retrieval, cross-encoder reranking can improve candidate table or column selection.
It should not decide financial calculations by itself. An embedding has no reliable concept of a SUM, a date boundary, or the difference between gross and net revenue.
A high similarity score can still select the wrong row and omit the calculation rules needed for the answer.
A reliable system retrieves evidence and uses query tools to produce the numbers.
Choose the answer path before retrieving data
The strongest systems don’t force every question through vector search. They classify each question first, then choose a document path, a SQL path, or both. Retrieval-augmented generation fits policy questions; SQL handles filters, joins, definitions, or arithmetic.
Use table augmented generation for mixed questions
Search works well for policies, field definitions, support notes, and unstructured explanations. SQL is appropriate for structured queries involving counts, totals, rankings, filters, and time comparisons. After routing, cross-encoder reranking can refine candidate tables or schema descriptions before query generation.
A user asking why renewal risk increased may need both. The system can run a query to identify affected accounts, then retrieve account notes or policy changes that explain the pattern.
Table augmented generation, often called TAG, gives the model structured table context and a controlled way to query tabular data. It keeps calculations inside the database engine, where results are computed rather than inferred from retrieved passages.
A recent research overview of RAG for heterogeneous enterprise data reaches the same practical conclusion: enterprise answers often need more than one retrieval method.
| Question pattern | Best source path | Useful response |
|---|---|---|
| Find a policy exception | Document retrieval | Quoted policy passage |
| Revenue by region in Q2 | Governed SQL | Result, filters, and query time |
| Explain a spike in returns | SQL plus documents | Aggregate with related notes |
| Find accounts with a known issue | Filtered record retrieval | Authorized account list |
The useful answer path depends on what must be exact.
Model tables as data products, not chunks
A database schema is not enough context for large language models. Raw DDL often exposes names such as fct_ord_ln or rev_adj_amt without explaining the business rules behind them, so table augmented generation needs a business-aware catalog.
Build a schema catalog the model can retrieve
I start with a catalog that serves as a knowledge representation for each approved table, recording its grain, owner, update cadence, primary keys, allowed joins, data classification, and column definitions. If the assistant also inspects CSV exports or JSON-like records, the catalog can cover structured and semi-structured sources too.
It should answer questions such as: Is revenue booked or billed? Does customer mean a billing account or a parent account? Are refunds stored as negative transactions or separate records?
If a semantic layer or dbt metric definition already exists, use it as a source of truth. The model should retrieve a small, relevant schema slice. Use cross-encoder reranking to prioritize catalog entries after retrieval, not to determine business truth.
Index rows only when record discovery is needed
Row-level indexing is useful for record discovery in tabular data, support tickets, CRM accounts, product catalogs, and case records. Each indexed item should retain its table name, primary key, permission tags, timestamp, and the fields needed for retrieval.
I don’t embed every row in a large fact table by default. That creates cost, stale indexes, and weak recall for queries that SQL handles better.
CSV files need the same discipline. Before using an AI assistant over exports, check types, duplicate IDs, date formats, missing values, and header quality. My guide to AI tools for analyzing CSV files covers the practical differences between flexible file analysis and traceable SQL-first workflows.
If a column’s business meaning is unclear to an analyst, it is unclear to the model too.
Combine dense and sparse signals with hybrid retrieval around the schema
Dense vectors capture business synonyms and improve semantic precision. Sparse search protects exact identifiers, SKUs, account codes, and regulatory terms.
Retrieve candidates on two signals
BM25 or another sparse method protects exact-match recall. Dense search handles questions such as “customers who stopped buying” when the schema uses inactive_buyer_flag.
Run both methods against the schema catalog, table descriptions, glossary, and eligible record summaries. Reciprocal rank fusion is a practical starting point because it combines rankings without assuming vector and BM25 scores share a natural scale. Cross-encoder reranking can then inspect a small fused set of table, column, glossary, or record-summary candidates.
For a closer look at the mechanics, see my hybrid search RAG guide.
Filter before reranking the results
Apply tenant, department, region, product, document-version, and similar constraints through metadata filtering before retrieval and reranking. Cross-encoder reranking can’t repair an unauthorized candidate set, so enforce these constraints first.
A reranker can then inspect the user’s question against a small set of candidate tables, columns, or row summaries. I use it to improve schema selection, not to replace query validation.
These filters also cut noise. A question about current contracts should not rank archived contract definitions merely because their wording is similar.
Generate SQL within a narrow contract
text-to-SQL and table augmented generation are valuable only inside a defined contract. Giving a model unrestricted access to data warehouses is not a governance contract.
Give the model approved definitions and joins
Cross-encoder reranking can narrow approved schema candidates before SQL is generated. It does not replace validation.
I convert the question into a structured intent for query generation. Its inputs include the requested metric, dimensions, date range, filters, expected grain, and user identity.
Then I provide only the approved tables, columns, join paths, and a few verified query patterns. A business glossary matters here. “Active customer” may have a formal definition that differs from “customer with a recent order.”
Teams testing products in this category should compare AI-powered SQL generation tools on schema awareness, query visibility, and governance, not on polished chat responses alone.
Parse and validate every generated query
Generated SQL should pass machine checks before execution:
- Permit read-only query types and block DDL, DML, external functions, and multi-statement requests.
- Validate table names, columns, joins, and functions against an approved allowlist.
- Parameterize user values instead of concatenating them into SQL strings.
- Apply query optimization, row limits, timeouts, warehouse cost limits, and query-plan checks where the database supports them.
- Return generic user-facing errors while keeping detailed diagnostics in protected logs.
The system can repair a failed query, but it should not keep retrying against production with broader access or a longer context window.
Treat numeric questions as execution tasks
In table augmented generation, numerical reasoning errors often begin when a model calculates from retrieved text. Delegate arithmetic to the database engine instead of asking it to add 200 values manually.
Keep arithmetic inside the query engine
Use SQL for sums, averages, rankings, percentage changes, and date logic across tabular data. Keep decimal precision, currency conversion rules, and time-zone handling in the approved data model.
The language model can explain the result after execution. It can say that return volume rose 14% month over month, but the percentage must come from a validated query or governed metric layer.
This execution-first design is non-negotiable in finance, operations, inventory, and performance reporting.
Show enough evidence to audit the answer
A business user should see the source tables, metric definition, applied filters, query timestamp, and data freshness timestamp. Provide the generated SQL when the audience can use it, or provide a plain-language query explanation and query ID.
Rounding also needs visibility. A dashboard may show $1.2M, while the underlying result is $1,184,392.17. That isn’t a cosmetic detail when the answer goes into a forecast or board deck.
Keep operational data current without full reindexing
Real-time data makes a static vector index stale quickly when orders, cases, inventory, or account status change every minute. The canonical database remains the source of truth.
Update only the affected records and metadata
Use CDC to identify inserts, updates, and deletes. A CDC event can refresh a record summary, remove an obsolete vector, update permissions metadata, or trigger a catalog refresh when a source table changes.
Apache Kafka’s event-streaming documentation describes this model as capturing events from sources such as databases and services. For teams planning the ingestion layer, this change data capture primer is useful for understanding how source changes reach downstream systems.
Don’t re-embed an entire corpus because one order status changed.
Make freshness visible in every answer
Define the freshness contract by question type. A monthly finance report may tolerate a warehouse refresh window. A fulfillment exception may need live operational data.
Every response should show its “as of” time, freshness timestamp, and replica or cache status. If the system reads from a delayed warehouse table, state its refresh window. Silence around freshness turns accurate queries into misleading business answers.
Put access control where the data lives
Prompt instructions such as “only show my team’s data” mark a data governance boundary, but they aren’t security controls. They are requests to a probabilistic model.
Pass user identity through the query path
The request needs a user identity, tenant, role, and allowed scope. Metadata filtering can narrow retrieval candidates using tenant, role, department, region, and allowed-scope metadata. The database must enforce the final row-level decision.
BigQuery’s row-level security model, for example, uses policies that filter rows for approved grantees through a SQL filter expression.
The model may propose a query, but the database must decide whether that query can return a row.
This protects against prompt injection, retrieval mistakes, and a model that generates valid SQL for the wrong audience.
Contain execution and control exports
Use read-only service accounts, short-lived credentials, query quotas, timeouts, and audit logs. Capture the user request, generated SQL, policy decision, query ID, row count, and export action.
Protected BigQuery tables require the right data and filtered-data permissions, as outlined in its row-level security management guidance. Do not bypass that enforcement with a privileged chatbot account.
Check platform limits too. BigQuery documents that row-level policies do not apply to JSON columns in its security feature limitations. Sensitive fields hidden in JSON need another protection plan.
Test the system against business failures
A happy-path demo doesn’t tell you whether a structured-data assistant is safe. I test questions that could create bad decisions or expose unauthorized data.
Build an evaluation set from real requests
Use approved, anonymized questions from finance, sales, support, and operations. Include exact lookups, fuzzy business terms, multi-table joins, date boundaries, missing data, conflicting definitions, and prohibited requests.
For each case, record the expected tables, allowed join path, expected result or result range, required citation details, and whether the system should refuse the request.
Test with fresh data and controlled snapshots. Both matter.
Score stages, not only the final sentence
Track schema-selection accuracy, cross-encoder reranking quality, SQL validity, execution success, factual accuracy, numeric accuracy, freshness, permission failures, latency, and query cost. A fluent answer can hide a wrong join or an outdated table.
I also review failed answers by category. Was the wrong table retrieved? Did the model misunderstand the metric? Did the validator block a valid query? Each failure points to a different fix.
Re-run the evaluation set after schema changes, model changes, policy updates, and retrieval changes. Production traffic will find edge cases that a demo never touches.
The reliable answer is a controlled query
The best business intelligence assistant is deliberately boring. It maps each request to approved definitions, authorized data, validated queries, and visible evidence.
I don’t judge RAG for structured data by chat fluency. I judge it by whether finance or operations teams can reproduce the result, understand its scope, and spot missing data before acting on it.
A useful answer includes the math, the source, the freshness, and the access boundary.
Frequently asked questions
Can traditional vector search query relational tables?
It can locate related table descriptions, row summaries, and business definitions. It isn’t reliable for exact counts, joins, filters, or calculations. Use it to select candidates, then execute SQL against approved tables.
What is the difference between RAG and table augmented generation?
Traditional RAG retrieves text passages for the model to use in an answer. TAG adds structured context and database tools, allowing the system to query tables and return calculated results with supporting context.
How can teams reduce inaccurate SQL generation?
Limit the schema context, provide approved definitions and joins, parse generated SQL, enforce allowlists, use read-only credentials, and execute queries under database-level permissions. Log every query and evaluate real business questions before broad release.
Suggested related articles
