SET UP DUCKDB & MOTHERDUCK

A COMPLETE WORKING PROFILE

The common case is a local database file. Drop this in your profiles.yml — no credentials, no server to start; the file is created the first time something writes to it:

my_project:
  target: duck_dev
  outputs:
    duck_dev:
      type: duckdb
      path: ./analytics.duckdb
      schema: main
      threads: 4

A relative path is resolved to an absolute one (and ~ is expanded), so every DVT lane opens the same file no matter where it was invoked from. Then finish the setup:

dvt sync           # installs the DuckDB adapter + engine, ensures extensions
dvt debug --all    # opens the file in both lanes and says exactly what failed if anything

dvt sync also ensures the DuckDB extensions DVT relies on — httpfs, json, and postgres_scanner — so bucket reads and file sources work out of the box.

IN-MEMORY — THE SCRATCHPAD

Leave path out entirely (or write it explicitly) and you get an in-memory database — genuinely useful for trying DVT out or running throwaway experiments, as long as you remember that nothing survives the process:

    duck_scratch:
      type: duckdb
      path: ":memory:"     # the default when path is omitted
      schema: main

MOTHERDUCK — SAME ADAPTER, MD: PATH

MotherDuck is DuckDB hosted in the cloud, and DVT serves it with the same adapter: point path at md: followed by your MotherDuck database name, and add your service token. Both the dbt lane and the data-movement lane speak MotherDuck natively — federation reads and writes work just as they do against a local file.

    md_prod:
      type: duckdb
      path: md:analytics
      motherduck_token: "{{ env_var('MOTHERDUCK_TOKEN') }}"
      schema: main

If motherduck_token isn't in the profile, DVT falls back to the MOTHERDUCK_TOKENenvironment variable — handy in CI, where the token lives in the runner's secrets anyway. A motherduck: prefix works the same as md:.

ENGINE BEHAVIOR WORTH KNOWING

The default schema is main, not public. That's DuckDB's own convention, and DVT carries it through every lane — models and federation results land in mainunless your profile says otherwise. If you're arriving from Postgres, this is the one habit to update.

One writer at a time.A DuckDB file is single-writer. DVT knows this and takes a per-file lock around its own operations, so parallel federation steps touching the same file queue up instead of colliding — but DVT can't lock out other programs. Close the DuckDB shell or notebook holding analytics.duckdb before a run, or the run will find the file locked. :memory: databases and MotherDuck have no file to lock and skip all of this.

Names are matched case-insensitively. DuckDB preserves the case you create identifiers with but resolves them insensitively, so quoting battles are rare.

The external materialization.dbt-duckdb's signature extra: models written straight out to Parquet or CSV instead of into the database file.

INCREMENTAL MODELS — THE STRATEGY LIST DEPENDS ON YOUR DUCKDB VERSION

DuckDB is the one engine here whose accepted strategies aren't a fixed list. Two are always available — append and delete+insert — and two more, merge and microbatch, appear only when the DuckDB you are connected to is 1.4.0-dev0 or newer. The adapter finds out by asking the live connection (select version()) rather than by trusting a config; if that query comes back empty you get Unable to determine DuckDB version: version() query returned no results instead of a wrong answer.

With no incremental_strategy set, DuckDB runs delete+insert — unconditionally, whether or not a unique_key is present. That differs from Postgres, where the presence of the key picks the default. It matters because delete+insert with no key silently skips the delete half and behaves as an append.

{{ config(
    materialized='incremental',
    incremental_strategy='delete+insert',   -- append | delete+insert
    unique_key='order_id'                   -- merge | microbatch need DuckDB >= 1.4.0-dev0
) }}                                        -- no strategy set? delete+insert, always

select order_id, status, updated_at
from {{ source('app_db', 'orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

Ask for merge on an older DuckDB and the adapter says so plainly, with the version it found and the way out: The 'merge' incremental strategy requires DuckDB >= 1.4.0-dev0. Current version: … Please upgrade DuckDB or use 'append' or 'delete+insert'. microbatch on an older DuckDB fails differently — only merge gets that tailored message, so microbatch falls through to the generic The incremental strategy 'microbatch' is not valid for this adapter. insert_overwrite is never valid at any version and produces that same generic refusal.

microbatchcarries three rules of its own, and one of them is the reverse of Postgres's: it requires an event_time model config, it needs a batch context (a start and an end), and it forbids unique_key — the strategy is implemented as delete+insert over the event-time window, so a key would be ignored and misleading. Supply one and the model refuses to compile.

merge on DuckDB has a wider config surface than any other engine in DVT: merge_on_using_columns, merge_update_condition, merge_insert_condition, merge_update_columns, merge_exclude_columns, merge_update_set_expressions, merge_returning_columns, and the full merge_clauses form with update / delete / do_nothing / error actions. Defaults are update-by-name when matched and insert-by-name when not. Two traps: merge_on_using_columns replaces the unique_key join outright, and with neither of them set the join predicate is literally FALSE— an insert-only run wearing a merge's name.

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='order_id',
    merge_exclude_columns=['created_at'],   -- basic form: cannot be combined
    incremental_predicates=["updated_at > current_date - 7"]
) }}

merge_clauses is mutually exclusive with those basic merge configs (merge_on_using_columns and merge_returning_columns excepted): mix them and DVT refuses with When merge_clauses is specified, the following basic merge configurations will be ignored and should be removed… rather than quietly dropping half your config. incremental_predicatesare type-checked too — a mapping or anything that isn't a string or a list of strings raises incremental_predicates must be a list of strings or a string — and predicates is accepted as an alias for the same config.

Two hosted-shape conditions to know. On DuckLake catalogs, a merge whose matched branch holds more than one update or delete action is refused — DuckLake currently supports only one UPDATE or DELETE operation per MERGE statement. On MotherDuck, incremental runs stage into a real table in a temp schema (dbt_temp) instead of a temporary relation, concurrent microbatch batches get the batch timestamp appended to the staging identifier so they can't collide, and index drops are skipped. Incremental models here may also be written in Python: on an incremental run the Python model builds the staging relation and the strategy SQL then runs against it.

When DuckDB is the target of a federated f_incremental model, none of the version arithmetic applies — the strategy no longer compiles into DuckDB SQL, it selects a load mode for the bulk loader that lands the rows. The vocabulary is append, merge, and delete+insert; insert_overwrite and microbatch are refused as having no equivalent there. merge and delete+insert become the same operation, a merge on unique_key, so a merge targeting an old DuckDB works federated where it would have been version-refused natively. A missing unique_key warns and falls back to a full refresh; so does append without a watermark_column.

DuckDB is also among the engines whose loader has been verified to honour an update-key-only load, so a keyless f_incremental driven by a watermark_column runs here rather than being refused with DVT026— the refusal that still stands on StarRocks, Snowflake, Databricks, BigQuery, Fabric and Athena. And DVT rewrites no strategy on DuckDB: the single substitution the product makes anywhere is ClickHouse's merge → delete+insert.

REFERENCE — EVERY PROFILE FIELD

Everything a type: duckdb output reads. No host, port, user, or password — the path isthe connection. Fields not listed here are ignored by DVT's connection mapping.