Make your Snowflake data safe for AI agents — governed metrics, no raw SQL, no hallucinated joins.
A production-pattern implementation of a dbt MetricFlow semantic layer with Snowflake Horizon Catalog integration and a FastAPI agentic query interface.
A production-ready pattern for teams moving from "AI querying raw tables" to "AI querying governed, version-controlled business logic." Includes migration strategy for existing dbt projects, Horizon Catalog governance configuration, and a production readiness checklist.
This system lets AI agents query Snowflake using governed business metrics instead of raw tables.
Instead of an agent writing SELECT SUM(amount) FROM orders WHERE... and guessing at joins, filters, and business rules — it asks "What was revenue last month by region?" and gets back a structured, auditable answer grounded in metric definitions that your entire organisation has agreed on.
Every metric is defined once in dbt MetricFlow, registered in Snowflake Horizon Catalog, and served through a natural language API. All analytics — from BI dashboards to AI agents to ad-hoc queries — draw from the same governed source. No inconsistency. No metric drift. No surprises.
Most analytics stacks solve part of the problem. This system solves the whole thing.
| Approach | What It Does | What's Missing |
|---|---|---|
| Looker / Tableau | Governed metrics for dashboards | Static — agents can't query it; logic locked in LookML/calculated fields |
| dbt alone | Clean, tested transformations | No semantic layer; agents still hit raw marts and guess at business logic |
| Snowflake Cortex only | AI queries over Snowflake data | No governed metric layer; each query re-derives logic independently |
| This system | Governed MetricFlow metrics + Horizon Catalog + agentic API | — |
The key difference: business logic is defined once and enforced everywhere — in BI tools, in AI agents, and in ad-hoc queries — through a single version-controlled semantic layer. Not per-dashboard. Not per-prompt.
Question: "What was total revenue last month by region?"
Step 1 — Intent Resolution
User question → Hybrid router
Rule-based triage: "revenue" keyword detected → candidate: total_revenue
✓ Fast path resolved in <1ms
Step 2 — MetricFlow Query
Metric: total_revenue
Group by: region, metric_time (grain: month)
Filter: order_status != 'cancelled' ← enforced at definition level
MetricFlow generates SQL →
Step 3 — SQL Generated by MetricFlow
SELECT
DATE_TRUNC('month', order_date) AS metric_time__month,
region,
SUM(order_amount_usd) AS total_revenue
FROM analytics.marts.fct_orders
WHERE order_status != 'cancelled'
GROUP BY 1, 2
Step 4 — Snowflake Executes
Query runs against marts.fct_orders
Horizon Catalog logs: agent identity, metric accessed, timestamp
Step 5 — Structured Response
{
"metric_name": "total_revenue",
"display_name": "Total Revenue (USD)",
"time_grain": "month",
"group_by": ["region"],
"rows": [
{ "metric_time__month": "2024-01-01", "region": "EMEA", "total_revenue": 48320.00 },
{ "metric_time__month": "2024-01-01", "region": "APAC", "total_revenue": 39150.75 },
{ "metric_time__month": "2024-01-01", "region": "AMER", "total_revenue": 55360.25 }
],
"generated_sql": "SELECT ...",
"governance_note": "Resolved through governed MetricFlow definitions. Version-controlled in dbt. Registered in Snowflake Horizon Catalog."
}
Every step is logged. Every result is traceable back to a version-controlled metric definition.
User / Agentic System
(LangGraph · Cortex · Claude · Custom)
│
│ Natural language question
▼
┌─────────────────────────────────┐
│ Semantic Layer Agent │
│ (FastAPI) │
│ │
│ ┌───────────────────────────┐ │
│ │ Hybrid Intent Router │ │
│ │ │ │
│ │ Rule-based (fast path) │ │
│ │ keyword → metric │ │
│ │ ↓ miss │ │
│ │ LLM tool-calling │ │
│ │ (slow path, Claude) │ │
│ └───────────────────────────┘ │
└──────────────┬──────────────────┘
│ Resolved metric + dimensions
▼
┌─────────────────────────────────┐
│ dbt Semantic Layer API │
│ (MetricFlow) │
│ │
│ Semantic models · Metrics │
│ Entities · Dimensions │
│ SQL generated at query time │
└──────────────┬──────────────────┘
│ Generated SQL
▼
┌─────────────────────────────────┐
│ Snowflake │
│ marts.fct_orders │
└──────────────┬──────────────────┘
│
▼
┌─────────────────────────────────────────────────────┐
│ Snowflake Horizon Catalog │
│ (Governance Layer) │
│ │
│ Semantic Views · Access Policies │
│ Data Lineage · Agent Identity Logging │
│ MCP Interface · Metric Discoverability │
│ │
│ Every agent query is logged with: │
│ - agent identity (human vs. AI) │
│ - metric accessed │
│ - timestamp and row-level access decision │
└─────────────────────────────────────────────────────┘
snowflake-semantic-agent/
│
├── dbt_project/
│ ├── dbt_project.yml
│ └── models/
│ ├── staging/
│ │ ├── stg_orders.sql # Typed, renamed raw orders
│ │ └── stg_customers.sql # Typed, renamed raw customers
│ ├── marts/
│ │ ├── fct_orders.sql # Orders fact table — semantic anchor
│ │ └── metricflow_time_spine.sql # Required MetricFlow time axis
│ └── semantic/
│ └── sem_orders.yml # ★ MetricFlow semantic model + all metrics
│
├── agent/
│ └── main.py # FastAPI semantic layer agent
│
├── docs/
│ ├── horizon_catalog_semantic_views.yml # Horizon Catalog semantic view definitions
│ ├── migration_guide.md # How to adopt MetricFlow on an existing dbt project
│ └── production_readiness.md # Pre-launch checklist for production deployment
│
├── .env.example
└── requirements.txt
sem_orders.yml is the core of the project — a MetricFlow semantic model that defines the single source of truth for all order metrics.
Entities (how MetricFlow traverses relationships at query time):
order(primary),customer(foreign),product(foreign)
Measures (aggregatable facts):
revenue—SUM(order_amount_usd)order_count—COUNT_DISTINCT(order_id)units_sold—SUM(order_quantity)customers_with_orders—COUNT_DISTINCT(customer_id)
Dimensions (slicing attributes):
order_date(time),region,acquisition_channel,order_status,customer_plan_type,customer_country
| Metric | Type | Description |
|---|---|---|
total_revenue |
Simple | Total confirmed order revenue (USD) |
order_volume |
Simple | Count of confirmed orders |
units_sold |
Simple | Total units shipped |
active_customers |
Simple | Distinct customers with confirmed orders |
average_order_value |
Ratio | total_revenue / order_volume |
revenue_per_customer |
Ratio | total_revenue / active_customers |
cumulative_revenue_mtd |
Cumulative | Month-to-date revenue, resets monthly |
cumulative_orders_mtd |
Cumulative | Month-to-date order count |
revenue_growth_wow |
Derived | Week-over-week revenue growth rate |
Every metric filters cancelled orders at definition level — not at the dashboard, not in the agent prompt. The business rule lives in one version-controlled place.
Metric definitions are registered as Semantic Views in Snowflake Horizon Catalog — making metrics discoverable and queryable by both humans and AI agents from the same trusted source.
| Semantic View | Metrics | Consumer |
|---|---|---|
sv_revenue_metrics |
Revenue, AOV, WoW growth, MTD | Finance, executive agents |
sv_customer_metrics |
Active customers, revenue/customer | CRM, churn agents |
sv_operational_metrics |
Order volume, units, MTD orders | Ops, supply chain agents |
Agents connect via the Model Context Protocol (MCP) — a standard interface that lets any agent framework query governed Semantic Views without custom integration per system. Horizon Catalog enforces access policy, logs agent identity, and distinguishes human vs. AI queries.
See docs/horizon_catalog_semantic_views.yml for the full definitions.
The agent uses a hybrid routing strategy designed to balance latency and coverage:
| Path | Trigger | Latency | Example |
|---|---|---|---|
| Rule-based (fast) | Unambiguous keyword match | <1ms | "What is revenue by region?" |
| LLM tool-calling (slow) | No keyword match; ambiguous intent | ~1–2s | "How are our top customers trending vs. last quarter?" |
| Fallback | LLM cannot resolve | — | Returns full metric catalog for re-prompting |
The LLM slow path uses Claude with the metric catalog exposed as tools — the model selects the right metric, dimensions, and filters through structured tool-calling, not free-text generation. This keeps outputs well-formed even for complex questions.
Deploying this architecture delivers measurable value across three areas:
Consistency — Revenue reported in Salesforce, your finance dashboard, and your AI agent's weekly summary will be the same number. Metric definitions are version-controlled and shared.
Analyst capacity — The long tail of metric queries ("What's MTD revenue for EMEA pro-tier customers?") is handled by the agent layer. Analysts focus on questions that require judgment, not SQL.
AI readiness — Agents that query raw tables are fragile and unauditable. This architecture makes your Snowflake data agent-safe: governed, logged, and traceable to a specific metric version.
This repository includes two documents that address the real concerns of deploying a semantic layer in an existing enterprise environment.
Migration Guide — How to introduce MetricFlow into an existing dbt project without disrupting current pipelines. Covers the full phased approach: audit, time spine, first semantic model, metric migration from legacy dbt metrics, Horizon Catalog registration, and agent consumption. Includes a table of common org constraints (multiple dbt projects, OSS dbt Core, Snowflake permission issues) and how to handle each.
Production Readiness Checklist — Eight areas to sign off before connecting agentic systems to a production Semantic Layer: semantic model correctness, metric parity validation, governance configuration, API security, agent safety, versioning, monitoring, and rollback readiness. Built to be used as an actual gate, with a sign-off table per area.
# 1. Clone and install
git clone https://github.com/HenryMorganDibie/snowflake-semantic-agent
cd snowflake-semantic-agent
pip install -r requirements.txt
# 2. Configure environment
cp .env.example .env
# Fill in: DBT_SL_TOKEN, DBT_ENVIRONMENT_ID, Snowflake credentials
# 3. Run dbt
cd dbt_project
dbt deps && dbt build
# 4. Validate MetricFlow semantic models
mf validate-configs
mf query --metrics total_revenue --group-by metric_time__month
# 5. Start the agent
cd ..
uvicorn agent.main:app --reloadRunning without credentials: the agent degrades gracefully — all endpoints respond with stub data so you can explore the API structure without a live Snowflake connection.
Hybrid intent routing over pure LLM routing Pure LLM routing adds 1–3 seconds of latency to every query. Rule-based triage handles the ~80% of queries that are unambiguous; LLM tool-calling handles the rest. p50 latency stays low while coverage remains high.
MetricFlow over warehouse-native metric definitions MetricFlow definitions travel with the dbt project. If the warehouse changes, the business logic doesn't.
generated_sql always returned
Agentic systems that can't be audited don't get deployed. Returning the MetricFlow-generated SQL closes the loop between the natural language question and the data that answered it.
Governance in Horizon Catalog, not the application layer Access policies, agent identity logging, and data classification live in Snowflake Horizon Catalog — not in application code that can be bypassed. Governance is infrastructure, not middleware.
- NaijaFinAI — Nigerian-context fraud intelligence agent (FastAPI + React)
- knowledge-rag-api — Hybrid RAG with pgvector, BM25, and RRF reranking
- Deal Intelligence Agent — 8-node LangGraph pipeline for PE/credit signal detection
Henry Dibie — ML Systems Engineer & Data Scientist LinkedIn · GitHub · Medium