Provides governed access to Apache Iceberg tables through a semantic layer, allowing AI agents to list metrics, read metric definitions, and query metric values while enforcing row and column policies and query guardrails.
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type @ followed by the MCP server name and your instructions, e.g., "@slh-server Show me revenue by region for Q1 2026"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
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.
Pattern | Where in the code |
Agent-safe governed views: agents read views, never raw tables |
|
Row and column policies per agent identity |
|
Query guardrails: allowlist, row limits, timeouts, cost cap, session budget |
|
A semantic layer exposed to agents over MCP |
|
An MCP server over an Iceberg catalog |
|
Related MCP server: Polaris Catalog & Iceberg Governance MCP Server
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/pytestslh-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-demoThe 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.
Tool | What it does |
| Metrics this role may query, with business definitions |
| Dimensions this role may group or filter by |
| A metric's definition, source view, time column, and the row policy in force |
| Compiles a request and returns the SQL without running it |
| 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.
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.
Values are bound as parameters. Filter values and dates never become SQL text.
The compiler emits SQL over governed views only, with one CTE per distinct metric filter and a LIMIT of at most max_rows.
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.
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.
The query runs with a timeout (cursor.interrupt() from a timer) on a connection with external access disabled and configuration locked.
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.
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 attemptsVariable | Default | Meaning |
|
| Catalog database, warehouse, and audit log |
|
| Semantic model file |
|
| Which role the server runs as |
Reset the data with slh-seed --reset, or delete .lakehouse/.
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:
Build an MCP server over an Apache Iceberg catalog: the server, tools, and catalog wiring in server.py, catalog.py, and seed.py.
Agent-safe governed views: why agents read views and never raw tables, and how row and column policies follow the agent's role.
Query guardrails for AI agents: allowlists, row limits, timeouts, and cost caps, as in compiler.py and guardrails.py.
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.
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.
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.
Apache License 2.0. See LICENSE.
Author: Alex Merced (alexmerced.com).
Show a metric's definition, source view, time column, and the row policy in force.
| Name | Required | Description | Default |
|---|---|---|---|
| name | Yes |
| Name | Required | Description |
|---|---|---|
No output parameters | ||
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
The annotations already declare readOnlyHint, idempotentHint, destructiveHint=false and a closed world, so safety behavior is fully covered. The description adds value by naming the returned facets (definition, source view, time column, row policy), but says nothing about error behavior for an unknown metric. With annotations carrying the safety profile, a 3 is appropriate.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
One short sentence that front-loads the action and enumerates the returned information with no filler. Nothing is wasted and nothing essential is omitted.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
An output schema exists, so the return shape needn't be spelled out, and the annotations cover the safety profile. For a simple single-parameter read tool the description is essentially complete, with only not-found handling left unstated.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
Schema description coverage is 0% for the single required 'name' parameter, but the tool is named describe_metric and the description speaks of 'a metric's definition', so the parameter's intent is readily inferable. The description adds no explicit syntax or format detail, but the gap is small for a one-parameter tool.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The verb 'Show' plus the resource 'a metric's definition, source view, time column, and the row policy' is specific and concrete about what the agent gets back. It does not, however, draw any boundary against close siblings like explain_metric_query or list_metrics, so an agent must infer the distinction.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
There is no when-to-use statement, no prerequisites, and no named alternative. Given the sibling explain_metric_query exists and the two could plausibly overlap, the absence of any routing guidance is a real gap, leaving usage only loosely implied by the name.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
Compile a metric query and return the governed SQL without running it.
| Name | Required | Description | Default |
|---|---|---|---|
| end | No | ||
| limit | No | ||
| start | No | ||
| filters | No | ||
| metrics | Yes | ||
| order_by | No | ||
| descending | No | ||
| dimensions | No |
| Name | Required | Description |
|---|---|---|
No output parameters | ||
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
Annotations already declare readOnlyHint, idempotentHint, destructiveHint=false and openWorldHint=false, so safety is covered. The description still adds a genuinely useful behavioral fact beyond the annotations: it compiles and returns SQL only, executing nothing and therefore returning no data rows — a distinction read-only alone does not convey. It omits error behavior for invalid metrics or permission requirements.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
A single front-loaded sentence with zero filler; the compile action and the no-execution constraint are both delivered immediately. Nothing is redundant with the name or title.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
An output schema exists, so return values need not be described, but for an 8-parameter tool with no schema-level parameter descriptions and no usage guidance, one sentence is not enough. Key call-site knowledge — what metrics/dimensions identifiers must reference, date formats, and default windowing — is entirely absent.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
Schema description coverage is 0% across 8 parameters (only nested MetricFilter fields carry descriptions), so the description is expected to compensate for metrics, filters, dimensions, start/end, limit, order_by and descending. The one-sentence description mentions no parameter at all, so an agent gets no added semantics for any of them.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
States a specific verb (compile), resource (metric query) and output (governed SQL), and the clause 'without running it' implicitly separates it from the executing sibling query_metric. An agent can distinguish this dry-run/compile tool from list_metrics, describe_metric and query_metric without opening a schema.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
The phrase 'without running it' implies the intended use case (inspecting/validating SQL before execution) and hints at the alternative query_metric, but no sibling is named and there is no explicit when-to-use vs when-not guidance. Usage is implied rather than stated.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
List the dimensions this agent may group or filter by.
| Name | Required | Description | Default |
|---|---|---|---|
No parameters | |||
| Name | Required | Description |
|---|---|---|
| result | Yes |
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
Annotations already declare readOnlyHint, idempotentHint, and destructiveHint=false, so the safety profile is fully covered and the description adds the useful semantic that these dimensions are for grouping/filtering. It says nothing about ordering, completeness, or catalog scope, which for a catalog-listing tool is a real but minor gap.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
One front-loaded sentence with no filler, stating the resource and its role immediately. Every word earns its place.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
An output schema exists, so return values need no explanation, and a zero-parameter lister needs little else. The only shortfall is the absence of routing guidance relative to list_metrics.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
The tool takes zero parameters, so the baseline is 4. There is nothing for the description to disambiguate, and it correctly does not invent parameter detail.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
States a specific verb (List) and resource (dimensions) and scopes it to 'this agent may group or filter by', so the resource is unambiguous. However, it does not name or distinguish itself from its closest sibling list_metrics, so an agent must infer the split between the two listing tools.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
The phrase 'may group or filter by' implies the downstream purpose (discovering groupable/filterable fields) but never states when to call this versus list_metrics or describe_metric. Usage is implied rather than directed.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
List the metrics this agent may query, with their business definitions.
| Name | Required | Description | Default |
|---|---|---|---|
No parameters | |||
| Name | Required | Description |
|---|---|---|
| result | Yes |
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
Annotations already declare readOnlyHint, idempotentHint, and non-destructive behavior, so safety is covered by structured data. The description does add meaningful access-control context ('metrics this agent may query'), i.e. the result set is permission-scoped rather than a global catalog, but says nothing about rate limits, pagination, or freshness.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
A single sentence that front-loads the action and resource with no filler. Nothing in it is redundant with the name or the annotations.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
For a no-parameter, read-only catalog call with an output schema that documents the returned shape, the description covers purpose, scope, and content adequately. The missing piece is guidance on how this relates to the sibling discovery/describe/query tools.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
The tool takes zero parameters, so the baseline of 4 applies; there is no parameter surface for the description to clarify. The stated 'business definitions' briefly signals result content, which is a small bonus on top of the empty schema.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
Clear verb (List) plus resource (metrics) and scope ('this agent may query'), with an added hint at returned content ('business definitions'). It does not explicitly contrast with siblings like list_dimensions or describe_metric, but the resource noun alone is differentiating enough that an agent can tell it from the other list/describe tools.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
Usage is only implied: an agent can infer this is the discovery call to run before describe_metric or query_metric, but the description never states when to use it versus those siblings or any prerequisites. No exclusions or alternative-routing guidance is offered.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
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.
| Name | Required | Description | Default |
|---|---|---|---|
| end | No | ||
| limit | No | ||
| start | No | ||
| filters | No | ||
| metrics | Yes | ||
| order_by | No | ||
| descending | No | ||
| dimensions | No |
| Name | Required | Description |
|---|---|---|
No output parameters | ||
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
Annotations already declare readOnly, idempotent and non-destructive, so safety is covered. The description adds real behavioral context beyond them: end is exclusive, large spans may be rejected by the cost cap, and results are row-capped with truncated=true when cut. That truncation/cost behavior is exactly the kind of trait an agent cannot get from annotations.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
Three sentences, front-loaded with the core action, then date semantics, then failure modes. The Q1 2026 example earns its space by anchoring the exclusive-end convention. No filler.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
An output schema exists so return values needn't be described, and it correctly notes truncated=true as a signal. But for an 8-parameter query tool, the filtering/pagination/ordering surface (filters, order_by, descending, limit) is unexplained, leaving an agent to guess at the most non-obvious parts of the call.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
Top-level schema description coverage is 0% across 8 parameters, so the description must compensate and it only partly does: start/end semantics (ISO dates, end exclusive) and the dimensions grouping and row-limit/truncation link are covered, but filters, order_by and descending get no explanation anywhere. Partial compensation lands at the baseline 3.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
States a specific verb+resource: 'Query one or more metrics, optionally grouped by dimensions.' This is clearly distinguishable in kind from siblings like list_metrics, describe_metric and explain_metric, which are inspection tools. It stops short of explicitly naming those siblings or the boundary between them, so it is not a 5.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
Gives one operational condition ('Queries over large time spans may be refused by the cost cap; narrow the range if so'), which is genuinely useful usage guidance. However it never says when to reach for query_metric versus explain_metric or list_dimensions, so the routing decision is left to inference.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
v0.1.0describe_metricexplain_metric_querylist_dimensionslist_metricsquery_metricScored across 5 tools
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.
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.
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.
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.
The grounded data layer for any LLM: governed SQL, metrics, lineage and catalog over your data.
Answer questions using your semantic layer and company knowledge graph.
Your Databricks Lakehouse in natural language: run SQL on your SQL warehouses, track long-running qu
Query your org's data in natural language — read-only MCP access to SQL, NoSQL, files & warehouses.
