Skip to content

cianfhoghlaim/cianchosaint / .agents/skills/motherduck

motherduck

Verified


name: motherduck description: Master routing skill for all MotherDuck operations. Use this to determine which of the 4 task-specific MotherDuck sub-skills to invoke (architecture, data-modeling, analytics, connections), or to wire the mcp-server-motherduck (the Cianfhoghlaim-preferred agent path). Powers the 4 BIEP Dives (lc_syllabus_topics, lc_exam_difficulty, lc_marking_complexity, gov_circulars_archive) + the lc6 MotherDuck Flights (scheduled DuckDB queries for BAML row backfill).

MotherDuck Master Router

You are operating within the cianfhoghlaim stack which utilizes MotherDuck for cloud data warehousing and DuckLake for local/hybrid S3 data Lakehouse capabilities.

When tasked with MotherDuck operations, use this guide to invoke the most appropriate sub-skill:

The 4 task-specific sub-skills

If you need to…Load this skill
Pick the storage pattern (managed MD / BYOB / DuckLake / own-compute), evaluate pricing, plan a migration, or design an end-to-end pipelinemotherduck-architecture
Design schemas, choose column types, load data from Parquet/CSV/dataframe/Postgres, set up dbt-duckdb or SQLMesh on MotherDuckmotherduck-data-modeling
Write DuckDB SQL, build Dives, compose dashboards, explore unknown databases, share data zero-copymotherduck-analytics
Connect from Python/Node/BI tools, manage service accounts + tokens, set up per-tenant isolation, wire mcp-server-motherduckmotherduck-connections

Do not execute general MotherDuck tasks blindly. Always try to load the relevant sub-skill for specialized reference material and best practices.

MCP server (mcp-server-motherduck)

MotherDuck ships an official MCP server (mcp-server-motherduck) that exposes DuckDB / MotherDuck SQL analytics to any MCP client (Cursor, VS Code, Claude Desktop, opencode). This is the Cianfhoghlaim-preferred way to drive oideachais analytics from inside an IDE or agent runtime without leaving the DuckDB dialect.

What it gives you

CapabilityDescription
Hybrid executionQuery a local DuckDB file and a MotherDuck cloud database from one server
Cloud storageRead S3 / object-storage DuckDB files transparently
Data sharingCreate + share MotherDuck databases via the SQL tool
SQL analyticsFull DuckDB SQL dialect (window functions, QUALIFY, macros, extensions)
ServerlessNo instance/cluster config; the server boots per-process

Tools exposed

  • query (the only tool): execute a SQL string. All interaction with DuckDB + MotherDuck goes through SQL. Results are auto-truncated to --max-rows 1024 (default) and --max-chars 50000 (default) so a single SELECT cannot blow out the agent's context.

  • duckdb-motherduck-initial-prompt: the one prompt the server provides, used to initialise the connection in a chat client.

CLI flags you'll actually use

FlagDefaultWhen to override
--db-pathmd:Use /path/to/local.duckdb for local-only; md:cianfhoghlaim for the read-only Cianfhoghlaim lakehouse (post-v7 canonical); s3://bucket/path.duckdb for object-storage
--motherduck-token$motherduck_token envRequired for any md: access
--read-onlyfalseAlways set true for the Cianfhoghlaim read-only consumer pattern
--saas-modefalseEnable in production for security: disables filesystem + write perms for local DuckDB
--max-rows1024Lower to 256 for chat contexts; raise to 4096 for marimo notebooks
--max-chars50000Raise to 200000 for marimo notebooks; lower to 5000 for narrow chat contexts
--query-timeout-1 (off)Set 300 (5 min) for production to prevent runaway scans
--transportstdioUse stream (HTTP streaming) for web clients; sse for legacy clients
--port / --host8000 / 127.0.0.1Required for sse and stream transports

Install + run

# Install (one-time, via uvx)
uvx mcp-server-motherduck --help

# Local DuckDB file (read-only)
uvx mcp-server-motherduck \
  --db-path ${CIANFHOGHLAIM_ROOT:-~/dev/cianfhoghlaim}/stedding/cianfhoghlaim.duckdb \
  --read-only

# MotherDuck cloud (Cianfhoghlaim read-only consumer)
uvx mcp-server-motherduck \
  --db-path md:cianfhoghlaim \
  --motherduck-token "$MOTHERDUCK_TOKEN" \
  --read-only --saas-mode

# Production: streaming HTTP + timeout
uvx mcp-server-motherduck \
  --transport stream \
  --db-path md:cianfhoghlaim \
  --motherduck-token "$MOTHERDUCK_TOKEN" \
  --saas-mode \
  --max-rows 4096 --max-chars 200000 \
  --query-timeout 300

