# duckdb-analytics-mcp

A read-only analytics MCP server over DuckDB, built around one idea: **an LLM's
scarce resource is context, not compute.**

Most database MCP servers expose a single `run_sql(query)` tool and hand the raw
result back as JSON. That fails in two predictable ways. It blows the context
window — a 50,000-row result set cannot be read by a model, and truncating it
silently is worse than erroring. And it produces confidently wrong answers,
because a model that only sees column names has no way to know that `cancelled`
orders still have line items attached to them.

This server is an attempt to fix both.

```
list_datasets  →  what tables exist, and what is dangerous about each
describe_table →  columns, types, null rates, sample values, join keys
profile_column →  distribution, outliers, coverage gaps, dirty values
explain        →  estimated cost, without executing
query          →  a single read-only SELECT, row-capped and time-capped
```

## Quickstart

```bash
git clone https://github.com/<you>/duckdb-analytics-mcp
cd duckdb-analytics-mcp
pip install -e ".[dev]"
python scripts/generate_data.py     # builds the sample warehouse (~150k rows)
pytest                              # 105 tests, ~10s
python scripts/verify.py            # end-to-end over the MCP protocol
python bench/token_report.py        # reproduces the numbers below
```

Then register it with any MCP client:

```json
{
  "mcpServers": {
    "warehouse": {
      "command": "python",
      "args": ["-m", "duckdb_analytics_mcp"],
      "env": { "WAREHOUSE_DATA_DIR": "/absolute/path/to/duckdb-analytics-mcp/data" }
    }
  }
}
```

The sample dataset ships in the repo, so the server is queryable the moment it
starts. Point `WAREHOUSE_DATA_DIR` at a directory of your own Parquet files and
it will build a warehouse from those instead.

## The context argument, measured

From `bench/token_report.py`, counted with `tiktoken` `cl100k_base`:

| what | naive format | tokens | this server | tokens | change |
|---|---|---|---|---|---|
| 50 rows × 5 cols | JSON objects | 2,468 | markdown table | 962 | **−61%** |
| 50 rows × 5 cols | padded markdown | 1,233 | unpadded markdown | 962 | −22% |
| "revenue by status" | 200 raw rows | 2,848 | aggregate in SQL | 86 | **−97%** |
| query plan | DuckDB's default | 629 | compact tree | 49 | **−92%** |
| `orders` schema | `information_schema.*` | 1,738 | `describe_table` | 410 | −76% |
| truncation footer | `count(*)` second pass | ~2× query time | fetch `limit + 1` | within noise | **free** |

Three decisions come out of that table:

**JSON repeats every key on every row.** Twenty rows of six columns means 120
redundant key tokens. A markdown table names each column once.

**Alignment padding is pure cost.** Pretty-printed tables pad cells with spaces
so the pipes line up. A model does not need the pipes to line up.

**The cheapest result set is the one you never return.** The largest saving on
that table is not a formatting trick — it is `query`'s tool description telling
the model to aggregate in SQL instead of pulling rows. 2,848 tokens of raw rows
answer the question worse than 86 tokens of `GROUP BY`.

## Truncation is disclosed, always

A silently truncated result is the worst failure mode available to a database
tool, because the model will average the visible 20 rows and report it as the
answer. Every truncated result says so, and says how big the real answer was:

```
order_id | status
--- | ---
100001 | returned
100002 | cancelled
100003 | completed

_Showing 3 of 50,000 matching rows (no LIMIT was given, so one was applied).
**The remaining 49,997 rows are not shown** -- do not treat this sample as the
full result set. Add your own LIMIT/OFFSET to page through, or aggregate in SQL
instead of pulling rows._
```

Truncation is detected by fetching one row past the cap: if that row comes
back, there is more. It is the same execution, so the cost stays inside
measurement noise. Knowing the *exact* total is a different matter — it needs a
second full execution wrapped in `count(*)`, which measured **+82% to +174%**
across runs, i.e. roughly double. That is now opt-in via `exact_total: true`
instead of being charged on every truncated query.

