warehouse-context-mcp

An MCP server that gives AI agents trustworthy, governed context about a data warehouse: what each table means, where it comes from, how fresh it is, and a way to query it that cannot write, cannot run away, and cannot leak personal data.

Coding and analytics agents are good at SQL and bad at knowing which table to trust. Schema alone does not tell them that fct_voyage_economics is the governed source for TCE, that stg_voyages is staging, that contact_email is personal data, or that a table stopped updating two days ago. This server puts that context one tool call away, and puts guardrails around everything else.

Tools

Related MCP server: Lagaam

Backends

Comments and tags stored in the warehouse are merged with the curated YAML catalog: curated values win, native ones fill the gaps, and a PII flag from either source sticks.

Guardrails

Read-only is enforced in three independent layers, each tested on its own:

1. Parsing, not string matching. SQL is parsed with sqlglot. It must be exactly one statement whose root is a SELECT or a set operation, and no node in the tree may be a write, DDL, SELECT … INTO, or an unmodelled command (COPY, ATTACH, PRAGMA, SET).

2. Read-only connection. DuckDB opens the file read-only; Snowflake and Databricks connect with a SELECT-only role or principal, so a guardrail bug still cannot write.

3. Bounded results. A row limit is always applied (tightened if the query asks for more) and queries are interrupted after a timeout.

Scope: queries may only touch tables in the configured schema. Anything qualified with another database or schema is refused, and unqualified names are rewritten to fully qualified ones, so a query never depends on the session's default schema.

PII: columns flagged pii: true in the catalog can be counted (COUNT(DISTINCT email)) but never read, filtered on or grouped by, including through aliases and subqueries. SELECT * is allowed; PII values come back masked.

See docs/adr/0001-read-only-by-construction.md for the reasoning.

Quick start

Requires uv and Python 3.14 or 3.15 (uv installs it if missing).

uv sync                                         # creates .venv from uv.lock
uv run examples/build_demo.py examples/demo.duckdb   # fully synthetic shipping data
uv run pytest

Connect it to Claude Code:

claude mcp add warehouse -- uv run --directory "$PWD" warehouse-context-mcp \
  --duckdb examples/demo.duckdb --catalog examples/catalog.yaml

Then ask: "Which vessel class had the best average TCE last quarter, and is that table fresh?"

Snowflake or Databricks

uv sync --extra snowflake                # or --extra databricks, or --all-extras
cp .env.example .env                     # fill in your own values; .env is git-ignored
# run examples/snowflake_demo.sql (or databricks_demo.sql) once in the warehouse
uv run warehouse-context-mcp --backend snowflake --catalog examples/catalog.yaml

Settings come from SNOWFLAKE_* / DATABRICKS_* environment variables or ./.env; real environment variables win, and blank values count as unset. Key-pair auth for Snowflake: uv run scripts/snowflake_keypair.py prints the one ALTER USER statement to run.

Live tests run the demo expectations against a real warehouse and are skipped otherwise:

WCM_LIVE=snowflake uv run pytest -m live

On Windows the same commands work in PowerShell; set the variable with $env:WCM_LIVE = "snowflake".

On the Databricks Free Edition the personal access token belongs to the workspace admin, so the read-only connection layer is not available there; the query policy still applies. In a real workspace, use a service principal with only USE and SELECT grants.

Python versions

The catalog

Meaning lives in a YAML file reviewed in Git like code (example):

tables:
  fct_voyage_economics:
    description: Time-charter-equivalent economics per voyage.
    owner: freight-analytics
    grain: voyage_id
    upstream: [stg_voyages]
    freshness_column: computed_at
    expected_freshness_hours: 12
    columns:
      tce_usd_per_day: { description: TCE in USD per day. }

In production it would be generated from dbt docs, Unity Catalog comments or a data catalog.

Architecture

src/warehouse_context_mcp/
├── domain/        catalog, result models, namespace, errors (no I/O beyond YAML)
├── guardrails/    QueryPolicy: parse, scope, PII rules, row limit; result masking
├── warehouses/    Warehouse protocol + DuckDB, Snowflake, Databricks adapters
├── service.py     WarehouseContext: combines the three; no MCP dependency
├── server.py      MCP transport: six read-only tools with typed inputs and outputs
├── settings.py    pydantic-settings for connection config and secrets
└── cli.py         entry point

Dependencies point inwards: the transport knows the service, the service knows the domain, guardrails and the Warehouse protocol, and nothing in the domain knows about MCP or any driver.

• Typed end to end. Tool results are frozen pydantic models, so MCP clients receive a JSON schema for every output. Tool arguments carry their bounds (depth 1–5, max_rows ≤ 1000) in the input schema. DB-API drivers are reached through a Cursor[P] protocol generic over the driver's paramstyle, so binding positional parameters to a named-style driver is a type error.

• Errors as data. Guardrail refusals come back as {"ok": false, "error": ...} so the agent can repair its query; programming errors and unknown tables raise.

• Warehouse-agnostic core. Adapters only describe where metadata lives; fetching, caching, allow-listing and catalog assembly are shared in DbApiWarehouse.

See docs/adr for the decisions behind the guardrails and the toolchain.

Development

uv run ruff format          # format
uv run ruff check           # lint: every ruff rule, with a short documented ignore list
uv run ty check             # type check, strict rules
uv run pytest --cov         # tests with branch coverage

CI runs the same on Python 3.14 and 3.15 (Linux) and 3.14 (Windows), and builds and smoke-tests the wheel.

Roadmap

• v0.1: DuckDB adapter, catalog, six tools, three-layer guardrails, tests, CI

• v0.2: Snowflake and Databricks Unity Catalog adapters, native comments and PII tags, schema scoping

• v0.3: Python 3.14+, uv, ruff and ty; layered package; typed tool inputs and outputs; table names qualified by the policy

• Catalog import from dbt manifest.json

• Evaluation: 20 analyst questions answered with and without the server, scored for correctness and table choice

• Per-tool audit log

License

MIT