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.

What it shows

Related MCP server: Polaris Catalog & Iceberg Governance MCP Server

5-minute quickstart

You need Python 3.10 or newer. uv is the fastest path; plain pip works too.

# 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:

== 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:

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):

{
  "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

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

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

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:

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.

Author: Alex Merced (alexmerced.com).