The same rule applies one dimension over. A cell value too long to print is
shortened, and the footer says so:

```
_200 rows. **3 cell values were shortened** to 60 characters (longest
original: 4,182 characters) -- treat those values as incomplete, and select a
substring or an aggregate in SQL if you need the full content._
```

A cut JSON blob that looks like a whole one is the same failure as a cut
result set that looks complete.

## The semantic layer

This is the part that changes answers rather than costs.

`data/datasets.yaml` carries, per table, the business rules that determine
whether a query is *correct* — as opposed to whether it *runs*. They are
surfaced by `list_datasets` and `describe_table`, so the model sees them before
it writes SQL rather than after:

```yaml
- table: orders
  caveats:
    - "REVENUE RULE: exclude status IN ('cancelled','returned'). Cancelled and
       returned orders still have order_items rows attached, so an unfiltered
       join overstates revenue by 13.6%."
    - "There are zero orders between 2025-03-10 and 2025-03-16 (a checkout
       outage, not missing data)."
```

None of those are SQL errors. A query that ignores them runs fine, returns a
number, and the number is wrong by 13.6%. That is the failure mode a schema
dump cannot prevent, and it is the reason `describe_table` exists instead of
letting the model read `information_schema` itself.

The claims in that file are load-bearing, so the test suite asserts them
against the data. `test_revenue_caveat_is_arithmetically_true` recomputes the
13.6% figure, and `tests/test_catalog.py` checks every table, column, key and
backticked name the YAML mentions against the live schema — a caveat naming a
column that was renamed is worse than no caveat, because it reads as
authoritative.

`profile_column` finds the same class of problem empirically, without being
told:

```
# customers.country
value | count | share
--- | --- | ---
US | 1,269 | 31.7%
...
us | 52 | 1.3%

⚠ **Case/format collisions:** 'us' (2 variants). Grouping on this column raw
will split what is really one value. Normalise with `lower()` or `upper()`.
```

On a date column it reports gaps in coverage; on a numeric column it reports
Tukey outliers and tells you when the mean has been dragged off the median.

## Security model

The threat is not a malicious user — it is a model that has read a prompt
injection in a data cell and is now trying to write files. Two independent
layers, neither trusting the other:

**1. The connection cannot write.** DuckDB is opened `read_only=True` with
`enable_external_access=false`. No `httpfs`, no `read_csv` on arbitrary paths,
no `ATTACH`. Even a query that defeated the parser would find nothing to do.

**2. Every statement is parsed before it runs.** `guards.py` uses `sqlglot` to
build an AST and rejects anything that is not a single `SELECT`. Parsing, not
regex — a regex guard is defeated by `SELECT/**/1;DROP TABLE t` and false-fires
on a customer named "Delete Co.". There is a test for both.

Blocked: every DDL/DML statement, multi-statement queries, filesystem and
network functions, and — via an allowlist on the `FROM` clause — any data
source that is not an existing table, CTA, subquery, or safe generator.

Writing that test suite found two real bypasses in my own guard:

- `read_parquet` was not caught. `sqlglot` parses some functions into dedicated
  classes (`exp.ReadParquet`) and the rest into `exp.Anonymous`; the guard only
  checked the latter. Fixed by checking `sql_names()` on every `exp.Func`.
- `SELECT * FROM 'https://host/data.parquet'` was not caught. DuckDB treats a
  quoted string in `FROM` as a file to scan. Fixed by rejecting path-shaped
  identifiers.

Both are in `tests/test_guards.py` as regression tests.

Runaway queries are the other half. DuckDB has no `statement_timeout`, so
`engine.py` runs a watchdog thread that calls `connection.interrupt()` at the
deadline. `explain` exists so the model can check first — it catches an
accidental cross join at 5.1 billion estimated rows without executing anything.

