# MotherDuck Documentation - Orchestration

> Focused MotherDuck documentation context for Orchestration.

Generated: 2026-08-25

> MotherDuck is a serverless cloud data warehouse built on DuckDB.
It combines the speed and simplicity of DuckDB with cloud scalability, collaboration features, and AI-powered analytics.

## Key capabilities

- **Serverless DuckDB in the Cloud**: Run DuckDB queries on cloud data with 100ms cold starts (compared to seconds/minutes on traditional warehouses)
- **Hybrid Execution**: Query data locally and in the cloud seamlessly in a single session
- **MCP Server**: Connect AI assistants (Claude, ChatGPT, Cursor) to query your data using natural language
- **Data Sharing**: Share databases and query results with team members and external users
- **Multiple Interfaces**: Connect via Python, Node.js, Go, Java, JDBC, ODBC, or the web UI
- **Cloud Storage Integration**: Query data directly from S3, GCS, Azure Blob Storage, and more
- **AI Functions**: Built-in LLM functions for text analysis, embeddings, and SQL generation

## When to use MotherDuck

Use MotherDuck when the user needs to analyze data with DuckDB-compatible SQL, share databases with people or applications, run collaborative cloud analytics, or let an AI assistant query their connected data through MCP.

## Agent guidance

If your environment provides MCP tools and the user asks about MotherDuck or DuckDB behavior, SQL syntax, permissions, sharing, service accounts, tokens, Dives, or other product features, use the MotherDuck MCP `ask_docs_question` tool before general web search. It answers from official DuckDB and MotherDuck documentation.

For broad context, start with https://motherduck.com/docs/llms-full.txt, then follow the most specific focused context link. Use https://motherduck.com/docs/llms-full-complete.txt only for bulk indexing or large-context workflows.

To connect an MCP client, use the remote MotherDuck MCP server at `https://api.motherduck.com/mcp`. Setup instructions: https://motherduck.com/docs/key-tasks/ai-and-motherduck/mcp-setup. Tool reference: https://motherduck.com/docs/sql-reference/mcp/core/ask-docs-question.

For the documented Admin REST API, use the OpenAPI specification at https://motherduck.com/docs/openapi.json.

## Account setup for agents

If the user wants to start using MotherDuck and doesn't have an account, offer the agent signup flow. Creating an account changes external state, so get the user's confirmation before sending the request.

`POST https://new.motherduck.com` creates a Free Plan organization. No request body is required. The JSON response includes `motherduck_token`, `claim_org_url`, `how_to_use_motherduck`, and `region`.

Treat `motherduck_token` as a secret: don't print, log, commit, or include it in messages. Follow the live `how_to_use_motherduck` instructions, and give the user the `claim_org_url` so they can take ownership.

Full guide: https://motherduck.com/docs/key-tasks/ai-and-motherduck/agent-account-signup.

## Included documentation

Source: https://motherduck.com/docs/key-tasks/data-warehousing/orchestration/github-action-cron

# GitHub Actions
> Schedule MotherDuck SQL and dbt jobs with GitHub Actions as a lightweight cron-based orchestrator.
GitHub Actions works well as a lightweight orchestrator for simple MotherDuck jobs: nightly SQL scripts, small ELT steps, dbt builds, smoke tests, and periodic exports. It is not a full data orchestrator, but it is often enough when a pipeline has one or two steps and can tolerate GitHub's scheduler behavior.

## When to use this pattern

| Use GitHub Actions when | Use a dedicated orchestrator when |
|-------------------------|-----------------------------------|
| The job has a small number of steps | Jobs have complex dependencies or branching |
| A missed or delayed run can be retried manually | Every run needs strict service-level guarantees |
| The pipeline can run from repository files | State, retries, and backfills need first-class tracking |
| GitHub is already where you review pipeline changes | Multiple teams need a shared orchestration UI |

