horizon-mcp-demo
A working demonstration of a governed semantic layer exposed via MCP, built on dbt Core and DuckDB. Models a P&C insurance company with policies, claims, and earned premium data.
Built as research and reference material for the Horizon Data Partners white paper: The Governed Data Layer: Why AI Agents Fail Without One, and How to Build It.
What this demonstrates
- A governed semantic layer — dbt models with enforced metric definitions for loss ratio, earned premium, incurred loss, and claim frequency
- A custom MCP server — a lightweight Python server that reads dbt artifacts (
manifest.json) and exposes four governed tools to any MCP-compatible agent - An agent demo — Claude querying the semantic layer via MCP, answering business questions using governed metric definitions rather than raw tables
The key architectural point: the agent never queries raw tables directly. It queries governed definitions. It cannot hallucinate a metric definition that isn't in the model.
Architecture
Claude (agent)
│
│ MCP protocol (stdio)
▼
horizon_mcp_server.py ← reads dbt artifacts, exposes 4 tools
│
├── manifest.json ← generated by dbt run (model docs + definitions)
│
└── horizon_insurance.duckdb ← generated by dbt seed + dbt run
│
├── main_raw (seeds: policies, claims, premiums)
├── main_staging (stg_policies, stg_claims, stg_premiums)
└── main_marts (mart_policy_summary, mart_loss_ratio_by_segment,
mart_monthly_premium_trend)
Project structure
horizon-mcp-demo/
├── seeds/ # Raw fake data (CSV)
│ ├── policies.csv # 25 P&C insurance policies
│ ├── claims.csv # 20 claims (open and closed)
│ └── premiums.csv # 109 monthly earned premium records
├── models/
│ ├── staging/ # Cleaned, typed source models (views)
│ │ ├── stg_policies.sql
│ │ ├── stg_claims.sql
│ │ └── stg_premiums.sql
│ ├── marts/ # Governed analytical models (tables)
│ │ ├── mart_policy_summary.sql
│ │ ├── mart_loss_ratio_by_segment.sql
│ │ └── mart_monthly_premium_trend.sql
│ └── schema.yml # Metric definitions, column docs, data tests
├── mcp_server/
│ └── horizon_mcp_server.py # MCP server — exposes dbt artifacts as tools
├── scripts/
│ └── run_agent.py # Agent demo: Claude + MCP + 5 demo questions
├── dbt_project.yml # dbt project config
├── profiles.yml # DuckDB connection (no credentials needed)
└── requirements.txt # Python dependencies
MCP tools exposed
The MCP server exposes four tools to the agent:
| Tool | What it does |
|---|---|
list_models |
Returns all dbt models with descriptions and schemas |
get_model_details |
Returns column definitions, metric notes, and tests for a specific model |
get_metric_definitions |
Returns all governed metric definitions with governance rules |
query_data |
Executes a read-only SELECT against the DuckDB database |
Write operations (INSERT, UPDATE, DELETE, DROP) are blocked at the tool layer in addition to the database being opened read-only.
Setup (Windows)
Prerequisites
- Python 3.12 — not 3.13 or 3.14, which have compatibility issues with dbt's dependencies
- Git
1. Clone the repo
git clone https://github.com/christianashworth/horizon-mcp-demo.git
cd horizon-mcp-demo
2. Create and activate virtual environment
py -3.12 -m venv .venv
.venv\Scripts\activate
If you get a script execution error:
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser
3. Install dependencies
python -m pip install --upgrade pip
pip install -r requirements.txt
4. Verify dbt installed correctly
dbt --version
Expected output: dbt Core 1.11.x with the duckdb plugin.
5. Build the database
dbt seed
dbt run
This loads the seed CSVs into DuckDB and builds all six models. Creates horizon_insurance.duckdb in the project root.
6. Verify the build
dbt test
All 9 tests should pass.
Running the agent demo
Set your Anthropic API key
$env:ANTHROPIC_API_KEY = "your-api-key-here"
Get a key at console.anthropic.com under API Keys.
Run the demo
python scripts/run_agent.py
The agent works through five questions. Each tool call is logged as it happens so you can follow exactly what the agent is doing and why.
Demo questions:
- What models are available and what does each contain?
- How is loss ratio defined in this semantic layer?
- What is the loss ratio by product type and state? Which segment has the worst performance?
- How many open claims are there, and what is the total case reserve outstanding?
- Which state has the highest claim frequency per 100 policies for auto insurance?
Metric definitions
All metric definitions are governed at the semantic layer and exposed to the agent via the get_metric_definitions tool. The agent is instructed to call this tool before performing any calculation involving these metrics.
| Metric | Definition | Governance rule |
|---|---|---|
| loss_ratio | sum(incurred_loss) / sum(earned_premium) |
Never use written premium as denominator. Never average policy-level ratios — always aggregate numerator and denominator independently before dividing. |
| incurred_loss | paid losses + case reserves | Do not use paid_loss alone as a proxy. Open claims carry reserves that must be included. |
| earned_premium | Premium allocated to the measurement period based on policy term elapsed | Use earned, not written, as the denominator in all loss ratio calculations. |
| claim_frequency_per_100 | (claim_count / policy_count) × 100 |
Segment level only (product_type + state). |
| case_reserve | incurred_loss − paid_loss |
Zero on closed claims. Exists only on open claims. |
Notes
- The
.duckdbfile is excluded from version control. It is generated locally bydbt seed && dbt runand will appear in the project root after setup. - The MCP server opens DuckDB in read-only mode. Write operations are also blocked at the tool layer as a second control.
profiles.ymlis included in the repo because it contains no credentials — DuckDB is a local file with no authentication required.- If you encounter issues running the demo script on Windows, ensure your PowerShell execution policy is set:
Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope CurrentUser
