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

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:

{
  "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.

Related MCP server: sqldb-mcp-server

The context argument, measured

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

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:

- 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:

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