Skip to content

motherduckdb/agent-skills / plugins/motherduck-skills-claude/skills/motherduck-load-data

motherduck-load-data

Verified

Load files, object storage, dataframes, or external databases into MotherDuck with bulk ingestion. Use for any import of data into MotherDuck.

FileKindSize
SKILL.mdskill md3.8 KB
references/INGESTION_PATTERNS.mdmarkdown18.0 KB

references/INGESTION_PATTERNS.md

View on GitHub
# Ingestion Patterns Reference

Advanced reference for data ingestion into MotherDuck. Covers format-specific options, cloud authentication, table formats (Delta Lake, Iceberg), large dataset strategies, and database replication patterns.

## Contents

| Section | Covers |
| --- | --- |
| Choose the client path first | Native DuckDB client vs Postgres-endpoint thin client, decision guide, PG-endpoint SQL patterns, local DuckDB database upload |
| Remote DuckDB Database Files | Physical import from S3 or another documented cloud URL |
| CSV Advanced Options | `read_csv` parameters, common scenarios, all-VARCHAR fallback |
| Parquet Advanced Options | Hive partitioning, schema evolution with `union_by_name` |
| JSON Advanced Options | Format types, nested JSON extraction |
| Cloud Storage Authentication | S3, GCS, Azure secrets and credential options |
| Delta Lake Ingestion | `delta_scan` patterns |
| Iceberg Ingestion | `iceberg_scan` patterns |
| Large Dataset Strategies | Filter during load, partitioned loads, column pruning, `COPY` / `COPY TO` |
| Database Replication Patterns | PostgreSQL, MySQL, MongoDB, API sources via dlt |
| Troubleshooting | Common load failures and fixes |

---

## Choose the client path first

Before choosing a file format or SQL shape, choose the ingestion surface:

- **Node.js with `@duckdb/node-api`** is a native DuckDB client path
- **Python with `duckdb`** is a native DuckDB client path
- **Node.js with `pg`**, **Python with `psycopg`**, or any other PostgreSQL driver talking to MotherDuck is a **Postgres-endpoint thin client path**

That distinction matters more than the programming language itself.

### Native DuckDB client paths

Treat these as full DuckDB-capable ingestion surfaces.

Use them when you need:

- local-file `COPY`
- loading from local DuckDB files
- uploading an existing DuckDB database into MotherDuck
- dataframe or Arrow registration
- `CREATE SECRET`
- extension-backed reads
- local execution behavior such as `MD_RUN = LOCAL`

These paths are the default when the data starts on local disk, in memory, or in a local DuckDB workflow.

### Postgres-endpoint thin client paths

Treat these as SQL submission paths, not as full DuckDB clients.

Use them when:

- the environment already speaks PostgreSQL
- the source files already live in object storage or HTTPS
- the app only needs to send SQL to MotherDuck

Do not expect the PG endpoint to behave like a native DuckDB client. It is best for:

- `CREATE TABLE AS SELECT` from remote files with `MD_RUN = REMOTE`
- `INSERT INTO ... SELECT` from remote files with `MD_RUN = REMOTE`
- explicit multi-row `INSERT` batches when the source exists only in application memory

Do not use the PG endpoint for:

- local-file `COPY`
- `CREATE SECRET`
- local DuckDB attachments
- extension install/load workflows
- local execution paths

### Best-practice decision guide

| Situation | Best path |
| --- | --- |
| Local file on disk | Native DuckDB client |
| Dataframe or Arrow buffer in memory | Native DuckDB client |
| Existing service already built on PostgreSQL drivers (Node.js, Python, etc.) | PG endpoint, but prefer remote-read CTAS or batched multi-row inserts |
| Files already in S3, GCS, R2, Azure, or HTTPS | PG endpoint or native DuckDB client; default to remote-read SQL |
| Need `CREATE SECRET` before loading | Native DuckDB client first, then PG endpoint is optional |

### SQL patterns for the PG endpoint

Preferred remote-read pattern:

```sql
CREATE OR REPLACE TABLE "my_db"."ingest"."orders_stage" AS
SELECT *
FROM read_parquet(
    's3://bucket/orders/*.parquet',
    MD_RUN = REMOTE
);
```

