MotherDuck

Supermetal replicates to MotherDuck through its Postgres wire protocol endpoint.

Prerequisites

A MotherDuck account with an access token.
An external object store configured as the Supermetal buffer. Local file paths are not supported over the Postgres endpoint.
A persistent secret in MotherDuck for the object store (see step 2 below).

Setup

Connect to MotherDuck

MotherDuck exposes a Postgres wire protocol endpoint. Use it to connect from Supermetal without running DuckDB locally.

Field	Value
Host	`pg.<region>-aws.motherduck.com` (e.g., `pg.us-east-1-aws.motherduck.com`)
Port	`5432`
User	`postgres`
Password	Your MotherDuck access token
Database	Database name, or `md:` for the default database

Access token

See Authenticating to MotherDuck to create an access token.

Create object store secret

Create a secret so MotherDuck can read Parquet files from Supermetal's buffer (S3, Azure Blob Storage).

Open the MotherDuck SQL console and run one of the following.

CREATE SECRET IN MOTHERDUCK (
    TYPE S3,
    KEY_ID '...',
    SECRET '...',
    REGION 'us-east-1'
);

CREATE SECRET IN MOTHERDUCK (
    TYPE AZURE,
    CONNECTION_STRING 'AccountName=myaccount;AccountKey=...'
);

Target database and schema

Field	Default	Description
Target Database	(required)	Database to write into. Created automatically if it does not exist.
Target Schema		Optional. When set, all source tables land in this schema regardless of their source schema. Otherwise, each table keeps its source schema. Created if it does not exist.

Primary Keys

Opt in. Supermetal deduplicates using MERGE without primary key constraints on the table. Enabling primary keys adds the constraint, enforcing uniqueness at the database level.

Multi table transactions

Opt in. When enabled, each CDC batch wraps in a transaction, preserving source transaction boundaries across tables.

DuckLake

Supermetal can write to DuckLake databases on MotherDuck. Create a DuckLake database (fully managed or BYOB) and set it as the target database.

CREATE DATABASE my_ducklake (TYPE DUCKLAKE);

Arrow Type	DuckDB Type	Notes
`Int8`	`TINYINT`
`Int16`	`SMALLINT`
`Int32`	`INTEGER`
`Int64`	`BIGINT`
`UInt8`	`UTINYINT`
`UInt16`	`USMALLINT`
`UInt32`	`UINTEGER`
`UInt64`	`UBIGINT`
`Float16`	`FLOAT`
`Float32`	`FLOAT`
`Float64`	`DOUBLE`
`Decimal128(p,s)`	`DECIMAL(p,s)`	Falls back to `VARCHAR` when precision exceeds 38
`Decimal256(p,s)`	`DECIMAL(p,s)`	Falls back to `VARCHAR` when precision exceeds 38

Arrow Type	DuckDB Type	Notes
`Boolean`	`BOOLEAN`

Arrow Type	DuckDB Type	Notes
`Date32`, `Date64`	`DATE`
`Time32`, `Time64`	`TIME`
`Timestamp(s, None)`	`TIMESTAMP_S`
`Timestamp(ms, None)`	`TIMESTAMP_MS`
`Timestamp(us, None)`	`TIMESTAMP`	Microsecond precision, DuckDB's default
`Timestamp(ns, None)`	`TIMESTAMP_NS`
`Timestamp(ns, "UTC")`	`TIMESTAMP_NS`
`Timestamp(*, "UTC")`	`TIMESTAMP`	UTC timestamps except nanosecond
`Timestamp(*, tz)`	`TIMESTAMPTZ`	Time zones other than UTC
`Interval`	`INTERVAL`
`Duration`	`VARCHAR`

Arrow Type	DuckDB Type	Notes
`Utf8`, `LargeUtf8`	`VARCHAR`

Arrow Type	DuckDB Type	Notes
`Binary`, `LargeBinary`, `FixedSizeBinary`	`BLOB`

Arrow Type	DuckDB Type	Notes
`Utf8` with `arrow.json` extension	`VARIANT`	DuckDB 1.5+. Falls back to `JSON` on older versions.
`Struct`	`STRUCT(...)`	Field names and types preserved
`Map`	`MAP(K, V)`

Arrow Type	DuckDB Type	Notes
`List<T>`, `LargeList<T>`, `FixedSizeList<T>`	`T[]`

Nullable source columns are nullable on the target. All columns except primary keys are nullable by default to handle CDC edge cases. Enable preserve_source_nullability to carry over NOT NULL constraints from the source schema.