Content
# Tool List
## DuckDB Local MCP — Agent-Native Data Pipeline
Local DuckDB MCP Server + dlt data pipeline, replicating MotherDuck Flights core process:
AI agent operates DuckDB through MCP using dlt for declarative pipelines, scheduled by cron.
**No MotherDuck account required. No cloud services dependency.**
## Architecture
```
AI Agent (Hermes / Claude / Cursor)
│
├── MCP Server (mcp-server-motherduck)
│ └── Tools: execute_query, list_tables, list_databases, list_columns
│ └── Support: local .duckdb / :memory: / S3
│
├── dlt (data load tool)
│ └── Declarative pipeline: schema evolution, incremental merge, Parquet bulk load
│
└── Cron Scheduler
└── no_agent mode: pure script execution, zero token consumption
│
▼
~/data/main.duckdb
```
## Quick Start
### 1. Installation
```bash
# DuckDB MCP Server
uv venv ~/envs/duckdb-mcp
uv pip install --python ~/envs/duckdb-mcp/bin/python mcp-server-motherduck
# dlt data pipeline environment
uv venv ~/envs/dlt
uv pip install --python ~/envs/dlt/bin/python "dlt[duckdb]" httpx pyarrow
```
### 2. Configure Hermes
Add the following under `mcp_servers:` in `~/.hermes/config.yaml` (note: YAML does not support `~`, replace with your actual home directory path):
```yaml
duckdb-local:
command: <YOUR_HOME>/envs/duckdb-mcp/bin/mcp-server-motherduck
args:
- --db-path
- <YOUR_HOME>/data/main.duckdb
- --read-write
enabled: true
timeout: 120
connect_timeout: 30
```
Restart Hermes to use.
### 3. Deploy Example Pipeline
```bash
# Copy script to Hermes scripts directory
cp scripts/github-stars-pipeline.py ~/.hermes/scripts/
# Run manually once for verification
~/scripts/github-stars-pipeline.py
```
### 4. Register dlt-pipeline Skill
```bash
cp -r skills/dlt-pipeline ~/.hermes/skills/devops/dlt-pipeline
```
Restart Hermes, and the agent can write data pipelines in natural language.
## MCP Tools
| Tool | Parameters | Description |
|:-----|:-----|:-----|
| `execute_query` | `sql: str` | Execute SQL query (up to 1024 rows) |
| `list_tables` | — | List all tables and views |
| `list_databases` | — | List all attached databases |
| `list_columns` | `table: str` | List table columns and types |
## Example Conversation
```
You: Find the top 10 repositories with the most stars in analytics.repos
Agent: [Call execute_query]
duckdb/duckdb 39,115 ⭐
duckdb/pg_duckdb 3,142 ⭐
duckdb/ducklake 2,826 ⭐
...
You: Help me write a pipeline to fetch popular articles from Hacker News API to DuckDB every hour
Agent: [Load dlt-pipeline skill → Write script → Deploy → Set cron]
```
## GitHub Stars Pipeline
`scripts/github-stars-pipeline.py` — Fetch star/fork/issue data for all DuckDB organization repositories:
```
3 organizations × 156 repositories, hourly incremental merge
```
Cron scheduling (Hermes):
```
Name: github-stars-pipeline
Script: github-stars-pipeline.py
Mode: no_agent (pure script, zero token)
Schedule: 0 */6 * * *
```
## Directory Structure
```
~/
├── envs/
│ ├── duckdb-mcp/ # mcp-server-motherduck (Python 3.14)
│ └── dlt/ # dlt[duckdb] + httpx + pyarrow
├── data/
│ └── main.duckdb # Database file
├── .hermes/
│ ├── config.yaml # MCP configuration
│ ├── scripts/
│ │ └── github-stars-pipeline.py
│ └── skills/devops/
│ └── dlt-pipeline/
│ └── SKILL.md
└── scripts/
└── github-stars-pipeline.py # Source file
```
## Comparison with MotherDuck Flights
| Dimension | MotherDuck Flights | This project |
|:-----|:-------------------|:-------|
| DuckDB Engine | MotherDuck cloud Duckling | Local .duckdb |
| MCP Tools | create_flight, run_flight | execute_query, list_tables |
| Pipeline Deployment | Cloud Python runtime | Local cron + Python |
| Secret Management | MotherDuck Secrets | Environment variables |
| Version Control | Flight version history | git |
| **Cost** | Paid | **Zero cost** |
| **Privacy** | Data in cloud | **Data locally** |
## Dependencies
- Python 3.9+
- [mcp-server-motherduck](https://github.com/motherduckdb/mcp-server-motherduck) (MIT)
- [dlt](https://github.com/dlt-hub/dlt) (Apache 2.0)
- [DuckDB](https://duckdb.org/) (MIT)
