Advanced Analytica Advanced Analytica: IBOM
← All connectors
MotherDuck logo

Destination connector

MotherDuck

Send governed data into MotherDuck with a clear, practical setup guide.

Version 0.2.6 certified alpha

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

  1. In MotherDuck, open your organization settings and select Create token.

  2. Select Read/Write, configure an expiration if needed, and copy the token.

  3. In Airbyte, create a MotherDuck destination.

  4. Enter these values:

    • MotherDuck Access Token: Enter the Read/Write token you created.
    • Destination DB: Enter md:<database-name>, for example md:analytics. To use MotherDuck’s default my_db database, enter md:.

    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 as raw-data, fails during a sync with Invalid SQL name.
  5. 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 modeSupported?
Full Refresh - OverwriteYes
Full Refresh - AppendYes
Full Refresh - Overwrite + DedupedYes
Incremental Sync - AppendYes
Incremental Sync - Append + DedupedYes

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 userId and userid, 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
VersionDatePull RequestSubject
0.2.62026-08-0483694Fix JSON column serialization being skipped for non-lowercase source property names, causing empty STRUCT failures
0.2.52026-07-2282244Fix sync failures on array fields containing empty objects by serializing JSON array columns before load
0.2.42026-07-1481511Fix silent data loss on multi-stream syncs by no longer discarding other streams’ buffered records when one stream is flushed
0.2.32026-03-3175645Bump version to force registry update for supportLevel change to certified
0.2.22026-03-1770438Fix for camelCase columns being NULL
0.2.12026-01-2970999Fix for empty STRUCTs
0.2.02026-01-0771063Upgrade DuckDB to v1.4.2 and duckdb-engine to v0.17.0
0.1.262025-10-2168338Update dependencies
0.1.252025-10-1467952Update dependencies
0.1.242025-10-0766822Update dependencies
0.1.232025-08-0864161feat: allow null values in primary key fields. Primary keys are no longer declared as table constraints.
0.1.222025-07-2263714fix(destination-motherduck): handle special characters in stream name when creating tables
0.1.212025-07-2263709fix: resolve error “Can’t find the home directory at ‘/nonexistent’” #63710
0.1.202025-07-0762133fix: when primary_key is not defined in the catalog, use source_defined_primary_key if available
n/a2025-06-2648673Update dependencies
0.1.192025-05-2860906Allow Unicode characters in database/table names
0.1.182025-03-0154737Update airbyte-cdk to ^6.0.0 in destination-motherduck
0.1.172024-12-2650425Fix bug overwrite write method not saving all batches
0.1.162024-12-0648562Improved handling of config parameters during SQL engine creation.
0.1.152024-11-0948405Updated docs and hover text for schema, API key, and database name.
0.1.142024-10-3048006Fix bug in _flush_buffer, explicitly register DataFrame before inserting
0.1.132024-10-3047969Preserve Platform-generated id in state messages.
0.1.122024-10-3047987Disable PyPi publish.
0.1.112024-10-3047979Rename package.
0.1.102024-10-2947958Add state counts and other fixes.
0.1.92024-10-2947950Fix bug: add double quotes to column names that are reserved keywords.
0.1.82024-10-2947952Fix: Add max batch size for loads.
0.1.72024-10-2947706Fix bug: incorrect column names were used to create new stream table when using multiple streams.
0.1.62024-10-2947821Update dependencies
0.1.52024-10-2847694Resolve write failures, move processor classes into the connector.
0.1.42024-10-2847688Use new destination table name format, explicitly insert PyArrow table columns by name and add debug info for column mismatches.
0.1.32024-10-2347315Fix bug causing MotherDuck API key to not be correctly passed to the engine.
0.1.22024-10-2347315Use saas_only mode during connection check to reduce ram usage.
0.1.12024-10-2347312Fix: generate new unique destination ID
0.1.02024-10-2346904New MotherDuck destination
Advanced Analytica

Most businesses are sitting on information they can’t use at speed. The people who hold it are busy, and when they leave, the knowledge goes with them. The documents are static, and the processes around them weren’t designed for AI.

Advanced Analytica works with these businesses to turn that information into structured data assets. Once they're integrated, AI systems can govern it, and the business can use it. We bring in the strategy, the specification and the AI skills to rework how the business operates.

MOVE FAST. STAY SAFE.