Skip to main content
Glama

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

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

Related MCP server: Semantic Rails 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

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

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/.

Read the patterns behind the code

Each pattern this repo implements has a step-by-step page on agenticlakehouse.com, with the reasoning, the trade-offs, and the snippets from this code:

If you are deciding whether and how to roll this out, rather than building it, agenticanalyticsnow.com has tools for leaders:

  • Readiness assessment: fifteen questions on semantic layer, governance, data quality, open interfaces, and team, scored in your browser into a maturity level with next steps.

  • Use cases by function: what finance, sales, supply chain, support, and marketing ask analytics agents, the data each question needs, and the governance controls.

  • Evaluation checklist: 32 vendor-neutral questions for evaluating agentic analytics products, each with what a good answer includes.

  • 90-day rollout playbook: five phases across thirteen weeks for one business domain, ending in a written decision to expand, extend, or stop.

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.

Author: Alex Merced (alexmerced.com).

Available Tools

5 tools
describe_metricB
Read-onlyIdempotent

Show a metric's definition, source view, time column, and the row policy in force.

Inputs: name (required). Returns: object

explain_metric_queryA
Read-onlyIdempotent

Compile a metric query and return the governed SQL without running it.

Inputs: metrics (required), end, limit, start, +4 more. Returns: object

list_dimensionsA
Read-onlyIdempotent

List the dimensions this agent may group or filter by.

Inputs: no named parameters. Returns: result

list_metricsA
Read-onlyIdempotent

List the metrics this agent may query, with their business definitions.

Inputs: no named parameters. Returns: result

query_metricA
Read-onlyIdempotent

Query one or more metrics, optionally grouped by dimensions.

start and end are ISO dates (end is exclusive), for example start=2026-01-01, end=2026-04-01 for Q1 2026. Queries over large time spans may be refused by the cost cap; narrow the range if so. Results are capped at the server's row limit and report truncated=true when rows were cut.

Inputs: metrics (required), end, limit, start, +4 more. Returns: object

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

Loading tool schema changelog…

    TDQS

    A3.9/5.0

    Scored across 5 tools

    Disambiguation5/5

    Each tool targets a distinct step of the metrics workflow: listing metrics, listing dimensions, describing a single metric, compiling a query without running it, and executing a query. The boundaries between describe_metric and explain_metric_query are clear from their descriptions.

    Naming Consistency5/5

    All five tools follow a consistent snake_case verb_noun pattern (list_metrics, list_dimensions, describe_metric, explain_metric_query, query_metric). The convention is predictable throughout.

    Tool Count5/5

    Five tools is well-scoped for a governed metrics query interface. Each tool earns its place across the discover-explain-execute workflow with no redundancy.

    Completeness4/5

    The surface covers the full read lifecycle: discovering metrics and dimensions, inspecting definitions, dry-running a query, and executing it. Minor gaps exist (e.g., no explicit tool for listing available time ranges or inspecting the row limit/cost cap), but agents can work around these.

    Related MCP Connectors

    Related MCP Servers

    • A
      license
      Not graded
      quality
      C
      maintenance
      Enables agents to answer analytics questions with provable correctness by resolving metrics through the semantic layer, tracing lineage, traversing knowledge graphs, checking freshness, and searching glossary definitions, while enforcing access controls and logging every call.
      MIT
    • A
      license
      A
      quality
      C
      maintenance
      Enables AI agents and their users to ask business questions in plain language and receive governed metric values, breakdowns, share-of-total figures, and the exact parameterized SQL behind them, drawn from a single semantic model rather than ad-hoc SQL. It also lets clients inspect available metrics and their definitions, and preview optimized queries without executing them.
      4
      MIT