Preferred publish-after-stage pattern:

```sql
CREATE OR REPLACE TABLE "my_db"."main"."orders_curated" AS
SELECT
    order_id,
    customer_id,
    order_ts,
    total_amount
FROM "my_db"."ingest"."orders_stage";
```

If the source exists only in application memory, use larger multi-row batches instead of one-row-at-a-time inserts:

```sql
INSERT INTO "my_db"."main"."orders_batch" VALUES
  (1, 'a', 10.0),
  (2, 'b', 20.0),
  (3, 'c', 30.0);
```

For the PG endpoint, think in classic thin-client terms:

- fewer round trips
- larger batches
- append into staging first
- transactions that stay comfortably bounded

If you find yourself trying to simulate a local DuckDB ingestion workflow over the PG wire, switch to a native DuckDB client path instead.

### Upload a local DuckDB database

When the source is a whole local DuckDB database, use a native DuckDB client or CLI connection. This is not a Postgres-endpoint workflow.

Upload the current active local database:

```sql
ATTACH 'md:';
CREATE OR REPLACE DATABASE remote_database_name FROM CURRENT_DATABASE();
```

Upload an attached local database:

```sql
ATTACH '/path/to/local/database.duckdb' AS local_db_name;
ATTACH 'md:';
CREATE OR REPLACE DATABASE remote_database_name FROM local_db_name;
```

Upload directly from a file path:

```sql
ATTACH 'md:';
CREATE OR REPLACE DATABASE remote_database_name FROM '/path/to/local/database.duckdb';
```

Uploading a database does not switch the active query context. After the upload, qualify remote tables or `USE`/connect to the remote database before validating.

### Import a remote DuckDB database file

Create a native MotherDuck database by physically copying a remote `.duckdb`/`.db` file:

```sql
CREATE DATABASE remote_database_name
FROM 's3://my-bucket/path/source.duckdb';
```

Configure the documented cloud-storage secret first for private objects. Unlike cloning another MotherDuck database or an unfiltered share, importing a local or remote file copies its data and can take time and storage proportional to the source. Validate table counts and key aggregates after import.

A filtered share cannot be the source of `CREATE DATABASE ... FROM` because cloning would bypass its hidden-table boundary. Use `COPY FROM DATABASE <filtered_share> TO <target>` to copy only the visible tables.

---

## CSV Advanced Options

Use these parameters when `read_csv()` auto-detection fails or produces incorrect results.

```sql
SELECT * FROM read_csv('file.csv',
    -- Delimiters and quoting
    delim = ',',              -- Column delimiter (default: auto-detected)
    quote = '"',              -- Quote character (default: '"')
    escape = '"',             -- Escape character inside quotes (default: '"')

    -- Header and row handling
    header = true,            -- First row is column names (default: auto-detected)
    skip = 0,                 -- Number of rows to skip at the start
    null_padding = true,      -- Pad rows with fewer columns with NULLs
    ignore_errors = false,    -- Skip rows that fail to parse

    -- Type inference
    auto_detect = true,       -- Auto-detect types (default: true)
    all_varchar = false,      -- Read all columns as VARCHAR (disable type inference)
    sample_size = 20480,      -- Number of rows to sample for type inference

    -- Explicit column definitions
    columns = {               -- Override auto-detected types
        'id': 'INTEGER',
        'name': 'VARCHAR',
        'amount': 'DECIMAL(10,2)'
    },

    -- Date and time formats
    dateformat = '%Y-%m-%d',
    timestampformat = '%Y-%m-%d %H:%M:%S',

    -- Multi-file options
    filename = true,          -- Add source filename as a column
    union_by_name = true      -- Match columns by name across files (not position)
);
```

### Common CSV Scenarios

