Lakehouse Copilot

An AI data analyst for my streaming lakehouse. An MCP server gives a language model safe, read-only tools over the lakehouse's gold, silver and monitoring tables. An agent uses those tools to answer questions like "Which category made the most revenue?" or "Which producer is sending bad events?". An evaluation suite of 31 questions measures how often it gets the answer right, refuses unsafe requests, and admits when the data cannot answer.

flowchart LR
    U[Question] --> A[Agent loop<br/>Ollama · OpenAI · Claude]
    A <-->|MCP over stdio| S[MCP server<br/>7 read-only tools]
    S --> G{SQL guard<br/>sqlglot}
    G -->|allowed| D[(DuckDB, read-only<br/>no file or network access)]
    G -->|blocked| E[Readable error<br/>the agent can act on]
    S --> M[Semantic layer<br/>metrics.yml]
    M --> D
    D --- L[Lakehouse gold, silver<br/>and monitoring tables]
    EV[Eval suite<br/>31 questions] -.scores.-> A

What it demonstrates

Related MCP server: Data Platform MCP Server

The evaluation suite

The harness is tested from both directions. A reference agent that answers with the right SQL scores 100%, and an agent that always says "I'm not sure" scores 0%. CI runs both on every push. That proves the harness, the MCP round trip and the graders work. It says nothing yet about a real model.

Model results

Run make eval PROVIDER=ollama MODEL=qwen2.5:7b (or another provider) to produce a report in evals/results/. Results for real models are added here as they are measured.

Run it

Requires Python 3.11+ and git.

git clone https://github.com/charan32/lakehouse-copilot.git
cd lakehouse-copilot
python -m venv .venv && source .venv/bin/activate
make install
make demo-data      # clones and builds the streaming lakehouse locally (about a minute)
make test

Ask questions with a free local model

Install Ollama, then:

ollama pull qwen2.5:7b
make ask Q="Which product category made the most revenue?"
make ask Q="Why are events being rejected, and which producer should fix it first?"
make eval           # score the model on all 31 questions

To use Claude or OpenAI instead, set ANTHROPIC_API_KEY or OPENAI_API_KEY and add PROVIDER=anthropic or PROVIDER=openai.

Use it from Claude Desktop

Add the server to Claude Desktop's config (example in docs/claude_desktop_config.json), restart Claude Desktop, and ask questions about the lakehouse in the chat.

Tools the MCP server exposes

Project layout

copilot/
  server.py      MCP server (FastMCP) and its 7 tools
  guard.py       SQL guardrails (sqlglot)
  warehouse.py   read-only DuckDB access, catalog, semantic layer
  metrics.yml    metric definitions
  agent.py       tool-calling loop over an MCP client session
  providers.py   Ollama / OpenAI / Anthropic adapters
  ask.py         command-line interface
evals/
  questions.yaml 31 questions with reference SQL
  grading.py     rule-based graders
  run.py         runs the suite, writes Markdown and JSON reports
  oracle.py      perfect and useless agents for harness self-tests
tests/           46 tests

Design decisions

• Defense in depth instead of trusting the prompt. The system prompt asks the model to stay read-only, but nothing depends on it. The parser, the read-only connection and the limits each block writes on their own.

• Errors are written for the model. A blocked query returns a sentence such as "Table 'bronze.events' is not available. Use schema-qualified tables from: gold, monitoring, reference, silver", so the agent can correct itself instead of failing.

• Metrics before free-form SQL. Free-form SQL is where models make subtle mistakes, such as counting declined orders as revenue. Governed metrics remove that class of error for the common questions.

• Rule-based grading. Cheap, repeatable and auditable. The trade-off is that an unusual phrasing can be marked wrong, so every report shows the full answer next to the verdict.

• Expected answers computed at run time. The evals never go stale when the lakehouse data is regenerated.