motherduck-build-data-pipeline

name	motherduck-build-data-pipeline
description	Design an end-to-end MotherDuck data pipeline. Use for ETL/ELT workflows -- choosing raw, staging, and analytics boundaries, bulk ingestion paths, transformation sequencing, dlt/dbt integration, publication targets, or whether DuckLake is actually required.
license	MIT

Build a Data Pipeline with MotherDuck

Use this skill when the user needs an ingestion-to-serving workflow, not just a single load step.

This is a use-case skill. It orchestrates motherduck-connect, motherduck-load-data, motherduck-model-data, motherduck-query, motherduck-share-data, and motherduck-ducklake.

Start Here: Is a MotherDuck Server Active?

• If a remote MotherDuck MCP server or local MotherDuck server is active, use it.
• If the user names the destination database, use it without adding a confirmation step.
• Explore the live environment:
  • current databases and schemas
  • raw, staging, and analytics boundaries if they already exist
  • source tables, target tables, and table grain
  • key columns, date fields, and join keys

Use that discovery to decide whether the pipeline is:

• landing into an empty workspace
• extending an existing warehouse layout
• publishing into an existing analytics model

If no server is active, use any supplied source and target context. For planning work, proceed with explicit assumptions when safe; ask for missing details only when they block a reliable result.

Pipeline Defaults

• batch over streaming
• raw landing before curation
• explicit raw -> staging -> analytics boundaries
• bulk ingest paths over row-by-row writes
• idempotent stage rebuilds or append contracts before scheduled automation
• verify the MotherDuck-supported DuckDB client version before recommending upstream-only write, checkpoint, or lakehouse features
• native MotherDuck storage unless DuckLake is explicitly required

Workflow

1. Inspect the available MotherDuck server or supplied source and target context.
2. Inspect the current workspace and target data model.
3. Define raw, staging, and analytics boundaries.
4. Ingest raw data.
5. Deduplicate, type, and promote into staging.
6. Materialize analytics-ready outputs.
7. Validate counts, freshness, uniqueness, and business metrics before publishing downstream assets.

Match execution to the request: answer, review, or planning work returns the requested pipeline artifacts; build or change work creates the requested in-scope files and warehouse objects and validates them. Ask before destructive actions, unrelated external writes, or a material expansion of scope.

When this skill produces a native DuckDB (md:) connection, watermark it with custom_user_agent=agent-skills/2.4.0(harness-<harness>;llm-<llm>). If metadata is missing, fall back to harness-unknown and llm-unknown.

Output

The output of this skill should be:

• the stage layout
• the ingestion method
• the transformation sequence
• the serving tables or views
• the validation checks

If the caller explicitly asks for structured JSON, return raw JSON only with no Markdown fences or prose before/after it. This is mainly for automated tests, regression checks, or downstream tooling that needs a stable machine-readable shape. Normal human-facing use of the skill can stay in prose unless JSON is explicitly requested.

Use this exact top-level shape when JSON is requested:

{
  "summary": {},
  "assumptions": [],
  "implementation_plan": [],
  "validation_plan": [],
  "risks": []
}

References

• references/dlt-dbt-motherduck-project/ -- fully runnable MotherDuck reference project using dlt, dbt-duckdb, and validation queries
• references/PIPELINE_IMPLEMENTATION_GUIDE.md -- preserved detailed pipeline guidance that used to live in this skill
• ../motherduck-load-data/references/INGESTION_PATTERNS.md -- lower-level ingestion patterns

Runnable Artifact

• artifacts/pipeline_stage_example.py -- MotherDuck-backed Python example that stages a Parquet extract, lands it into raw, deduplicates it, and publishes analytics output across raw/staging/analytics databases
• artifacts/pipeline_stage_example.ts -- TypeScript companion artifact with the same stage layout and output contract
• references/dlt-dbt-motherduck-project/ -- end-to-end MotherDuck example that bootstraps the target database, lands raw data with dlt, builds staging and analytics models with dbt, and validates the final mart

Run it with:

uv run --with duckdb python skills/motherduck-build-data-pipeline/artifacts/pipeline_stage_example.py

Run the same stage pattern against temporary MotherDuck databases:

MOTHERDUCK_ARTIFACT_USE_MOTHERDUCK=1 \
uv run --with duckdb python skills/motherduck-build-data-pipeline/artifacts/pipeline_stage_example.py

Validate the TypeScript companion artifact:

uv run scripts/test_typescript_artifacts.py

For the full MotherDuck project:

cd skills/motherduck-build-data-pipeline/references/dlt-dbt-motherduck-project
export MOTHERDUCK_TOKEN=...
export MOTHERDUCK_PIPELINE_DB=md_skills_pipeline_demo
uv sync --python 3.12
uv run python pipeline/run_all.py
uv run python pipeline/cleanup.py

Verified Notes

• Bootstrap the target MotherDuck database before running dlt. The motherduck destination does not create the database for you.
• Use Python 3.11 or 3.12 to reproduce this reference project; its tested dbt-duckdb path did not run reliably on Python 3.14.
• If you want exact schema names like raw, staging, and analytics in dbt, override generate_schema_name.
• When a long-lived Python process loads data and a separate dbt subprocess builds models, run post-build validation in a fresh process or refresh database state before reading new relations.

Related Skills

• motherduck-connect -- choose the right connection path
• motherduck-load-data -- ingestion mechanics
• motherduck-model-data -- shape the analytics layer
• motherduck-query -- write transformations and validations
• motherduck-share-data -- publish curated outputs
• motherduck-ducklake -- only when open-table-format storage is a real requirement