duckdb-analytics-mcp
The server provides a read-only, context-efficient analytics interface over a DuckDB warehouse, optimized for LLMs. It enables safe dataset exploration, schema understanding, column profiling, query validation, and execution.
Capabilities
list_datasets: Lists all tables with row counts, grain, and business-critical caveats (e.g., filter out cancelled orders for revenue).describe_table: Returns full schema details—columns, types, null rates, join keys, sample values, and semantic definitions—in one efficient call.profile_column: Deep-dives into a column’s distribution, outliers, coverage gaps, and formatting issues, adapting to numeric, date, or text types.explain: Shows DuckDB’s query plan and estimated row count without execution, helping avoid expensive mistakes.query: Executes a single read-onlySELECTwith automatic row/time caps, explicit truncation disclosure, and support for markdown/JSON output—encourages aggregation to save tokens.
Key Features
Strict Safety: Read-only mode, external access disabled, AST-based SQL parsing rejects all non-SELECT or dangerous operations, plus watchdog-enforced timeouts.
Semantic Layer: Surfaces business rules and caveats directly in tool outputs to prevent logically incorrect queries.
Token Efficiency: Returns compact markdown by default, advises aggregation over raw row fetching, and provides full truncation clarity.
Actionable Errors: Clear messages with concrete recovery guidance to lead the LLM toward successful interactions.
Provides read-only analytics over DuckDB databases, offering tools to list datasets, describe tables, profile columns, explain query plans, and run SELECT queries with row and time caps.
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type
@followed by the MCP server name and your instructions, e.g., "@duckdb-analytics-mcp Describe the orders table with caveats."
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
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-cappedQuickstart
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 belowThen 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: sqlite-mcp-server
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% |
|
| 1,738 |
| 410 | −76% |
truncation footer |
| ~2× query time | fetch | 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:
- 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_parquetwas not caught.sqlglotparses some functions into dedicated classes (exp.ReadParquet) and the rest intoexp.Anonymous; the guard only checked the latter. Fixed by checkingsql_names()on everyexp.Func.SELECT * FROM 'https://host/data.parquet'was not caught. DuckDB treats a quoted string inFROMas 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 aboveConfiguration 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/$refschema with an extra nesting level, which costs tokens in every tool listing.list_datasetsruns onecount(*)per table. Fine at four tables, wasteful at four hundred — it should readduckdb_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_tablebatches 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
Available Tools
5 toolsdescribe_tableARead-onlyIdempotent
Describe one table: columns, types, null rates, distinct counts, examples.
Call this before writing a query against an unfamiliar table. It answers "what can I filter on, and what will be NULL" in a single round trip, which a raw information_schema dump does not.
Args: table (str): Table name, case-insensitive (e.g. 'orders').
Returns: str: Markdown containing: - header with row count and grain - a table of (column, type, null%, distinct, range, examples) - column definitions from the semantic layer - declared join keys - caveats that affect correctness
On failure: "Error: No table named 'x'. Available tables: ..."Examples: - Use when: "What columns does orders have?" - Use when: a query failed with an unknown-column error. - Don't use when: you need the distribution of one column (use profile_column instead).
Inputs: table (required). Returns: result
explainARead-onlyIdempotent
Show the query plan and estimated row count WITHOUT executing the query.
Use this when a query might be expensive, or after one has timed out. The estimate comes from DuckDB's optimizer, so it is an estimate, not a guarantee -- but it is enough to catch an accidental cross join before it runs for fifteen seconds.
Args: sql (str): The SELECT to plan. It is not executed.
Returns: str: Markdown containing the estimated cardinality, a verdict on whether the query is worth running, and the physical plan.
On failure: "Error: <guard message>" for non-SELECT input.Examples: - Use when: a previous query hit the timeout. - Use when: joining two large tables for the first time. - Don't use when: you just want the answer (call query directly).
Inputs: sql (required). Returns: result
list_datasetsARead-onlyIdempotent
List every table in the warehouse with its row count, grain, and caveats.
Start here. The output is deliberately small -- one line per table plus the business rules that change query correctness -- so it is cheap to call before anything else.
Returns: str: Markdown containing: - a table of (table, rows, grain, description) - a "Before you write SQL" section listing every caveat in the warehouse, each prefixed with its table name
On failure: "Error: <message>".Examples: - Use when: "What data do I have access to?" - Use when: starting any analysis, before describe_table. - Don't use when: you already know the table and need its columns (use describe_table instead).
Inputs: no named parameters. Returns: result
profile_columnARead-onlyIdempotent
Profile one column: distribution, outliers, coverage gaps, dirty values.
The profile adapts to the column type:
numeric: min/p25/median/mean/p75/p95/max/stddev, plus a Tukey outlier count and the largest values when the distribution is skewed
date/timestamp: range, distinct days, and the largest gaps in coverage
text/boolean: top values with shares, plus a warning when values differ only by case or formatting
Args: table (str): Table name, case-insensitive. column (str): Column name, case-insensitive.
Returns: str: Markdown profile, ending with the column's definition and any caveats that mention it.
On failure: "Error: Table 'orders' has no column 'x'. Columns: ..."Examples: - Use when: "Is unit_price skewed? Should I use mean or median?" - Use when: "Are there missing days in order_date?" - Use when: a GROUP BY returned more groups than expected. - Don't use when: you want the whole schema (use describe_table).
Inputs: table (required), column (required). Returns: result
queryARead-onlyIdempotent
Run one read-only SELECT against the warehouse and return the rows.
Enforcement, in order: the statement is parsed and rejected unless it is a single SELECT; a LIMIT is injected if absent and lowered if it exceeds the server cap; execution is cancelled if it exceeds the time limit. When the result is truncated, the true total is counted and reported -- the output never implies it is complete when it is not.
Prefer aggregating in SQL over selecting raw rows. SELECT count(*), avg(x)
costs a handful of tokens; SELECT * costs hundreds and usually answers
less.
Args: sql (str): One SELECT statement in DuckDB SQL. max_rows (Optional[int]): Per-call row cap, clamped to the server cap (default 200). response_format (ResponseFormat): 'markdown' (default) or 'json'.
Returns: str: For 'markdown', an unpadded markdown table followed by a row-count footer that discloses truncation. For 'json', an object: { "columns": [str], "rows": [[Any]], "row_count": int, # rows returned "total_rows": int|null, # true total when truncated "truncated": bool, "elapsed_ms": float }
On failure: "Error: <message>" naming the recovery path.Examples: - Use when: "What was revenue by month in 2025?" -> aggregate in SQL. - Use when: "Show me 10 example rows from orders." - Don't use when: you do not yet know the column names (call describe_table first -- it is cheaper than a failed query).
Error Handling: - Non-SELECT statements, multiple statements, and filesystem functions are rejected before execution. - Queries exceeding the time limit are cancelled, not left running. - Unknown columns return DuckDB's message, which names the candidates.
Inputs: sql (required), max_rows, response_format. Returns: result
Tool Schema Changelog
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
Loading tool schema changelog…
TDQS
Scored across 5 tools
Each tool has a clearly distinct purpose: list_datasets for table inventory, describe_table for schema details, query for running SQL, profile_column for column-level analysis, and explain for query planning. There is no overlap or ambiguity between them.
All tool names follow a consistent lowercase_with_underscores convention, with a verb-led pattern (list_, describe_, query, profile_, explain). Though 'query' and 'explain' are single verbs, the naming is predictable and uniform.
With 5 tools, the server is well-scoped for an analytics warehouse. Each tool fills a necessary niche without redundancy, making the set feel lean but complete.
The tool surface covers the full analytical workflow: discover datasets, understand schema, profile data, run queries, and plan expensive queries. There are no obvious missing operations for the stated purpose of read-only analytics.
Maintenance
Related MCP Connectors
Query, join, profile, clean and convert CSV/JSON/Parquet with server-side DuckDB over MCP.
Hosted MotherDuck MCP server: explore data, run DuckDB SQL, and build Dives and Flights.
Query your warehouse or a CSV with Claude/ChatGPT over MCP, governed by table-level ACL + audit.
Query your org's data in natural language — read-only MCP access to SQL, NoSQL, files & warehouses.
Related MCP Servers
- AlicenseAqualityBmaintenanceA read-only MCP server that exposes SQL database access to LLMs, supporting multiple database types, compact columnar results, pagination, and file export.712 npmMIT
- AlicenseNot gradedqualityDmaintenanceA read-only MCP server that enables LLMs to safely explore and query any SQLite database via natural language. It exposes tools for listing tables, describing schemas, and executing SELECT/WITH queries with built-in safety guards like write prevention and row limits.MIT
- AlicenseNot gradedqualityDmaintenanceMCP server for SQL analytics on DuckDB and MotherDuck databases, enabling AI assistants and IDEs to query data via natural language.1MIT
- AlicenseAqualityCmaintenanceA minimal MCP server that provides a persistent DuckDB SQL engine to AI assistants, enabling natural-language querying of CSVs, Parquet, and cloud data with 12 tools and optional read-only mode.12MIT