MotherDuck
Overview
The MotherDuck destination writes each Airbyte stream to a table in a MotherDuck database. It uses the DuckDB client and supports MotherDuck-hosted databases and local DuckDB files.
:::caution
Use a MotherDuck-hosted database in Airbyte Cloud. Local DuckDB files aren’t persistent in Airbyte Cloud.
:::
Prerequisites
You need the following to write to MotherDuck:
- A MotherDuck account.
- A Read/Write access token. Scaling tokens are read-only and don’t work with this destination.
- The name of a MotherDuck database that the token’s account can write to.
For automated production workloads, you can use a MotherDuck service account with a Read/Write token. An organization Admin must create and configure service accounts.
Set up the destination
-
In MotherDuck, open your organization settings and select Create token.
-
Select Read/Write, configure an expiration if needed, and copy the token.
-
In Airbyte, create a MotherDuck destination.
-
Enter these values:
- MotherDuck Access Token: Enter the Read/Write token you created.
- Destination DB: Enter
md:<database-name>, for examplemd:analytics. To use MotherDuck’s defaultmy_dbdatabase, entermd:.
Don’t include the token in the connection string. Tokens in connection strings might appear in execution logs.
- Schema Name: Enter the schema where Airbyte creates destination tables. The default is
main. Use only letters, numbers, and underscores. The connection test doesn’t check this value. A schema name containing other characters, such asraw-data, fails during a sync withInvalid SQL name.
-
Select Set up destination and wait for the connection test to finish.
Destinations V2
This destination uses Destinations V2 to write source fields directly to typed destination columns.
Learn more in the Typing and deduping guide.
Supported sync modes
| Sync mode | Supported? |
|---|---|
| Full Refresh - Overwrite | Yes |
| Full Refresh - Append | Yes |
| Full Refresh - Overwrite + Deduped | Yes |
| Incremental Sync - Append | Yes |
| Incremental Sync - Append + Deduped | Yes |
Output schema
Each table contains at least these columns:
_airbyte_raw_id: A UUID that Airbyte assigns to each record._airbyte_extracted_at: The time Airbyte extracted the record from the source._airbyte_meta: A JSON object containing record metadata. This destination always writes an empty object.
The destination also creates typed columns for fields in each stream’s JSON schema. It stores object and array fields as DuckDB JSON columns.
Airbyte normalises table and column names before creating them:
- Letters are converted to lowercase.
- Whitespace and most special characters are replaced with underscores.
- Unicode letters and numbers are preserved.
- Names starting with a digit receive an underscore prefix.
Deduplication
For sync modes that remove duplicates, the connector first loads each batch into a temporary table. For each primary key, it keeps the record with the most recent _airbyte_extracted_at value. It then merges the result into the destination table.
Primary keys aren’t declared as table constraints, so records with null primary key values still load. If the source doesn’t define a primary key and you don’t select one, the connector appends every record. It doesn’t remove duplicates.
Use local DuckDB files
In Self-Managed Airbyte, you can enter a local .duckdb path for Destination DB. Airbyte normalises the path under /local. This path maps to the host directory configured by LOCAL_ROOT. The default host directory is /tmp/airbyte_local.
For example, /local/destination.duckdb maps to /tmp/airbyte_local/destination.duckdb with the default LOCAL_ROOT.
If you only need local DuckDB files, consider using the DuckDB destination.
Limitations
- The connector writes all selected streams to the configured Schema Name. It doesn’t map source namespaces to separate MotherDuck schemas.
- Stream names must be unique after normalisation, including streams from different source namespaces.
- Field names in each record must be unique after normalisation. If fields collide, such as
userIdanduserid, the connector skips the record. - Data generations aren’t supported.
Troubleshooting
Invalid SQL name
The Schema Name contains characters other than letters, numbers, and underscores. Edit the destination and enter a schema name containing only these characters.
Missing destination records
Check the sync logs for Data contained duplicate keys after normalization. The connector skips records whose field names collide after normalisation. The log lists the field names it dropped. Rename the conflicting source fields or exclude one from the connection. Then run the sync again.
Slow syncs after a fallback warning
If a batch can’t be prepared as an Arrow table, the connector logs Writing with PyArrow table failed, falling back to writing with executemany. It then loads the batch with individual INSERT statements. The sync still finishes, but more slowly. The warning’s stack trace identifies the underlying failure.
Changelog
Expand to review
| Version | Date | Pull Request | Subject |
|---|---|---|---|
| 0.2.6 | 2026-08-04 | 83694 | Fix JSON column serialization being skipped for non-lowercase source property names, causing empty STRUCT failures |
| 0.2.5 | 2026-07-22 | 82244 | Fix sync failures on array fields containing empty objects by serializing JSON array columns before load |
| 0.2.4 | 2026-07-14 | 81511 | Fix silent data loss on multi-stream syncs by no longer discarding other streams’ buffered records when one stream is flushed |
| 0.2.3 | 2026-03-31 | 75645 | Bump version to force registry update for supportLevel change to certified |
| 0.2.2 | 2026-03-17 | 70438 | Fix for camelCase columns being NULL |
| 0.2.1 | 2026-01-29 | 70999 | Fix for empty STRUCTs |
| 0.2.0 | 2026-01-07 | 71063 | Upgrade DuckDB to v1.4.2 and duckdb-engine to v0.17.0 |
| 0.1.26 | 2025-10-21 | 68338 | Update dependencies |
| 0.1.25 | 2025-10-14 | 67952 | Update dependencies |
| 0.1.24 | 2025-10-07 | 66822 | Update dependencies |
| 0.1.23 | 2025-08-08 | 64161 | feat: allow null values in primary key fields. Primary keys are no longer declared as table constraints. |
| 0.1.22 | 2025-07-22 | 63714 | fix(destination-motherduck): handle special characters in stream name when creating tables |
| 0.1.21 | 2025-07-22 | 63709 | fix: resolve error “Can’t find the home directory at ‘/nonexistent’” #63710 |
| 0.1.20 | 2025-07-07 | 62133 | fix: when primary_key is not defined in the catalog, use source_defined_primary_key if available |
| n/a | 2025-06-26 | 48673 | Update dependencies |
| 0.1.19 | 2025-05-28 | 60906 | Allow Unicode characters in database/table names |
| 0.1.18 | 2025-03-01 | 54737 | Update airbyte-cdk to ^6.0.0 in destination-motherduck |
| 0.1.17 | 2024-12-26 | 50425 | Fix bug overwrite write method not saving all batches |
| 0.1.16 | 2024-12-06 | 48562 | Improved handling of config parameters during SQL engine creation. |
| 0.1.15 | 2024-11-09 | 48405 | Updated docs and hover text for schema, API key, and database name. |
| 0.1.14 | 2024-10-30 | 48006 | Fix bug in _flush_buffer, explicitly register DataFrame before inserting |
| 0.1.13 | 2024-10-30 | 47969 | Preserve Platform-generated id in state messages. |
| 0.1.12 | 2024-10-30 | 47987 | Disable PyPi publish. |
| 0.1.11 | 2024-10-30 | 47979 | Rename package. |
| 0.1.10 | 2024-10-29 | 47958 | Add state counts and other fixes. |
| 0.1.9 | 2024-10-29 | 47950 | Fix bug: add double quotes to column names that are reserved keywords. |
| 0.1.8 | 2024-10-29 | 47952 | Fix: Add max batch size for loads. |
| 0.1.7 | 2024-10-29 | 47706 | Fix bug: incorrect column names were used to create new stream table when using multiple streams. |
| 0.1.6 | 2024-10-29 | 47821 | Update dependencies |
| 0.1.5 | 2024-10-28 | 47694 | Resolve write failures, move processor classes into the connector. |
| 0.1.4 | 2024-10-28 | 47688 | Use new destination table name format, explicitly insert PyArrow table columns by name and add debug info for column mismatches. |
| 0.1.3 | 2024-10-23 | 47315 | Fix bug causing MotherDuck API key to not be correctly passed to the engine. |
| 0.1.2 | 2024-10-23 | 47315 | Use saas_only mode during connection check to reduce ram usage. |
| 0.1.1 | 2024-10-23 | 47312 | Fix: generate new unique destination ID |
| 0.1.0 | 2024-10-23 | 46904 | New MotherDuck destination |