dbt-duckdb Adapter

1.10.1 · active · verified Fri Apr 10

dbt-duckdb is the adapter that connects dbt (data build tool) with DuckDB, an embedded analytical database. It allows users to manage and transform data using SQL or Python models, enabling a powerful local data stack or data lakehouse architecture. The library is actively maintained with frequent releases, currently at version 1.10.1.

Warnings

breaking Python 3.8 and 3.9 are no longer supported. Version 1.10.1 dropped support for both, and 1.9.2 dropped Python 3.8. Users must upgrade to Python 3.10 or newer.
Fix: Upgrade your Python environment to version 3.10 or later.
breaking Explicit dbt-core Installation Required: As of dbt-duckdb 1.8, dbt-core is no longer automatically installed as a dependency. Users must explicitly install `dbt-core` alongside `dbt-duckdb` (e.g., `pip install dbt-core dbt-duckdb`). Ensure `dbt-core` version is compatible; current dbt-duckdb versions target `dbt-core >= 1.8.x`.
Fix: Modify your installation command to `pip install dbt-core dbt-duckdb` and ensure your `dbt-core` version meets the adapter's requirements.
gotcha Default In-Memory Database (No Persistence): By default, `dbt-duckdb` runs against an in-memory DuckDB database, meaning any data generated by `dbt run` will not be persisted after the process exits. To store your data to disk, you *must* specify a `path` in your `profiles.yml` configuration (e.g., `path: /path/to/my/database.duckdb`).
Fix: Always set the `path` configuration in your `profiles.yml` to a file path on your filesystem to enable data persistence. Use `:memory:` explicitly if non-persistence is desired.
gotcha logbook Dependency Pin: Version 1.10.1 introduced a dependency pin `logbook < 1.9`. This is due to `pytest-logbook`'s reliance on a compat module removed in `logbook` 1.9.x. This might cause dependency conflicts if other libraries in your environment require a newer version of `logbook`.
Fix: If encountering conflicts, consider isolating dbt-duckdb in a dedicated virtual environment or checking if other dependencies can tolerate `logbook < 1.9`. Report issues if this impacts critical functionality.
deprecated DeltaLake Functions Removed: Legacy DeltaLake functions were removed in `dbt-duckdb` 1.8.4. Models or workflows relying on these older functions will break.
Fix: Update your dbt models and configurations to use the current, supported methods for interacting with DeltaLake if applicable. Refer to the dbt-duckdb documentation for updated patterns.
gotcha MotherDuck Limitations: When connecting to MotherDuck (cloud-hosted DuckDB), certain features are limited compared to a local DuckDB instance. This includes lack of support for custom extensions/user-defined functions and potential differences in behavior for some advanced SQL features like `dbt.listagg` macros and foreign-key constraints.
Fix: Be aware of these limitations when designing solutions for MotherDuck. Test thoroughly if using advanced SQL features or custom extensions.

Install

pip install dbt-core dbt-duckdb Recommended installation
pip install dbt-duckdb[glue] For AWS Glue Catalog integration (optional)
pip install dbt-duckdb[md] For MotherDuck integration (optional)

Quickstart

This quickstart guides you through initializing a dbt project, configuring the `dbt-duckdb` adapter in your `profiles.yml` for local storage, and running a simple dbt model. The `path` field is crucial for data persistence.

mkdir my_dbt_project
cd my_dbt_project
dbt init my_dbt_project_name
# Edit ~/.dbt/profiles.yml (or profiles.yml in project root)
# Add the following profile:
#
#   my_dbt_project_name:
#     target: dev
#     outputs:
#       dev:
#         type: duckdb
#         path: /tmp/dbt.duckdb  # Or './dbt_warehouse.duckdb' for project-local persistence
#         threads: 4
#
dbt debug --profile my_dbt_project_name
# Expected output: Connection test successful.

# Create a simple SQL model (e.g., models/my_model.sql)
# -- my_model.sql --
# SELECT 1 as id, 'hello' as message

# Run your dbt models
dbt run

view raw JSON →