duckdb-mcp

An MCP server that lets an AI assistant explore and query data files with DuckDB — CSV, Parquet, JSON/NDJSON, Excel, compressed variants, globs, https:// URLs and s3:// buckets.

Any statement DuckDB accepts runs, reads and writes alike — CREATE, INSERT, COPY … TO, ATTACH and SET included. The connection is one in-memory database that lives as long as the server, so tables and views created in one call are still there in the next; nothing touches disk unless a statement says so.

The server is therefore exactly as powerful as the user account running it. It can read anything that account can read — SELECT * FROM read_text('~/.aws/credentials') is a legitimate query — write wherever it can write, and fetch any URL. Run it as a user whose access you are happy to hand over, and treat the files it reads as untrusted input to whatever model is driving it: a CSV whose contents suggest running COPY … TO is now a CSV the model can act on.

Install

The only thing to install is uv. Everything else is fetched and cached automatically.

winget install --id=astral-sh.uv -e     # Windows

curl -LsSf https://astral.sh/uv/install.sh | sh   # macOS / Linux

There is no clone and no virtualenv to manage: uvx re-resolves the repository each time it starts the server, so a new session already runs the current main — there is no separate update step.

The same re-resolution means the server will not start without a network. A warm cache does not save you, and neither does pinning the URL to a commit: uvx contacts GitHub before it runs anything, and exits with a git error instead of starting the server. If you need it to work offline, do a development checkout — that runs from a local environment with nothing to fetch — and point your MCP config at .venv/Scripts/duckdb-mcp (.venv/bin/duckdb-mcp on macOS/Linux) instead of uvx.

Claude Code

claude mcp add -s user duckdb -- uvx --from git+https://github.com/lab1702/duck-mcp duckdb-mcp

-s user registers the server for every project. Without it, claude mcp add defaults to local scope and the server is only available in the directory you ran the command in — it will not appear under /mcp anywhere else. Restart Claude Code afterwards; /mcp reads the config at startup.

Claude Desktop / other MCP clients

Add to your client's MCP config (claude_desktop_config.json for Claude Desktop):

{
  "mcpServers": {
    "duckdb": {
      "command": "uvx",
      "args": [
        "--from",
        "git+https://github.com/lab1702/duck-mcp",
        "duckdb-mcp"
      ]
    }
  }
}

Related MCP server: motherduck-mcp

Tools

Finding and reading data:

Checking that an answer is the right one. Most of what goes wrong with a data file is not an error — it is a plausible number that happens to be wrong. These four look for that:

Files are referenced by path directly in SQL — there is no import or registration step:

SELECT region, sum(amount) AS total
FROM 'data/sales_*.parquet'
WHERE order_date >= DATE '2026-01-01'
GROUP BY 1 ORDER BY total DESC

Joining across formats works the same way:

SELECT c.name, sum(s.amount)
FROM 'data/sales.parquet' s
JOIN 'data/customers.csv' c ON c.id = s.customer_id
GROUP BY 1

Writing

Statements that write are ordinary query calls. An expensive intermediate is worth keeping, since the database outlives the call:

CREATE TABLE monthly AS
SELECT date_trunc('month', order_date) AS month, region, sum(amount) AS total
FROM 'data/sales_*.parquet' GROUP BY 1, 2

Later calls query monthly directly, without rescanning the parquet. It lives in memory and goes away with the server; COPY monthly TO 'monthly.parquet' puts it on disk, and ATTACH 'warehouse.db' gives you somewhere durable to create tables in the first place.

A statement that returns no rows reports itself rather than an empty table (CREATE statement completed.), and one that changes rows returns DuckDB's own count of them.

Sampling instead of the head

preview_file returns the first rows. For a file written in time or partition order that is systematically unrepresentative — one date, one region, and often the oldest and most schema-drifted records in the set. sample_rows draws a uniform random sample instead:

sample_rows('data/events_2026.parquet', rows=20, seed=42)

That costs a full scan, which the head does not, so it is the right tool for understanding a file rather than for glancing at one. Passing seed fixes the draw, so a follow-up call revisits the same rows.

When the parse looks wrong

Every other tool reads through DuckDB's CSV/JSON parser, so if auto-detection misreads a file there is nothing to check its answer against — the result is plausible-looking but wrong data, with no error. DuckDB's sniffer is good, but a report footer is enough to defeat it:

id,name,amount
1,alice,5
2,bob,6
-- end of report --

describe_file reports one VARCHAR column named id,name,amount, three rows, no error. inspect_raw shows the lines next to what the sniffer concluded:

1 | id,name,amount
2 | 1,alice,5
3 | 2,bob,6
4 | -- end of report --

DuckDB's CSV sniffer reads this as: delimiter ',', skip 0 row(s), header row yes, 3 column(s).

Three columns against the one describe_file produced — the disagreement is the diagnosis. inspect_raw also prints the sniffer's own read_csv call, which here is already the fix, and can be pasted straight into query:

FROM read_csv('report.csv', auto_detect=false, delim=',', header=true,
              columns={'id': 'BIGINT', 'name': 'VARCHAR', 'amount': 'BIGINT'},
              ignore_errors=true);   -- returns the 2 real rows

