Expose a Semantic Layer to AI Agents over MCP

A semantic layer holds the definitions the business agreed on: what revenue is, which orders count, how a quarter is labeled. The Model Context Protocol (MCP) is how an agent discovers and calls tools. Put the two together and the agent stops writing SQL against schemas it half understands. It picks a metric by its definition, and the server writes SQL that is correct by construction. This page builds that server with the official MCP Python SDK.

Tool design comes first

Five tools, all read-only, none of which accepts SQL:

Tool	Why the agent needs it
`list_metrics`	Find the metric whose definition matches the question.
`list_dimensions`	Find how the question wants numbers broken down.
`describe_metric`	Read the exact definition, filter, source view, and row policy before quoting a number.
`explain_metric_query`	See the SQL a request compiles to without running it.
`query_metric`	Get the numbers, with the SQL that produced them.

A generic run_sql tool would be shorter to write. It would also hand the agent every problem the semantic layer exists to solve: guessing join keys, forgetting that refunds are not revenue, and inventing a quarter boundary. Metric tools make the right query the only query.

Step 1: Write definitions an agent can choose from

The metric description is the text the agent reads when it decides which metric answers a question, so it has to say what is in and what is out. "Revenue" alone is not a definition.

metrics:
  revenue:
    description: Net revenue from completed orders, meaning amount minus discount, in USD. Refunded and cancelled orders are excluded.
    view: orders_enriched
    expression: SUM(amount - discount)
    filter: status = 'completed'
    format: currency
  refund_rate:
    description: Share of completed or refunded orders that were refunded. Cancelled orders are not counted.
    view: orders_enriched
    expression: AVG(CASE WHEN status = 'refunded' THEN 1.0 ELSE 0.0 END)
    filter: status IN ('completed', 'refunded')
    format: percent

dimensions:
  order_quarter:
    description: Calendar quarter the order was placed in, for example 2026-Q1.
    expression: strftime(order_date, '%Y') || '-Q' || CAST(quarter(order_date) AS VARCHAR)

Step 2: Compile metrics with different filters correctly

Revenue counts completed orders; refund rate counts completed and refunded ones. Asking for both by region cannot share one WHERE clause. The compiler groups metrics by filter, builds one CTE per group, and joins them on the dimensions:

groups: dict[str | None, list[str]] = {}
for m in req.metrics:
    groups.setdefault(model.metrics[m].filter, []).append(m)

ctes: list[str] = []
params: list[Any] = []
for i, (metric_filter, names) in enumerate(groups.items()):
    select = dims + [f"{model.metrics[m].expression} AS {m}" for m in names]
    conditions = ([f"({metric_filter})"] if metric_filter else []) + where
    sql = f"  SELECT {', '.join(select)}\n  FROM governed.{view.name}"
    if conditions:
        sql += f"\n  WHERE {' AND '.join(conditions)}"
    if dims:
        sql += "\n  GROUP BY ALL"
    ctes.append(f"m{i} AS (\n{sql}\n)")
    params += where_params

join = " m0"
for i in range(1, len(ctes)):
    join += f"\n  FULL OUTER JOIN m{i} USING ({', '.join(req.dimensions)})" if dims else f"\n  CROSS JOIN m{i}"

This is the part hand-written agent SQL gets wrong most often, and it is why the definitions belong in a layer the agent calls rather than in a prompt it reads.

Step 3: Create the MCP server and its tools

The MCP Python SDK 2.x calls its high-level server MCPServer (it was FastMCP in 1.x). Tools are plain functions; the SDK builds the input schema from the type hints and the tool description from the docstring. The instructions string is the server's short manual for the agent.

from mcp.server.mcpserver import MCPServer
from mcp.server.mcpserver.exceptions import ToolError
from mcp.types import ToolAnnotations
from pydantic import BaseModel, Field

READ_ONLY = ToolAnnotations(read_only_hint=True, destructive_hint=False, idempotent_hint=True, open_world_hint=False)


class MetricFilter(BaseModel):
    dimension: str = Field(description="A dimension name from list_dimensions.")
    op: str = Field(description="One of '=', '!=', 'in', 'not_in'.")
    values: list[str | int | float] = Field(description="Values to compare against. One value for '=' and '!='.")


