motherduckdb/agent-skills / plugins/motherduck-skills-claude/skills/motherduck-query
motherduck-query
VerifiedWrite, 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.
Query MotherDuck
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_guidefirst, 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 asCREATE SNAPSHOT,ALTER DATABASE ... SET SNAPSHOT,UNDROP DATABASE, and lifecycle commands such asSHUTDOWNas writes. Use the MotherDuck MCPquery_rwtool 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_agentwhen 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. Useprompt()for generated text or open extraction withstructorjson_schema. Both are per-row billable calls: test on a smallLIMIT, store results in a table, and rerun onlyNULLrows.
Workflow
- Confirm the actual tables, columns, and grain before writing SQL.
- Load relevant Guide context when MCP is available, without treating it as a substitute for schema inspection.
- Write the query in SQL first, then wrap it in Python or TypeScript only if needed.
- Use DuckDB-native patterns when they simplify the query; a simple lookup does not need a CTE or a materialization.
- Verify result shape and key aggregates. When performance is part of the request or execution shows a problem, check
EXPLAIN ANALYZEandmd_information_schema.query_history(wait_time,bytes_spilled_to_disk,instance_type) before changing the query or the Duckling size. - 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.mdfor DuckDB query patterns, exploration SQL, performance rules, common analytical shapes, and common mistakes - Read
references/AI_FUNCTIONS.mdwhen a query callsprompt_jev(),prompt(), orembedding() - Read
references/PERFORMANCE.mdwhen 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-connectfor session setupmotherduck-duckdb-sqlfor syntax and function referencemotherduck-explorefor understanding the source schema before writing queriesmotherduck-manage-guideswhen semantic definitions or reusable query rules need to be read or maintained
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| 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: 119k | Updated | Claude CodeCodex | Verified | Install |
| 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: 119k | Updated | Claude CodeCodex | Verified | Install |
| 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: 118k | Updated | Claude CodeCodex | Verified | Install |
| 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: 118k | Updated | Claude CodeCodex | Verified | Install |