MotherDuck Reference

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

Data Type Mapping

Source types map automatically to DuckDB/MotherDuck-compatible types. LakeXpress exports through Parquet, so the mapping is driven by the normalized source type name.

Pick your source database:

PostgreSQL

PostgreSQL to DuckDB/MotherDuck

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

SQL Server

SQL Server to DuckDB/MotherDuck

SQL Server Type	DuckDB/MotherDuck Type
TINYINT	TINYINT
SMALLINT	SMALLINT
INT	INTEGER
BIGINT	BIGINT
DECIMAL(p,s)	DECIMAL(p,s)
MONEY	DECIMAL(19,4)
SMALLMONEY	DECIMAL(10,4)
FLOAT, REAL	REAL
BIT	BOOLEAN
VARCHAR(n), NVARCHAR(n), CHAR(n), NCHAR(n), TEXT, NTEXT	VARCHAR
DATE	DATE
DATETIME, DATETIME2, SMALLDATETIME	TIMESTAMP
DATETIMEOFFSET	TIMESTAMPTZ
VARBINARY	BLOB
UNIQUEIDENTIFIER	UUID

Oracle

Oracle to DuckDB/MotherDuck

Oracle Type	DuckDB/MotherDuck Type
NUMBER	DECIMAL(38,9)
NUMBER(p,s)	DECIMAL(p,s)
FLOAT	REAL
VARCHAR2(n), NVARCHAR2(n), CHAR(n), CLOB, NCLOB	VARCHAR
DATE	DATE
TIMESTAMP	TIMESTAMP
RAW, BLOB	BLOB

Teradata

Teradata to DuckDB/MotherDuck

Teradata Type	DuckDB/MotherDuck Type
BYTEINT, VARCHAR(n), CHAR(n), CLOB, VARBYTE	VARCHAR
SMALLINT	SMALLINT
INTEGER	INTEGER
BIGINT	BIGINT
DECIMAL(p,s), NUMERIC(p,s)	DECIMAL(p,s)
NUMBER	DECIMAL(38,9)
FLOAT, REAL	REAL
DATE	DATE
TIME	TIME
TIMESTAMP	TIMESTAMP
BYTE, BLOB	BLOB

MySQL

MySQL to DuckDB/MotherDuck

MySQL Type	DuckDB/MotherDuck Type
TINYINT	TINYINT
SMALLINT	SMALLINT
MEDIUMINT, YEAR, VARCHAR(n), CHAR(n), TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, ENUM, SET, TINYBLOB, MEDIUMBLOB, LONGBLOB	VARCHAR
INT	INTEGER
BIGINT	BIGINT
DECIMAL(p,s)	DECIMAL(p,s)
FLOAT	REAL
DOUBLE	DOUBLE
BIT	BOOLEAN
DATE	DATE
TIME	TIME
DATETIME, TIMESTAMP	TIMESTAMP
BINARY, VARBINARY, BLOB	BLOB
JSON	JSON

MariaDB

MariaDB to DuckDB/MotherDuck

MariaDB Type	DuckDB/MotherDuck Type
TINYINT	TINYINT
SMALLINT	SMALLINT
MEDIUMINT, YEAR, VARCHAR(n), CHAR(n), TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, ENUM, SET, TINYBLOB, MEDIUMBLOB, LONGBLOB	VARCHAR
INT	INTEGER
BIGINT	BIGINT
DECIMAL(p,s)	DECIMAL(p,s)
FLOAT	REAL
DOUBLE	DOUBLE
BIT	BOOLEAN
DATE	DATE
TIME	TIME
DATETIME, TIMESTAMP	TIMESTAMP
BINARY, VARBINARY, BLOB	BLOB
JSON	JSON

SAP HANA

SAP HANA to DuckDB/MotherDuck

SAP HANA Type	DuckDB/MotherDuck Type
TINYINT	TINYINT
SMALLINT	SMALLINT
INTEGER	INTEGER
BIGINT	BIGINT
DECIMAL(p,s)	DECIMAL(p,s)
SMALLDECIMAL, VARCHAR(n), NVARCHAR(n), CHAR(n), NCHAR(n), CLOB, NCLOB, TEXT, SECONDDATE	VARCHAR
REAL	REAL
DOUBLE	DOUBLE
BOOLEAN	BOOLEAN
DATE	DATE
TIME	TIME
TIMESTAMP	TIMESTAMP
VARBINARY, 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} for internal, EXT_{schema} for the default external method)
--publish_table_pattern PATTERN	String	Dynamic table naming pattern (default: {table})
--publish_method TYPE	String	Table type: external (default) or internal
--pk_constraints	Flag	Add PRIMARY KEY constraints to native (internal) table DDL; not supported for external views
--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":

• LakeXpress auto-configures a lakexpress_storage secret from the --target_storage_id credential on every publish, so this usually means that credential itself is misconfigured (wrong keys, an unresolvable profile, etc.)
• If querying published views outside of a LakeXpress run, create a secret in MotherDuck manually 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