# motherduck-query
> Write, run, optimize, or troubleshoot DuckDB SQL against MotherDuck, including AI functions such as prompt_jev for in-SQL classification. Use for any SQL that targets MotherDuck, even simple queries, because MotherDuck has its own dialect and naming rules.
Write, run, optimize, or troubleshoot DuckDB SQL against MotherDuck, including AI functions such as prompt_jev for in-SQL classification. Use for any SQL that targets MotherDuck, even simple queries, because MotherDuck has its own dialect and naming rules.

[Utility skill](/sql-reference/agent-skills/#utility-skills) · [View source on GitHub](https://github.com/motherduckdb/agent-skills/tree/v3.0.0/skills/motherduck-query)

:::info

This page shows the skill's `SKILL.md`: the instructions a coding agent loads when a task matches the description above. To install every MotherDuck skill in Claude Code, Codex, Cursor, or Gemini CLI, see [Install MotherDuck Skills](/key-tasks/ai-and-motherduck/agent-skills/). To install only this skill with the [Skills CLI](https://github.com/vercel-labs/skills), run:

```bash
npx -y skills add motherduckdb/agent-skills --skill motherduck-query --yes --global
```

:::

## Prerequisites

- An established MotherDuck connection (or an active MotherDuck MCP server)
- Target database and tables identified

## Default posture

- When MotherDuck MCP is available and the SQL answers a business question, call `get_query_guide` first, then read only the relevant Guide topics and validate their claims against the live schema.
- Write DuckDB SQL, not PostgreSQL SQL, even when using the PG endpoint.
- Prefer fully qualified `"database"."schema"."table"` names; they are required once more than one database or share is attached.
- Preserve result grain and check join cardinality before optimizing or materializing a query.
- Filter early, aggregate early, and prefer serving tables or summaries for repeated reads.
- Keep SQL obvious, multi-line, and explicit about grain, filters, and output shape.
- Treat DDL, DML, `ATTACH`, `DETACH`, recovery commands such as `CREATE SNAPSHOT`, `ALTER DATABASE ... SET SNAPSHOT`, `UNDROP DATABASE`, and lifecycle commands such as `SHUTDOWN` as writes. Use the MotherDuck MCP `query_rw` tool when the user's change request authorizes the write. Ask for confirmation only when the action is destructive, externally visible, or outside the stated scope.
- Tag long-lived integrations with `custom_user_agent` when the connection path supports it; the tag appears per query in the Duckling Overview and in query history.
- For classification, scoring, routing, or yes/no judgments over text or images, use `prompt_jev()`: it returns typed probabilities and labels, bills input tokens only, and batches rows. Use `prompt()` for generated text or open extraction with `struct` or `json_schema`. Both are per-row billable calls: test on a small `LIMIT`, store results in a table, and rerun only `NULL` rows.

## Workflow

1. Confirm the actual tables, columns, and grain before writing SQL.
2. Load relevant Guide context when MCP is available, without treating it as a substitute for schema inspection.
3. Write the query in SQL first, then wrap it in Python or TypeScript only if needed.
4. Use DuckDB-native patterns when they simplify the query; a simple lookup does not need a CTE or a materialization.
5. Verify result shape and key aggregates. When performance is part of the request or execution shows a problem, check `EXPLAIN ANALYZE` and `md_information_schema.query_history` (`wait_time`, `bytes_spilled_to_disk`, `instance_type`) before changing the query or the Duckling size.
6. Materialize expensive repeated queries into serving tables or light views when warranted.

## References

Read only the reference sections needed for the current task.

- Read [`references/QUERY_PLAYBOOK.md`](https://github.com/motherduckdb/agent-skills/blob/v3.0.0/skills/motherduck-query/references/QUERY_PLAYBOOK.md) for DuckDB query patterns, exploration SQL, performance rules, common analytical shapes, and common mistakes
- Read [`references/AI_FUNCTIONS.md`](https://github.com/motherduckdb/agent-skills/blob/v3.0.0/skills/motherduck-query/references/AI_FUNCTIONS.md) when a query calls `prompt_jev()`, `prompt()`, or `embedding()`
- Read [`references/PERFORMANCE.md`](https://github.com/motherduckdb/agent-skills/blob/v3.0.0/skills/motherduck-query/references/PERFORMANCE.md) when a query is slow, spills, runs out of memory, or a Duckling looks undersized

## Related skills

Load related skills only for missing capabilities; reuse established context.

- [`motherduck-connect`](/sql-reference/agent-skills/motherduck-connect/) for session setup
- [`motherduck-duckdb-sql`](/sql-reference/agent-skills/motherduck-duckdb-sql/) for syntax and function reference
- [`motherduck-explore`](/sql-reference/agent-skills/motherduck-explore/) for understanding the source schema before writing queries
- [`motherduck-manage-guides`](/sql-reference/agent-skills/motherduck-manage-guides/) when semantic definitions or reusable query rules need to be read or maintained

## Files in this skill

The agent reads these files when the instructions above point to them.

- [`references/AI_FUNCTIONS.md`](https://github.com/motherduckdb/agent-skills/blob/v3.0.0/skills/motherduck-query/references/AI_FUNCTIONS.md)
- [`references/PERFORMANCE.md`](https://github.com/motherduckdb/agent-skills/blob/v3.0.0/skills/motherduck-query/references/PERFORMANCE.md)
- [`references/QUERY_PLAYBOOK.md`](https://github.com/motherduckdb/agent-skills/blob/v3.0.0/skills/motherduck-query/references/QUERY_PLAYBOOK.md)

## Docs this skill draws on

- [duckdb.org/docs](https://duckdb.org/docs/)
- [DuckDB SQL](https://motherduck.com/docs/sql-reference/duckdb-sql-reference/)
- [MotherDuck SQL](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/)
- [SHUTDOWN](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/shutdown-terminate/)
- [Data Recovery](https://motherduck.com/docs/concepts/database-and-storage/data-recovery/)
- [query_rw](https://motherduck.com/docs/sql-reference/mcp/core/query-rw/)
- [Postgres Endpoint](https://motherduck.com/docs/sql-reference/postgres-endpoint/)
- [PROMPT_JEV](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/prompt-jev/)
- [PROMPT](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/prompt/)
- [EMBEDDING](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/embedding/)
- [Optimizing query performance](https://motherduck.com/docs/key-tasks/query-performance/)
- [Duckling sizes](https://motherduck.com/docs/about-motherduck/billing/duckling-sizes/)


---

## Docs feedback

MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`.
For agents and automated tools, feedback submission should be user-confirmed before sending.

URL-encode query parameter values and send a GET request:

```text
GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fsql-reference%2Fagent-skills%2Fmotherduck-query%2F&page_title=motherduck-query&text=<url-encoded user feedback, max 2000 characters>
```

Optionally append `&source=<url-encoded interface identifier>` such as `claude.ai` or `chatgpt`.

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed query parameters, and `429` when rate-limited.
