DuckDB setup

Community plugin

Some core functionality may be limited. If you're interested in contributing, check out the source code for each repository listed below.

Overview of dbt-duckdb

• Maintained by: Community
• Authors: Josh Wills (https://github.com/jwills)
• GitHub repo: duckdb/dbt-duckdb
• PyPI package: dbt-duckdb
• Slack channel: #db-duckdb
• Supported dbt Core version: v1.0.1 and newer
• dbt Cloud support: Not Supported
• Minimum data platform version: DuckDB 0.3.2

Installing dbt-duckdb

pip is the easiest way to install the adapter:

python -m pip install dbt-duckdb

Installing dbt-duckdb will also install dbt-core and any other dependencies.

Configuring dbt-duckdb

For Duck DB-specifc configuration please refer to Duck DB Configuration

For further info, refer to the GitHub repository: duckdb/dbt-duckdb

Connecting to DuckDB with dbt-duckdb

DuckDB is an embedded database, similar to SQLite, but designed for OLAP-style analytics instead of OLTP. The only configuration parameter that is required in your profile (in addition to type: duckdb) is the path field, which should refer to a path on your local filesystem where you would like the DuckDB database file (and it's associated write-ahead log) to be written. You can also specify the schema parameter if you would like to use a schema besides the default (which is called main).

There is also a database field defined in the DuckDBCredentials class for consistency with the parent Credentials class, but it defaults to main and setting it to be something else will likely cause strange things to happen that cannot be fully predicted, so please avoid changing it.

As of version 1.2.3, you can load any supported DuckDB extensions by listing them in the extensions field in your profile. You can also set any additional DuckDB configuration options via the settings field, including options that are supported in any loaded extensions.

For example, to be able to connect to s3 and read/write parquet files using an AWS access key and secret, your profile would look something like this:

profiles.yml

your_profile_name:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: 'file_path/database_name.duckdb'
      extensions:
        - httpfs
        - parquet
      settings:
        s3_region: my-aws-region
        s3_access_key_id: "{{ env_var('S3_ACCESS_KEY_ID') }}"
        s3_secret_access_key: "{{ env_var('S3_SECRET_ACCESS_KEY') }}"