# Semantic Context MCP

An [MCP](https://modelcontextprotocol.io) server that gives an LLM the **meaning** of a data
warehouse, not just its schema.

A model with schema access sees a table called `tbl_rev_fnl_v2` and a column called `amt`. It has
no way to know whether that's gross or net, cents or dollars, or a table nobody has touched in
40 days — so it guesses, and the guess *runs* and returns a plausible, wrong number. This server
closes that gap by exposing four tools backed by real derived metadata: ownership, freshness,
column units, deprecation status, and column-level lineage.

**Stack:** Python · [Model Context Protocol](https://modelcontextprotocol.io) · dbt + DuckDB ·
SQLite (FTS5) · [sentence-transformers](https://www.sbert.net/) · [sqlglot](https://sqlglot.com/) ·
[Anthropic API](https://platform.claude.com) (tool use / eval harness) · pytest

| Tool | Answers |
|---|---|
| `search_tables` | "Which table holds daily net revenue?" |
| `describe_table` | "What does one row mean, and what unit is this column in?" |
| `trace_lineage` | "Where did this number come from, and what breaks if I change it?" |
| `check_health` | "Is this table safe to rely on right now?" |

time — see "Setup" below. Without it, `search_tables` still works, just as keyword-only BM25.

## Where the metadata actually comes from

The credibility of a project like this rests entirely on whether the "meaning" it reports is real
or hand-typed for the demo. It's mostly real:

| Signal | Source | Human-authored? |
|---|---|---|
| Table purpose, grain, materialization | `manifest.json` | No |
| Test pass/fail | dbt build results | No |
| Model-level lineage | `manifest.json`'s `depends_on` | No |
| Column-level lineage | `sqlglot.lineage()` over compiled SQL | No |
| Table search (keyword) | SQLite FTS5 / BM25 over name + description + columns | No |
| Table search (semantic) | Cosine similarity over MiniLM embeddings | No |
| Row counts, 7-day row deltas | Live DuckDB queries at ingestion time | No |
| Freshness (hours since latest data) | Recomputed live on every call | No |
| Null rates per column | Live DuckDB queries at ingestion time | No |
| Ownership | dbt `meta.owner`, else `git blame` on the model file | Partly |
| Column descriptions and units | dbt `schema.yml` | **Yes** — the same input any dbt project already has |
| Deprecation status + successor | dbt `meta.status` / `meta.superseded_by` | **Yes** |

Only two rows are human-authored, and both are ordinary dbt metadata any real project already
maintains — this isn't reading from a hand-curated demo file.

**Column-level lineage resolves 35 of 36 columns (97%)** in the fixture warehouse. The one exception
sums a column with no table-qualifying prefix across a JOIN — genuinely ambiguous SQL that `sqlglot`
correctly declines to guess on, falling back to model-level lineage instead. `trace_lineage` always
names exactly where a fallback happened, in `unresolved_notes` — never silently.

The demo warehouse is [`dbt-labs/jaffle_shop_duckdb`](https://github.com/dbt-labs/jaffle_shop_duckdb)
with four added "decoy" revenue models, since the stock fixture is too clean to show any ambiguity.
The decoys are synthetic and labelled as such in [`fixture/`](fixture/) — the *pipeline* that derives
metadata from them is not.

## Setup

Requires Python 3.12+ and [uv](https://docs.astral.sh/uv/). No GPU needed.

```bash
uv sync
```

This installs only what the server itself needs and gives you a fully working server:
`describe_table`, `trace_lineage`, and `check_health` are fully live, and `search_tables` works in
keyword (BM25) mode. Heavier, optional pieces are separate extras so the base install stays fast:

```bash
uv sync --extra search   # sentence-transformers + numpy -- semantic ranking for search_tables
uv sync --extra ingest   # sqlglot + duckdb -- only needed to (re)build catalog.db
uv sync --extra dbt      # dbt-core + dbt-duckdb -- only needed to build the fixture warehouse
uv sync --extra eval     # anthropic + pyyaml -- only needed to run the eval harness
```

> `uv run <anything>` re-syncs the environment to match whatever `--extra` flags *that* invocation
> passes — running a bare `uv run python ...` after syncing extras in can silently drop them again.
> If a tool that was working starts raising `ModuleNotFoundError`, re-run the `uv sync --extra ...`
> line below rather than debugging the code.

## Build the fixture warehouse

Needed once before the server has data to serve — without it, every tool call fails with a clear
error telling you to run this.

```bash
uv sync --extra dbt --extra ingest --extra search
uv run python fixture/build_warehouse.py       # seed -> dbt build -> dbt docs generate
uv run python -m semantic_context_mcp.ingest    # manifest + catalog + run_results -> catalog.db
```

Re-running this sequence is always safe — both scripts fully replace their outputs rather than
merging into them, and `build_warehouse.py` regenerates seed dates so the "stale" decoy table
always looks stale relative to today. dbt's very first invocation in a fresh venv can sit silent
for 20-30 seconds while it compiles bytecode for its own dependency tree before printing anything —
`build_warehouse.py` prints a heads-up before that step so it doesn't look hung; subsequent runs are
fast.

## Run it

```bash
uv run semantic-context-mcp
```

The server speaks MCP over stdio and sits silently waiting for a client — that's correct behavior,
not a hang. To exercise it directly without a client:

```bash
uv run python scripts/smoke_stdio.py
```

That script spawns the real server over the real transport and drives every tool through both
happy-path and error-path calls, printing each result.

### Try it as a data analyst would

```bash
uv run python -c "
from semantic_context_mcp.core import search, describe, health

for m in search.run('daily net revenue'):
    print(m.name, '-', m.purpose)

print()
print(describe.run('fct_revenue_daily').columns)
print()
print(health.run('tbl_rev_fnl_v2').summary)
"
```

### Connect to Claude Desktop

Add to `claude_desktop_config.json`
(`%APPDATA%\Claude\claude_desktop_config.json` on Windows,
`~/Library/Application Support/Claude/claude_desktop_config.json` on macOS):

```json
{
  "mcpServers": {
    "semantic-context": {
      "command": "uv",
      "args": ["--directory", "/absolute/path/to/semantic-context-mcp", "run", "semantic-context-mcp"]
    }
  }
}
```

Restart Claude Desktop, then ask *"What's our daily revenue table?"*

## Testing

```bash
uv run pytest
```

The suite covers synthetic unit tests (fast, no fixture needed, always run) and real-data
integration tests that build the actual dbt/DuckDB warehouse and ingest it into `catalog.db` — the
latter are what caught every real bug during development (a dbt artifact-ordering issue, a DuckDB
NULL-vs-zero aggregation bug, a naive/aware datetime crash, a missing test hiding a staleness
signal, and a false "lineage unresolved" report on a fully-resolved column). Two additional
scripts exercise the protocol layer directly rather than through pytest:

```bash
uv run python scripts/smoke_stdio.py                       # real server, real MCP stdio transport
uv run python -m semantic_context_mcp.eval.mcp_path_smoke  # proves the eval harness matches the real server
```

## Evaluation

`eval/` measures whether having this server's tools available actually changes whether Claude picks
the right table and states the right caveats, compared to schema access alone. Three arms, the same
25 questions, the same model and system prompt — only the tool set differs:

| Arm | Tools |
|---|---|
| `schema_only` | table + column names and SQL types only — no metadata (the realistic baseline) |
| `schema_and_server` | schema-only tools, plus the four semantic tools |
| `server_only` | the four semantic tools alone |

```bash
uv sync --extra eval
export ANTHROPIC_API_KEY=...                                       # or `ant auth login`
uv run python -m semantic_context_mcp.eval                         # all 3 arms, 25 questions x 3 runs
uv run python -m semantic_context_mcp.eval --questions 5 --runs 1  # a cheap smoke run
uv run python -m semantic_context_mcp.eval --dry-run               # exercises the harness, no API calls
```

Each question/run/arm writes a transcript to `eval/runs/<timestamp>/`, so any surprising result is
checkable rather than trusted. Grading (`eval/grading.py`) is deliberately simple keyword/table-name
matching over the model's final answer — not an LLM judge — and is fully unit-tested without any
network access.

## Repository layout

```
fixture/
  jaffle_shop_duckdb/    Cloned dbt project + models/decoys/*.sql (4 synthetic ambiguous tables)
  pristine_seeds/        Untouched original seed CSVs -- prepare_seeds.py's source of truth
  prepare_seeds.py       Shifts seed dates to end "yesterday", idempotently
  build_warehouse.py     seed -> dbt build -> dbt docs generate
src/semantic_context_mcp/
  models.py        Pydantic contract for all four tool returns
  errors.py        Errors written to be read by the model
  server.py        MCP adapter -- no logic
  core/            The actual logic; also what eval/tools.py calls
  ingest/          dbt artifacts + git + SQLite -> catalog.db
  eval/            Three-arm measurement harness (see "Evaluation" above)
scripts/
  smoke_stdio.py   Drives the real server over the real MCP transport
tests/             Contract, consistency, ingestion, eval, and stdout-purity guards
```