```sql
-- Pipe-delimited
SELECT * FROM read_csv('data.txt', delim = '|') LIMIT 10;

-- Tab-delimited (TSV)
SELECT * FROM read_csv('data.tsv', delim = '\t') LIMIT 10;

-- No header row
SELECT * FROM read_csv('data.csv', header = false) LIMIT 10;

-- Skip metadata rows at the top of the file
SELECT * FROM read_csv('report.csv', skip = 3, header = true) LIMIT 10;

-- Inconsistent column counts (ragged CSV)
SELECT * FROM read_csv('ragged.csv', null_padding = true) LIMIT 10;

-- Multiple CSV files with different column orders
CREATE TABLE "my_db"."main"."combined" AS
SELECT * FROM read_csv('s3://bucket/exports/*.csv', union_by_name = true);

-- Custom date format
CREATE TABLE "my_db"."main"."dated" AS
SELECT * FROM read_csv('data.csv', dateformat = '%d/%m/%Y');
```

### Force All Columns to VARCHAR for Manual Casting

When auto-detection produces wrong types, load everything as strings and cast manually:

```sql
CREATE TABLE "my_db"."main"."raw_import" AS
SELECT * FROM read_csv('messy_data.csv', all_varchar = true);

CREATE TABLE "my_db"."main"."clean_import" AS
SELECT
    CAST(id AS INTEGER) AS id,
    name,
    CAST(amount AS DECIMAL(10,2)) AS amount,
    strptime(date_str, '%m/%d/%Y')::DATE AS order_date
FROM "my_db"."main"."raw_import";
```

---

## Parquet Advanced Options

```sql
SELECT * FROM read_parquet('file.parquet',
    hive_partitioning = true,   -- Extract partition keys from directory structure
    filename = true,            -- Add source filename as a column
    union_by_name = true        -- Match columns by name across files
);
```

### Hive-Partitioned Datasets

Hive partitioning encodes column values in the directory path (e.g., `year=2024/month=01/data.parquet`).

```sql
-- Directory structure:
-- s3://bucket/data/year=2023/month=01/part-001.parquet
-- s3://bucket/data/year=2024/month=01/part-001.parquet

CREATE TABLE "my_db"."main"."partitioned_data" AS
SELECT * FROM read_parquet('s3://bucket/data/**/*.parquet',
    hive_partitioning = true
);
-- Result includes 'year' and 'month' as regular INTEGER columns
```

### Combining Files with Different Schemas

When source files have evolved schemas (added columns over time):

```sql
CREATE TABLE "my_db"."main"."merged" AS
SELECT * FROM read_parquet('s3://bucket/exports/*.parquet',
    union_by_name = true
);
-- Missing columns are filled with NULL
```

---

## JSON Advanced Options

```sql
SELECT * FROM read_json('file.json',
    format = 'auto',           -- 'auto', 'array', 'unstructured', 'newline_delimited'
    auto_detect = true,        -- Auto-detect schema (default: true)
    columns = {                -- Override auto-detected types
        'id': 'INTEGER',
        'payload': 'JSON',
        'tags': 'VARCHAR[]'
    },
    maximum_depth = -1,        -- Max nesting depth (-1 = unlimited)
    sample_size = 20480,       -- Rows to sample for schema inference
    filename = true,           -- Add source filename as a column
    union_by_name = true       -- Match columns by name across files
);
```

### JSON Format Types

```sql
-- Standard JSON array: [{"id": 1}, {"id": 2}]
SELECT * FROM read_json('users.json', format = 'array');

-- Newline-delimited JSON (NDJSON): one JSON object per line
SELECT * FROM read_json('events.ndjson', format = 'newline_delimited');
```

### Deeply Nested JSON

Load as raw JSON and extract fields with DuckDB JSON functions:

```sql
CREATE TABLE "my_db"."main"."raw_api" AS
SELECT * FROM read_json('api_response.json', maximum_depth = 2);

SELECT
    data->>'$.user.id' AS user_id,
    data->>'$.user.name' AS user_name,
    data->'$.user.addresses' AS addresses
FROM "my_db"."main"."raw_api";
```

---

## Cloud Storage Authentication

### S3

**Option 1: Environment Variables (Recommended)**

```bash
export AWS_ACCESS_KEY_ID="AKIA..."
export AWS_SECRET_ACCESS_KEY="..."
export AWS_DEFAULT_REGION="us-east-1"
export AWS_SESSION_TOKEN="..."  # Optional: for temporary credentials
```

**Option 2: CREATE SECRET**

```sql
CREATE SECRET my_s3_secret (
    TYPE S3,
    KEY_ID 'AKIA...',
    SECRET '...',
    REGION 'us-east-1'
);
```

