# mcp-duckdb-analyst

A read-only [MCP](https://modelcontextprotocol.io) server that lets Claude (Desktop or Code) or any other MCP client explore and query local CSV, Parquet and JSON files, or an existing DuckDB database, with guarded SQL.

Most useful analysis questions in a mid-sized company start with "what is actually in this export?" and end long before a data warehouse project is justified. Handing a language model raw SQL access to those files is quick, but the default trade-off is uncomfortable: the same connection that can read a CSV can also write files, attach databases, install extensions and read anything on disk. This server keeps the useful part (discover tables, profile columns, run analytical SQL, explain plans) and removes the rest by design: a SQL guard, DuckDB's own sandbox settings, row and time caps, and a data directory the model cannot leave.

[![CI](https://github.com/nkrimmel/mcp-duckdb-analyst/actions/workflows/ci.yml/badge.svg)](https://github.com/nkrimmel/mcp-duckdb-analyst/actions/workflows/ci.yml)
[![License: MIT](https://img.shields.io/badge/license-MIT-blue.svg)](LICENSE)
[![Python 3.11+](https://img.shields.io/badge/python-3.11%2B-blue.svg)](pyproject.toml)

## Features

- **Zero-setup catalog**: every `.csv`, `.parquet`, `.json`, `.jsonl` and `.ndjson` file below `--data` (recursively) becomes a DuckDB view named from its relative path (`articles/2025.csv` becomes `articles_2025`), with deterministic handling of name collisions. Optionally attach an existing DuckDB file read-only with `--db`.
- **Nine analysis tools**: `list_tables`, `describe_table`, `profile_table`, `sample_rows`, `query`, `explain_query`, `search_columns`, `table_relationships`, plus opt-in `save_query` / `list_saved_queries`. All statistics are computed in SQL; no pandas.
- **Resources and prompt**: `duckdb://schema` and `duckdb://schema/{table}` give the model a Markdown schema overview; the `analyze_table` prompt encodes a repeatable first-pass analysis.
- **SQL guard** (`guard.py`, unit-tested with an 84-case allow/deny matrix): single `SELECT` / `WITH` / `DESCRIBE` / `SHOW` / `SUMMARIZE` / `EXPLAIN` statements only; every DDL/DML, `COPY`, `ATTACH`, `INSTALL`, `PRAGMA`, `SET` and friends is rejected wherever it appears, including inside CTEs and behind comments; file-reading functions may only reference files inside the data directory; `LIMIT` is enforced by wrapping the statement.
- **Second line of defence in DuckDB itself**: `enable_external_access = false`, `allowed_directories = [data dir]`, extension autoloading off, `lock_configuration = true`, `--db` attached `READ_ONLY`.
- **Bounded results**: row cap (`--max-rows`), cell cap (`--max-cells`), per-query timeout via `interrupt()` on a worker thread, a `truncated` flag and notes that tell the model how to refine.
- **Structured errors with hints**: every rejection comes back as `[code] message Hint: ...`, so the model can fix its call instead of guessing.
- **Two transports**: stdio (Claude Desktop, Claude Code) and streamable HTTP.
- **Synthetic sample data** for a fictional publisher (subscribers, orders, articles, web events), generated deterministically and checked in CI.

## Quickstart

Requirements: Python 3.11+ and [uv](https://docs.astral.sh/uv/).

```bash
git clone https://github.com/nkrimmel/mcp-duckdb-analyst.git
cd mcp-duckdb-analyst
uv sync
uv run mcp-duckdb-analyst --data examples/data --check
```

`--check` registers the files, prints the catalog to stderr and exits:

```text
mcp-duckdb-analyst 0.1.0 - data: .../mcp-duckdb-analyst/examples/data
table          kind   rows  cols  source
articles_2025  view    150     8  articles/2025.csv
articles_2026  view    110     8  articles/2026.csv
orders         view  1,200     9  orders.parquet
subscribers    view    300    13  subscribers.csv
web_events     view    900     8  web_events.jsonl
max-rows=500  max-cells=20000  timeout=30s  extensions=off  saved-queries=off  transport=stdio
```

Without `--check` the same command serves MCP over stdio. Run the tests with `uv run pytest -q`.

## Usage

### Claude Desktop

Add the server to `claude_desktop_config.json` (macOS: `~/Library/Application Support/Claude/`, Windows: `%APPDATA%\Claude\`). `uv run --directory` makes the project's virtual environment available without activating it; use absolute paths, Claude Desktop does not expand `~`.

```json
{
  "mcpServers": {
    "duckdb-analyst": {
      "command": "uv",
      "args": [
        "run", "--directory", "/ABSOLUTE/PATH/TO/mcp-duckdb-analyst",
        "mcp-duckdb-analyst", "--data", "/ABSOLUTE/PATH/TO/your-data-folder"
      ]
    }
  }
}
```

Restart Claude Desktop; the tools appear under the server name. To expose a DuckDB database as well, append `"--db", "/ABSOLUTE/PATH/TO/warehouse.duckdb"` to `args`.

### Claude Code

```bash
claude mcp add duckdb-analyst -- \
  uv run --directory /ABSOLUTE/PATH/TO/mcp-duckdb-analyst mcp-duckdb-analyst --data ./data
```

Or check a project-scoped `.mcp.json` into the repository whose data you want to analyse:

```json
{
  "mcpServers": {
    "duckdb-analyst": {
      "command": "uv",
      "args": ["run", "--directory", "/ABSOLUTE/PATH/TO/mcp-duckdb-analyst",
               "mcp-duckdb-analyst", "--data", "./data", "--max-rows", "200"]
    }
  }
}
```

### Streamable HTTP

```bash
uv run mcp-duckdb-analyst --data examples/data --transport streamable-http --port 8931
```

The endpoint is `http://127.0.0.1:8931/mcp`. A raw `initialize` request answers with the server's capabilities and instructions:

```text
event: message
data: {"jsonrpc":"2.0","id":1,"result":{"capabilities":{"prompts":{...},"resources":{...},"tools":{...}},
       "instructions":"Read-only analytical access to local data files via DuckDB SQL.\nTables (5): ...",
       "protocolVersion":"2025-06-18","serverInfo":{"name":"mcp-duckdb-analyst","version":"0.1.0"}}}
```

There is no authentication layer; bind to localhost (the default) or put a reverse proxy with auth in front of it.

### Example conversation

The sample data describes a fictional publisher, "Nordlicht Verlag". Tool calls and results below are real output from this server (abbreviated).

> **User:** Which subscription plans lose the most customers?
>
> **Claude** calls `list_tables()`:
> ```json
> {"tables": [{"name": "articles_2025", "kind": "view", "source": "articles/2025.csv", "row_count": 150, "column_count": 8},
>             {"name": "orders", "kind": "view", "source": "orders.parquet", "row_count": 1200, "column_count": 9},
>             {"name": "subscribers", "kind": "view", "source": "subscribers.csv", "row_count": 300, "column_count": 13}, ...]}
> ```
> then `query(sql="SELECT plan, count(*) AS subscribers, round(100.0 * avg(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END), 1) AS cancelled_pct FROM subscribers GROUP BY plan ORDER BY cancelled_pct DESC")`:
>
> | plan | subscribers | cancelled_pct |
> | --- | --- | --- |
> | student | 64 | 21.9 |
> | digital | 80 | 20.0 |
> | print_digital | 90 | 15.6 |
> | print | 66 | 13.6 |
>
> *(`truncated: false`, `elapsed_ms: 9.72`)*
>
> **Claude:** Student and digital-only plans cancel most often (21.9 % and 20.0 %), print-only plans least (13.6 %). Shall I break this down by acquisition channel?
>
> **User:** Yes. And export the subscriber table to CSV for me.
>
> **Claude** calls `query(sql="COPY subscribers TO '/tmp/subscribers.csv'")` and receives a tool error:
> ```text
> [denied_keyword] The statement contains 'COPY', which is not allowed on this read-only server.
> Hint: Only single SELECT / WITH ... SELECT / DESCRIBE / SHOW / EXPLAIN / SUMMARIZE statements are
> accepted by this read-only server. If this is a column name, double-quote it.
> ```
> then `query(sql="SELECT acquisition_channel, count(*) AS n, round(100.0 * avg(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END), 1) AS cancelled_pct FROM subscribers GROUP BY 1 ORDER BY cancelled_pct DESC")`:
>
> | acquisition_channel | n | cancelled_pct |
> | --- | --- | --- |
> | web | 48 | 25.0 |
> | partner | 64 | 21.9 |
> | newsletter | 53 | 18.9 |
> | phone | 66 | 15.2 |
> | app | 69 | 10.1 |
>
> **Claude:** The server is read-only, so I cannot write a CSV; here is the query you can run yourself. By channel, web sign-ups cancel most (25.0 %) and app sign-ups least (10.1 %).

### Python API

The MCP layer is thin; the same operations are available directly:

```python
from pathlib import Path

from mcp_duckdb_analyst import Analyst, Engine, ServerConfig

engine = Engine(ServerConfig(data_dir=Path("examples/data"), max_rows=500))
analyst = Analyst(engine)

result = engine.query(
    "SELECT plan, count(*) AS orders, round(sum(amount_eur), 2) AS revenue_eur "
    "FROM orders WHERE status = 'paid' GROUP BY plan ORDER BY revenue_eur DESC"
)
print(result.markdown)
print(result.truncated, result.row_count, result.elapsed_ms)

profile = analyst.profile_table("orders", ["plan", "amount_eur"])
print(profile.model_dump_json(indent=2))
```

Output (the profile JSON is condensed here; the real output has one key per line):

```text
| plan | orders | revenue_eur |
| --- | --- | --- |
| print_digital | 239 | 31837.37 |
| print | 176 | 18229.87 |
| digital | 195 | 6855.85 |
| student | 178 | 2716.95 |
False 4 1.39
{
  "name": "orders",
  "row_count": 1200,
  "columns": [
    {"name": "plan", "type": "VARCHAR", "is_numeric": false, "count": 1200, "null_count": 0,
     "null_pct": 0.0, "distinct_count": 4, "min": "digital", "max": "student", "mean": null, "stddev": null,
     "top_values": [{"value": "print_digital", "count": 364}, {"value": "digital", "count": 323},
                    {"value": "print", "count": 257}, {"value": "student", "count": 256}]},
    {"name": "amount_eur", "type": "DECIMAL(10,2)", "is_numeric": true, "count": 1200, "null_count": 0,
     "null_pct": 0.0, "distinct_count": 36, "min": 3.68, "max": 418.8, "mean": 73.488592,
     "stddev": 110.934603, "top_values": null}
  ],
  "low_cardinality_threshold": 20
}
```

`table_relationships()` on the sample data finds the two real foreign keys and nothing else:

```text
orders.subscriber_id     -> subscribers.subscriber_id  (containment 1.0, 295 sampled values, target unique)
web_events.subscriber_id -> subscribers.subscriber_id  (containment 1.0, 260 sampled values, target unique)
```

## How it works

```mermaid
flowchart LR
    C[MCP client<br/>Claude Desktop / Code] -- stdio or HTTP --> S[MCPServer<br/>tools, resources, prompt]
    S --> A[Analyst<br/>describe, profile, sample,<br/>search, relationships, explain]
    A --> E[Engine<br/>timeout, row and cell caps,<br/>error translation]
    S -- query --> E
    E --> G[SqlGuard<br/>tokenizer scan, statement type,<br/>AST walk, path check, LIMIT wrap]
    G --> D[(DuckDB in-memory<br/>views over files,<br/>optional read-only DB)]
    D --> F[data directory<br/>csv parquet json jsonl]
```

### Catalog

`catalog.py` walks the data directory (hidden entries and `saved_queries.json` are skipped) and creates one view per file using the matching reader (`read_csv`, `read_parquet`, `read_json` with `format = 'auto'` or `'newline_delimited'`). Names are lower-cased, non-alphanumeric runs become `_`, names starting with a digit get a `t_` prefix and reserved keywords a trailing `_`. Collisions (`sales/2026.parquet` next to `sales_2026.csv`, or a file named like a table in `--db`) get `_2`, `_3` ... in sorted path order, so the mapping is stable across restarts. Files DuckDB cannot read are reported in the startup summary and skipped.

### The SQL guard

Every statement sent to `query`, `explain_query` or `save_query` passes four independent checks; any one of them can reject it:

1. **Keyword scan on tokenizer output.** DuckDB's own tokenizer (`duckdb.tokenize`) classifies every token; only tokens of type *keyword* are compared against the deny list (`INSERT UPDATE DELETE TRUNCATE MERGE CREATE DROP ALTER COPY EXPORT IMPORT ATTACH DETACH INSTALL LOAD PRAGMA SET RESET CALL VACUUM CHECKPOINT FORCE GRANT REVOKE BEGIN COMMIT ROLLBACK PREPARE EXECUTE DEALLOCATE USE`). Comments and string literals are therefore never mistaken for SQL, and keywords hidden between comments or inside a CTE are still caught.
2. **Statement type.** `duckdb.extract_statements` must return exactly one statement (so `;` chaining fails) and its type must be `SELECT` or `EXPLAIN`; DuckDB represents `DESCRIBE`, `SHOW` and `SUMMARIZE` as `SELECT` statements internally. `PIVOT`/`UNPIVOT` expand into several statements and are rejected with a hint to use conditional aggregation.
3. **AST walk.** The statement is serialised with `json_serialize_sql`; every file-reading table function (`read_csv*`, `read_parquet`, `read_json*`, `glob`, `read_text`, `read_blob`, `sqlite_scan`, ...) and every path-like table reference (`FROM 'x.csv'`) must use string literals whose resolved path lies inside the data directory. Computed paths, `..` escapes, `~`, hidden files and remote schemes (`http://`, `s3://`, `hf://`, ...) are rejected. Accepted literals are rewritten to their absolute form so that DuckDB's `allowed_directories` check (which sees raw paths) accepts them too.
4. **LIMIT enforcement.** The statement is wrapped as `SELECT * FROM (...) AS _guarded LIMIT n + 1` with `n = min(limit, --max-rows)`; the extra row is how the `truncated` flag is detected without a second query. `EXPLAIN` is executed as-is.

The engine then executes the statement on a worker thread and calls `connection.interrupt()` when `--timeout-s` elapses, lowers the row limit further if `rows x columns` would exceed `--max-cells`, converts values to JSON-safe types (dates to ISO strings, decimals to numbers) and renders a Markdown table alongside the row list.

### Security model

| Layer | What it guarantees | Where |
| --- | --- | --- |
| SQL guard | Only read statements; file access only inside the data directory; one statement per call; bounded rows | `guard.py` |
| DuckDB configuration | `enable_external_access = false` plus `allowed_directories = [data dir]`: even generated SQL cannot read or write elsewhere; `autoinstall/autoload_known_extensions = false`; `lock_configuration = true` freezes all of this for the life of the process; `--db` is attached `READ_ONLY` | `engine.py` |
| Resource caps | `--max-rows`, `--max-cells`, `--timeout-s` (interrupt on a worker thread); spill files go to a per-process directory under the system temp dir | `engine.py` |
| Transport | stdio by default: no network listener at all; HTTP binds to `127.0.0.1` unless told otherwise | `cli.py` |
| Errors | Rejections are returned as tool errors (`isError: true`) with a code, message and hint; never as protocol failures or tracebacks | `server.py` |

What the server does **not** do: it does not authenticate HTTP clients, it does not cap memory (DuckDB's default limit applies), and `--allow-extensions` deliberately re-enables external access at the DuckDB level so `INSTALL`/`LOAD` can work; the SQL guard still applies, but use that flag only with clients you trust. Anything inside the data directory that is not a hidden file is readable by design, so point `--data` at a folder that contains only what the model may see.

## Configuration

All options are CLI flags of `mcp-duckdb-analyst`:

| Flag | Default | Meaning |
| --- | --- | --- |
| `--data PATH` | required | Directory scanned recursively for `.csv .parquet .json .jsonl .ndjson` |
| `--db PATH` | none | Existing DuckDB database file, attached read-only as catalog `db` |
| `--transport stdio\|streamable-http` | `stdio` | MCP transport |
| `--host`, `--port` | `127.0.0.1`, `8000` | Bind address for streamable HTTP; the endpoint is `/mcp` |
| `--max-rows N` | `500` | Hard cap on rows per result; `query(limit=...)` can only lower it |
| `--max-cells N` | `20000` | Cap on rows x columns per result |
| `--timeout-s S` | `30` | Per-query timeout; the query is interrupted |
| `--allow-extensions` | off | Accept `INSTALL`/`LOAD` and extension autoloading (relaxes the DuckDB sandbox) |
| `--allow-saved-queries` | off | Enable `save_query` / `list_saved_queries` (writes `saved_queries.json` into the data directory) |
| `--log-level LEVEL` | `WARNING` | Server log level; logs go to stderr |
| `--check` | | Register the files, print the catalog and exit |
| `--version`, `--help` | | |

Diagnostics always go to stderr because stdout carries the MCP protocol on stdio.

### Tools at a glance

| Tool | Returns |
| --- | --- |
| `list_tables()` | name, kind (`view`/`table`), source (relative file path or `db:<schema>.<name>`), row count, column count |
| `describe_table(name)` | columns with DuckDB type, declared nullability, observed null count, three sample values |
| `profile_table(name, columns?)` | per column: count, null %, distinct, min, max, mean and sample stddev for numerics, top-5 values with counts when distinct <= 20 |
| `sample_rows(name, n=10)` | reproducible reservoir sample (fixed seed) |
| `query(sql, limit?)` | columns, JSON rows, Markdown table, `truncated`, `row_limit`, `elapsed_ms`, the executed SQL, notes |
| `explain_query(sql)` | DuckDB's physical plan as text |
| `search_columns(pattern)` | columns across all tables matching a substring or `*`/`?` glob |
| `table_relationships()` | `*_id` columns whose sampled values are contained (>= 90 %) in a unique column of a matching table |
| `save_query(name, sql, description?)`, `list_saved_queries()` | opt-in named queries, guard-validated before writing |

## Project structure

```text
mcp-duckdb-analyst/
├── .github/workflows/ci.yml       lint + tests on Python 3.11 and 3.13, sample-data reproducibility
├── examples/data/                 synthetic publisher data set (229 KB)
│   ├── articles/2025.csv, 2026.csv
│   ├── orders.parquet
│   ├── subscribers.csv
│   └── web_events.jsonl
├── scripts/make_sample_data.py    deterministic generator (--check verifies the committed files)
├── src/mcp_duckdb_analyst/
│   ├── analysis.py                describe / profile / sample / search / relationships / explain
│   ├── catalog.py                 file discovery, view naming, collisions, --db objects
│   ├── cli.py                     Typer entry point (mcp-duckdb-analyst)
│   ├── config.py                  ServerConfig (pydantic)
│   ├── engine.py                  DuckDB connection, sandbox settings, timeout, caps
│   ├── errors.py                  AnalystError hierarchy (code, message, hint)
│   ├── formatting.py              JSON-safe values, Markdown tables, quoting
│   ├── guard.py                   the SQL guard
│   ├── models.py                  pydantic result models (= MCP output schemas)
│   ├── saved_queries.py           opt-in saved_queries.json store
│   └── server.py                  MCPServer assembly: tools, resources, prompt
├── tests/                         186 tests, see Development
├── CHANGELOG.md
├── LICENSE
├── pyproject.toml
└── uv.lock
```

## Development

```bash
uv sync                      # installs the package (editable) and the dev group
uv run pytest -q             # 186 tests, about 3 seconds
uv run ruff check .          # lint
uv run ruff format --check . # formatting
uv run python scripts/make_sample_data.py --out build/sample-data --check examples/data
```

The test suite covers the guard allow/deny matrix (including comments hiding keywords, `INSERT` inside a CTE, `ATTACH` in odd casing and whitespace, `read_csv` inside vs. outside the data directory, `LIMIT` rewriting and path rewriting), view naming and collisions, profile statistics against hand-computed values, every tool called in-process, the CLI, and one end-to-end session in which a real `mcp` stdio client spawns the server, lists tools, runs allowed and denied queries and reads a resource. Tests need no network access. CI runs the same commands on `ubuntu-latest` for Python 3.11 and 3.13 and checks that the sample data regenerates byte-for-byte.

## Limitations

- The sample data is entirely synthetic: names, cities, plans and prices are generated from a fixed seed and do not describe any real publisher or person.
- The guard is a deny-list over DuckDB's parser output plus DuckDB's own sandbox; it is designed to block writes and file escapes, not to hide data that lives inside the data directory. Do not point `--data` at a folder with files the model must not read.
- `PIVOT`/`UNPIVOT` are rejected because DuckDB expands them into several statements. Prefixed string literals (`E'...'`) are not rewritten and fall through to DuckDB's sandbox, which rejects relative paths.
- Result sets are capped and truncated; the server is meant for aggregation and inspection, not for bulk export.
- Memory is not capped by the server; a pathological join can still make DuckDB use a lot of RAM before the timeout hits.
- Requires the `mcp` Python SDK 2.x (`MCPServer`, formerly `FastMCP`).

## License

MIT, see [LICENSE](LICENSE).

Built by [Nicholas Krimmel](https://nicholaskrimmel.com) · [LinkedIn](https://www.linkedin.com/in/nicholas-krimmel/)