MotherDuck Publishing

Commands use Linux syntax. See Examples & Recipes for the Windows equivalents and the arguments every sync needs.

The credentials entries used below, and the MotherDuck setup they rely on, are described in MotherDuck Publishing.

Example 1: Basic Export with External Views

./LakeXpress config create \
    -a credentials.json \
    --lxdb_auth_id lxdb_postgres \
    --source_db_auth_id source_postgres \
    --source_db_name mydb \
    --source_schema_name public \
    --target_storage_id s3_01 \
    --sub_path mydata \
    --publish_target motherduck_prod

Result: Database my_analytics, schema EXT_public (the default schema pattern for the default external publish method), external views over S3 Parquet files. Pass --publish_schema_pattern "{schema}" explicitly for a schema named public instead.

Example 2: Internal Tables with Parallel Execution

./LakeXpress config create \
    -a credentials.json \
    --lxdb_auth_id lxdb_postgres \
    --source_db_auth_id source_postgres \
    --source_db_name tpch \
    --source_schema_name tpch_1 \
    --target_storage_id s3_01 \
    --sub_path mydata \
    --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 config create \
    -a credentials.json \
    --lxdb_auth_id lxdb_postgres \
    --source_db_auth_id source_postgres \
    --source_db_name mydb \
    --source_schema_name sales \
    --target_storage_id s3_01 \
    --sub_path "daily/$(date +%Y%m%d)" \
    --publish_target motherduck_prod \
    --publish_schema_pattern "sales_{date}"

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

Example 4: Environment Variable Token

export motherduck_token="your-token-here"

./LakeXpress config create \
    -a credentials.json \
    --lxdb_auth_id lxdb_postgres \
    --source_db_auth_id source_postgres \
    --source_db_name mydb \
    --source_schema_name public \
    --target_storage_id s3_01 \
    --sub_path mydata \
    --publish_target motherduck_env

Example 5: GCS Storage Backend

{
    "gcs_01": {
        "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 config create \
    -a credentials.json \
    --lxdb_auth_id lxdb_postgres \
    --source_db_auth_id source_postgres \
    --source_db_name mydb \
    --source_schema_name public \
    --target_storage_id gcs_01 \
    --sub_path mydata \
    --publish_target motherduck_prod

Example 6: With Primary Key Constraints

Add --pk_constraints to include primary key definitions in native (internal) table DDL:

./LakeXpress config create \
    -a credentials.json \
    --lxdb_auth_id lxdb_postgres \
    --source_db_auth_id source_postgres \
    --source_schema_name public \
    --target_storage_id s3_01 \
    --publish_method internal \
    --publish_target motherduck_prod \
    --pk_constraints