MotherDuck Publishing

LakeXpress creates MotherDuck tables from exported Parquet files in cloud storage (S3, GCS, Azure).

Table of Contents

• Prerequisites
• Authentication
• Configuration Options
• Table Types
• Dynamic Naming Patterns
• Usage Examples
• Data Type Mapping
• CLI Reference

Prerequisites

1. MotherDuck Account

Sign up at motherduck.com and generate an access token:

1. Log in to MotherDuck
2. Go to Settings → Access Tokens
3. Create a new token
4. Copy the token for your credentials file

2. Cloud Storage Setup

Configure credentials for cloud storage and MotherDuck:

{
    "s3_datalake": {
        "ds_type": "s3",
        "auth_mode": "profile",
        "info": {
            "directory": "s3://my-datalake-bucket/lakexpress/",
            "profile": "my-aws-profile"
        }
    },
    "motherduck_prod": {
        "ds_type": "motherduck",
        "auth_mode": "token",
        "info": {
            "database": "my_analytics",
            "token": "your-motherduck-access-token"
        }
    }
}

3. Storage Access from MotherDuck (Required for External Views)

Important: When using external views (--publish_method external), MotherDuck needs credentials to read your Parquet files from cloud storage. Without this, you’ll see errors like:

IO Error: No files found that match the pattern "s3://..."
HTTP Error: Permission error: Missing or invalid credentials

Configure secrets in MotherDuck before querying external views:

For AWS S3 (most common):

CREATE SECRET aws_s3_secret (
    TYPE S3,
    KEY_ID 'your_aws_access_key_id',
    SECRET 'your_aws_secret_access_key',
    REGION 'us-east-1'  -- your bucket's region
);

To find your AWS credentials:

aws configure get aws_access_key_id
aws configure get aws_secret_access_key
aws configure get region

For GCS:

CREATE SECRET gcs_secret (
    TYPE GCS,
    KEY_ID 'your_hmac_access_key',
    SECRET 'your_hmac_secret_key'
);

For Azure:

CREATE SECRET azure_secret (
    TYPE AZURE,
    ACCOUNT_NAME 'your_storage_account',
    ACCOUNT_KEY 'your_account_key'
);

Verify secrets are configured:

SELECT * FROM duckdb_secrets();

Authentication

Token Authentication (Recommended)

Store your token in the credentials file:

{
    "motherduck_prod": {
        "ds_type": "motherduck",
        "auth_mode": "token",
        "info": {
            "database": "my_analytics",
            "token": "your-motherduck-access-token"
        }
    }
}

Environment Variable Authentication

Read the token from an environment variable:

{
    "motherduck_env": {
        "ds_type": "motherduck",
        "auth_mode": "env",
        "info": {
            "database": "my_analytics"
        }
    }
}

export motherduck_token="your-motherduck-access-token"
lakexpress ...

Configuration Options

Table Types

External Views (Default)

Queries data directly from cloud storage via read_parquet().

lakexpress ... --publish_target motherduck_prod --publish_method external

Generated SQL:

CREATE OR REPLACE VIEW "my_database"."tpch_1"."orders" AS
SELECT * FROM read_parquet('s3://bucket/exports/tpch_1/orders/*.parquet')

Pros:

• No data copying, instant publishing
• No MotherDuck storage costs
• Always reflects latest cloud storage data
• Best for large datasets, infrequent queries, exploration

Trade-offs:

• Query speed depends on cloud storage latency
• Requires cloud storage credentials in MotherDuck

Internal Tables

Loads data into MotherDuck native columnar storage.

lakexpress ... --publish_target motherduck_prod --publish_method internal

Generated SQL:

CREATE OR REPLACE TABLE "my_database"."tpch_1"."orders" AS
SELECT * FROM read_parquet('s3://bucket/exports/tpch_1/orders/*.parquet')

Pros:

• Faster queries (optimized columnar format)
• No storage credentials needed after load
• Better caching for repeated queries
• Best for dashboards and production analytics

Trade-offs:

• Slower publishing (data must load)
• MotherDuck storage costs apply
• Data is a point-in-time snapshot

Dynamic Naming Patterns

Schema and table names support token-based patterns.

Supported Tokens

Common Patterns

Prefixed Schemas

lakexpress \
  --publish_schema_pattern "lx_{schema}" \
  --publish_table_pattern "{table}" \
  --publish_target motherduck_prod \
  ...

# Results:
# Schema: lx_tpch_1
# Tables: customer, orders, lineitem

Date-Partitioned Schemas

lakexpress \
  --publish_schema_pattern "{schema}_{date}" \
  --publish_table_pattern "{table}" \
  --publish_target motherduck_prod \
  ...

# Results:
# Schema: tpch_1_20251210
# Tables: customer, orders, lineitem

Consolidated Multi-Schema

lakexpress \
  --source_schema_name schema1,schema2 \
  --publish_schema_pattern "consolidated" \
  --publish_table_pattern "{schema}_{table}" \
  --publish_target motherduck_prod \
  ...

# Results:
# Schema: consolidated
# Tables: schema1_customer, schema2_customer

Usage Examples

Example 1: Basic Export with External Views

lakexpress -a credentials.json \
    --log_db_auth_id log_db \
    --source_db_auth_id postgres_prod \
    --source_schema_name public \
    --target_storage_id s3_datalake \
    --fastbcp_dir_path /path/to/FastBCP \
    --publish_target motherduck_prod

Result: Database my_analytics, schema public, external views over S3 Parquet files.

Example 2: Internal Tables with Parallel Execution

lakexpress -a credentials.json \
    --log_db_auth_id log_db \
    --source_db_auth_id postgres_prod \
    --source_schema_name tpch_1 \
    --target_storage_id s3_datalake \
    --fastbcp_dir_path /path/to/FastBCP \
    --publish_target motherduck_prod \
    --publish_method internal \
    --publish_schema_pattern "lx_{schema}" \
    --n_jobs 4

Result: Database my_analytics, schema lx_tpch_1, native tables loaded from S3.

Example 3: Daily Snapshots

lakexpress -a credentials.json \
    --log_db_auth_id log_db \
    --source_db_auth_id postgres_prod \
    --source_schema_name sales \
    --target_storage_id s3_datalake \
    --fastbcp_dir_path /path/to/FastBCP \
    --publish_target motherduck_prod \
    --publish_schema_pattern "sales_{date}" \
    --sub_path "daily/$(date +%Y%m%d)"

Result: Schema sales_20251210, data at s3://bucket/lakexpress/daily/20251210/.

Example 4: Environment Variable Token

export motherduck_token="your-token-here"

lakexpress -a credentials.json \
    --log_db_auth_id log_db \
    --source_db_auth_id postgres_prod \
    --source_schema_name public \
    --target_storage_id s3_datalake \
    --fastbcp_dir_path /path/to/FastBCP \
    --publish_target motherduck_env

Example 5: GCS Storage Backend

{
    "gcs_datalake": {
        "ds_type": "gcs",
        "auth_mode": "profile",
        "info": {
            "directory": "gs://my-datalake-bucket/lakexpress/",
            "profile": "/path/to/service-account.json"
        }
    },
    "motherduck_prod": {
        "ds_type": "motherduck",
        "auth_mode": "token",
        "info": {
            "database": "my_analytics",
            "token": "your-motherduck-token"
        }
    }
}

lakexpress -a credentials.json \
    --log_db_auth_id log_db \
    --source_db_auth_id postgres_prod \
    --source_schema_name public \
    --target_storage_id gcs_datalake \
    --fastbcp_dir_path /path/to/FastBCP \
    --publish_target motherduck_prod

Data Type Mapping

Source types map to DuckDB-compatible types automatically.

PostgreSQL → DuckDB/MotherDuck

SQL Server → DuckDB/MotherDuck

Oracle → DuckDB/MotherDuck

CLI Reference

MotherDuck Publishing Arguments

Querying MotherDuck Tables

Once published, query tables via any of these methods.

MotherDuck Web UI:

Log in to app.motherduck.com and run:

SELECT * FROM my_analytics.lx_tpch_1.customer LIMIT 10;

DuckDB CLI:

duckdb "md:my_analytics?motherduck_token=your_token"

SELECT * FROM lx_tpch_1.customer LIMIT 10;

Python (duckdb):

import duckdb

conn = duckdb.connect("md:my_analytics?motherduck_token=your_token")
df = conn.execute("SELECT * FROM lx_tpch_1.customer LIMIT 10").df()
print(df)

Python with environment variable:

import os
import duckdb

os.environ["motherduck_token"] = "your_token"
conn = duckdb.connect("md:my_analytics")
df = conn.execute("SELECT * FROM lx_tpch_1.customer LIMIT 10").df()
print(df)

Troubleshooting

Common Issues

“Authentication failed”:

• Verify your token is valid and not expired
• Check token permissions for the target database
• For env mode, ensure motherduck_token is set

“Database not found”:

• Verify the database name matches an existing MotherDuck database
• Create the database in MotherDuck UI before publishing

“IO Error: No files found that match the pattern” or “Permission error: Missing or invalid credentials”:

• This means MotherDuck cannot access your private S3/GCS/Azure bucket
• Create a secret in MotherDuck with your cloud storage credentials (see Storage Access)
• Verify secrets are configured: SELECT * FROM duckdb_secrets();
• Test access: SELECT count(*) FROM read_parquet('s3://your-bucket/path/*.parquet');

“Cannot read file” (external views):

• Verify cloud storage credentials in MotherDuck
• Check that the S3/GCS/Azure path contains valid Parquet files
• Ensure the bucket allows access from MotherDuck
• Verify the files exist: aws s3 ls s3://your-bucket/path/ --recursive | head

“Connection timeout”:

• Check network connectivity
• MotherDuck requires outbound HTTPS
• Verify no firewall is blocking the connection

Verifying Setup

Test MotherDuck connectivity:

duckdb "md:my_analytics?motherduck_token=your_token" -c "SELECT 1"

Test cloud storage access from MotherDuck:

SELECT * FROM read_parquet('s3://your-bucket/path/to/file.parquet') LIMIT 1;

Configuring Storage Secrets

See Storage Access from MotherDuck in Prerequisites for detailed instructions on configuring S3, GCS, or Azure secrets.

Comparison with Other Targets