Most enterprise AI projects for data start the same way: someone exports Confluence, dumps in a few Notion wikis, maybe pulls in some Slack threads, and calls it a knowledge base. Run a retrieval pipeline over it, hook up an LLM, and now the business can "ask questions about data." The demo works. Then three people use it in production and the answers are wrong.
This is not a model quality problem. It is a source-of-truth problem. And it points to a different architecture entirely.
The RAG approach and why it fails
The retrieval-augmented generation pattern is straightforward enough. You chunk your documentation, embed it, store the vectors, and at query time you retrieve the most relevant chunks and let the LLM synthesise an answer. For support tickets, product documentation, and legal FAQs, this works reasonably well.
For business data questions, it has a structural flaw: documentation describes what was planned or what was true at some point. It does not describe what exists in the warehouse right now. Docs get written during a project kickoff, updated once when someone files a complaint, and then drift quietly into irrelevance. The metric definition in your Notion page is probably the version from three strategy pivots ago — Notion pages don't get deprecated, they just quietly become historical fiction.
A user asks "what is our churn rate this quarter?" The RAG system retrieves a doc that says churn is calculated as cancelled MRR divided by starting MRR, churns out a confident answer, and nobody notices that the definition was revised six months ago to exclude downgrades from the numerator. The warehouse has the correct answer. The wiki does not. You indexed the wiki.
The real source of truth is the warehouse. The docs are a shadow of it, written by humans, maintained inconsistently, and always lagging.
The alternative: governed AI on structured data
Instead of teaching an LLM to synthesise answers from documentation, give it access to a parameterised query layer built on top of your tested, governed dbt models. The architecture is less exciting to describe but far more reliable in practice.
Think of it like a taxi dispatcher versus a stranger handed a rental car in a city they've never visited. The dispatcher doesn't need to know how to drive every possible route — they just match your request ("airport, please") to a driver who already knows that exact route cold. The rental-car stranger might get you there, might take a detour through a closed bridge, and might confidently turn the wrong way down a one-way street, all while sounding sure of themselves. Free-form SQL generation against a warehouse the model has never truly "driven" is the rental car. Intent classification against a pre-approved template is the dispatcher.
It works like this: the LLM classifies user intent, matches that intent to an allowlisted query template, fills in the parameters, executes the SQL against the warehouse, and presents the result. At no point does the LLM write SQL. It selects from a finite, reviewed set of templates.
import re
import snowflake.connector
QUERY_TEMPLATES = {
"monthly_churn_rate": """
SELECT
DATE_TRUNC('month', churned_at) AS month
, COUNT(DISTINCT customer_id) AS churned_customers
, SUM(mrr_lost) AS churned_mrr
, ROUND(SUM(mrr_lost) / NULLIF(SUM(starting_mrr), 0), 4) AS churn_rate
FROM mart_subscription_churn
WHERE DATE_TRUNC('month', churned_at) = DATE_TRUNC('month', '{period}'::DATE)
GROUP BY 1
""",
"new_mrr_by_segment": """
SELECT
segment
, SUM(new_mrr) AS new_mrr
, COUNT(DISTINCT customer_id) AS new_customers
FROM mart_new_business
WHERE DATE_TRUNC('month', closed_at) = DATE_TRUNC('month', '{period}'::DATE)
GROUP BY 1
ORDER BY 2 DESC
""",
"top_churned_accounts": """
SELECT
customer_id
, customer_name
, mrr_lost
, churn_reason
FROM mart_subscription_churn
WHERE DATE_TRUNC('month', churned_at) = DATE_TRUNC('month', '{period}'::DATE)
ORDER BY mrr_lost DESC
LIMIT {limit}
""",
}
def run_intent(intent: str, params: dict, conn) -> list[dict]:
if intent not in QUERY_TEMPLATES:
raise ValueError(f"Unknown intent: {intent}")
safe_params = {
k: int(v) if k == "limit" else str(v)
for k, v in params.items()
}
sql = QUERY_TEMPLATES[intent].format(**safe_params)
cursor = conn.cursor(snowflake.connector.DictCursor)
cursor.execute(sql)
return cursor.fetchall()
The LLM's job is narrow: given a natural language question, pick the right key from QUERY_TEMPLATES and extract the parameter values. That is a classification and extraction task, which LLMs handle well. Writing correct SQL against a schema the LLM has never seen is not a classification task — it is a code generation task with a high failure rate in edge cases.
You can implement the intent classification with a simple prompt that lists the available intents and their descriptions, asks the model to return a JSON object with intent and params, and validates the output before anything touches the database.
Why dbt makes this work
The pattern above is only trustworthy if the underlying models are trustworthy. This is where dbt earns its place in the stack.
Your mart models are documented and tested. The schema.yml descriptions are precise enough to drive the intent-matching prompt — you do not need a separate wiki for the LLM to understand what each template does. The descriptions live next to the models, which means they get updated when the model gets updated. That is a much smaller gap than keeping a Confluence page in sync.
# schema.yml
models:
- name: mart_subscription_churn
description: >
One row per churned subscription. A subscription is counted as churned
in the month the cancellation became effective. Downgrades are excluded —
see mart_subscription_downgrades for those. MRR figures use the final
contracted amount, not list price.
columns:
- name: customer_id
description: Surrogate key from dim_customers.
tests:
- not_null
- name: mrr_lost
description: Monthly recurring revenue lost at cancellation, in USD cents.
tests:
- not_null
- dbt_utils.expression_is_true:
expression: ">= 0"
- name: churn_reason
description: >
Categorised exit reason from CRM at close. Values: price, competitor,
product_gap, budget_freeze, unknown.
The description field on mart_subscription_churn above is specific enough that an LLM can correctly decide whether to route a "why did we lose revenue last month" question to this model versus mart_subscription_downgrades. That specificity comes from writing good dbt docs — something you should be doing anyway for your analysts.
dbt tests add a second layer of confidence. If the mrr_lost column starts returning negative values because someone changed the upstream transformation, the test catches it before the LLM surfaces a nonsensical answer to a stakeholder. The governance you built for your analytics engineering practice extends to the AI layer for free.
-- mart_subscription_churn.sql (simplified)
WITH source AS (
SELECT *
FROM {{ ref('stg_crm_subscriptions') }}
WHERE status = 'cancelled'
)
, enriched AS (
SELECT
s.subscription_id
, s.customer_id
, c.customer_name
, c.segment
, s.cancelled_at AS churned_at
, s.mrr AS mrr_lost
, s.cancellation_reason AS churn_reason
, b.mrr AS starting_mrr
FROM source s
LEFT JOIN {{ ref('dim_customers') }} c USING (customer_id)
LEFT JOIN {{ ref('mart_subscription_bookings') }} b
ON s.customer_id = b.customer_id
AND DATE_TRUNC('month', s.cancelled_at)
= DATE_TRUNC('month', b.created_at)
)
SELECT * FROM enriched
What it does not solve
This pattern has a hard boundary: it can only answer questions you have templates for. Ask something outside the template library and you get nothing useful. That is not a bug — it is the point. A system that answers every question, some of them incorrectly, is harder to trust than a system that answers a defined set of questions correctly. A confidently wrong answer is a worse outcome than "I don't have a template for that" — the second one at least doesn't end up screenshotted into a board deck.
The template library needs ongoing maintenance. When the business redefines a metric, the template changes. When a new reporting segment is introduced, a new template gets added. This is maintenance work, but it is the same maintenance work you are doing when you update your dbt models. You are not adding a new workflow, you are extending an existing one.
It does not replace an analyst. What it does is handle the high-volume routine questions — weekly churn, last month's new MRR by segment, top churned accounts — so that analysts spend their time on questions that actually require judgement. The analyst who was running the same Snowflake query every Monday morning for six stakeholders is now available to think about something harder.
Decision Tree: RAG, governed templates, or neither?
Takeaways
- Index the warehouse, not the wiki — documentation describes intent; the warehouse contains the actual state of your data, including all the definition changes your docs never caught up with.
- Keep the LLM out of SQL generation — use it for intent classification and parameter extraction, then execute pre-written, reviewed templates. The failure modes are far more predictable.
- dbt schema.yml descriptions are your LLM's knowledge base — write precise model and column descriptions and they do double duty: they orient your analysts and they drive your intent-matching prompts without a separate maintenance burden.
- Bounded coverage is a feature, not a limitation — a system that correctly answers 30 well-defined questions is more useful in practice than one that attempts any question and gets 20% of them wrong.
- The governance you already have extends to the AI layer — dbt tests, freshness checks, and column-level lineage mean your AI answers inherit the same quality gates as your dashboards.
What is one question your business asks on a weekly basis that you could template right now — and what has stopped you from automating it sooner?