Skip to content

motherduckdb/agent-skills / plugins/motherduck-skills-claude/skills/motherduck-build-data-pipeline

motherduck-build-data-pipeline

Unverified

Build ingestion-to-serving pipelines on MotherDuck, including stage boundaries, transformations, and publication. Use for any recurring ingestion from an API, SaaS app, or database into MotherDuck.

FileKindSize
SKILL.mdskill md4.7 KB
artifacts/pipeline_stage_example.pyscript3.5 KB
artifacts/pipeline_stage_example.tsscript2.6 KB
references/EXECUTION_REFERENCE.mdmarkdown2.7 KB
references/PIPELINE_IMPLEMENTATION_GUIDE.mdmarkdown25.6 KB
references/dlt-dbt-motherduck-project/.env.exampleother86 B
references/dlt-dbt-motherduck-project/.gitignoreother54 B
references/dlt-dbt-motherduck-project/.user.ymlconfig41 B
references/dlt-dbt-motherduck-project/README.mdmarkdown3.8 KB
references/dlt-dbt-motherduck-project/data/customers.jsonlother288 B
references/dlt-dbt-motherduck-project/data/orders.jsonlother824 B
references/dlt-dbt-motherduck-project/dbt_project.ymlconfig333 B
references/dlt-dbt-motherduck-project/macros/generate_schema_name.sqlother207 B
references/dlt-dbt-motherduck-project/models/marts/fct_customer_revenue.sqlother375 B
references/dlt-dbt-motherduck-project/models/marts/marts.ymlconfig285 B
references/dlt-dbt-motherduck-project/models/staging/sources.ymlconfig116 B
references/dlt-dbt-motherduck-project/models/staging/staging.ymlconfig455 B
references/dlt-dbt-motherduck-project/models/staging/stg_customers.sqlother222 B
references/dlt-dbt-motherduck-project/models/staging/stg_orders.sqlother695 B
references/dlt-dbt-motherduck-project/pipeline/__init__.pyscript1 B
references/dlt-dbt-motherduck-project/pipeline/bootstrap.pyscript1.2 KB
references/dlt-dbt-motherduck-project/pipeline/cleanup.pyscript727 B
references/dlt-dbt-motherduck-project/pipeline/load_raw.pyscript1.4 KB
references/dlt-dbt-motherduck-project/pipeline/run_all.pyscript1.2 KB
references/dlt-dbt-motherduck-project/pipeline/settings.pyscript1.1 KB
references/dlt-dbt-motherduck-project/pipeline/validate.pyscript2.0 KB
references/dlt-dbt-motherduck-project/profiles.ymlconfig434 B
references/dlt-dbt-motherduck-project/pyproject.tomlmanifest326 B
references/dlt-dbt-motherduck-project/uv.lockmanifest210.0 KB

references/dlt-dbt-motherduck-project/README.md

View on GitHub
# dlt + dbt + MotherDuck Reference Project

This is a minimal end-to-end pipeline reference for the `motherduck-build-data-pipeline` skill.

## Contents

| Section | Covers |
| --- | --- |
| Why One Database | Database layout for the example |
| Verified Constraints | What the project was verified against |
| Files | Project layout |
| Run It | Commands to run the pipeline |
| Expected Output | What a successful run prints |
| Real MotherDuck Test Posture | Temporary databases and cleanup |

It captures the pipeline shape that this repo repeatedly verified against real MotherDuck runs:

- `dlt` for the raw loading step
- `dbt-duckdb` for staging and analytics modeling
- Python validation and cleanup around the workflow

The example is deliberately small and fully runnable:

- `dlt` lands raw JSONL data in MotherDuck
- `dbt` models staging and analytics relations in the same MotherDuck database
- Python validation checks the final outputs

## Why One Database

The main skill recommends separate lifecycle stages. For this reference project, the simplest runnable shape is one MotherDuck database with explicit schemas:

- `raw`
- `staging`
- `analytics`

That keeps the dbt project small and avoids extra attach configuration. When the pipeline grows and stage boundaries matter operationally, split the stages into separate MotherDuck databases and use dbt `attach`.

## Verified Constraints

