Skip to content

motherduckdb/agent-skills / plugins/motherduck-skills-claude/skills/motherduck-explore

motherduck-explore

Verified

Find and understand MotherDuck databases, tables, columns, shares, and sample data, including which tables hold a given kind of data. Use first when a MotherDuck workspace or schema is unfamiliar.

FileKindSize
SKILL.mdskill md3.0 KB
references/EXPLORATION_PLAYBOOK.mdmarkdown5.8 KB

references/EXPLORATION_PLAYBOOK.md

View on GitHub
# Exploration Playbook

Reference for discovering databases, tables, columns, views, shares, and data quality signals in MotherDuck.

## Contents

| Section | Covers |
|---|---|
| Language Focus | Python vs TypeScript/JavaScript starters |
| Exploration Workflow (Steps 1-5) | Databases, tables/views, columns, `SUMMARIZE`, previews |
| Working with Shares | Listing, attaching, refreshing, querying shares |
| MCP Tools Available | MotherDuck MCP tool table and `query_rw` boundaries |
| Advanced Exploration Patterns | Pattern search, type search, row counts, nested types |
| Key Rules / Common Mistakes | Hard rules and failure patterns |

## Language Focus

- Prefer **Python** when exploration is part of notebook work, profiling source data before modeling, or batch validation scripts.
- Prefer **TypeScript/Javascript** when exploration is part of API endpoints, admin tools, or schema discovery inside developer tooling.
- In Python, small result sets can be fetched into DataFrames after the SQL is correct.
- In TypeScript/Javascript, keep exploration server-side and return compact summaries instead of raw catalog dumps.

### TypeScript/Javascript Starter

```ts
import pg from "pg";

const client = new pg.Client({
  host: "pg.us-east-1-aws.motherduck.com",
  port: 5432,
  database: "analytics",
  user: "postgres",
  password: process.env.MOTHERDUCK_TOKEN,
  ssl: { rejectUnauthorized: true },
});

await client.connect();
const databases = await client.query(`SELECT alias, type FROM MD_ALL_DATABASES()`);
const tables = await client.query(`
  SELECT database_name, schema_name, table_name, comment
  FROM duckdb_tables()
  WHERE database_name = 'analytics'
`);
await client.end();
```

### Python Starter

```python
import duckdb

conn = duckdb.connect("md:")
databases = conn.sql("SELECT alias, type FROM MD_ALL_DATABASES()").fetchall()
columns = conn.sql("""
    SELECT column_name, data_type, comment
    FROM duckdb_columns()
    WHERE database_name = 'analytics'
      AND table_name = 'orders'
""").fetchall()
conn.close()
```

## Exploration Workflow

1. List databases to see what is available.
2. List tables in the target database.
3. Inspect columns and types for the target table.
4. Run `SUMMARIZE` to get statistics.
5. Sample rows to see actual values.

## Step 1: List Databases

```sql
SELECT alias AS database_name, type
FROM MD_ALL_DATABASES();
```

## Step 2: List Tables in a Database

```sql
SELECT database_name, schema_name, table_name, comment
FROM duckdb_tables()
WHERE database_name = 'my_database';
```

### List Views

```sql
SELECT database_name, schema_name, view_name, comment, sql
FROM duckdb_views()
WHERE database_name = 'my_database';
```

## Step 3: Inspect Columns and Types

```sql
SELECT column_name, data_type, comment, is_nullable
FROM duckdb_columns()
WHERE database_name = 'my_database'
  AND table_name = 'my_table';
```

Pay attention to:

- `data_type`
- `is_nullable`
- `comment`

## Step 4: Get Quick Statistics with `SUMMARIZE`

```sql
SUMMARIZE "my_database"."main"."my_table";
```

`SUMMARIZE` returns one row per column with min, max, approximate distinct counts, percentiles, counts, and null percentages.

## Step 5: Preview Data

```sql
FROM "my_database"."main"."my_table" LIMIT 10;
```

When exploring several MotherDuck databases in one session, prefer a workspace connection (`md:`).

## Working with Shares

### List Shares Available to You

```sql
FROM MD_INFORMATION_SCHEMA.SHARED_WITH_ME;
```

### List Your Owned Shares

```sql
FROM MD_INFORMATION_SCHEMA.OWNED_SHARES;
```

### Attach a Shared Database

```sql
ATTACH '<share_url>' AS shared_db;
```

### Refresh Shared Data

```sql
REFRESH DATABASE shared_db;
```

### Query Shared Data

```sql
FROM shared_db.main.my_table LIMIT 10;
```

## MCP Tools Available

When using the MotherDuck MCP server, prefer:

| Tool | Purpose |
|---|---|
| `list_databases` | List attached databases |
| `list_tables` | List tables in a database |
| `list_columns` | List columns and types |
| `search_catalog` | Search the data catalog |
| `list_shares` | List available data shares |
| `query` | Execute read-only SQL |
| `query_rw` | Execute DDL, DML, or connection-state changes only when the user explicitly asks for a write and confirms the change |
| `ask_docs_question` | Clarify product or SQL behavior |

Use `search_catalog` when you do not know which database or table contains the data you need. Do not use `query_rw` for exploration that can be answered with read-only metadata or `SELECT` queries.

## Advanced Exploration Patterns

### Find Tables Matching a Pattern

```sql
SELECT database_name, schema_name, table_name, comment
FROM duckdb_tables()
WHERE table_name LIKE '%sales%';
```

### Find Columns of a Specific Type

```sql
SELECT table_name, column_name, data_type
FROM duckdb_columns()
WHERE database_name = 'my_db'
  AND data_type = 'TIMESTAMP';
```

### Get Table Row Counts

```sql
SELECT table_name, estimated_size
FROM duckdb_tables()
WHERE database_name = 'my_db'
ORDER BY estimated_size DESC;
```

### Find Columns by Name Across Tables

```sql
SELECT table_name, column_name, data_type
FROM duckdb_columns()
WHERE database_name = 'my_db'
  AND column_name LIKE '%customer%';
```

### Explore Nested and Complex Types

```sql
SELECT complex_column
FROM "my_db"."main"."my_table"
LIMIT 5;
```

```sql
SELECT UNNEST(list_column)
FROM "my_db"."main"."my_table"
LIMIT 20;
```

## Key Rules

- Explore top-down: databases, then tables, then columns.
- Run `SUMMARIZE` before writing analytical queries.
- Use fully qualified table names.
- Check shared databases before concluding data is unavailable.
- Read table and column comments.
- Use MCP tools when available.

## Common Mistakes

- Querying tables without checking the schema first
- Missing shared databases
- Skipping `SUMMARIZE`
- Using unqualified table names
- Ignoring views that already contain curated logic
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall