DuckDB Adapter Integration

Relevant source files

The following files were used as context for generating this wiki page:

• dbt_ext/dbt_files/profiles/duckdb/profiles.yml
• dbt_ext/extension.py
• dbt_ext/main.py
• README.md
• dbt_ext/dbt_files/bundle/transform/dbt_project.yml
• pyproject.toml

The DuckDB Adapter Integration within the Meltano dbt extension provides a streamlined way to manage dbt transformations using DuckDB as the analytical engine. This integration is part of a broader utility extension designed to wrap the dbt CLI, allowing Meltano to manage dbt project initialization, configuration via environment variables, and execution.

The integration focuses on automating the deployment of profiles.yml and project files, ensuring that the DuckDB-specific settings are correctly mapped to the Meltano environment.

Sources: README.md:1-5, dbt_ext/extension.py:27-31

Architecture and Initialization

The DuckDB integration is activated by setting the DBT_EXT_TYPE environment variable to duckdb. This triggers the extension to locate and deploy the specific DuckDB profile templates during the initialization phase.

Initialization Flow

When the initialize command is executed, the extension performs the following steps:

1. Project Directory Creation: Ensures the transform directory exists.
2. Bundle Deployment: Copies base dbt project files (like dbt_project.yml) from the extension's internal bundle to the project directory.
3. Profile Deployment: Identifies the duckdb subdirectory within the extension's profiles and copies its contents to the target profiles directory.

This flowchart illustrates the initialization logic managed by the dbt class:

Configuration and Profile Mapping

The integration utilizes a templated profiles.yml file that dynamically configures DuckDB based on environment variables provided by Meltano. This allows for environment-aware configurations (dev, staging, prod).

Profile Structure

The DuckDB profile defines three targets (dev, staging, prod), all of which share the same underlying type: duckdb configuration.

Parameter	Description	Environment Variable	Default Value
type	The dbt adapter type	N/A	duckdb
path	Path to the DuckDB database file	DBT_DUCKDB_PATH	N/A
threads	Number of concurrent threads	N/A	4
target	Default profile target	MELTANO_ENVIRONMENT	dev

Sources: dbt_ext/dbt_files/profiles/duckdb/profiles.yml:1-16, dbt_ext/extension.py:44-55

Code Example: DuckDB Profile Template

The following snippet demonstrates how the extension uses Jinja templating within the profile to inject Meltano environment details:

meltano:
  target: "{{ env_var('MELTANO_ENVIRONMENT', 'dev') }}"
  outputs:
    dev:
      type: duckdb
      path: "{{ env_var('DBT_DUCKDB_PATH') }}"
      threads: 4

Sources: dbt_ext/dbt_files/profiles/duckdb/profiles.yml:1-8

Execution Lifecycle

The extension manages the lifecycle of dbt commands through pre-invoke and invoke hooks. For DuckDB, this ensures that the local database environment is prepared before transformations run.

Pre-Invoke Logic

By default, the extension executes dbt clean and dbt deps before any main command (except for clean or deps themselves). This behavior can be disabled using the DBT_EXT_SKIP_PRE_INVOKE setting.

Component Overview

Component	Responsibility	Relevant Files
dbt Class	Core logic for path management and CLI wrapping.	dbt_ext/extension.py
dbt_invoker	Passthrough entry point for running dbt directly.	dbt_ext/pass_through.py
profiles.yml	DuckDB-specific adapter configuration.	dbt_ext/dbt_files/profiles/duckdb/profiles.yml
dbt_project.yml	Base project configuration for all adapters.	dbt_ext/dbt_files/bundle/transform/dbt_project.yml

Sources: pyproject.toml:23-25, dbt_ext/extension.py:34-58, dbt_ext/dbt_files/bundle/transform/dbt_project.yml:1-29

Conclusion

The DuckDB Adapter Integration provides a robust mechanism for using DuckDB within a Meltano-managed dbt project. By automating the deployment of configurations and handling the pre-requisite dbt commands, it allows developers to focus on SQL transformations while the extension manages the adapter-specific environment setup and path resolution.