**Option 3: IAM Role (EC2 / ECS / Lambda)**

DuckDB uses the instance metadata service automatically when an IAM role is attached. No credentials needed.

### GCS

**Option 1: Service Account Key**

```sql
CREATE SECRET my_gcs_secret (
    TYPE GCS,
    KEY_ID 'GOOG...',
    SECRET '...'
);
```

**Option 2: gcloud CLI (Local Development)**

```bash
gcloud auth application-default login
```

**Option 3: Workload Identity (GKE)** -- automatic, no configuration needed.

### Azure Blob Storage

**Option 1: Connection String**

```sql
CREATE SECRET my_azure_secret (
    TYPE AZURE,
    CONNECTION_STRING 'DefaultEndpointsProtocol=https;AccountName=...;AccountKey=...;EndpointSuffix=core.windows.net'
);
```

**Option 2: Service Principal**

```sql
CREATE SECRET my_azure_secret (
    TYPE AZURE,
    PROVIDER CREDENTIAL_CHAIN,
    ACCOUNT_NAME 'mystorageaccount'
);
```

**Option 3: Environment Variables**

```bash
export AZURE_STORAGE_ACCOUNT="mystorageaccount"
export AZURE_STORAGE_KEY="..."
```

### Public Buckets

No credentials needed:

```sql
SELECT * FROM read_parquet('s3://public-bucket/data.parquet') LIMIT 10;
SELECT * FROM read_parquet('https://data.example.com/public/data.parquet') LIMIT 10;
```

---

## Delta Lake Ingestion

Requires the pre-installed `delta` extension.

```sql
CREATE TABLE "my_db"."main"."delta_data" AS
SELECT * FROM delta_scan('s3://bucket/delta-table/');

-- Preview and filter
SELECT * FROM delta_scan('s3://bucket/delta-table/') LIMIT 10;
DESCRIBE SELECT * FROM delta_scan('s3://bucket/delta-table/');

CREATE TABLE "my_db"."main"."recent_delta" AS
SELECT * FROM delta_scan('s3://bucket/delta-table/')
WHERE created_at >= '2024-01-01';
```

---

## Iceberg Ingestion

Requires the pre-installed `iceberg` extension.

```sql
CREATE TABLE "my_db"."main"."iceberg_data" AS
SELECT * FROM iceberg_scan('s3://bucket/iceberg-table/');

SELECT * FROM iceberg_scan('s3://bucket/iceberg-table/') LIMIT 10;

CREATE TABLE "my_db"."main"."iceberg_subset" AS
SELECT user_id, event_type, event_time
FROM iceberg_scan('s3://bucket/iceberg-table/');
```

---

## Large Dataset Strategies

### Filter During Load

```sql
CREATE TABLE "my_db"."main"."orders_2024" AS
SELECT * FROM read_parquet('s3://bucket/orders/**/*.parquet',
    hive_partitioning = true
)
WHERE year = 2024;
```

### Partition Load by Date

```sql
CREATE TABLE "my_db"."main"."q1_2024" AS
SELECT * FROM read_parquet('s3://bucket/data/year=2024/month=01/*.parquet',
    hive_partitioning = true);

INSERT INTO "my_db"."main"."q1_2024"
SELECT * FROM read_parquet('s3://bucket/data/year=2024/month=02/*.parquet',
    hive_partitioning = true);
```

### Select Only Needed Columns

Parquet's columnar format means unselected columns are never read from disk:

```sql
CREATE TABLE "my_db"."main"."narrow" AS
SELECT user_id, event_type, timestamp, revenue
FROM read_parquet('s3://bucket/wide_events.parquet');
```

### Use COPY for Bulk Operations

```sql
COPY "my_db"."main"."orders" FROM 's3://bucket/orders.csv' (FORMAT CSV, HEADER true);
COPY "my_db"."main"."events" FROM 's3://bucket/events.parquet' (FORMAT PARQUET);
```

### COPY TO (Export)

