Build a Data Pipeline with MotherDuck

motherduck-connect

motherduck-load-data

motherduck-model-data

motherduck-query

motherduck-share-data

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 already knows the destination database, confirm it before designing stages.
• 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

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

Use This Skill When

• The user needs ingestion plus transformation plus serving output.
• The work spans raw landing, curation, and publication.
• The user needs a stage-by-stage pipeline pattern rather than one command.
• The problem is bigger than a single import step or one ad hoc transformation.

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. Confirm whether live MotherDuck discovery is available.
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.

md:

custom_user_agent=agent-skills/2.2.0(harness-<harness>;llm-<llm>)

harness-unknown

llm-unknown

Output

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

{
  "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

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

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

uv run scripts/test_typescript_artifacts.py

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.
• Keep this stack on Python 3.11 or 3.12 for now. The tested

  dbt-duckdb

  path here was not reliable 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