Wire into Cursor / VS Code / Claude Desktop

Cursor (~/.cursor/mcp.json or per-project .cursor/mcp.json):

{
  "mcpServers": {
    "mcp-server-motherduck": {
      "command": "uvx",
      "args": [
        "mcp-server-motherduck",
        "--db-path", "md:cianfhoghlaim",
        "--motherduck-token", "<YOUR_MOTHERDUCK_TOKEN_HERE>",
        "--read-only", "--saas-mode"
      ]
    }
  }
}

VS Code (.vscode/mcp.json or user mcp.json): use the inputs: [{type: promptString, id: motherduck_token, password: true}] pattern so the token is prompted once and stored in the secret store, then referenced as "${input:motherduck_token}".

Claude Desktop (claude_desktop_config.json): same mcpServers shape as Cursor.

Cianfhoghlaim production rules

  • Always --read-only --saas-mode for the oideachais consumer pattern. The agent must not be able to CREATE TABLE, INSERT, or write to local disk.
  • Always set --query-timeout 300 (or lower) in production. MotherDuck's serverless compute will not kill a runaway scan for you; the agent could issue a cross-database join that scans 50M rows.
  • Always use --max-rows 256 for chat contexts; raise only for marimo / notebook agents that explicitly need large result sets.
  • Never put the MotherDuck token in .env plain; it should come from the Infisical dev-baile vault via mise (the MOTHERDUCK_TOKEN env var) and never be written to disk in plaintext.

Pair this skill with

  • motherduck-connect — for the native-DuckDB vs Postgres-endpoint decision tree
  • motherduck-duckdb-sql — for the full DuckDB SQL dialect reference (the MCP tool will only execute valid DuckDB SQL)
  • motherduck-security-governance — for the service-account + read-only token policy
  • .agents/skills/motherduck/ + .agents/skills/iceberg-lakekeeper/SKILL.md — for the DuckLake / MotherDuck / Lakekeeper read-only mental model

See docs/teanga/motherduck_mcp.md for the full 457-line reference including all transport modes, the SaaS-mode security model, and the data-sharing semantics.

2026-06 updates (from the upstream-package-monitoring openspec change)

  • DuckLake 1.0 launched 2026-04-16 on MotherDuck. The Cianfhoghlaim production lakehouse (cianfhoghlaim/) uses DuckLake 1.0. New features in 1.0:
    • Data inlining — also applies to updates and deletes (not just inserts), so small DuckLake tables can live entirely on the catalog.
    • Data clustering — 10× faster reads on clustered tables via the new set_sorted_by helper in dlt/dlt_utils/ducklake_options.py.
    • Bucket partitioning — new set_bucket_partition helper for multi-tenant workloads where row counts vary by 10× across partitions.
    • Geometry + variant types — DuckLake 1.0 adds first-class support for the GEOMETRY and VARIANT types, useful for the geospatial assets in orchestration/defs/2_materials/geospatial_assets.py.
  • 3 hosting options — fully managed (MotherDuck SaaS, default for Cianfhoghlaim dev), BYOB (your own Garage S3 bucket, default for Cianfhoghlaim production per dlt/dlt_utils/motherduck_options.py:byob_destination), and BYOC (your own compute + your own bucket — for regulated workloads).
  • MotherDuck upstream monitor — motherduck_blog.yml in infrastructure/firecrawl/monitors/upstream_packages/ is the Firecrawl monitor that detects DuckLake / BYOB / Cortex Code releases via the LLM-judge --goal filter. See the change-detection skill (Layer 4) for the architecture.

Verified 2026-06-29 — Dives MCP tool list (8 tools, was 7)

ToolDescriptionParameters
list_divesList all Dives in the workspace (returns current_version per Dive)SELECT *
read_diveRead a single Dive; supports version paramid, version?
read_dive_versionRead a specific historical version of a Dive (read-only)id, version
save_divePersist a Dive to the workspace (creates version 1)spec JSON
update_diveUpdate an existing Dive (creates a new version)id + spec JSON
delete_diveDelete a Diveid
view_diveOpen a Dive in the UIid
share_dive_dataCreate org-scoped shares for a Dive's dataid

Source: /docs/key-tasks/ai-and-motherduck/dives/ and /sql-reference/mcp/.

MotherDuck token — Business-tier required (carry forward)

