The AI Layer
An implementation guide for the reasoning layer that sits on top of the data warehouse and turns it into a self-service analytics and reporting tool.
A data warehouse gives the business clean, governed data. The AI layer is what lets anyone ask a question in plain language and get back a trustworthy answer - without waiting on the data team to write a query.
An AI model connected to the warehouse can already write fluent queries. What it needs is not a smarter model, but the right structure underneath it - built directly on top of the warehouse. This guide lays out what to build, in priority order.
Where This Layer Sits
The AI layer does not replace the warehouse. It is one component built on top of it. The warehouse does not change.
What to Build, In Priority Order
Step 1 - Clean, Consistent Data Foundation
Organise underlying tables into clean, consistently named fields with one clear grain per table. One row per event, not a mix of daily and monthly rollups.
What this looks like:
- Rename ambiguous columns (
val1-subscription_amount) - Standardise date and currency formats across every source table
- Ensure every fact table has one single, well-defined grain
Why it matters: Consistent tables let the model read data correctly. It can then focus on getting the business logic right rather than untangling the schema.
Step 2 - Governed Semantic Layer
Write official, locked-in definitions for every key metric. The exact rule for "active user," "activation," MRR, and any population that must be excluded. Once encoded, the model calls the definition directly instead of calculating it from scratch.
What this looks like:
active_users:
definition: distinct users with at least one session in the period
excludes: staff accounts (domain = @company.com), test regions (region = 'test')
activation:
definition: user reached first value event within 7 days of signup
grain: one row per user, not per session
mrr:
definition: total contracted annual value / 12
note: annual plans divided evenly, not revenue-recognised
Can be built as a dedicated semantic layer tool (dbt Semantic Layer, Cube, Looker LookML), a dbt model, or a well-built view per metric.
Why this is the highest-priority step: A governed definition means the number is correct by construction, every time it is asked for. One definition reused and sliced many ways - no rebuilding logic for every new question.
Step 3 - A Library of Verified Examples
Approved question - correct query pairs for business knowledge that a metric definition cannot capture on its own. Things like which channels are internal test traffic, or which regions have pre-launch data to exclude.
What this looks like:
| Question | Approved query intent |
|---|---|
| "What was real acquisition spend last quarter?" | Excludes internal partnerships test channel |
| "What's retention for the May cohort?" | Excludes pre-launch test cohort in that region |
| "How many active users signed up via organic?" | Applies the governed active_users definition, filters source = organic |
Why it matters: Verified examples deliver business context in a way the model can apply consistently - the same way every time. The most reliable way to encode nuanced knowledge that would otherwise only live in a person's head.
Step 4 - A Metric Tree
Map how core metrics break down into their parts, and how they drive one another. An AI engine can then decompose a change through this map and surface the likely cause automatically.
Two relationship types:
- Identity relationships (arithmetic): total engagement = users x frequency x depth
- Driver relationships (business dynamics): reminder frequency drives session frequency, which drives value moments
Why it matters: This turns the AI from a reporting tool into a diagnostic one - able to answer "why did this number move," not just "what is this number." Collapses what would otherwise be a long, manual investigation into a single decomposition.
Supporting Practices
- Restrict model access to governed metrics once this structure is in production. Use MCP to expose only the approved metric catalogue - not raw, ungoverned table access.
- Match model to task: lighter models handle day-to-day reporting well once the structure is in place. Reserve the most capable model for diagnostic "why" questions where reasoning depth matters.
- Build in sequence, not in parallel: semantic layer first, then verified examples, then the metric tree. Each layer depends on the one before.
- Prefer governed metrics over written rules: wherever a rule can be expressed as a metric definition or a verified example, do that - it is more durable and more consistent than a paragraph of documentation the model has to interpret.
Relationship to Other Patterns
| Pattern | How it connects |
|---|---|
| Text-to-SQL | The AI layer is the prerequisite for reliable Text-to-SQL. Without governed metrics and verified examples, Text-to-SQL produces technically correct but semantically wrong queries. |
| MCP | Use MCP to expose the governed metric catalogue as a structured tool interface - restricts model to approved definitions only. |
| RAG | Verified examples are a form of retrieval - the model retrieves the right business context before generating a query. |
| Evaluation and monitoring | The metric tree drives evaluation: a regression in "active users" traces directly to which definition or example broke. |
Key Takeaways
- The semantic layer is the highest-priority step. A governed metric definition means the number is correct by construction - the model cannot get it wrong because it calls the definition, not its own inference.
- Verified examples are the most reliable way to encode business context. Written rules degrade; approved query pairs do not.
- The metric tree is what separates a reporting tool from a diagnostic one. "Why did MRR drop?" requires a map of how metrics relate, not just a query engine.
- Restrict model access to the governed layer in production. Unrestricted table access means every question is a chance to get the business logic wrong.
- Build in sequence. A metric tree on top of an inconsistent schema produces confidently wrong answers.