skills.rest
kkkaoru
kkkaoruCOMMUNITY
Explore all skills by this author →
𝕏@kkk4oruGitHub

motherduck-build-data-pipeline

Design end-to-end MotherDuck pipelines with raw, staging, and analytics stages.

Updated Oct 15, 2019
Search Tags:#analytics#data pipeline#dbt#etl#duckdb#dlt#motherduck
One-click install
npx skills add https://github.com/kkkaoru/dotfiles --skill motherduck-build-data-pipeline-kkkaoru
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: motherduck-build-data-pipeline
Source: https://github.com/kkkaoru/dotfiles/tree/main/.agents/skills-stroage/motherduck-build-data-pipeline
Command: npx skills add https://github.com/kkkaoru/dotfiles --skill motherduck-build-data-pipeline-kkkaoru

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill requires duckdb, dbt-duckdb, dlt, and includes references (resource) components.

What problem does it solve? Building a data pipeline involves more than a single load step: you must decide where raw data lands, how it is cleaned and deduplicated, how analytics tables are materialized, and how results are validated and published. This Skill orchestrates the full ingestion-to-serving workflow on MotherDuck so stage boundaries, transformation sequencing, and validation checks are designed deliberately instead of ad hoc. ## Core Features & Use Cases - Stage Architecture Design: Defines explicit raw, staging, and analytics boundaries (as separate databases or schemas) with bulk Parquet ingestion, deduplication, and idempotent CREATE OR REPLACE rebuilds. - Runnable Reference Project: Ships a verified dlt + dbt-duckdb + MotherDuck project that bootstraps the database, loads raw JSONL data, builds staging and analytics models, and validates the final mart. - Companion Artifacts: Includes Python and TypeScript pipeline stage examples demonstrating bulk Parquet staging, deduplication, and analytics aggregation with workload tagging via custom_user_agent. - Use Case: A data engineer needs to move S3 order files into MotherDuck, deduplicate them, build a daily revenue mart, and share the analytics database with the team. The Skill walks through each stage and provides a runnable dlt/dbt template. ## Quick Start Ask the assistant to design a MotherDuck pipeline that lands your source files into a raw stage, deduplicates them into staging, and publishes an analytics-ready table with validation checks.

Frequently Asked Questions about motherduck-build-data-pipeline

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I build a data pipeline with MotherDuck?▼

Structure the pipeline as raw, staging, and analytics stages: land source files unchanged into raw with bulk Parquet reads, deduplicate and cast types into staging, then materialize analytics tables with joins and aggregations. Validate row counts, nulls, and uniqueness between every stage.

How to use dlt and dbt with MotherDuck?▼

Bootstrap the MotherDuck database first since the dlt motherduck destination does not create it, then load raw data with dlt and build staging and analytics models with dbt-duckdb. Override generate_schema_name to get exact schema names like raw, staging, and analytics.

Should I use separate databases or schemas for pipeline stages in MotherDuck?▼

Production pipelines benefit from separate raw, staging, and analytics databases, since MotherDuck supports cross-database queries with fully qualified names. For minimal dbt projects, one database with three explicit schemas is an acceptable simpler shape.

Does MotherDuck support scheduled pipeline runs?▼

MotherDuck has no built-in scheduling, so use external schedulers like cron, GitHub Actions, Dagster, Airflow, or Prefect. Store transformations as version-controlled .sql files and execute them from a Python runner using duckdb.connect("md:") or the PG endpoint.

When is DuckLake required instead of native MotherDuck storage?▼

Native MotherDuck storage is the default for pipelines. DuckLake is only needed when open-table-format lakehouse storage is an explicit requirement, and you should verify the MotherDuck-supported DuckDB client version before relying on upstream-only write or checkpoint features.

Why does dbt-duckdb fail on newer Python versions with MotherDuck?▼

The tested dbt-duckdb stack in this reference project was not reliable on Python 3.14. Keep the pipeline environment on Python 3.11 or 3.12, and run post-build validation in a fresh process so it sees relations written by the separate dbt subprocess.

Related Skills

Explore More Data & Analytics Skills→
motherduckdbmotherduckdb
53

motherduck-explore

Discover MotherDuck databases, tables, columns, and data shares via metadata functions.

Official
Intermediate
motherduckdbmotherduckdb
53

motherduck-enable-self-serve-analytics

Plans a governed self-serve analytics rollout for a MotherDuck workspace.

Official
Advanced
grafanagrafana
213

cost-management

Manage Grafana Cloud costs with label-based attribution and usage alerts.

Official
Intermediate
motherduckdbmotherduckdb
53

motherduck-load-data

Select MotherDuck ingestion pathways and SQL patterns for data loading.

Official
Intermediate