Cianfhoghlaim notebooks use 4 shared databases (cianfhoghlaim_public, cianfhoghlaim_team, leabharlann_public, leabharlann_team). Lite is 3 users, 2 service accounts, 10 GB — too small. The token must be a Business-tier PAT.

British-Isles Education pipeline — Canonical Cianfhoghlaim pattern (post-v4)

The post-v4 lc6 pipeline (openspec/changes/lc6-biep/) consumes the BAML-extracted DuckLake tables via 4 MotherDuck Dives (the read-only consumer surface) and 6 MotherDuck Flights (the scheduled backfill surface). All 10 are wired into the oideachais MotherDuck database under the cianfhoghlaim.leaving_cert.* and cianfhoghlaim.education.ie.* schemas.

The 4 Dives (read-only dashboards):

  1. lc_syllabus_topics — module / topic coverage per LC subject, partitioned by language (en/ga) and level (higher/ordinary)
  2. lc_exam_difficulty — per-topic difficulty score derived from BAML ExtractExamPaperLayout over the last 10 years of SEC past papers
  3. lc_marking_complexity — annotation density per topic from BAML ExtractMarkingSchemeGuideline
  4. gov_circulars_archive — every gov.ie/.../circulars/... PDF extracted by the government_circulars v1 CocoIndex App
from motherduck.dives import save_dive

save_dive(
    name="lc_syllabus_topics",
    sql="""
        SELECT
            subject,
            level || '_' || language AS partition,
            module_id,
            title,
            hours,
            cardinality(learning_outcomes) AS n_outcomes
        FROM cianfhoghlaim.leaving_cert.curriculum_syllabus
        WHERE subject IN (
            'mathematics', 'chemistry', 'geography',
            'gaeilge', 'english', 'computer_science'
        )
    """,
)

The 6 Flights (scheduled backfills):

  • flight_lc_mathematics_backfill — re-extracts the BAML rows for Mathematics from cianfhoghlaim.leaving_cert.mathematics_* on the 1st of every month
  • flight_lc_chemistry_backfill, flight_lc_geography_backfill, flight_lc_gaeilge_backfill, flight_lc_english_backfill, flight_lc_computer_science_backfill — same template, per-subject
  • flight_gov_circulars_backfill — runs daily at 02:00 UTC to ingest new gov.ie circulars

British-Isles Education pipeline use case:

  • 6 LC subjects × 2 languages × 2 levels — Mathematics, Chemistry, Geography, Gaeilge, English, Computer Science, each partitioned by en/ga and higher/ordinary (24 partitions per BAML extraction stage).
  • gov.ie circulars — the 7th subject (the government_circulars partition) ingests circulars from gov.ie/.../circulars/... for cross-referencing with NCCA syllabus changes.
  • Cross-Dive joins — the marimo notebooks use duckdb.connect("md:cianfhoghlaim") to join across all 4 Dives for end-to-end analytics.

Cross-references:

v7 flattening migration notes (added 2026-07-19)

Per openspec/changes/2026-07-14-fix-foundation-v7-flattening-and-baml-drift-v1:

  • The canonical MotherDuck database alias is md:cianfhoghlaim (NOT md:cianfhoghlaim which was the pre-v7 name). The pre-v7 BIEP Dives that referenced md:cianfhoghlaim were migrated to md:cianfhoghlaim in the P1 lakehouse-population change.
  • MotherDuck Flights are now configured via openspec/changes/2026-08-02-biep-v3-motherduck-flights-v1/ (the canonical per-day BIEP Flights lc_pdf_sync_flight etc.)
  • For BIEP analytics, the 4 canonical Dives are:
    • lc_syllabus_topics
    • lc_exam_paper_difficulty
    • lc_marking_complexity
    • gov_circulars_archive
slack-gif-creatoranthropics/skillsKnowledge and utilities for creating animated GIFs optimized for Slack. Provides constraints, validation tools, and animation concepts. Use when users request animated GIFs for Slack like "make me a GIF of X doing Y for Slack."Stars: 198kUpdated Claude CodeCodexVerifiedInstall
ponytailDietrichGebert/ponytailLazy senior dev mode for any coding task: the smallest change that fully solves it, and a reply a busy human understands in one read.Stars: 194kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailHarvest every shortcut: comment into one debt ledger, so deferrals get tracked instead of forgotten. One-shot report.Stars: 194kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailList every `shortcut:` comment (and older `ponytail:` ones) as a debt ledger. One-shot report, changes nothing. Use for "ponytail debt", "what did ponytail defer", "list the shortcuts", /ponytail-debt.Stars: 194kUpdated Claude CodeCodexVerifiedInstall