RAG Over Warehouse Data: Text-to-SQL, Curated Marts, and the Guardrails Between Them

“Chat with your data” demos beautifully and fails quietly. The demo question is “how many orders last month” against a five-table schema. The production question is “what was net revenue for enterprise accounts in EMEA excluding trials” against 400 tables where three columns are called revenue and none of them are net revenue.

I have shipped RAG over warehouse data twice now. The first version was raw text-to-SQL and it answered confidently and wrongly. The second one worked, because we stopped asking the model to reverse- engineer our warehouse and gave it a semantic model to compile against instead.

This post is the architecture that survived: when to generate SQL, when to retrieve from curated marts instead, and the guardrails that belong in front of both. The general retrieval mechanics live in RAG pipelines for data engineers; this is the warehouse-specific part.

flowchart LR
  q[User question] --> router{"Aggregation or lookup?"}
  router -->|aggregation| sem[Semantic model]
  sem --> sql[Generated SQL]
  sql --> guard[Guardrails and read-only role]
  guard --> wh[Warehouse]
  router -->|lookup| ret[Retrieval over curated marts]
  ret --> ans[Grounded answer]
  wh --> ans

Two paths, one router. Aggregations compile against the semantic model; lookups retrieve from marts.

Why raw text-to-SQL fails on real schemas

The model does not fail at SQL. Modern LLMs write better window functions than most analysts. It fails at three things your schema does not encode.

Join paths. orders joins to customers on customer_id, except the historical rows that need legacy_customer_map. Nothing in INFORMATION_SCHEMA says that. The model picks the plausible join and returns numbers that are 4% off — the worst kind of wrong, because nobody notices for a quarter.

Metric definitions. “Revenue” in my last warehouse meant gross_amount - refunds - credits, recognized on ship date, not order date. That definition lived in a dbt model and two heads. A model given raw DDL will SUM(amount) and call it revenue.

Column semantics. status = 'closed' means won in the CRM schema and cancelled in the fulfillment schema. Same word, opposite meanings, one prompt.

Dumping 400 CREATE TABLE statements into context does not fix any of this. It makes it worse: more plausible-looking columns to pick badly from. Schema is not semantics.

The semantic model is the actual product

The fix is to stop generating SQL against tables and start generating against a semantic model: a curated YAML (or equivalent) document that names the metrics, the dimensions they can be sliced by, and the exact SQL for each. The model’s job shrinks from “invent a query” to “select and combine verified building blocks.”

Mine looks like this, and it is versioned in the same repo as the dbt project because it is the same contract — see data contracts for pipeline teams for why that co-location matters:

semantic_model:
  name: finance_core
  base: analytics.gold.fct_order_lines
  entities:
    - name: customer
      join: analytics.gold.dim_customer on customer_sk
  metrics:
    - name: net_revenue
      description: Gross minus refunds and credits, ship-date recognized.
      expr: sum(gross_amount - refund_amount - credit_amount)
      grain_date: shipped_at
    - name: order_count
      expr: count(distinct order_id)
  dimensions:
    - name: region
      expr: dim_customer.region
      values: ['AMER', 'EMEA', 'APAC']
    - name: segment
      expr: dim_customer.segment
  filters:
    - name: exclude_trials
      expr: dim_customer.is_trial = false

The prompt then contains this document, not the DDL. The model maps “net revenue for enterprise in EMEA excluding trials” to net_revenue + segment = 'Enterprise' + region = 'EMEA' + exclude_trials. If a question needs a metric that is not in the model, the correct behavior is to say so, not to improvise.

This is the same idea Snowflake ships as Cortex Analyst and dbt ships as the Semantic Layer. Build it once regardless of vendor. The YAML is the asset; the LLM is the interchangeable part.

When retrieval beats SQL generation

Half the questions people ask a “chat with your data” bot are not aggregations. “What is the SLA in the Acme contract?” “Why did the Jenkins account churn?” “What did the CSM note on the last renewal call?” There is no GROUP BY that answers these.

For those, generate nothing. Embed the curated text — account notes, ticket summaries, contract clauses — retrieve top-k, and answer from the retrieved chunks with citations. The pipeline for building that index is a normal data pipeline and I treat it like one; the mechanics are in embedding pipelines in production and the store comparison is in vector databases comparison.

The trap is retrieval over raw tables. Embedding every row of a 50-million-row fact table is expensive and useless: a fact row is not a passage, it is a coordinate. Retrieve over curated, denormalized, sentence-shaped marts. I build a specific mart for it:

create or replace table analytics.gold.account_briefs as
select
  c.customer_id,
  c.customer_name,
  concat_ws(' ',
    'Account:', c.customer_name,
    'Segment:', c.segment, 'Region:', c.region,
    'ARR:', to_varchar(round(m.arr, 0)),
    'Renewal:', to_varchar(m.renewal_date),
    'Health:', m.health_status,
    'Open tickets:', to_varchar(m.open_tickets),
    'Last QBR notes:', coalesce(n.latest_qbr_summary, 'none')
  ) as brief_text,
  current_timestamp() as built_at
