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

1. A governed semantic layer — dbt models with enforced metric definitions for loss ratio, earned premium, incurred loss, and claim frequency
2. A custom MCP server — a lightweight Python server that reads dbt artifacts (manifest.json) and exposes four governed tools to any MCP-compatible agent
3. 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:

1. What models are available and what does each contain?
2. How is loss ratio defined in this semantic layer?
3. What is the loss ratio by product type and state? Which segment has the worst performance?
4. How many open claims are there, and what is the total case reserve outstanding?
5. 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 .duckdb file is excluded from version control. It is generated locally by dbt seed && dbt run and 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.yml is 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