Skip to content
PipelineZ
Get Started

MotherDuck

The motherduck connector reads and writes a database hosted on MotherDuck through DuckDB’s own motherduck extension. The engine’s session attaches the database once per connection, and every read and write is a plain SQL statement that MotherDuck executes. The connector runs on the native tier only.

Connection

Section titled “Connection”
connections.yml
cloud:
  connector: motherduck
  database: my_db
  token: ${MOTHERDUCK_TOKEN}
Key Required Default Meaning
database Yes – The MotherDuck database to attach. It must already exist in the account.
token Yes – A MotherDuck access token. Use an environment reference, never a literal.

An entity is table or schema.table, named the way MotherDuck names it. The connector does not create databases or schemas. The connection names no local file or directory, so there is no project-relative path and no .pz/ guard.

Read options

Section titled “Read options”
Key Required Default Meaning
columns No – Column name to type map. When declared, the read projects only these columns.

A read compiles to a select over "<database>"."<table>", with the incremental watermark and any bounded window pushed into the query, so MotherDuck returns only the rows the run needs.

sync and retry under read: are the shared keys documented in the connections.yml reference.

Write options

Section titled “Write options”

motherduck takes no connector-specific write options. It supports every strategy:

Strategy What runs
append create table if not exists from the staged rows’ shape, then insert, matched by column name rather than position.
replace One create or replace table … as select.
merge One merge into, matched on keys:, executed by MotherDuck. Matched rows update, unmatched rows insert, and an empty batch leaves the target untouched.
connections.yml
cloud:
  connector: motherduck
  database: my_db
  token: ${MOTHERDUCK_TOKEN}
  entities:
    orders_current:
      write:
        strategy: merge
        keys: [order_id]

strategy, keys, schema_policy, and retry under write: are the shared keys documented in the connections.yml reference.

Capabilities

Section titled “Capabilities”
Flag Meaning
NativeScan Reads compile to a select over the attached database.
NativeCopy Writes compile to native insert, create-or-replace, or merge statements.
ReplaceWrites Supports strategy: replace.
Merge Supports strategy: merge through a server-side merge into.
BoundedWindow Pushes a watermark upper bound into the generated query.
InclusiveWatermarkBound Accepts an inclusive lower watermark bound (cursor >= value).

The connector does not declare Transactional: commit semantics belong to MotherDuck.

Notes

Section titled “Notes”
  • motherduck is native-only. Declaring engine.force_universal on a motherduck entity fails at plan time; remove that setting instead.
  • append matches columns by name, not position. A target column the pipeline does not produce keeps its existing default; a column the pipeline produces that the target lacks is an error naming it.
  • One token per run. The extension accepts a token only once per process, before its first attach. Every connection in a run that uses the same database and token shares that one setup. A second connection with a different token fails its own setup with a redacted error. Use one token per project. pz validate catches this ahead of a run: two motherduck connections that declare different tokens produce a PZ0311 warning naming both connections and comparing the resolved tokens, never printing either — this warning does not fail validation, since a project whose runs each touch only one of the connections is sound.
  • The token never appears in an error. It rides a session setting, not the attach string. A wrong token fails as a permanent, redacted error that names only md:<database>.
  • Duplicate keys within a merge batch collapse to one survivor. MotherDuck matches every staged row on its own against the target as it stood before the statement, so the generated MERGE INTO keys the staged side unique first. Which duplicate survives is not defined; the engine warns with PZ0522 so the pipeline can deduplicate deterministically when a specific row must win.
  • pz validate --connect reports the connection as not checked. There is no offline probe; the first run authenticates.
Code When
PZ0209 A strategy: merge write declares no keys:.
PZ0311 The token setting or the attach failed: a wrong token, a second token in the same run, or a database that does not exist.
PZ0312 engine.force_universal is set on a motherduck entity.
Section titled “Related”