<div align="center">

# mcp-bigquery-evals

**The BigQuery MCP server with mandatory dry-run cost caps and a reproducible NL-to-SQL eval harness.**

[![PyPI](https://img.shields.io/pypi/v/mcp-bigquery-evals.svg?color=blue)](https://pypi.org/project/mcp-bigquery-evals/)
[![accuracy](https://img.shields.io/endpoint?url=https://raw.githubusercontent.com/Umarfarook1/mcp-bigquery-evals/main/evals/badge.json)](#eval-harness)
[![CI](https://github.com/Umarfarook1/mcp-bigquery-evals/actions/workflows/ci.yml/badge.svg)](https://github.com/Umarfarook1/mcp-bigquery-evals/actions/workflows/ci.yml)
[![Python](https://img.shields.io/pypi/pyversions/mcp-bigquery-evals.svg)](https://pypi.org/project/mcp-bigquery-evals/)
[![License](https://img.shields.io/pypi/l/mcp-bigquery-evals.svg)](LICENSE)

`uvx mcp-bigquery-evals` &nbsp;·&nbsp; works with any MCP-compatible client &nbsp;·&nbsp; v0.1.0

</div>

---

## Why use this over the other BigQuery MCPs

| | Most BQ MCPs | `mcp-bigquery-evals` |
|---|---|---|
| Cost guardrails | none | **mandatory** dry-run before every query, refuses if over cap |
| Quality signal | "trust me" | **eval harness you can run yourself**, 15 golden pairs against `bigquery-public-data` |
| Write operations | usually enabled | **refused by statement type**, checked against BigQuery's own dry-run parse |
| Errors when things break | raw API exceptions | **7 stable error codes** an agent can switch on |
| Local dev without GCP | impossible | **in-memory sqlite-backed fake** ships in the box |

## What ships in the box

- **7 read-only MCP tools** for warehouse discovery and querying
- **Mandatory dry-run cost cap** on every `run_query` (default 100 MB scanned, about $0.0005 per query)
- **Read-only guard** on `run_query`: refuses any statement BigQuery's dry-run does not type as `SELECT`
- **Result-set-equivalence eval harness** (Spider/BIRD methodology) with 15 golden pairs against `bigquery-public-data`, runnable against your own model and project
- **Structured BigQuery errors** with 7 stable codes (`invalid_sql`, `table_not_found`, `permission_denied`, `unauthenticated`, `rate_limited`, `query_timeout`, `unknown`)
- **Two BigQueryClient implementations**: `RealBigQueryClient` (production, wraps `google-cloud-bigquery`) and `FakeBigQueryClient` (in-memory, sqlite-backed, for dev and CI without GCP credentials)

## Quickstart (5 minutes)

### 1. Install

```bash
uvx mcp-bigquery-evals --help
```

First run takes about 30s while `uv` fetches dependencies; subsequent runs are instant from the local cache. Plain `pip install mcp-bigquery-evals` also works.

### 2. Authenticate to GCP

```bash
gcloud auth application-default login
```

### 3. Wire into your MCP client

Open your MCP client's server config (developer settings) and add:

```json
{
  "mcpServers": {
    "bigquery": {
      "command": "uvx",
      "args": ["mcp-bigquery-evals", "serve"],
      "env": {
        "BIGQUERY_PROJECT": "YOUR_GCP_PROJECT_ID_HERE"
      }
    }
  }
}
```

Restart your client. The MCP indicator should show "bigquery" with 7 tools.

### 4. Try it

> Using the bigquery tool, find the top 5 most-viewed Stack Overflow questions tagged 'python'.

The agent chains `list_datasets`, `list_tables`, `describe_table`, `run_query` to answer. Every `run_query` is dry-run-cost-capped before execution.

Detailed setup, troubleshooting, and the alternative `pip` install path live in [`docs/mcp_client_setup.md`](docs/mcp_client_setup.md).

## The 7 tools

| Tool | Purpose |
|---|---|
| `list_datasets()` | List all datasets in your GCP project |
| `list_tables(dataset_id)` | List tables in a dataset |
| `describe_table(table_id)` | Schema, row count, size |
| `sample_table(table_id, n=5)` | Up to n sample rows |
| `search_schema(term)` | Fuzzy-match a term against all column names |
| `estimate_cost(sql)` | Free dry-run; returns bytes_scanned and estimated USD |
| `run_query(sql, max_bytes_scanned=100MB)` | Refuse non-`SELECT`, dry-run, refuse if over cap, then execute |

All seven tools read. `run_query` is the only one that executes SQL, and it refuses anything that is not a `SELECT`. See [Read-only guard](#read-only-guard) below for how, and [`docs/architecture.md`](docs/architecture.md) for why it is layered that way.

## Cost guardrails

Every `run_query` call dry-runs first (free) before execution. If the dry-run estimate exceeds `max_bytes_scanned`, the call returns a structured error rather than burning bytes:

```json
{
  "error": "cost_cap_exceeded",
  "would_scan": "1.4 GB",
  "cap": "100.0 MB",
  "estimated_usd": 0.007,
  "hint": "narrow your WHERE clause or pass max_bytes_scanned=1500000000 to override"
}
```

The agent reads the structured error and self-corrects (narrows the WHERE clause, raises the cap explicitly, picks a different table).

## Read-only guard

`run_query` executes reads only. Two checks, in this order:

1. The leading keywords of the SQL, with comments skipped. Costs nothing and catches the obvious case before any round trip.
2. The `statementType` BigQuery reports on the dry-run job. BigQuery has parsed the query by then, so a write cannot hide behind a comment, a CTE or a subquery.

Anything BigQuery does not type as `SELECT` comes back as a structured refusal:

```json
{
  "error": "write_statement_refused",
  "statement_type": "DROP_TABLE",
  "detected_by": "dry_run",
  "hint": "run_query executes read statements only. Rewrite this as a SELECT, or run the write yourself outside the MCP server."
}
```

The statement check runs before the byte cap, because DDL scans zero bytes and the cap would pass a `DROP` straight through.

This is not a substitute for IAM. Grant the service account `roles/bigquery.dataViewer` and `roles/bigquery.jobUser` so that a bug in this package is not the only thing between an agent and your tables.

## Eval harness

The repo ships a result-set-equivalence eval suite you can run against `bigquery-public-data` with your own model and GCP project. No accuracy number is published yet: the 15 golden pairs are unverified and the badge above reads `pending` until the suite runs. The methodology matches the Spider and BIRD academic benchmarks: execute both gold and predicted SQL, then compare result sets as multisets of rows (order-independent, with float tolerance, Decimal handling, NULL equality, NaN equality, ARRAY/STRUCT recursion, bool/int distinction).

Run locally:

```bash
mcp-bigquery-evals evals run --model <your-model-id>
```

Full methodology, golden-pairs YAML format, and how to add your own pairs: [`docs/how_evals_work.md`](docs/how_evals_work.md).

## Development

```bash
git clone https://github.com/Umarfarook1/mcp-bigquery-evals
cd mcp-bigquery-evals
python -m venv .venv && source .venv/bin/activate  # Windows: .venv\Scripts\activate
pip install -e ".[dev]"

pytest                    # unit tests (no GCP needed; 211 tests)
pytest -m bq              # real-BQ integration tests (needs GCP creds)
pytest -m live            # end-to-end with real model + real BQ
```

## Contributing

Issues and PRs welcome. Highest-leverage contributions:

1. **More verified golden NL-to-SQL pairs** against `bigquery-public-data`
2. **Prompt improvements** with the before/after eval reports from your own run attached
3. **Bug reports** with minimum reproductions

## License

MIT, see [`LICENSE`](LICENSE).