```sql
COPY "my_db"."main"."orders" TO 's3://bucket/export/orders.parquet' (FORMAT PARQUET);

COPY (
    SELECT customer_id, SUM(amount) AS total
    FROM "my_db"."main"."orders"
    GROUP BY customer_id
) TO 's3://bucket/export/totals.parquet' (FORMAT PARQUET);

COPY "my_db"."main"."events" TO 's3://bucket/export/events' (
    FORMAT PARQUET,
    PARTITION_BY (year, month)
);
```

---

## Database Replication Patterns

### PostgreSQL to MotherDuck

**Option 1: Managed ETL (Recommended).** Use Fivetran, Airbyte, or Estuary for continuous CDC replication. These handle schema changes, soft deletes, and incremental updates automatically.

**Option 2: pg_dump + Load (One-time or Periodic)**

```bash
psql -d mydatabase -c "COPY orders TO STDOUT WITH CSV HEADER" > orders.csv
```

```sql
CREATE TABLE "my_db"."main"."orders" AS
SELECT * FROM read_csv('s3://staging-bucket/orders.csv');
```

**Option 3: Direct PostgreSQL Attach (native DuckDB API only)**

```sql
ATTACH 'dbname=mydb user=myuser host=pg-host.example.com' AS pg_source (TYPE POSTGRES);

CREATE TABLE "my_db"."main"."orders" AS
SELECT * FROM pg_source.public.orders;
```

Note: The `postgres` extension is not pre-installed on MotherDuck. This works only via a local DuckDB instance.

### MySQL to MotherDuck

**Option 1: Managed ETL.** Use Fivetran, Airbyte, or Streamkap.

**Option 2: mysqldump + Load**

```bash
mysql -u user -p -e "SELECT * FROM orders" --batch --raw mydatabase > orders.tsv
```

```sql
CREATE TABLE "my_db"."main"."orders" AS
SELECT * FROM read_csv('s3://staging-bucket/orders.tsv', delim = '\t');
```

**Option 3: Direct MySQL Attach (native DuckDB API only)**

```sql
ATTACH 'host=mysql-host user=myuser password=mypass database=mydb' AS mysql_src (TYPE MYSQL);
CREATE TABLE "my_db"."main"."customers" AS
SELECT * FROM mysql_src.customers;
```

Note: The `mysql` extension is not pre-installed on MotherDuck. This works only via a local DuckDB instance.

### MongoDB to MotherDuck

Use Airbyte, Estuary, or Streamkap for MongoDB CDC. There is no direct DuckDB connector for MongoDB.

### API Sources to MotherDuck

Use **dlt** (data load tool) for REST API ingestion:

```python
import dlt

pipeline = dlt.pipeline(
    pipeline_name="api_ingest",
    destination="motherduck",
    dataset_name="api_data"
)

data = [{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]
pipeline.run(data, table_name="users")
```

---

## Troubleshooting

### "No files found" error

Verify the path and credentials. S3 paths are case-sensitive.

```sql
SELECT * FROM read_parquet('s3://bucket/path/*.parquet') LIMIT 1;
```

### Type mismatch on INSERT

Compare source and target schemas, then cast explicitly:

```sql
DESCRIBE SELECT * FROM read_csv('source.csv');
DESCRIBE SELECT * FROM "my_db"."main"."target_table";

INSERT INTO "my_db"."main"."target_table"
SELECT
    CAST(id AS INTEGER),
    CAST(amount AS DECIMAL(10,2)),
    CAST(date_str AS DATE)
FROM read_csv('source.csv');
```

### CSV parsing errors

Use `ignore_errors` to skip malformed rows, then check what was lost:

```sql
CREATE TABLE "my_db"."main"."clean_data" AS
SELECT * FROM read_csv('messy.csv', ignore_errors = true);

SELECT count(*) AS source_rows FROM read_csv('messy.csv', all_varchar = true);
SELECT count(*) AS loaded_rows FROM "my_db"."main"."clean_data";
```

### Slow loads from cloud storage

- Prefer Parquet over CSV.
- Select only needed columns instead of `SELECT *`.
- Filter during load with WHERE.
- Load in batches by partition key for very large datasets.

### Encoding issues

If the CSV contains non-UTF-8 characters, load as `all_varchar = true` and handle encoding in a transformation step, or convert the file to UTF-8 before loading.
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall