MotherDuck Publishing

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

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_01": {
        "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 (Automatic, with a Manual Fallback)

MotherDuck needs credentials to read your Parquet files from cloud storage, whether you publish external views (queried live from storage) or internal tables (loaded once at publish time).

LakeXpress configures this automatically. On every publish, it takes the storage credential referenced by --target_storage_id (S3, GCS, or Azure) and issues a CREATE OR REPLACE SECRET lakexpress_storage (...) statement against the MotherDuck connection before creating tables/views, so no manual step is normally required.

If you still see errors like the ones below, it usually means the storage credential itself is misconfigured (e.g. a profile MotherDuck's environment cannot resolve), or a pre-existing secret is conflicting with the auto-created one:

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

You can also configure the secret manually in MotherDuck (useful for querying published views outside of a LakeXpress run, or for troubleshooting):

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 (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"
        }
    }
}

Field	Required	Description
ds_type	Yes	Must be "motherduck"
auth_mode	Yes	Set to "token"
database	Yes	Target MotherDuck database name
token	Yes	MotherDuck access token

Environment Variable

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 sync ...

Field	Required	Description
ds_type	Yes	Must be "motherduck"
auth_mode	Yes	Set to "env"
database	Yes	Target MotherDuck database name

Configuration Options

Option	Description	Default
--publish_target ID	Credential ID for MotherDuck target (required)	-
--publish_schema_pattern PATTERN	Schema naming pattern. The resolved name is lowercased, and characters other than letters, digits and _ become _	{schema} for internal, EXT_{schema} for the default external method (unless set explicitly)
--publish_table_pattern PATTERN	Table naming pattern	{table}
--publish_method TYPE	Table type: external or internal	external
--pk_constraints	Add PRIMARY KEY constraints to native (internal) table DDL; not supported for external views	off
--n_jobs N	Parallel workers for table creation	1

Table Types

External Views (Default)

Queries data directly from cloud storage via read_parquet().

Options for config create:

--publish_target motherduck_prod
--publish_method external

Generated SQL (default schema EXT_{schema}, lowercased):

CREATE OR REPLACE VIEW "my_database"."ext_tpch_1"."orders" AS
SELECT "o_orderkey", "o_custkey", ... 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.

Options for config create:

--publish_target motherduck_prod
--publish_method internal

Generated SQL (default schema {schema}):

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

With --pk_constraints, LakeXpress creates the table with its columns and PRIMARY KEY, then inserts the rows.

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

Token	Description	Example Output
{schema}	Source schema name	tpch_1
{table}	Source table name	customer
{date}	Current date (YYYYMMDD)	20251210
{timestamp}	Current timestamp (YYYYMMDD_HHMMSS)	20251210_143022
{uuid}	UUID4 identifier (consistent per run)	a1b2c3d4
{subpath}	CLI --sub_path parameter	staging

Common Patterns

Prefixed Schemas

--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

--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

--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

Ready-to-run commands for MotherDuck are in Examples & Recipes: MotherDuck.

Write Strategies

Incremental tables support the append (default), upsert and delete_insert strategies. A table using upsert or delete_insert needs a primary key and is always created as an internal table. MotherDuck has no MERGE: both strategies delete the matching rows, then insert. See Loading Strategies.

See Also

• MotherDuck ReferenceData type mappings, CLI arguments, querying, troubleshooting
• CLI ReferenceAll command-line options
• Examples & RecipesWorking command examples