For larger workflows, use a tool from the [MotherDuck orchestration ecosystem](https://motherduck.com/ecosystem/?category=Orchestration).

## Set up authentication

Create a [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#creating-an-access-token), preferably from a service account dedicated to the pipeline. Store it as a GitHub repository secret named `MOTHERDUCK_TOKEN`:

```bash
gh secret set MOTHERDUCK_TOKEN
```

Use the token as an environment variable in workflow steps. Avoid putting tokens directly into SQL files, command arguments, artifacts, or logs.

## Choose the trigger

Most MotherDuck cron jobs should support both manual and scheduled runs with GitHub Actions [`workflow_dispatch`](https://docs.github.com/en/actions/reference/workflows-and-actions/workflow-syntax#onworkflow_dispatch) and [`schedule`](https://docs.github.com/en/actions/reference/workflows-and-actions/events-that-trigger-workflows#schedule) triggers:

```yaml
on:
  workflow_dispatch:
  schedule:
    - cron: "17 2 * * *"
```

Keep these GitHub Actions scheduling details in mind:

- Scheduled workflows run from the latest commit on the default branch.
- Cron schedules use UTC by default.
- The shortest supported interval is every 5 minutes.
- Jobs scheduled at the top of the hour can be delayed or dropped during periods of high GitHub Actions load. Pick a non-zero minute such as `17` or `43`.
- `workflow_dispatch` lets you test the same workflow manually and rerun failed jobs after a fix.

## Example: run a SQL file on a schedule

This example runs a checked-in SQL script every night and on demand. It uses:

- Least-privilege repository permissions
- A timeout so failed jobs do not burn runner minutes indefinitely
- A concurrency group so two runs do not write to the same target at once
- The MotherDuck install script for a compatible DuckDB CLI

Create `.github/workflows/motherduck-nightly-sql.yml`:

```yaml
name: motherduck nightly sql

on:
  workflow_dispatch:
  schedule:
    - cron: "17 2 * * *"

permissions:
  contents: read

concurrency:
  group: motherduck-nightly-sql
  cancel-in-progress: false

jobs:
  run-sql:
    runs-on: ubuntu-24.04
    timeout-minutes: 15
    env:
      motherduck_token: ${{ secrets.MOTHERDUCK_TOKEN }}
    steps:
      - name: Check out repository
        uses: actions/checkout@v6

      - name: Install DuckDB CLI
        run: |
          install_home="$RUNNER_TEMP/motherduck"
          mkdir -p "$install_home"
          curl -s https://install.motherduck.com | env -u motherduck_token HOME="$install_home" sh
          echo "$install_home/.duckdb/cli/latest" >> "$GITHUB_PATH"

      - name: Run nightly SQL
        run: duckdb "md:" < sql/nightly_orders.sql
```

Create `sql/nightly_orders.sql`:

```sql
CREATE DATABASE IF NOT EXISTS analytics;
USE analytics;

CREATE SCHEMA IF NOT EXISTS orchestration;

CREATE TABLE IF NOT EXISTS orchestration.github_action_runs (
    run_id VARCHAR,
    workflow_name VARCHAR,
    run_started_at TIMESTAMP
);

DELETE FROM orchestration.github_action_runs
WHERE run_id = getenv('GITHUB_RUN_ID');

INSERT INTO orchestration.github_action_runs
VALUES (
    getenv('GITHUB_RUN_ID'),
    getenv('GITHUB_WORKFLOW'),
    current_timestamp
);
```

Replace `analytics` with the MotherDuck database your pipeline should write to. The example creates the database if it does not already exist so a new repository can run without extra setup.

The GitHub secret is named `MOTHERDUCK_TOKEN`, while the workflow exposes it as `motherduck_token`. The DuckDB CLI can use that environment variable to connect to MotherDuck non-interactively in GitHub Actions.

The install step uses `RUNNER_TEMP` as `HOME` and unsets `motherduck_token` for the installer process so the install script does not try to update the runner's shell profile or validate the connection before the SQL step runs.

## Example: run dbt on a schedule

For dbt projects, keep the dbt profile in the repository and read the MotherDuck token from the GitHub secret.

Create `.github/workflows/motherduck-dbt.yml`:

```yaml
name: motherduck dbt

on:
  workflow_dispatch:
  schedule:
    - cron: "43 3 * * *"

permissions:
  contents: read

concurrency:
  group: motherduck-dbt-prod
  cancel-in-progress: false

jobs:
  dbt-build:
    runs-on: ubuntu-24.04
    timeout-minutes: 30
    env:
      MOTHERDUCK_TOKEN: ${{ secrets.MOTHERDUCK_TOKEN }}
    steps:
      - name: Check out repository
        uses: actions/checkout@v6

      - name: Set up Python
        uses: actions/setup-python@v6
        with:
          python-version: "3.12"
          cache: pip

      - name: Install dbt
        run: python -m pip install -r requirements.txt

      - name: Install dbt packages
        run: dbt deps

      - name: Build dbt project
        run: dbt build --profiles-dir .github/dbt --target prod
```

Create `requirements.txt`:

```text
dbt-duckdb>=1.9,<2.0
```

Create `.github/dbt/profiles.yml`:

```yaml
motherduck:
  target: prod
  outputs:
    prod:
      type: duckdb
      path: "md:analytics?motherduck_token={{ env_var('MOTHERDUCK_TOKEN') }}"
      threads: 4
```

In `dbt_project.yml`, set the same profile name:

```yaml
profile: motherduck
```

## Production checklist

| Area | Recommendation |
|------|----------------|
| Authentication | Use a service account token stored as `MOTHERDUCK_TOKEN`. Rotate it on the same cadence as other production secrets. |
| Permissions | Set `permissions: contents: read` unless the workflow must write to the repository or call GitHub APIs. |
| Scheduling | Use non-zero cron minutes and keep `workflow_dispatch` enabled for manual retries. |
| Concurrency | Use a `concurrency` group for jobs that write to the same tables. |
| Idempotency | Make SQL safe to rerun. Prefer `CREATE TABLE IF NOT EXISTS`, `CREATE OR REPLACE TABLE`, `MERGE`, or delete-and-insert patterns keyed by the run or partition. |
| Timeouts | Set `timeout-minutes` on every job. |
| Dependencies | Pin dependencies in `requirements.txt` or an equivalent lock file. Use dependency caching for Python/dbt jobs. |
| Environments | Use separate service accounts and databases for development, staging, and production. |
| Observability | Write a run record to a small audit table and rely on GitHub Actions notifications for failures. |

## Related content

- [Authenticating to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/)
- [dbt with DuckDB and MotherDuck](/integrations/transformation/dbt/)
- [DuckDB CLI](/getting-started/interfaces/connect-query-from-duckdb-cli/)
- [Orchestration integrations](https://motherduck.com/ecosystem/?category=Orchestration)

---

Source: https://motherduck.com/docs/key-tasks/data-warehousing/orchestration/dagster

# Dagster
> Orchestrate an incremental S3-to-MotherDuck data loading pipeline with Dagster and Python.
Use Dagster when you want asset lineage, schedules, retries, and run history around a Python data loading job. This guide builds a minimum viable Dagster asset that reads Parquet data from S3, loads rows newer than the last successful run, upserts them into MotherDuck, and stores a watermark for the next run.

The example uses a public S3 Parquet file from the MotherDuck sample data bucket. Replace the S3 path and column mapping with your own bucket layout when you move from the demo to your pipeline.

## How the pipeline works

```mermaid
graph LR
    S3[("S3 Parquet file")]:::yellow
    A["Dagster asset<br/>taxi_trips"]:::watermelon
    W[("ingestion_watermarks")]:::yellow
    T[("taxi_trips")]:::yellow

    W --> A
    S3 --> A
    A --> T
    A --> W
```

The asset keeps the state in MotherDuck:

- `taxi_trips` is the target table.
- `ingestion_watermarks` stores the latest `pickup_at` value loaded by this pipeline.
- Each run reads only rows where `tpep_pickup_datetime` is greater than the stored watermark.
- The target table has a primary key, so reprocessing the same row updates the existing row instead of creating a duplicate.

## Prerequisites

Before you start, ensure you have:

- Python 3.10 or later.
- `uv` for Python project and dependency management.
- A MotherDuck access token in `MOTHERDUCK_TOKEN`.
- A MotherDuck database name for the pipeline. The example creates the database if it doesn't exist.
- For private S3 buckets, a MotherDuck S3 secret. See [Amazon S3 credentials](/integrations/cloud-storage/amazon-s3/) for setup.

:::tip
Use a dedicated MotherDuck service account for scheduled ingestion jobs. This keeps ingestion compute, permissions, and cost attribution separate from analyst and application workloads. See [Hypertenancy](/concepts/hypertenancy/) for the compute isolation model.
:::

## Create the Dagster project

Create a small Python project and add Dagster with DuckDB:

```bash
> uv init dagster-motherduck-s3
> cd dagster-motherduck-s3
> uv add dagster dagster-webserver duckdb
```

Create `definitions.py`:

```python
import os
import re

import dagster as dg
import duckdb

S3_URI = os.getenv(
    "S3_URI",
    "s3://us-prd-motherduck-open-datasets/nyc_taxi/parquet/yellow_cab_nyc_2022_11.parquet",
)
MOTHERDUCK_DATABASE = os.getenv("MOTHERDUCK_DATABASE", "dagster_s3_demo")
PIPELINE_NAME = "dagster_s3_taxi_trips"

# Optional cap for running the demo quickly. Leave unset for a real pipeline.
INGESTION_END_TS = os.getenv("MOTHERDUCK_INGESTION_END_TS")

PUBLIC_DEMO_SCOPE = "s3://us-prd-motherduck-open-datasets/"

def database_identifier(name: str) -> str:
    if not re.fullmatch(r"[A-Za-z_][A-Za-z0-9_]*", name):
        raise ValueError("Use a database name with letters, numbers, and underscores.")
    return name

def open_motherduck_connection() -> duckdb.DuckDBPyConnection:
    database = database_identifier(MOTHERDUCK_DATABASE)
    con = duckdb.connect("md:")
    con.execute(f"CREATE DATABASE IF NOT EXISTS {database}")
    con.execute(f"USE {database}")

    if S3_URI.startswith(PUBLIC_DEMO_SCOPE):
        con.execute("""
            CREATE OR REPLACE TEMPORARY SECRET public_motherduck_open_data (
                TYPE S3,
                PROVIDER config,
                REGION 'us-east-1',
                SCOPE 's3://us-prd-motherduck-open-datasets/'
            )
        """)

    return con

@dg.asset
def taxi_trips(context: dg.AssetExecutionContext) -> dg.MaterializeResult:
    con = open_motherduck_connection()
    try:
        con.execute("""
            CREATE TABLE IF NOT EXISTS taxi_trips (
                trip_id VARCHAR PRIMARY KEY,
                pickup_at TIMESTAMP,
                dropoff_at TIMESTAMP,
                passenger_count DOUBLE,
                trip_distance DOUBLE,
                total_amount DOUBLE,
                source_file VARCHAR,
                loaded_at TIMESTAMP DEFAULT now()
            )
        """)
        con.execute("""
            CREATE TABLE IF NOT EXISTS ingestion_watermarks (
                pipeline_name VARCHAR PRIMARY KEY,
                last_pickup_at TIMESTAMP
            )
        """)
        con.execute("""
            INSERT INTO ingestion_watermarks
            VALUES (?, TIMESTAMP '1970-01-01')
            ON CONFLICT (pipeline_name) DO NOTHING
        """, [PIPELINE_NAME])

        last_pickup_at = con.execute(
            "SELECT last_pickup_at FROM ingestion_watermarks WHERE pipeline_name = ?",
            [PIPELINE_NAME],
        ).fetchone()[0]

        con.execute("""
            CREATE OR REPLACE TEMP TABLE new_taxi_trips AS
            SELECT
                md5(concat_ws('|',
                    VendorID::VARCHAR,
                    tpep_pickup_datetime::VARCHAR,
                    tpep_dropoff_datetime::VARCHAR,
                    PULocationID::VARCHAR,
                    DOLocationID::VARCHAR,
                    total_amount::VARCHAR
                )) AS trip_id,
                tpep_pickup_datetime AS pickup_at,
                tpep_dropoff_datetime AS dropoff_at,
                passenger_count,
                trip_distance,
                total_amount,
                filename AS source_file,
                now() AS loaded_at
            FROM read_parquet(?, filename = true)
            WHERE tpep_pickup_datetime > ?
              AND (? IS NULL OR tpep_pickup_datetime < ?::TIMESTAMP)
        """, [S3_URI, last_pickup_at, INGESTION_END_TS, INGESTION_END_TS])

        rows_loaded = con.execute("SELECT count(*) FROM new_taxi_trips").fetchone()[0]

        con.execute("""
            INSERT INTO taxi_trips BY NAME
            SELECT * FROM new_taxi_trips
            ON CONFLICT (trip_id) DO UPDATE SET
                pickup_at = excluded.pickup_at,
                dropoff_at = excluded.dropoff_at,
                passenger_count = excluded.passenger_count,
                trip_distance = excluded.trip_distance,
                total_amount = excluded.total_amount,
                source_file = excluded.source_file,
                loaded_at = excluded.loaded_at
        """)

        max_pickup_at = con.execute(
            "SELECT max(pickup_at) FROM new_taxi_trips"
        ).fetchone()[0]
        if max_pickup_at is not None:
            con.execute(
                "UPDATE ingestion_watermarks SET last_pickup_at = ? WHERE pipeline_name = ?",
                [max_pickup_at, PIPELINE_NAME],
            )

        total_rows = con.execute("SELECT count(*) FROM taxi_trips").fetchone()[0]
        context.log.info("Loaded %s rows into taxi_trips", rows_loaded)

        return dg.MaterializeResult(
            metadata={
                "rows_loaded": rows_loaded,
                "total_rows": total_rows,
                "last_pickup_at": str(max_pickup_at or last_pickup_at),
            }
        )
    finally:
        con.close()

daily_s3_ingestion = dg.ScheduleDefinition(
    name="daily_s3_taxi_trips",
    cron_schedule="0 2 * * *",
    target=[taxi_trips],
)

defs = dg.Definitions(
    assets=[taxi_trips],
    schedules=[daily_s3_ingestion],
)

if __name__ == "__main__":
    result = dg.materialize([taxi_trips])
    if not result.success:
        raise RuntimeError("Dagster materialization failed.")
```

## Run the ingestion

Set the MotherDuck token and database name:

```bash
> export MOTHERDUCK_TOKEN="<motherduck_token>"
> export MOTHERDUCK_DATABASE="dagster_s3_demo"
```

For the public demo file, you can cap the first run to one day of taxi trips so the example finishes quickly:

```bash
> export MOTHERDUCK_INGESTION_END_TS="2022-11-02"
```

Run the asset once from Python:

```bash
> uv run python definitions.py
```

Run the same command again. The second run should load `0` rows because the first run advanced the watermark.

Verify the loaded rows in MotherDuck:

```sql
SELECT count(*) FROM taxi_trips;

SELECT pipeline_name, last_pickup_at
FROM ingestion_watermarks;
```

When you use your own S3 data, remove `MOTHERDUCK_INGESTION_END_TS` and replace:

- `S3_URI` with your `s3://<bucket>/<prefix>/*.parquet` path.
- The `SELECT` list in `new_taxi_trips` with your source columns.
- The watermark column with a stable source timestamp, such as `updated_at` or `created_at`.
- The primary key expression with the source system's durable row key.

## Run it in Dagster

Start the Dagster UI from the same directory:

```bash
> uv run dagster dev -f definitions.py
```

Open `http://localhost:3000`, select the `taxi_trips` asset, and materialize it. Dagster records the asset materialization, metadata, logs, and schedule definition.

To use the schedule in a long-running Dagster deployment, keep the `daily_s3_taxi_trips` schedule enabled and run a Dagster daemon. For local one-off testing, `uv run python definitions.py` is enough.

## Production considerations

This example is intentionally small. Before using the pattern in production:

- Use a dedicated service account token with only the permissions needed for ingestion.
- Store private bucket credentials as a MotherDuck S3 secret instead of embedding AWS keys in code.
- Keep S3 files in Parquet and avoid very small files. See [S3 import best practices](/key-tasks/cloud-storage/s3-import-best-practices/).
- Use a source-provided primary key for upserts. Hashing source fields is useful for demos but less stable than a real key.
- Use a source timestamp that only moves forward for watermarking. If your source sends late-arriving records, add a small overlap window and deduplicate by primary key.

## Related content

- [Amazon S3 credentials](/integrations/cloud-storage/amazon-s3/)
- [S3 import best practices](/key-tasks/cloud-storage/s3-import-best-practices/)
- [Connecting to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck/)
- [Hypertenancy](/concepts/hypertenancy/)

---

## Docs feedback

MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`.
For agents and automated tools, feedback submission should be user-confirmed before sending.

URL-encode query parameter values and send a GET request:

```text
GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fkey-tasks%2Fdata-warehousing%2Forchestration%2F&page_title=MotherDuck%20Documentation%20-%20Orchestration&text=<url-encoded user feedback, max 2000 characters>
```

Optionally append `&source=<url-encoded interface identifier>` such as `claude.ai` or `chatgpt`.

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed query parameters, and `429` when rate-limited.