Skip to content

Targets

MotherDuck

Load into MotherDuck (beta) through a separate DuckDB 1.5 process, with the same SQL and write strategies as DuckDB.

This target is in beta: it is tested, but gets less everyday use than the other connectors, so expect rough edges, and please report them in GitHub issues.

A MotherDuck target loads into a MotherDuck database with the same SQL, types and write strategies as a DuckDB target.

SQLLoad bundles DuckDB 2.0, and MotherDuck doesn’t accept DuckDB 2.0 clients yet: it supports DuckDB 1.4.1 to 1.5.6 (duckdb-versions.json). So SQLLoad sends a MotherDuck target’s SQL to a separate Python process running Python’s duckdb package at a version MotherDuck accepts. The two DuckDB versions never share a process, and every other target still uses SQLLoad’s own DuckDB.

Install the motherduck extra, which adds duckdb>=1.4.1,<=1.5.6, and pyarrow, which a MotherDuck source reads through:

uv tool install 'sqlload[motherduck]'
# or, in a virtual environment
pip install 'sqlload[motherduck]'

The sqlload command runs that process on its own Python. With sqlload-cli, or to use another Python, set SQLLOAD_MOTHERDUCK_PYTHON to a Python that has duckdb 1.4.1 to 1.5.6; without it, sqlload-cli uses python3 on PATH (python on Windows).

If the Python can’t run, lacks duckdb, or has a version MotherDuck refuses, sqlload debug, plan and load say so and how to fix it:

$ sqlload debug
fail target shop MotherDuck: cannot open md:shop: MotherDuck doesn't accept the version of Python's duckdb package that SQLLoad connects through: install duckdb 1.4.1 to 1.5.6 there (pip install 'sqlload[motherduck]'), or set SQLLOAD_MOTHERDUCK_PYTHON to a Python that has it. MotherDuck says: Your DuckDB version (v2.0.0) is not yet supported by MotherDuck. …
[connections.shop]
kind = "motherduck"
database = "shop"
token = "${ENV:MOTHERDUCK_TOKEN}"
[targets.shop]
connection = "shop"
loader_schema = "raw"
Key Meaning
database The MotherDuck database, with or without md:. Required, here or in the target. It can’t hold the token.
token A MotherDuck access token. Use ${ENV:NAME}. Default: the motherduck_token or MOTHERDUCK_TOKEN environment variable.

A target’s database overrides the connection’s, so one connection can serve, say, a shop_dev and a shop database. The database must exist. MotherDuck attaches the account’s other databases too, so SQLLoad names every table with its database, and a target that names no database is refused. Without a token SQLLoad refuses the connection rather than wait for MotherDuck’s browser sign-in. The token reaches the separate process over a pipe, never in its command line, and is taken out of every error. A duckdb connection refuses an md: path; use kind = "motherduck". The first connection installs MotherDuck’s DuckDB extension into ~/.duckdb, which needs the network once.

SQLLoad writes each batch to a temporary Parquet file on this machine, and the separate process inserts it with read_parquet inside the batch’s transaction, then the file is removed. plan names the route for every table.

Parquet files of a files source in S3 load straight from the bucket: MotherDuck’s servers read them with read_parquet (MD_RUN = REMOTE), and no row passes through this machine. MotherDuck reads the bucket with a secret that lasts only for the load’s session, made from the keys SQLLoad finds for the s3 connection (the environment, else the shared credentials file, else the AWS credential chain resolved on this machine, set again every four minutes as its keys may expire); nothing is stored in your MotherDuck account. A secret of your own in MotherDuck scoped to a narrower path of the bucket, such as s3://example-bucket/events/, wins over it. plan reads the first file to load on MotherDuck’s servers, and fails the table with the fix when MotherDuck can’t read it. MotherDuck’s servers read from their own network, so a bucket whose policy only allows your network or VPC endpoint can’t load into MotherDuck, and its keys need kms:Decrypt on a bucket’s KMS key:

$ sqlload plan
…
└── route DuckDB reads directly from s3://example-bucket/events/ (nothing through the loader)

Parquet files in a gcs connection with an HMAC key (hmac_key_id and hmac_secret) load the same way: MotherDuck’s servers read gs:// with a session-only secret made from that key, and plan reads the first file on MotherDuck’s servers first. Without an HMAC key SQLLoad reads the files, as MotherDuck takes no service-account key. The key’s service account needs roles/storage.objectViewer on the bucket, and MotherDuck’s servers read it from their own network, so a VPC Service Controls perimeter around the bucket keeps them out.

The route loads the same values as SQLLoad’s reader, and goes through SQLLoad’s reader for the same reasons as on DuckDB, such as CSV or JSON files, masked columns or a custom S3 endpoint, which MotherDuck’s servers don’t reach. Each batch fails unless MotherDuck reports inserting as many rows as the files’ footers hold. Files in a local folder or over SFTP, and a Snowflake source’s unloaded files, are read by SQLLoad.

append, merge, delete_insert and replace run the same statements as on DuckDB, and so do deletes and deletes_from on a merge table. Each table loads on its own connection in the separate process, so one table’s transaction never mixes with another’s. If the process ends mid-load, the open batch isn’t committed, the table fails, and the next load picks up from the table’s committed rows.

json_type = "variant" on the target loads json columns as VARIANT, as on DuckDB. It needs Python’s duckdb 1.5 or later in the separate process; on 1.4, plan fails the table with the fix. MotherDuck’s servers run DuckDB 1.5, which stores a VARIANT column whole rather than splitting its common fields into columns as DuckDB 2.0 does, so queries on its fields can be much slower than on JSON. plan and load warn on each table with VARIANT columns, without changing the exit code:

Warnings (1)
└── cloud__customers
└── MotherDuck runs DuckDB 1.5, which stores VARIANT unshredded: queries on its fields can be much slower than on JSON until MotherDuck moves to DuckDB 2.0; json_type = "json" keeps JSON

The warning goes once MotherDuck’s md_version() reports DuckDB 2.0. When MotherDuck reports no version, it goes by the separate process’s duckdb and says so.

  • Live, against a MotherDuck account on DuckDB 1.5.6: create, load, every write strategy, an incremental load and a rerun that adds nothing, a new column, every portable type, json_type = "variant" with its warning, and dropping the test databases afterwards.
  • Live, Parquet in S3 read on MotherDuck’s servers: append, replace, delete_insert on a partition cursor and by replace_key, loading at the same time, each compared row for row, lineage columns included, with the same files read by SQLLoad, and a second run. A bucket only this machine may read failed plan with the fix.
  • Offline, on every change: every write strategy and portable type loaded through the separate process into a stand-in for MotherDuck and compared with the same load into DuckDB, an incremental rerun, the process ending mid-load, a value MotherDuck rejects, and the setup errors.

Any command that connects to a MotherDuck target, such as sqlload debug, plan or load, starts the process itself, on the first of these Pythons:

  1. The one SQLLOAD_MOTHERDUCK_PYTHON names.
  2. With the sqlload command of the Python package, that package’s own Python.
  3. Otherwise, python3 on PATH (python on Windows).

There’s nothing to start, stop or clean up by hand. The process exits when SQLLoad does, including when SQLLoad is stopped or killed, and its open transactions roll back, so a stopped load leaves no part of a batch behind. DuckDB targets and every other target never start it.