inspect_raw reads only as many lines as you ask for, so it is safe on a multi-gigabyte file, and it tolerates input a real parse rejects — mixed line endings, unterminated quotes, ragged rows. Tabs, carriage returns and other invisible characters are escaped so they can be seen. It is text-only; parquet and Excel are rejected with a pointer to describe_file.

Parquet layout

Everything else here reads data. parquet_metadata reads the footer, so it answers layout questions on a file far too large to profile — and answers "how many rows?" for free, since parquet records that in metadata:

**events/*.parquet** — 12 parquet file(s), 4,500,000 rows, 45 row group(s), 210.5MB on disk

| column | type | codec | compressed | ratio | nulls | row_group_order |
| ------ | ---- | ----- | ---------- | ----- | ----- | --------------- |
| id     | INT64      | SNAPPY | 1.1MB  | 2.0x  | 0       | ascending       |
| ts     | INT64      | SNAPPY | 1.7MB  | 1.3x  | 0       | ascending       |
| label  | BYTE_ARRAY | SNAPPY | 1.4MB  | 3.4x  | 0       | ascending       |
| bucket | INT64      | SNAPPY | 6.7KB  | 16.7x | 0       | scattered (9/9) |
| notes  | BYTE_ARRAY | SNAPPY | 290B   | 0.9x  | 300,000 | no stats        |

row_group_order is the useful part. A parquet reader skips a row group when its recorded min/max cannot match the filter, so a range filter is cheap on a column whose row-group ranges climb through the file (ascending) and reads everything on one whose ranges overlap (scattered). Above, filtering on ts prunes; filtering on bucket — which cycles through the same seven values in every row group — cannot. no stats means the column records no min/max at all, so nothing can be inferred either way.

Comparisons are numeric when both bounds parse as numbers and lexicographic otherwise, which is what parquet itself does for strings and matches the ISO-8601 text DuckDB gives timestamps.

The tool also flags layouts that make scans cost more than the data warrants — undersized row groups, or a glob of many small files:

Note: row groups average only 1,000 rows. Small row groups add per-group
overhead and give the reader less to parallelise over; 128MB-ish groups are
the usual target.

Note: 4 files averaging 4.3KB each. Many small files cost one open per file,
which dominates on object storage.

row_groups=True adds a per-row-group table of row counts and sizes, for finding uneven splits. Actual value ranges are not reported here — footer statistics are approximate by design; use profile_columns for real min/max.

Schema drift across a glob

Reading data/*.parquet when the files disagree is not reliably an error. DuckDB takes its schema from the first file and reconciles the rest against it, so two of the four possible outcomes are silent and wrong:

compare_schemas reports which of those you are in:

**data/*.parquet** — 3 files, 2 distinct schemas across 3 compared

A plain read takes its schema from the first file, `a.parquet`: id INTEGER, amount INTEGER

| column | files | types                   | a plain read                              |
| ------ | ----- | ----------------------- | ----------------------------------------- |
| id     | 3/3   | INTEGER                 | ok                                        |
| amount | 3/3   | INTEGER (2), DOUBLE (1) | narrows DOUBLE to INTEGER — values are lost |
| extra  | 1/3   | VARCHAR                 | drops it — absent from a.parquet          |

Whether a difference is harmful depends on direction, so the tool asks DuckDB rather than guessing: the common supertype of the two types is the one that loses nothing, so a first file whose type is not that supertype must be narrowing. DOUBLE first and INTEGER later is fine and reported as such; INTEGER first and DOUBLE later loses your decimals.

The fix in every case is to reconcile by name, which the tool prints with the reader matching your format:

SELECT * FROM read_parquet('data/*.parquet', union_by_name=true)

Schemas are read one file at a time, so cost is linear in the file count. Above max_files (default 100) it compares a spread across the glob rather than the first N — drift tends to track write order, so the first N files would be the oldest and would miss exactly the recent change worth finding. The first and last file are always included. Files that cannot be read at all are reported rather than skipped silently.

Checking a join before trusting it

If the right side of a join holds more than one row per key, the join duplicates left rows and every aggregate over the result is inflated. There is no error, and the total still looks plausible:

sum(amount) alone      = 10000.0
sum(amount) after join = 11000.0     <- 20 duplicate customer ids

check_join reports that from grouped key counts, without materialising the join:

**orders.parquet** ⋈ **customers.parquet** on customer_id = id

| side  | rows  | distinct keys | max rows per key | matched        | unmatched | null keys |
| ----- | ----- | ------------- | ---------------- | -------------- | --------- | --------- |
| left  | 1,000 | 200           | 5                | 1,000 (100.0%) | 0         | 0         |
| right | 220   | 200           | 2                | 220 (100.0%)   | 0         | 0         |

Relationship: many-to-many — up to 5 left rows and 2 right rows per key.
The join yields 1,100 rows from 1,000 on the left.

Direction is what matters: five orders per customer is normal, two customer rows per id is the bug. So many-to-one is reported as a safe lookup while many-to-many gets a warning. The predicted row count is exact — a test asserts it against the join it declined to run.

It also reports rows that match nothing, so you can see an inner join dropping half your data, and diagnoses a join returning nothing at all: keys that are entirely NULL, or key columns whose types cannot be compared.

Gaps in a series

A daily table missing three days still sums and averages perfectly happily — the total is just quietly short. One where a day was loaded twice sums too high. Neither shows up as an error, and neither changes anything a row count would reveal:

count(*) = 29, sum = 290       <- looks entirely fine

check_coverage infers the step from the data and reports what is absent:

29 rows, 28 distinct values from 2026-01-01 to 2026-01-31.
Step looks like 1 day (25 of 27 intervals), so 31 values were expected.

3 missing (9.7% of the expected range) across 2 gaps.

| after      | before     | missing |
| ---------- | ---------- | ------- |
| 2026-01-08 | 2026-01-11 | 2       |
| 2026-01-24 | 2026-01-26 | 1       |

1 repeated value. A sum over this column double-counts them.

| value      | rows |
| ---------- | ---- |
| 2026-01-05 | 2    |

The step is inferred rather than assumed, so the same tool covers dates, timestamps and plain integer sequences. Calendar steps are not constant — a month is 28 to 31 days — so an interval counts as a gap only once it is half again the usual step, which finds a missing month without reporting February as a hole every year.

For timestamps carrying a time of day that really describe a daily series, pass granularity='day' (or hour, month, …) to bucket them first.

Finding where a value lives

find_value('data/*.parquet', 'NEEDLE')

Searches every column as text — numbers, dates and nested values included — and reports which columns match, with an example, plus which files they are in. Useful for locating a join key in a dataset whose schema you do not know yet, which is exactly when you cannot write the query yourself. % and _ in the search value are matched literally rather than as wildcards.

Remote files

https:// and s3:// paths work through DuckDB's httpfs extension, which is installed on first start. S3 authentication uses DuckDB's credential chain, so AWS_ACCESS_KEY_ID/AWS_SECRET_ACCESS_KEY, ~/.aws/credentials, AWS_PROFILE and instance roles are all picked up automatically. Public buckets and URLs need no configuration.

Credentials are resolved once, at startup — if you add them afterwards, restart the server. On a machine with no AWS credentials at all, that step is skipped (DuckDB validates the chain when the secret is created) and public buckets still work. Likewise, if the machine is offline at startup, extension setup is skipped and local files still work. The server writes a line to stderr on startup reporting which of httpfs, excel and the S3 credential chain came up.

Because all of that happens at startup, a read that fails later says nothing about the real cause. So when something set up at startup is missing, the error carries the reason with it:

HTTP Error: HTTP GET error reading 'https://bucket.s3.amazonaws.com/data.parquet'
in region '' (HTTP 403 Forbidden) AccessDenied: Access Denied

No AWS credentials were resolved when the server started (unavailable: Secret
Validation Failure: ... Credential Chain: 'config'), so this bucket is being read
anonymously — which is what a 403 here usually means. DuckDB resolves credentials
once at startup, so setting them now requires a restart.

The same applies to an https:// or s3:// path when httpfs failed to install, and to .xlsx when excel did. The hint is added only when the capability is actually missing and the failure is the kind it would explain — a 404 on S3 is a missing object, not a credentials problem, and gets no hint.

Limits

Defaults, all overridable:

Truncated results say so explicitly, e.g. (showing first 500 rows (more available)), including when a schema or profile table is cut short by the size cap. The timeout covers a whole tool call, not each statement in it, so a tool that runs several queries still finishes within it; a call that overruns is cancelled and reported.

No row cap goes above 10,000, however it is set. A timeout of 0 means no time limit.

The memory ceiling is left to DuckDB by default, which sizes it against system memory — the right answer for the single instance this server runs. Setting it is for the case DuckDB cannot see: MCP starts one server process per client, so several concurrent sessions mean several DuckDB instances on one machine, each holding its own independent ceiling. --memory-limit takes a size with a unit (4GB, 512MB); percentages are not accepted.

Unusable values are not silently accepted: a bad flag is a startup error, and a bad environment variable is ignored with a line on stderr.

{
  "mcpServers": {
    "duckdb": {
      "command": "uvx",
      "args": ["--from", "git+https://github.com/lab1702/duck-mcp", "duckdb-mcp",
               "--max-rows", "1000", "--timeout", "300"]
    }
  }
}

Development

git clone https://github.com/lab1702/duck-mcp
cd duck-mcp
uv sync --extra dev --upgrade
uv run pytest

There is no committed uv.lock. Dependencies are declared with lower bounds only, so a fresh install — and uvx — resolves to the current duckdb and mcp releases. --upgrade re-resolves an existing checkout to the latest versions; run the tests after, since tracking upstream means meeting its breaking changes early rather than at a pinned upgrade later.

Or without uv:

python -m venv .venv
.venv/Scripts/python -m pip install -e ".[dev]"   # .venv/bin/python on macOS/Linux
.venv/Scripts/python -m pytest

License

MIT