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.


RAG on docs vs. governed warehouse — architecture comparison RAG ON DOCS GOVERNED WAREHOUSE User question Confluence / Notion docs Embed + vector search LLM synthesises answer Answer from stale docs definition may be months out of date vs User question LLM: classify intent only no SQL generation Query template allowlist pre-reviewed · parameterised only dbt mart (Snowflake) tested · documented · current Answer from live warehouse data same definition as your dashboards
RAG routes through documentation. Governed warehouse routes through tested, live models.

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?

Question is about policy, process, or "how do I..." RAG over docs is fine — documentation is the actual source of truth here, not the warehouse.
Question is about a metric the warehouse already certifies Route it through an allowlisted query template against a tested dbt mart — never let the LLM write the SQL.
Question doesn't match any existing template Say so. Bounded coverage beats a confident guess against a schema the model has never really driven.
A new recurring question shows up often enough to matter Add it to the template library — this is dbt-model maintenance, not a new workflow.
Your schema.yml descriptions are vague or missing Fix those first — they're the same descriptions driving your intent-matching prompts.

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?