# mcp-semantic-lakehouse-demo

A small, runnable MCP server that gives an AI agent governed access to Apache Iceberg tables through a semantic layer. The agent can list metrics, read their definitions, and ask for numbers. It cannot write SQL, cannot see raw tables, and cannot see rows or columns its role is not allowed to see.

Everything runs on a laptop: PyIceberg with a SQLite catalog and a local folder warehouse, DuckDB as the query engine, a YAML semantic model, and the official MCP Python SDK. No services, no cloud account.

This is the companion code for the implementation pattern pages on [agenticlakehouse.com](https://agenticlakehouse.com/patterns/).

## What it shows

| Pattern | Where in the code |
|---|---|
| Agent-safe governed views: agents read views, never raw tables | `semantic_model.yaml` (`governed_views`), `governance.py` |
| Row and column policies per agent identity | `semantic_model.yaml` (`policies`), `governance.py` |
| Query guardrails: allowlist, row limits, timeouts, cost cap, session budget | `compiler.py`, `guardrails.py` |
| A semantic layer exposed to agents over MCP | `server.py`, `service.py` |
| An MCP server over an Iceberg catalog | `catalog.py`, `seed.py`, `governance.py` |

## 5-minute quickstart

You need Python 3.10 or newer. [uv](https://docs.astral.sh/uv/) is the fastest path; plain `pip` works too.

```sh
# 0. Get the code
git clone https://github.com/alexmerced-oss/mcp-semantic-lakehouse-demo.git
cd mcp-semantic-lakehouse-demo

# 1. Install (seconds with uv, about a minute with pip)
uv venv .venv
uv pip install -e ".[test]"
#   or: python -m venv .venv && .venv/bin/pip install -e ".[test]"

# 2. Create the Iceberg tables (writes ./.lakehouse: catalog.db + warehouse/)
.venv/bin/slh-seed

# 3. Watch an MCP client call the tools, including the refusals
.venv/bin/slh-demo

# 4. Run the tests
.venv/bin/pytest
```

`slh-demo` connects a real MCP client to the server in-process and makes the calls an agent would make. Part of its output:

```text
== query_metric: Q1 2026 revenue and order count by region
WITH m0 AS (
  SELECT region AS region, SUM(amount - discount) AS revenue, COUNT(*) AS order_count
  FROM governed.orders_enriched
  WHERE (status = 'completed') AND order_date >= ? AND order_date < ?
  GROUP BY ALL
)
SELECT region, revenue, order_count
FROM m0
ORDER BY revenue DESC NULLS LAST
LIMIT 101
   {'region': 'NA', 'revenue': 448605.18, 'order_count': 2189}
   {'region': 'EMEA', 'revenue': 305682.91, 'order_count': 1632}
   ...

== query_metric: all-time revenue (the cost cap should refuse this)
REFUSED: ... This query would scan about 36,000 rows, above the cap of 25,000. Narrow the time_range and try again.

== query_metric: a column that is not in the semantic model
REFUSED: ... Unknown or restricted dimension 'email'. Call list_dimensions.
```

Run it again as a narrower identity and the same question returns only EMEA rows:

```sh
AGENT_ROLE=emea_sales_agent .venv/bin/slh-demo
```

## Connect an agent

The server speaks MCP over stdio. Point any MCP client at `slh-server`. For Claude Desktop or Claude Code, add this to the client's MCP configuration (use absolute paths):

```json
{
  "mcpServers": {
    "semantic-lakehouse": {
      "command": "/path/to/mcp-semantic-lakehouse-demo/.venv/bin/slh-server",
      "env": {
        "DEMO_HOME": "/path/to/mcp-semantic-lakehouse-demo/.lakehouse",
        "SEMANTIC_MODEL": "/path/to/mcp-semantic-lakehouse-demo/semantic_model.yaml",
        "AGENT_ROLE": "analyst_agent"
      }
    }
  }
}
```

Then ask something like "Which region grew revenue fastest between Q4 2025 and Q1 2026?" and watch the tool calls.

## The tools

| Tool | What it does |
|---|---|
| `list_metrics` | Metrics this role may query, with business definitions |
| `list_dimensions` | Dimensions this role may group or filter by |
| `describe_metric` | A metric's definition, source view, time column, and the row policy in force |
| `explain_metric_query` | Compiles a request and returns the SQL without running it |
| `query_metric` | Compiles, checks, and runs a metric request |

All tools are annotated read-only. None accepts SQL. `query_metric` takes metric names, dimension names, filters (`=`, `!=`, `in`, `not_in`), an optional `start`/`end` date range (end exclusive), `order_by`, and `limit`.

## How a request is handled

1. **Names are looked up, never interpolated.** Metrics and dimensions must exist in `semantic_model.yaml` and not be denied to the role. Anything else is refused with a message that tells the agent which tool to call.
2. **Values are bound as parameters.** Filter values and dates never become SQL text.
3. **The compiler emits SQL over governed views only**, with one CTE per distinct metric filter and a `LIMIT` of at most `max_rows`.
4. **DuckDB's parser checks the result** (`json_serialize_sql`): exactly one SELECT, reading only relations in the allowlist. This is independent of the compiler, so a compiler bug cannot leak a raw table.
5. **The cost cap reads Iceberg metadata.** `plan_files()` on the orders table, filtered by the time range, prunes month partitions and sums record counts from the manifests. No data files are read. Requests estimated above `max_scan_rows` are refused.
6. **The query runs with a timeout** (`cursor.interrupt()` from a timer) on a connection with external access disabled and configuration locked.
7. **Every call is written to `audit.jsonl`**, including refusals, with the role, the request, the SQL, and the row count.

Row policies are not a `WHERE` clause the compiler adds. They are written into the governed view when the server starts, from the role in `AGENT_ROLE`, so no request can remove them.

## Layout

```text
semantic_model.yaml          metrics, dimensions, governed views, roles, guardrails
src/semantic_lakehouse/
  catalog.py                 PyIceberg SQL catalog (SQLite) + local warehouse
  seed.py                    deterministic data, two Iceberg tables, month partitions
  semantic.py                loads and validates the YAML model
  governance.py              raw -> governed views, row and column policy, locked DuckDB
  compiler.py                metric request -> parameterized SQL over governed views
  guardrails.py              allowlist, timeout, Iceberg-metadata cost cap, session budget
  service.py                 the operations the tools call, plus the audit log
  server.py                  the MCP server (MCPServer from the official SDK)
  demo.py                    in-process MCP client walkthrough
tests/                       60 tests, including injection and escape attempts
```

## Configuration

| Variable | Default | Meaning |
|---|---|---|
| `DEMO_HOME` | `./.lakehouse` | Catalog database, warehouse, and audit log |
| `SEMANTIC_MODEL` | `./semantic_model.yaml`, then the repo copy | Semantic model file |
| `AGENT_ROLE` | `policies.default_role` | Which role the server runs as |

Reset the data with `slh-seed --reset`, or delete `.lakehouse/`.

## Limits of a demo

- DuckDB loads the Iceberg tables into memory at startup. That keeps the demo small. A production setup would query Iceberg in place with an engine that enforces policies itself, and the cost estimate would come from that engine or from the same Iceberg metadata this demo uses.
- One server process serves one role. In production the role comes from the authenticated user behind the agent (for example, an OAuth token on a Streamable HTTP transport), not from an environment variable.
- The row filter values are trusted configuration. Keep them out of anything an agent can write.

## Versions tested

Tested on September 29, 2026 on Linux with Python 3.12.10 (uv) and 3.13.3 (pip), same package versions:

| Package | Version |
|---|---|
| mcp (official MCP Python SDK) | 2.2.0 |
| pyiceberg | 0.12.0 |
| pyiceberg-core | 0.10.1 |
| duckdb | 1.5.6 |
| pyarrow | 25.0.1 |
| sqlalchemy | 2.1.1 |
| pyyaml | 6.0.3 |
| pytest | 9.1.1 |

`requirements.lock` has the full set. Install exactly those with `uv pip install -r requirements.lock -e .`.

The MCP SDK 2.x renamed `FastMCP` to `MCPServer` (`from mcp.server.mcpserver import MCPServer`). If you are on the 1.x SDK, the server code maps one to one onto `FastMCP`.

## License

Apache License 2.0. See [LICENSE](LICENSE).

Author: Alex Merced ([alexmerced.com](https://alexmerced.com)).