## Errors are recovery instructions

An error a model cannot act on is a dead end. Every rejection names the path
forward:

```
Error: DROP is not allowed. This server is read-only and exposes exactly one
way to read data: a single SELECT statement. To explore what is available,
call `list_datasets` for the table list or `describe_table` for columns.

Error: No table named 'ordrs'. Available tables: customers, order_items,
orders, products. Call `list_datasets` for descriptions of each.

Error: Query exceeded the 15s limit and was cancelled. Narrow it with a WHERE
clause, aggregate instead of selecting raw rows, or call `explain` first to see
the estimated scan size.
```

`test_rejection_messages_name_a_recovery_path` asserts this for every blocked
statement in the suite.

## Evaluations

MCP has no real testing story, so `EVALS.md` holds ten natural-language
questions with verified answers, and `evals/evaluation.xml` holds the same set
in the harness format. Seven of the ten are designed so that the obvious query
returns a plausible wrong answer — they test whether the semantic layer is
actually doing its job:

| question | naive answer | correct answer |
|---|---|---|
| 2025 revenue | 69,713,408 | **61,207,080** |
| US customers | 1,269 | **1,321** |
| typical unit price | 689.70 (mean) | **424.29** (median) |

Answers were computed directly against the warehouse, not by asking a model.
The dataset is seeded, so they are stable across machines, and
`scripts/verify.py` re-derives all ten through the live `query` tool and fails
if `EVALS.md` or `evaluation.xml` has drifted from them.

## Layout

```
src/duckdb_analytics_mcp/
  server.py      five tools, input schemas, error mapping
  guards.py      the SQL security boundary
  engine.py      execution, timeouts, compact EXPLAIN
  profiling.py   describe_table / profile_column
  catalog.py     the semantic layer loader
  formatting.py  token-efficient rendering
  warehouse.py   read-only connection, warehouse build
data/            sample Parquet + datasets.yaml
tests/           105 unit tests
scripts/         data generator + end-to-end protocol verification
bench/           the token measurements above
```

Configuration is by environment variable: `WAREHOUSE_DATA_DIR`,
`WAREHOUSE_DB_PATH`, `WAREHOUSE_CATALOG_PATH`, `WAREHOUSE_MAX_ROWS` (200),
`WAREHOUSE_TIMEOUT_S` (15), `WAREHOUSE_MAX_CELL_CHARS` (60).

## Notes and limitations

- Tool parameters are flat (`sql=...`) rather than wrapped in a Pydantic model.
  Both work; a wrapper emits a `$defs`/`$ref` schema with an extra nesting
  level, which costs tokens in every tool listing.
- `list_datasets` runs one `count(*)` per table. Fine at four tables, wasteful
  at four hundred — it should read `duckdb_tables()` instead.
- Exact `COUNT(DISTINCT)` is budgeted in *cells* (rows × columns), not rows,
  because that is what drives the cost — a 50k-row table with 150 columns is
  far more expensive than a 4M-row table with three. Above the budget it falls
  back to HyperLogLog and says so.
- `describe_table` batches its aggregates 25 columns at a time and caps output
  at 60 columns by default. A single monolithic query over a wide table has no
  partial result: exceed the deadline and you get nothing. It took 21.7s on a
  150-column table before this change, against a 15s server timeout — the tool
  used to kill itself on exactly the tables where a schema description helps
  most. Now 3.9s.
- Reads get one DuckDB cursor per query rather than sharing one behind a lock.
  Throughput barely moves (DuckDB already parallelises a single query), but a
  cheap call no longer queues behind an expensive scan: 3ms instead of waiting
  out the whole slow query.
- The semantic layer is hand-written YAML. The obvious next step is generating
  it from a dbt `manifest.json`, where these definitions usually already live.
- Single-database, stdio only. Multi-tenant use would need streamable HTTP,
  per-tenant connections, and auth.

## License

MIT