These are based on a real local run against MotherDuck:

- Bootstrap the MotherDuck database before `dlt` runs. The `motherduck` destination does not create the target database for you.
- Use Python 3.11 or 3.12 for this stack. `dbt-duckdb` did not run correctly on Python 3.14 in this environment.
- Keep `dbt` concurrency at `threads: 1` for a small MotherDuck project like this.
- Override `generate_schema_name` so dbt uses exact schema names instead of `main_<schema>`.
- Run post-build validation in a fresh process. A long-lived local DuckDB process may not immediately see schemas written by a separate `dbt` subprocess.

## Files

- `pipeline/bootstrap.py`: creates the MotherDuck database and schemas
- `pipeline/load_raw.py`: loads raw data into MotherDuck with `dlt`
- `pipeline/run_all.py`: runs bootstrap, load, dbt build, and validation
- `pipeline/validate.py`: asserts row counts and final mart output
- `dbt_project.yml`, `profiles.yml`, `models/`, `macros/`: dbt project
- `data/*.jsonl`: tiny input dataset

## Run It

Set credentials:

```bash
export MOTHERDUCK_TOKEN=...
export MOTHERDUCK_PIPELINE_DB=md_skills_pipeline_demo
```

Install dependencies with a supported Python:

```bash
uv sync --python 3.12
```

Run the whole pipeline:

```bash
uv run python pipeline/run_all.py
```

Drop the temporary MotherDuck database when you are done:

```bash
uv run python pipeline/cleanup.py
```

Run the steps individually if you want to inspect them:

```bash
uv run python pipeline/bootstrap.py
uv run python pipeline/load_raw.py
DBT_PROFILES_DIR=. uv run dbt build
uv run python pipeline/validate.py
```

## Expected Output

After a successful run, the final mart contains three rows:

| customer_id | customer_name     | order_count | total_amount |
|-------------|-------------------|-------------|--------------|
| c1          | Acme Rockets      | 2           | 155.00       |
| c2          | Birch Analytics   | 1           | 75.00        |
| c3          | Cedar Logistics   | 1           | 200.00       |

The staging model also proves two common pipeline patterns:

- deduplicate by latest `updated_at`
- filter analytics output to `PAID` orders only

## Real MotherDuck Test Posture

This project is intended to run against a real MotherDuck database, not just local DuckDB.

- use a temporary `MOTHERDUCK_PIPELINE_DB` for validation runs
- bootstrap the database first
- run validation after `dbt build`
- drop the temporary database with `pipeline/cleanup.py` after the run
writing-skillsobra/superpowersUse when creating new skills, editing existing skills, or verifying skills work before deploymentStars: 323kUpdated Claude CodeCodexVerifiedInstall
xlsxanthropics/skillsUse this skill any time a spreadsheet file is the primary input or output. This means any task where the user wants to: open, read, edit, or fix an existing .xlsx, .xlsm, .xltx, .csv, or .tsv file (e.g., adding columns, computing formulas, formatting, charting, cleaning messy data); create a new spreadsheet from scratch or from other data sources; or convert between tabular file formats. Trigger especially when the user references a spreadsheet file by name or path — even casually (like "the xlsx in my downloads") — and wants something done to it or produced from it. Also trigger for cleaning or restructuring messy tabular data files (malformed rows, misplaced headers, junk data) into proper spreadsheets. The deliverable must be a spreadsheet file. Do NOT trigger when the primary deliverable is a Word document, HTML report, standalone Python script, database pipeline, or Google Sheets API integration, even if tabular data is involved.Stars: 198kUpdated Claude CodeCodexVerifiedInstall
shipping-and-launchaddyosmani/agent-skillsPrepares production launches. Use when preparing to deploy to production, or when asking what needs to be in place before shipping. Use when you need a pre-launch checklist, when setting up monitoring, when planning a staged rollout, or when you need a rollback strategy.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
childhoodthedotmack/claude-memBabysit a shipped release after merge, publication, or deployment. Verify published packages, production behavior, installed plugins, and late CI or user reports until the observation window is clean; use babysit for PR readiness before release.Stars: 118kUpdated Claude CodeCodexVerifiedInstall