mcp = MCPServer(
    name="semantic-lakehouse",
    title="Governed semantic layer over Apache Iceberg",
    instructions=(
        "Answer business questions with the metrics in this server. "
        "Call list_metrics and list_dimensions first, describe_metric when a definition matters, "
        "then query_metric. You cannot run SQL; you choose metrics, dimensions, filters, and a "
        "time range, and the server compiles governed SQL. Quote the metric definition when you "
        "report a number."
    ),
)


@mcp.tool(annotations=READ_ONLY)
def list_metrics() -> list[dict[str, str]]:
    """List the metrics this agent may query, with their business definitions."""
    return svc().list_metrics()


@mcp.tool(annotations=READ_ONLY)
def query_metric(
    metrics: list[str],
    dimensions: list[str] | None = None,
    filters: list[MetricFilter] | None = None,
    start: str | None = None,
    end: str | None = None,
    order_by: str | None = None,
    descending: bool = False,
    limit: int | None = None,
) -> dict[str, Any]:
    """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.
    """
    try:
        return svc().query_metric(
            metrics=metrics,
            dimensions=dimensions,
            filters=[f.model_dump() for f in filters or []],
            start=start,
            end=end,
            order_by=order_by,
            descending=descending,
            limit=limit,
        )
    except GuardrailError as exc:
        raise ToolError(str(exc)) from exc

In the repository these definitions sit inside build_server(), which also creates the service lazily (svc()) so tests can pass in their own. The other three tools follow the same pattern.

Three details that make a difference to the agent:

The docstring teaches the date convention. "end is exclusive" plus a worked Q1 example removes a whole class of off-by-one-month answers.
ToolError for anticipated failures. The SDK returns its message to the model with is_error set. Any other exception becomes a generic error and the model learns nothing. Guardrail messages are written to be read by the agent, so they go through ToolError.
Read-only annotations. Clients can use them to skip confirmation prompts for tools that cannot change anything.

The result carries the SQL alongside the rows. When an agent reports a number, it can show where the number came from, and a person can check it.

Step 4: Test through a real MCP client, in process

The SDK's Client accepts an MCPServer instance and connects to it in memory. That exercises schema generation, argument validation, and error handling exactly as a remote client would, with no subprocess:

from mcp import Client


async def test_tools_are_listed_and_none_take_sql(analyst):
    async with Client(build_server(analyst)) as client:
        tools = {t.name: t for t in (await client.list_tools()).tools}
    assert set(tools) == {"list_metrics", "list_dimensions", "describe_metric", "explain_metric_query", "query_metric"}
    for tool in tools.values():
        assert "sql" not in tool.input_schema.get("properties", {})
        assert tool.annotations.read_only_hint is True


async def test_guardrail_refusal_is_a_tool_error_the_agent_can_read(analyst):
    async with Client(build_server(analyst)) as client:
        result = await client.call_tool("query_metric", {"metrics": ["revenue"]})
    assert result.is_error
    assert "Narrow the time_range" in result.content[0].text

Step 5: Connect an agent

The server speaks MCP over stdio. For Claude Desktop, Claude Code, or any MCP client that launches local servers, point the client at the slh-server entry point:

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

Ask "Rank the regions by revenue in Q1 2026 and include order counts." The agent should list metrics, pick revenue and order_count, and call query_metric once with start=2026-01-01 and end=2026-04-01. The server compiles and returns this (demo data):

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}
   {'region': 'APAC', 'revenue': 202720.91, 'order_count': 1032}
   {'region': 'LATAM', 'revenue': 96268.18, 'order_count': 488}

That output is from slh-demo, which runs the same calls through an in-process client so you can see the tool traffic without an LLM.

Done when

Every metric and dimension has a description that says what is included and excluded.
The agent can discover, describe, preview, and query metrics, and cannot send SQL.
Metrics with different filters combine correctly in one request.
Refusals reach the model as readable tool errors that say what to change.
Results include the compiled SQL, so any number can be traced.
Tests run through an MCP client, not only through the Python functions.

Taking it to production

Many semantic layers and lakehouse platforms now ship their own MCP servers. The design questions on this page still apply when you evaluate one: does it expose metrics or raw SQL, do tool descriptions carry real definitions, does it return the SQL it ran, and does it run as the user's identity? For remote agents, serve over MCP's Streamable HTTP transport with authentication so the role comes from the caller's token (see row and column policies).

Exposing a semantic layer to agents over MCP