Skip to main content
Glama
duckdb-analytics-mcp

duckdb-analytics-mcp

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

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:

- 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

Available Tools

5 tools
describe_tableA
Read-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

explainA
Read-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_datasetsA
Read-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_columnA
Read-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

queryA
Read-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

    A4.9/5.0

    Scored across 5 tools

    Disambiguation5/5

    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.

    Naming Consistency5/5

    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.

    Tool Count5/5

    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.

    Completeness5/5

    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

    ActivityInactive
    ResponsivenessOutdated data

    Related MCP Connectors

    Related MCP Servers

    • A
      license
      A
      quality
      B
      maintenance
      A read-only MCP server that exposes SQL database access to LLMs, supporting multiple database types, compact columnar results, pagination, and file export.
      7
      12 npm
      MIT
    • A
      license
      Not graded
      quality
      D
      maintenance
      A 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
    • A
      license
      Not graded
      quality
      D
      maintenance
      MCP server for SQL analytics on DuckDB and MotherDuck databases, enabling AI assistants and IDEs to query data via natural language.
      1
      MIT
    • A
      license
      A
      quality
      C
      maintenance
      A 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.
      12
      MIT