MotherDuck Reference

Reference material for MotherDuck Publishing -- data type mappings, CLI arguments, querying, and troubleshooting.

Data Type Mapping

Source types map to DuckDB-compatible types automatically.

PostgreSQL to DuckDB/MotherDuck

PostgreSQL Type	DuckDB Type
INTEGER, INT4	INTEGER
BIGINT, INT8	BIGINT
SMALLINT, INT2	SMALLINT
NUMERIC(p,s)	DECIMAL(p,s)
REAL, FLOAT4	REAL
DOUBLE PRECISION	DOUBLE
VARCHAR(n), TEXT	VARCHAR
DATE	DATE
TIMESTAMP	TIMESTAMP
TIMESTAMPTZ	TIMESTAMPTZ
TIME	TIME
BOOLEAN	BOOLEAN
BYTEA	BLOB
JSON, JSONB	JSON
UUID	UUID

SQL Server to DuckDB/MotherDuck

SQL Server Type	DuckDB Type
INT	INTEGER
BIGINT	BIGINT
SMALLINT	SMALLINT
TINYINT	TINYINT
DECIMAL(p,s)	DECIMAL(p,s)
MONEY	DECIMAL(19,4)
FLOAT	DOUBLE
REAL	REAL
VARCHAR(n), NVARCHAR(n)	VARCHAR
DATE	DATE
DATETIME, DATETIME2	TIMESTAMP
DATETIMEOFFSET	TIMESTAMPTZ
TIME	TIME
BIT	BOOLEAN
VARBINARY	BLOB
UNIQUEIDENTIFIER	UUID

Oracle to DuckDB/MotherDuck

Oracle Type	DuckDB Type
NUMBER	DECIMAL(38,9)
NUMBER(p,s)	DECIMAL(p,s)
FLOAT	DOUBLE
VARCHAR2, NVARCHAR2	VARCHAR
CLOB, NCLOB	VARCHAR
DATE	TIMESTAMP
TIMESTAMP	TIMESTAMP
RAW, BLOB	BLOB

CLI Reference

MotherDuck Publishing Arguments

Option	Type	Description
--publish_target ID	String	Credential ID for MotherDuck publishing (required)
--publish_schema_pattern PATTERN	String	Dynamic schema naming pattern (default: {schema})
--publish_table_pattern PATTERN	String	Dynamic table naming pattern (default: {table})
--publish_method TYPE	String	Table type: external (default) or internal
--n_jobs N	Integer	Number of parallel workers for table creation (default: 1)

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

Feature	MotherDuck	BigQuery	Snowflake	DuckLake
Query Engine	DuckDB	BigQuery	Snowflake	DuckDB
External Tables	Views with read_parquet()	External tables	External tables	DuckLake catalog
Native Tables	CREATE TABLE AS SELECT	Load jobs	COPY INTO	Not supported
Serverless	Yes	Yes	No (warehouse required)	N/A (local)
SQL Dialect	DuckDB/PostgreSQL	BigQuery SQL	Snowflake SQL	DuckDB/PostgreSQL
Best For	Analytics, exploration	Large-scale analytics	Enterprise data warehouse	Local development

See Also

• MotherDuck Publishing - Setup and usage guide
• CLI Reference - All command-line options