dbt-duckdb

You can reference external files (CSV, JSON, Parquet) directly in dbt models or as sources using the external_location property.

Placement and Behavior:

• Use meta for external_location if you want the setting to appear in dbt docs generate.
• Use config for external_location if you want to exclude it from generated documentation.
• external_location can be a path string (supporting f-string patterns like {name}) or a DuckDB function call (e.g., read_parquet(...)).

String Formatting Strategies: If your external_location contains characters that conflict with Python string formatting (like curly braces in a function call), use the formatter option:

• newstyle: Default.
• oldstyle: Uses % formatting.
• template: Uses str.format style.

Example: Using a function call for a source table:

sources:
  - name: flights_source
    tables:
      - name: flights
        config:
          external_location: "read_csv('flights.csv', types={'FlightDate': 'DATE'}, names=['FlightDate', 'UniqueCarrier'])"
          formatter: oldstyle

sources:
  - name: external_source
    meta:
      external_location: "s3://my-bucket/my-sources/{name}.parquet"
    tables:
      - name: source1
      - name: source2
        config:
          external_location: "read_parquet(['s3://my-bucket/my-sources/source2a.parquet', 's3://my-bucket/my-sources/source2b.parquet'])"

Use the external materialization strategy to create dbt models backed by external files (Parquet, CSV, or JSON).

Configuration Options:

• location: The path/filename to write to. If omitted, the file is named after the model. If specified, the format is inferred from the extension (v1.4.1+).
• format: The file format (parquet, csv, or json). Default is parquet.
• delimiter: For CSV files, the field delimiter (Default: ,).
• options: Arbitrary options passed to DuckDB's COPY operation (e.g., partition_by, codec).
• glue_register: Boolean; if true, registers the file with the AWS Glue Catalog (Default: false).
• glue_database: The AWS Glue database name.

Note: Incremental materialization is not supported for external models. Files are created relative to the current working directory unless external_root is set in the DuckDB profile.

{{ config(materialized='external', location='local/directory/file.parquet') }}
SELECT m.*, s.id IS NOT NULL as has_source_id
FROM {{ ref('upstream_model') }} m
LEFT JOIN {{ source('upstream', 'source') }} s USING (id)

dbt-duckdb features a plugin system to extend functionality, such as defining custom Python UDFs or loading data from Excel, Google Sheets, or SQLAlchemy.

To use a plugin, add it to the plugins property in your dbt profile. Each plugin requires a module property. Built-in plugins (like excel or gsheet) can be referenced by their base filename, while custom plugins must use their full Python module path.

Plugin Configuration Keys:

• module: The location of the Plugin class.
• alias: (Optional) An override for the plugin's name used in logging.
• config: A dictionary of arbitrary key-value pairs passed to the plugin's initialization.

Built-in Plugin Dependencies:

• excel: requires pandas, and openpyxl or xlsxwriter.
• gsheet: requires gspread and pandas.
• iceberg: requires pyiceberg and Python >= 3.10.
• sqlalchemy: requires pandas, sqlalchemy, and the appropriate driver.
• delta (Experimental): requires deltalake.

default:
  outputs:
    dev:
      type: duckdb
      path: /tmp/dbt.duckdb
      plugins:
        - module: gsheet
          config:
            method: oauth
        - module: sqlalchemy
          alias: sql
          config:
            connection_url: "{{ env_var('DBT_ENV_SECRET_SQLALCHEMY_URI') }}"
        - module: path.to.custom_udf_module