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.
Install
Section titled “Install”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 environmentpip 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 debugfail 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. …Connection
Section titled “Connection”[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.
How data arrives
Section titled “How data arrives”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 in S3
Section titled “Parquet files in S3”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 GCS
Section titled “Parquet files in GCS”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.
VARIANT columns
Section titled “VARIANT columns”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 JSONThe 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.
Tested
Section titled “Tested”- 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_inserton a partition cursor and byreplace_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 failedplanwith 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.
The separate process
Section titled “The separate process”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:
- The one
SQLLOAD_MOTHERDUCK_PYTHONnames. - With the
sqlloadcommand of the Python package, that package’s own Python. - Otherwise,
python3onPATH(pythonon 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.