motherduck-cookbook

name	motherduck-cookbook
description	Work on MotherDuck example READMEs, Flight Plans, README front matter, catalog generation, catalog release publishing, or the get-starter script. Use when changing this repository's catalog, examples, templates/plans, or agent-facing documentation.

MotherDuck Cookbook

Use this skill before changing the MotherDuck cookbook catalog, README metadata, Flight Plans, catalog generation, catalog validation, or starter download flow.

Repository Model

This repo is becoming a flat, agent-readable catalog of MotherDuck examples. Each catalog entry is an independent folder with working code and a README that serves both humans and agents.

There are two entry families:

• Top-level example folders: standalone projects such as dbt projects, app demos, ingestion scripts, UI walkthroughs, and integrations. Some can also deploy as a MotherDuck Flight and carry features: [flights] plus a "Deploy as a Flight" section, but they still live at the repo root.
• flight-plans/: reusable, single-file Flight templates (type: template) that an agent adapts and deploys. This directory is purely for template-style Flight files, not concrete examples. See the directory for the current set (S3, dlt, Postgres, Snowflake, and BigQuery ingests, freshness alerts, user-database provisioning, Dive usage metrics, ...).

Keep all examples at the repo root, even flight-capable ones. Do not create an examples/ wrapper folder, and do not move concrete examples into flight-plans/.

Durable Decisions

• README YAML front matter is the catalog metadata source of truth.
• Flight Plans are non-deterministic plans an agent adapts after asking the user a few questions; their READMEs describe deployment through the Flight SQL surface (MD_CREATE_FLIGHT, MD_RUN_FLIGHT, MD_FLIGHTS()), and no create-flight SQL is checked in. (This rule is about flight-plans/ templates.)
• The Flight runtime attaches a MotherDuck token automatically and injects it as MOTHERDUCK_TOKEN at run time. Do not document an access_token_name argument in READMEs.
• READMEs must work for humans and for agents on any client: name MCP tools (get_flight_guide, create_flight, query_rw, ...) only in ## Caveats or ## Learn more. In every other section reference the SQL surface (MD_CREATE_FLIGHT, MD_RUN_FLIGHT, CREATE SECRET, ...) or the UI instead.
• A concrete example that deploys as a Flight may ship a deploy script that calls the Flight SQL surface (MD_CREATE_FLIGHT / MD_UPDATE_FLIGHT, resolving by name via MD_FLIGHTS()), the same way a Dive example ships a deploy-dive.sh. Prefer the project's pinned, MotherDuck-compatible duckdb client so there's no separate CLI version to manage.
• catalog.json is generated by CI and published as a GitHub Release asset. It is not committed as a generated file.
• The catalog schema is committed in catalog.schema.json.
• When a recipe needs the organization's region (for example to pick the right MOTHERDUCK_HOST or Postgres endpoint hostname), the canonical way to determine it is SELECT region FROM md_user_info(); (https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md-user-info). Prefer this query over asking the user or pointing at UI settings. The region column requires a DuckDB 1.5.3 or later client, and the function is disabled in SaaS mode.

Vercel-style front matter and skill docs are useful inspiration, but the local direction is README front matter plus a generated Release asset, not Contentful sync, GitHub Pages, or committed generated JSON.

README Metadata

Catalog READMEs start with YAML front matter. For catalog entries, keep exactly these nine keys:

---
title: Build Hacker News Models From S3 With dbt
id: dbt-ingestion-s3
description: >-
  Query public Hacker News Parquet data in S3 and build dbt models. Use when a
  user wants to ingest S3/Parquet with dbt, run locally or on MotherDuck.
type: example
category: ingestion
features: []
tags: [dbt]
prompt: >-
  I want to build dbt models directly on Parquet/CSV in S3 without copying the
  data first, running locally against DuckDB or in the cloud against MotherDuck.
  Help me adapt the "Build Hacker News Models From S3 With dbt" recipe to my own
  data and use case, using it as a guide:
  https://motherduck.com/docs/cookbook/dbt-ingestion-s3
published_date: 2024-12-11
---

Rules:

• id must match the folder name.
• type is example or template.
• category is the single primary navigation bucket for the entry. It must be one of getting-started, ingestion, analytics, integrations, end-to-end, or automation. Pick by primary intent: a Flight that ingests data is ingestion, while a Flight that sends operational alerts is automation; flights remains a feature, not a category.
• features is a subset of the MotherDuck capabilities the code actually uses: admin_api, dives, ducklake, flights, mcp, pg_duckdb, pg_endpoint, shares, wasm. Use pg_endpoint for the MotherDuck Postgres wire endpoint and pg_duckdb for the pg_duckdb extension; they are different.
• tags must come from the curated ALLOWED_TAGS list in scripts/build-catalog.py. Tags name significant third-party tools, frameworks, languages, platforms, and libraries (for example dbt, dlt, cloudflare, vercel, nextjs, pandas, node-postgres). They are NOT for datasets (nyc-taxi, tpc-h, hacker-news), generic concepts (sql, etl, serverless, incremental), the DuckDB engine itself, redundant variants of an existing tag (cloudflare-workers when cloudflare exists), or anything already expressed as a feature (pg_duckdb, ducklake). Tags may be empty. Add a new tag to the list only for a significant new third-party thing.
• prompt is a first-person, copy-paste prompt that tells an agent to use the recipe as a guide and adapt it to the user's own data and use case. Lead with the user's goal (drawn from the description), name the recipe by its exact title, keep the "use it as a guide / adapt it to my own data and use case" message, and end with its docs URL https://motherduck.com/docs/cookbook/<id>.
• published_date is the recipe's publish date as YYYY-MM-DD; downstream surfaces order by it. Use the date the recipe first landed in the repo. The builder also accepts an unquoted YAML date and normalizes it.
• flight-plans/ is only for reusable Flight templates: items there must be type: template and include flights. Concrete examples never live there.
• Examples at the repo root may include flights when they can deploy as a Flight; that does not move them under flight-plans/.
• Nested or supporting READMEs may use catalog: false to opt out before normal required-field validation.

README Body Pattern

Write every catalog README to serve BOTH a human reader and an agent. Phrase sections so they read naturally to a person and as instructions to an agent. Avoid one-sided phrasing like "ask the user"; prefer neutral wording such as "Questions to answer" and "Decide ...". Keep it practical and derived from the actual files in that folder.

Use this structure:

1. # <title> and a short paragraph explaining what the example does and which MotherDuck pattern it demonstrates.
2. ## How it works: the concrete pattern, architecture, or files that explain what the example does before readers start changing values.
3. ## Questions to answer: the information needed before adapting the example (source, target database/schema, load pattern, schedule, credentials).
4. ## Caveats: footguns, limitations, and cases where the example is the wrong tool.
5. ## What you'll adjust: a table of real knobs found in the code, with each knob's purpose and example values/options.
6. ## Run it: local commands and environment variables. For flight-capable entries (features includes flights), add a ### Deploy as a Flight subsection under ## Run it.
7. ## Security: credential handling, SQL safety, access-control notes, and other security-specific concerns when relevant.
8. ## Learn more: link to extra files in the folder and point to MCP guides such as get_flight_guide, get_dive_guide, or ask_docs_question. This and ## Caveats are the only places where MCP tool names belong; every other section uses the SQL surface (MD_CREATE_FLIGHT, MD_RUN_FLIGHT) or the UI so the README works on any client.

This structure is a frame, not a cap. Add sections when the example has valuable content that does not fit, for example ## Caveats for footguns, limitations, and negative tests (what not to do, common errors), or ## Security, ## Routes, and ## How it works with code. Do not drop valuable, example-specific information (safety patterns, parameterized queries, gotchas, connection details) just to fit the skeleton. Progressive disclosure is for generic MotherDuck concepts, not for the specific lessons this example teaches.

Do not put every possible explanation in the README. Prefer progressive disclosure through nearby files and MCP guide pointers.

Catalog Tooling

The catalog builder is scripts/build-catalog.py. It validates front matter by default and writes JSON only when --output is provided.

Use:

uv run scripts/build-catalog.py
uv run scripts/build-catalog.py --output .catalog-preview/catalog.json
uv run --with check-jsonschema check-jsonschema --schemafile catalog.schema.json .catalog-preview/catalog.json

Run tests with:

uv run --with pytest --with pyyaml --with jsonschema python -m pytest tests/test_build_catalog.py tests/test_catalog_workflow.py -q

The GitHub Actions workflow validates on PRs, uploads a catalog-preview artifact, and creates a GitHub Release with catalog.json attached on pushes to main. The latest catalog is available at https://github.com/motherduckdb/motherduck-cookbook/releases/latest/download/catalog.json; older catalogs remain available on earlier catalog releases.

Starter Script

scripts/get-starter.sh must keep short-name resolution for both top-level examples and flight-plans/<id>.

When testing a PR branch:

BRANCH=my-branch curl -fsSL https://raw.githubusercontent.com/motherduckdb/motherduck-cookbook/my-branch/scripts/get-starter.sh | bash -s dbt-ingestion-s3

Working Guidance

• Before rewriting a README, read the example's code, config, and current README enough to identify real adjustable parts.
• Keep README front matter minimal. Move deployment details, schedules, requirements, parameters, and caveats into prose.
• Use uv for Python tooling.
• Use gh for GitHub work when needed.
• Prefer DuckDB SQL examples and MotherDuck terminology that match the existing docs style.
• Preserve unrelated user or agent changes in this Conductor workspace.