from analytics.gold.dim_customer c
join analytics.gold.customer_metrics m using (customer_id)
left join analytics.gold.customer_notes_agg n using (customer_id)
where c.is_active;

One row per account, written as prose, rebuilt nightly. That table embeds well, retrieves well, and — critically — inherits the tests and lineage of the dbt project that builds it.

The router between the two paths does not need to be clever. A cheap classification call (“does this question require computing a number from many rows, or looking up facts about specific entities?”) routes correctly well over 95% of the time in my logs.

Guardrails: the part you cannot skip

Whatever generates the SQL, the SQL runs under constraints. All of them, from day one.

create role if not exists rag_reader;

grant usage on database analytics to role rag_reader;
grant usage on schema analytics.gold to role rag_reader;
grant select on all tables in schema analytics.gold to role rag_reader;
grant select on future tables in schema analytics.gold to role rag_reader;

create warehouse if not exists rag_wh
  warehouse_size = 'xsmall'
  auto_suspend = 60
  statement_timeout_in_seconds = 30;

grant usage on warehouse rag_wh to role rag_reader;

alter user rag_service set
  default_role = rag_reader,
  default_warehouse = rag_wh;

Gold schema only. No bronze, no PII schemas, no ACCOUNTADMIN anywhere near it. The application layer adds three more checks before execution: the statement parses as a single SELECT (reject anything with DDL, DML, or semicolons), a LIMIT is appended if absent, and every query carries a tag like QUERY_TAG = 'rag:session_id' so I can attribute cost and audit what the bot actually ran. The deeper security story — prompt injection blast radius included — is in agent access to the warehouse.

The last guardrail is evaluation. Before launch I wrote 60 golden questions with known-correct answers, computed by hand or by existing dashboards. Every prompt change, semantic model change, or model upgrade reruns the set. The first run scored 41 of 60. That number, not the demo, told us we were not ready.

flowchart TD
  gen[Generated SQL] --> parse{"Single SELECT, no DML?"}
  parse -->|no| reject[Reject and log]
  parse -->|yes| limit[Append LIMIT and query tag]
  limit --> run["Run as rag_reader, 30s timeout"]
  run --> cite[Return rows plus the SQL itself]

The SQL is shown to the user. Hiding it converts a wrong answer into a trusted wrong answer.

Always show the generated SQL. Analysts catch bad joins in seconds when they can see them. A hidden query is an unauditable one.

Pitfalls

Dumping DDL into the prompt. Schema is not semantics. 400 tables of CREATE TABLE gives the model more ways to be plausibly wrong, not fewer.

Skipping the router. Text-to-SQL asked “why did this account churn” writes a query anyway. Retrieval asked “sum revenue by month” hallucinates a number from three chunks. Route first.

Embedding fact tables row by row. A fact row is a coordinate, not a passage. Build a prose-shaped mart and embed that.

Trusting the demo over the golden set. Ten cherry-picked questions prove nothing. Sixty questions with verified answers, rerun on every change, is the minimum bar.

One service account with broad grants. The day someone prompt- injects your bot, its role is your blast radius. Gold schema, read-only, timeout, tag.

Letting the semantic model rot. A metric renamed in dbt but not in the YAML is a silent wrong answer. CI should diff both.

FAQ

Should I fine-tune a model on my schema instead? No. Fine-tuning bakes today’s schema into weights that are stale by next sprint. The semantic model is data, versioned in git, updated in the same PR as the dbt change. Context beats weights for facts that change.

Text-to-SQL or retrieval — if I can only build one first? Retrieval over curated marts. It fails safe: a bad retrieval returns an irrelevant passage the user can see is irrelevant. Bad SQL returns a confident number. Ship the safe failure mode first.

How big does the semantic model get before it stops fitting in context? Mine covers about 30 metrics and 25 dimensions in under 6k tokens. If yours is bigger, retrieve the relevant slice of the semantic model first — RAG over the semantic model itself works well.

Do I need a vector database for the warehouse RAG part? Not necessarily. If your warehouse has native vector search (Snowflake, BigQuery, Postgres with pgvector), keeping embeddings next to the data avoids a sync pipeline. The tradeoffs are in vector databases comparison.

What accuracy is good enough to launch? Depends who reads the answer. For internal analysts who see the SQL, I shipped at 85% on the golden set. For anything customer-facing, I would not ship generated aggregations at all — only retrieval with citations.

What this means for data engineers

RAG over warehouse data is a data modeling problem wearing an AI costume. The semantic model is a contract, the curated mart is a gold table, the golden question set is a test suite, and the read-only role is the same least-privilege discipline you already apply to service accounts.

The LLM is the smallest part of the system and the easiest to swap. Spend your time where the failures actually happen: join paths, metric definitions, and the guardrails around execution. Build the YAML before you build the chat window.