mcp-duckdb-analyst

A read-only MCP 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.

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.

Related MCP server: text2sql-agent

Quickstart

Requirements: Python 3.11+ and uv.

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:

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 ~.

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

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:

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

Streamable HTTP

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:

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

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

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

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

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

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

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

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:

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

Tools at a glance

Project structure

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

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.

Built by Nicholas Krimmel · LinkedIn