### Install Autocompletion Support

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Install the optional iterfzf package to enable model name autocompletion in the shell.

```bash
pip install iterfzf
```

--------------------------------

### Launch Interactive Shell

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Commands to start the dbt-duckdb interactive CLI.

```bash
# Start interactive shell with default profile
python -m dbt.adapters.duckdb.cli

# Start with specific profile
python -m dbt.adapters.duckdb.cli --profile my_profile
```

--------------------------------

### Start the Interactive Shell

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Commands to launch the dbt-duckdb interactive CLI, optionally specifying a profile.

```bash
python -m dbt.adapters.duckdb.cli
```

```bash
python -m dbt.adapters.duckdb.cli --profile my_profile
```

--------------------------------

### Install dbt-duckdb

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Install dbt-duckdb and its dependencies using pip. Ensure compatibility with dbt-core and duckdb versions.

```bash
pip3 install dbt-duckdb
```

--------------------------------

### Configure Microbatch Incremental Strategy

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Microbatching processes data in time-based windows. It requires an event_time column, a start date, and a batch size; unique_key is not supported.

```yaml
models:
  - name: my_microbatch_model
    config:
      materialized: incremental
      incremental_strategy: microbatch
      event_time: event_time
      begin: '2025-01-01'
      batch_size: day
      incremental_predicates: ["country = 'US'"]
```

--------------------------------

### Basic Incremental Merge Strategy (DuckDB 1.4.0+)

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Utilize the 'merge' incremental strategy for upserting records based on a unique key, leveraging native DuckDB MERGE statement support. This basic example focuses on the unique key.

```sql
{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='id'
) }}

SELECT id, name, age, status, updated_at
FROM {{ source('raw', 'users') }}
{% if is_incremental() %}
WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}
```

--------------------------------

### Register Upstream External Models

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Ensures external models are registered at the start of a dbt run.

```yaml
# dbt_project.yml - Register external models on run start
on-run-start:
  - "{{ register_upstream_external_models() }}"
```

--------------------------------

### Configure DuckLake Table Partitioning by Day, Month, Year, and Hour

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Demonstrates partitioning a DuckLake table using multiple date/time truncations for finer-grained data organization.

```sql
{{ config(materialized='table', partitioned_by=['event_day', 'event_month', 'event_year', 'event_hour']) }}

select
  *,
  date_trunc('day', event_time) as event_day,
  date_trunc('month', event_time) as event_month,
  date_trunc('year', event_time) as event_year,
  date_trunc('hour', event_time) as event_hour
from {{ ref('upstream_model') }}
```

--------------------------------

### Configure DuckLake Table Partitioning

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Set up physical partitioning for tables backed by DuckLake.

```sql
-- models/partitioned_table.sql
{{ config(
    materialized='table',
    partitioned_by=['year', 'month']
) }}

SELECT
    *,
    year(event_time) as year,
    month(event_time) as month
FROM {{ ref('upstream_model') }}
```

--------------------------------

### Configure Basic dbt-duckdb Profiles

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Basic configurations for in-memory and file-based DuckDB instances.

```yaml
# profiles.yml - Basic in-memory configuration
default:
  outputs:
    dev:
      type: duckdb
      # path: ':memory:' is default for in-memory
  target: dev
```

```yaml
# profiles.yml - File-based database with extensions and settings
default:
  outputs:
    dev:
      type: duckdb
      path: /tmp/dbt.duckdb
      threads: 4
      extensions:
        - httpfs
        - parquet
        - name: h3
          repo: community
      settings:
        memory_limit: '4GB'
        threads: 4
  target: dev
```

--------------------------------

### Configure fsspec Filesystems

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Set up fsspec-compatible filesystems for cloud storage access.

```yaml
# profiles.yml - fsspec S3 filesystem with Localstack
default:
  outputs:
    dev:
      type: duckdb
      path: /tmp/dbt.duckdb
      filesystems:
        - fs: s3
          anon: false
          key: "{{ env_var('S3_ACCESS_KEY_ID') }}"
          secret: "{{ env_var('S3_SECRET_ACCESS_KEY') }}"
          client_kwargs:
            endpoint_url: "http://localhost:4566"
  target: dev
```

--------------------------------

### Configure SQLAlchemy Plugin in profiles.yml

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Sets up a connection to an external database using the SQLAlchemy plugin.

```yaml
# profiles.yml - SQLAlchemy plugin configuration
default:
  outputs:
    dev:
      type: duckdb
      plugins:
        - module: sqlalchemy
          alias: postgres_source
          config:
            connection_url: "postgresql://user:password@localhost:5432/mydb"
```

--------------------------------

### Configure fsspec filesystems

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Enable fsspec-compatible filesystems by defining the protocol and configuration parameters.

```yaml
default:
  outputs:
    dev:
      type: duckdb
      path: /tmp/dbt.duckdb
      filesystems:
        - fs: s3
          anon: false
          key: "{{ env_var('S3_ACCESS_KEY_ID') }}"
          secret: "{{ env_var('S3_SECRET_ACCESS_KEY') }}"
          client_kwargs:
            endpoint_url: "http://localhost:4566"
  target: dev
```

--------------------------------

### Implement Custom Plugin

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Extends BasePlugin to define custom loading and storage logic for external APIs.

```python
# plugins/custom_api_plugin.py
from typing import Any, Dict
import pandas as pd
import requests
from dbt.adapters.duckdb.plugins import BasePlugin
from dbt.adapters.duckdb.utils import SourceConfig, TargetConfig

class Plugin(BasePlugin):
    def initialize(self, plugin_config: Dict[str, Any]):
        """Initialize plugin with config from profiles.yml"""
        self.api_key = plugin_config.get('api_key')
        self.base_url = plugin_config.get('base_url')

    def configure_connection(self, conn):
        """Configure DuckDB connection (add UDFs, etc.)"""
        # Example: Register custom UDF
        conn.create_function('api_transform', lambda x: x.upper())

    def load(self, source_config: SourceConfig):
        """Load data from external source"""
        endpoint = source_config.get('endpoint')
        response = requests.get(
            f"{self.base_url}/{endpoint}",
            headers={'Authorization': f'Bearer {self.api_key}'}
        )
        return pd.DataFrame(response.json())

    def store(self, target_config: TargetConfig):
        """Store data to external destination"""
        # Read the data that was written
        df = pd.read_parquet(target_config.location.path)
        # Send to external API
        requests.post(
            f"{self.base_url}/upload",
            json=df.to_dict(orient='records'),
            headers={'Authorization': f'Bearer {self.api_key}'}
        )
```

--------------------------------

### Configure external materialization

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Use the external materialization strategy to define a model backed by an external file. Ensure the location is specified to control the output path.

```sql
{{ 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)
```

--------------------------------

### Implement Microbatch Incremental Strategy

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Use the microbatch strategy for time-based incremental processing with configurable batch sizes.

```sql
-- models/incremental_microbatch.sql
{{ config(
    materialized='incremental',
    incremental_strategy='microbatch',
    event_time='event_time',
    begin='2025-01-01',
    batch_size='day',
    incremental_predicates=["country = 'US'"]
) }}

SELECT
    id,
    event_type,
    event_time,
    country,
    value
FROM {{ source('raw', 'events') }}
```

--------------------------------

### Configure DuckDB extensions and settings

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Define core and community extensions along with custom settings in the dbt profile.

```yaml
default:
  outputs:
    dev:
      type: duckdb
      path: /tmp/dbt.duckdb
      extensions:
        - httpfs
        - parquet
        - name: h3
          repo: community
        - name: uc_catalog
          repo: core_nightly
  target: dev
```

--------------------------------

### Configure DuckLake Table Partitioning by Year and Month

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Use `partitioned_by` in the `config` block to partition DuckLake-backed tables by year and month. This setting is ignored on non-DuckLake targets.

```sql
{{ config(materialized='table', partitioned_by=['year', 'month']) }}

select
  *,
  year(event_time) as year,
  month(event_time) as month
from {{ ref('upstream_model') }}
```

--------------------------------

### Configure Custom Plugin in profiles.yml

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Registers the custom plugin directory and provides necessary configuration parameters.

```yaml
# profiles.yml - Custom plugin configuration
default:
  outputs:
    dev:
      type: duckdb
      module_paths:
        - "./plugins"  # Add custom plugin directory to path
      plugins:
        - module: custom_api_plugin
          config:
            api_key: "{{ env_var('API_KEY') }}"
            base_url: "https://api.example.com"
```

--------------------------------

### Configure DuckLake on MotherDuck

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Connect to a DuckLake instance on MotherDuck by setting 'is_ducklake: true' in your profile and providing the appropriate connection string.

```yaml
default:
  outputs:
    dev:
      type: duckdb
      path: md:my_ducklake_db
      is_ducklake: true
  target: dev
```

--------------------------------

### Connect to MotherDuck

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Configure your dbt-duckdb profile to connect to MotherDuck using a 'md:<database>' connection string in the 'path' field. Note MotherDuck's compatibility and extension limitations.

```yaml
default:
  outputs:
    dev:
      type: duckdb
      path: md:my_motherduck_db
  target: dev
```

--------------------------------

### Integrate Google Sheets Plugin

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Configure the gsheet plugin in profiles.yml and define sources to load data from Google Sheets.

```yaml
# profiles.yml - Configure gsheet plugin
default:
  outputs:
    dev:
      type: duckdb
      path: /tmp/dbt.duckdb
      plugins:
        - module: gsheet
          config:
            method: oauth  # or 'service' for service account
```

```yaml
# models/sources.yml - Google Sheet source
sources:
  - name: google_sheets
    tables:
      - name: sales_data
        meta:
          plugin: gsheet
          title: "My Sales Spreadsheet"  # Open by title
          # OR key: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"  # Open by key
          # OR url: "https://docs.google.com/spreadsheets/d/..."  # Open by URL
          worksheet: "Sheet1"  # Optional: specific worksheet name or index
```

```sql
-- models/sales_analysis.sql
SELECT
    product,
    SUM(quantity) as total_quantity,
    SUM(revenue) as total_revenue
FROM {{ source('google_sheets', 'sales_data') }}
GROUP BY product
```

--------------------------------

### Configure Excel Plugin in profiles.yml

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Defines the Excel plugin settings including output file path and styling options.

```yaml
# profiles.yml - Excel plugin configuration
default:
  outputs:
    dev:
      type: duckdb
      plugins:
        - module: excel
          config:
            output:
              file: output_workbook.xlsx
              mode: w
              engine: xlsxwriter
              header_styling: true
```

--------------------------------

### Attach External Databases

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Connect multiple DuckDB, SQLite, or PostgreSQL databases for cross-database queries.

```yaml
# profiles.yml - Attach multiple databases
default:
  outputs:
    dev:
      type: duckdb
      path: /tmp/dbt.duckdb
      attach:
        - path: /tmp/other.duckdb
        - path: ./analytics.duckdb
          alias: analytics
        - path: s3://my-bucket/data.duckdb
          read_only: true
        - path: sqlite.db
          type: sqlite
        - path: postgresql://user:pass@host/dbname
          type: postgres
        - path: /tmp/special.duckdb
          options:
            cache_size: 1GB
            threads: 4
  target: dev
```

```yaml
# profiles.yml - MotherDuck with DuckLake
default:
  outputs:
    dev:
      type: duckdb
      path: "md:my_database"
      attach:
        - path: "md:my_ducklake"
          is_ducklake: true
  target: dev
```

--------------------------------

### Incremental Model with Append Strategy

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Implement a simple incremental model using the 'append' strategy for inserting new records without deduplication. Includes a predicate to fetch records newer than the last run.

```sql
{{ config(
    materialized='incremental',
    incremental_strategy='append',
    incremental_predicates=["created_at > (select max(created_at) from {{ this }})"]
) }}

SELECT id, name, created_at
FROM {{ source('raw', 'events') }}
{% if is_incremental() %}
WHERE created_at > (SELECT MAX(created_at) FROM {{ this }})
{% endif %}
```

--------------------------------

### Minimal dbt-duckdb Profile Configuration

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

A basic dbt-duckdb profile configuration for an in-memory DuckDB database. This is useful for testing and pipelines operating on external files.

```yaml
default:
  outputs:
    dev:
      type: duckdb
  target: dev
```

--------------------------------

### dbt profiles.yml Configuration for DuckDB

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Configure your dbt project to use the DuckDB adapter, specifying the database path and enabling plugins like gsheet, sqlalchemy, and excel.

```yaml
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: excel
          config:
            output:
              file: output.xlsx
              mode: w
              engine: xlsxwriter
  target: dev
```

--------------------------------

### Scope credentials by storage prefix

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Define multiple secrets with specific scopes to match different storage paths.

```yaml
default:
  outputs:
    dev:
      type: duckdb
      path: /tmp/dbt.duckdb
      extensions:
        - httpfs
        - parquet
      secrets:
        - type: s3
          provider: credential_chain
          scope: [ "s3://bucket-in-eu-region", "s3://bucket-2-in-eu-region" ]
          region: "eu-central-1"
        - type: s3
          region: us-west-2
          scope: "s3://bucket-in-us-region"
```

--------------------------------

### Create DuckLake Database on MotherDuck

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

SQL command to create a DuckLake database on MotherDuck, specifying the data path for S3 storage. This is required before connecting dbt-duckdb to it.

```sql
-- to use create your own database in MotherDuck first
CREATE DATABASE my_ducklake
  (TYPE ducklake, DATA_PATH 's3://...')
```

--------------------------------

### Develop Python Models

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Write dbt models using Python to leverage DuckDB connections and DataFrame operations.

```python
# models/python_model.py - Basic Python model
import pandas as pd

def model(dbt, session):
    dbt.config(materialized='table')

    # Reference upstream models and sources as DuckDB Relations
    upstream = dbt.ref("my_sql_model")
    source_data = dbt.source('raw', 'events')

    # Convert to pandas DataFrame and process
    df = upstream.df()
    df['new_column'] = df['value'] * 2

    return df
```

```python
# models/incremental_python.py - Incremental Python model
def model(dbt, session):
    dbt.config(
        materialized="incremental",
        unique_key='id'
    )

    df = dbt.ref("upstream_model")

    if dbt.is_incremental:
        # Filter for incremental runs
        df = df.filter("id > 5")

    return df
```

```python
# models/python_pyarrow.py - Using PyArrow for batch processing
import pyarrow as pa

def batcher(batch_reader: pa.RecordBatchReader):
    for batch in batch_reader:
        df = batch.to_pandas()
        # Process batch
        df['processed'] = True
        yield pa.RecordBatch.from_pandas(df)

def model(dbt, session):
    dbt.config(materialized='table')

    big_model = dbt.ref("large_dataset")
    batch_reader = big_model.record_batch(100_000)
    batch_iter = batcher(batch_reader)

    return pa.RecordBatchReader.from_batches(batch_reader.schema, batch_iter)
```

--------------------------------

### Configure dbt-duckdb plugins in profiles.yml

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Define plugins within the dbt profile to enable features like Google Sheets integration or custom UDFs.

```yaml
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
```

--------------------------------

### Configure DuckLake Attachments

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Specify DuckLake connections using the ducklake: prefix or md: with is_ducklake set to true.

```yaml
attach:
  - path: "ducklake:my_ducklake.ddb"
  - path: "md:my_other_ducklake"
    is_ducklake: true
```

--------------------------------

### Configure Delete+Insert Incremental Strategy

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

The delete+insert strategy requires a unique_key to identify records for replacement. Composite keys can be provided as a list.

```yaml
models:
  - name: my_incremental_model
    config:
      materialized: incremental
      incremental_strategy: delete+insert
      unique_key: id  # or ['id', 'date'] for composite keys
      incremental_predicates: ["updated_at >= '2023-01-01'"]
```

--------------------------------

### Configure External Location in Source Meta

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Use `meta.external_location` to define a pattern for external files. This setting is propagated to documentation.

```yaml
sources:
  - name: external_source
    meta:
      external_location: "s3://my-bucket/my-sources/{name}.parquet"
    tables:
      - name: source1
      - name: source2
```

--------------------------------

### Configure Persistent DuckDB Database

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Configure your dbt-duckdb profile to use a persistent DuckDB file. Set the 'path' field to your desired file location. The database will be created if it doesn't exist.

```yaml
default:
  outputs:
    dev:
      type: duckdb
      path: /path/to/your/database.duckdb
  target: dev
```

--------------------------------

### Configure Database Attachments in profiles.yml

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Define multiple database attachments in your dbt profile, supporting various file paths, aliases, and database types.

```yaml
default:
  outputs:
    dev:
      type: duckdb
      path: /tmp/dbt.duckdb
      attach:
        - path: /tmp/other.duckdb
        - path: ./yet/another.duckdb
          alias: yet_another
        - path: s3://yep/even/this/works.duckdb
          read_only: true
        - path: sqlite.db
          type: sqlite
        - path: postgresql://username@hostname/dbname
          type: postgres
        # Using the options dict for arbitrary ATTACH options
        - path: /tmp/special.duckdb
          options:
            cache_size: 1GB
            threads: 4
            enable_fsst: true
```

--------------------------------

### Create Table Function Materializations

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Define parameterized views using table functions for late binding and filter pushdown.

```sql
-- models/table_function_no_params.sql
{{ config(materialized='table_function') }}

SELECT * FROM {{ ref("example_table") }}
```

```sql
-- models/table_function_with_params.sql
{{ config(
    materialized='table_function',
    parameters=['where_a', 'where_b']
) }}

SELECT *
FROM {{ ref("example_table") }}
WHERE 1=1
    AND a = where_a
    AND b = where_b
```

```sql
-- Usage in downstream models
-- Call with no parameters (parentheses required!)
SELECT * FROM {{ ref("table_function_no_params") }}()

-- Call with parameters
SELECT * FROM {{ ref("table_function_with_params") }}(1, 2)
```

--------------------------------

### Incremental Model with Delete+Insert Strategy

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Configure an incremental model using the 'delete+insert' strategy for upserting records based on a unique key. It includes a predicate to process records updated after a specific date.

```sql
{{ config(
    materialized='incremental',
    incremental_strategy='delete+insert',
    unique_key='id',
    incremental_predicates=["updated_at >= '2023-01-01'"]
) }}

SELECT
    id,
    name,
    status,
    updated_at
FROM {{ source('raw', 'customers') }}
{% if is_incremental() %}
WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}
```

--------------------------------

### Compiled SQL for External Source

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

The SQL query compiled from the dbt model, showing the resolved external file path.

```sql
SELECT *
FROM 's3://my-bucket/my-sources/source1.parquet'
```

--------------------------------

### Define SQLAlchemy Source in sources.yml

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Configures a database table as a source, optionally providing a custom query.

```yaml
# models/sources.yml - SQLAlchemy source with query
sources:
  - name: postgres
    tables:
      - name: customers
        meta:
          plugin: postgres_source
          query: "SELECT * FROM customers WHERE active = true"
          # OR table: "customers"  # Read entire table
```

--------------------------------

### Configure Append Incremental Strategy

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Use the append strategy to add new records to a table, optionally filtering with incremental_predicates.

```yaml
models:
  - name: my_incremental_model
    config:
      materialized: incremental
      incremental_strategy: append
      incremental_predicates: ["created_at > (select max(created_at) from {{ this }})"]
```

--------------------------------

### Configure Retry Behavior

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Sets retry attempts and specific exceptions for connection and query failures.

```yaml
# profiles.yml - Retry configuration
default:
  outputs:
    dev:
      type: duckdb
      path: /tmp/dbt.duckdb
      retries:
        connect_attempts: 5  # Retry connection attempts
        query_attempts: 3    # Retry failed queries
        retryable_exceptions:
          - IOException
          - CatalogException
```

--------------------------------

### Write Partitioned Parquet Data Externally

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Utilize the `external` materialization to write partitioned Parquet data to a specified location, with options for partitioning columns and compression codec.

```sql
{{ config(
    materialized='external',
    location='s3://my-bucket/data/',
    options={'partition_by': 'year, month', 'codec': 'zstd'}
) }}

SELECT
    id,
    value,
    YEAR(created_at) as year,
    MONTH(created_at) as month
FROM {{ ref('events') }}
```

--------------------------------

### Use Arbitrary ATTACH Options

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Pass custom key-value pairs to the ATTACH statement using the options dictionary, ensuring no conflicts with direct fields.

```yaml
attach:
  # Standard way using direct fields
  - path: /tmp/db1.duckdb
    type: sqlite
    read_only: true

  # New way using options dict (equivalent to above)
  - path: /tmp/db2.duckdb
    options:
      type: sqlite
      read_only: true

  # Mix of both (no conflicts allowed)
  - path: /tmp/db3.duckdb
    type: sqlite
    options:
      block_size: 16384

  # Using options dict for future DuckDB attachment options
  - path: /tmp/db4.duckdb
    options:
      type: duckdb
      # Example: hypothetical future options DuckDB might add
      compression: lz4
      memory_limit: 2GB
```

--------------------------------

### Create Table Function Materialization

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Define a table function materialization with optional parameters for late-binding views.

```sql
{{
    config(
        materialized='table_function'
    )
}}
select * from {{ ref("example_table") }}
```

```sql
{{
    config(
        materialized='table_function',
        parameters=['where_a', 'where_b']
    )
}}
select *
from {{ ref("example_table") }}
where 1=1
    and a = where_a
    and b = where_b
```

--------------------------------

### Write dbt Models to External Parquet Files

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Use the `external` materialization to write dbt model output to external Parquet files. This is the default format.

```sql
{{ config(materialized='external', location='output/data.parquet') }}

SELECT id, name, created_at
FROM {{ ref('upstream_model') }}
```

--------------------------------

### Process Large Datasets with Python Models

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Use PyArrow RecordBatchReader to process data in chunks, enabling manipulation of datasets that exceed available memory.

```python
import pyarrow as pa

def batcher(batch_reader: pa.RecordBatchReader):
    for batch in batch_reader:
        df = batch.to_pandas()
        # Do some operations on the DF...
        # ...then yield back a new batch
        yield pa.RecordBatch.from_pandas(df)

def model(dbt, session):
    big_model = dbt.ref("big_model")
    batch_reader = big_model.record_batch(100_000)
    batch_iter = batcher(batch_reader)
    return pa.RecordBatchReader.from_batches(batch_reader.schema, batch_iter)
```

--------------------------------

### Write Data to Excel in SQL

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Uses the external materialization strategy to export model data to an Excel file.

```sql
-- models/excel_output.sql - Write to Excel
{{ config(
    materialized='external',
    plugin='excel',
    format='parquet'
) }}

SELECT * FROM {{ ref('summary_data') }}
```

--------------------------------

### Sync Data to External Database in SQL

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Exports dbt model results to an external database via a configured SQLAlchemy plugin.

```sql
-- models/external_sync.sql - Sync data to external database
{{ config(
    materialized='external',
    plugin='postgres_source'
) }}

SELECT id, name, email, updated_at
FROM {{ ref('customers_transformed') }}
```

--------------------------------

### Register Upstream External Models

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Enable automatic registration of external models in dbt_project.yml to support in-memory DuckDB runs.

```yaml
on-run-start:
  - "{{ register_upstream_external_models() }}"
```

--------------------------------

### Configure CSV External Source with Type Casting and Formatter

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Configure a CSV external source using `read_csv` with specific types and names, and set `formatter: oldstyle` to prevent issues with `str.format`.

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

--------------------------------

### Define Excel Source in sources.yml

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Specifies an Excel file as a data source for dbt models.

```yaml
# models/sources.yml - Excel source
sources:
  - name: excel_data
    tables:
      - name: financial_report
        meta:
          plugin: excel
          external_location: "data/financial_data.xlsx"
          sheet_name: "Q4 Report"  # Optional: specific sheet
```

--------------------------------

### Configure Cloud Storage Secrets

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Manage credentials for S3, GCS, or Azure using DuckDB's Secrets Manager.

```yaml
# profiles.yml - S3 with explicit credentials
default:
  outputs:
    dev:
      type: duckdb
      path: /tmp/dbt.duckdb
      extensions:
        - httpfs
        - parquet
      secrets:
        - type: s3
          region: us-east-1
          key_id: "{{ env_var('S3_ACCESS_KEY_ID') }}"
          secret: "{{ env_var('S3_SECRET_ACCESS_KEY') }}"
  target: dev
```

```yaml
# profiles.yml - S3 with credential chain provider (IAM roles, web identity tokens)
default:
  outputs:
    dev:
      type: duckdb
      path: /tmp/dbt.duckdb
      extensions:
        - httpfs
        - parquet
      secrets:
        - type: s3
          provider: credential_chain
  target: dev
```

```yaml
# profiles.yml - Scoped secrets for multiple buckets with different credentials
default:
  outputs:
    dev:
      type: duckdb
      path: /tmp/dbt.duckdb
      secrets:
        - type: s3
          provider: credential_chain
          scope: ["s3://bucket-in-eu-region", "s3://bucket-2-in-eu-region"]
          region: "eu-central-1"
        - type: s3
          region: us-west-2
          key_id: "{{ env_var('US_KEY') }}"
          secret: "{{ env_var('US_SECRET') }}"
          scope: "s3://bucket-in-us-region"
  target: dev
```

--------------------------------

### dbt Model Referencing External Source

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

A dbt model that references a source configured with an `external_location` pattern.

```sql
SELECT *
FROM {{ source('external_source', 'source1') }}
```

--------------------------------

### Write dbt Models to External CSV Files with Custom Delimiter

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Configure the `external` materialization to write model output to CSV files, specifying a custom delimiter.

```sql
{{ config(
    materialized='external',
    location='output/data.csv',
    format='csv',
    delimiter='|'
) }}

SELECT id, name, value
FROM {{ ref('source_model') }}
```

--------------------------------

### Configure Custom Merge Clauses

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Define complex merge logic for incremental models using explicit update and insert actions.

```yaml
models:
  - name: my_incremental_model
    config:
      materialized: incremental
      incremental_strategy: merge
      unique_key: id
      merge_clauses:
        when_matched:
          - action: update
            mode: explicit
            condition: "DBT_INTERNAL_SOURCE.status = 'active'"
            update:
              include: ['name', 'email', 'status']
              exclude: ['created_at']
              set_expressions:
                updated_at: "CURRENT_TIMESTAMP"
                version: "COALESCE(DBT_INTERNAL_DEST.version, 0) + 1"
          - action: delete
            condition: "DBT_INTERNAL_SOURCE.status = 'deleted'"
        when_not_matched:
          - action: insert
            mode: explicit
            insert:
              columns: ['id', 'name', 'email', 'created_at']
              values: ['DBT_INTERNAL_SOURCE.id', 'DBT_INTERNAL_SOURCE.name', 'DBT_INTERNAL_SOURCE.email', 'CURRENT_TIMESTAMP']
```

--------------------------------

### Basic Merge Strategy Configuration in dbt-duckdb

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Configure the default merge strategy for incremental models by specifying `incremental_strategy: merge` and `unique_key`. This uses `UPDATE BY NAME` and `INSERT BY NAME`.

```yaml
models:
  - name: my_incremental_model
    config:
      materialized: incremental
      incremental_strategy: merge
      unique_key: id  # or ['id', 'date'] for composite keys
```

```sql
MERGE INTO target AS DBT_INTERNAL_DEST
USING source AS DBT_INTERNAL_SOURCE
ON (DBT_INTERNAL_SOURCE.id = DBT_INTERNAL_DEST.id)
WHEN MATCHED THEN UPDATE BY NAME
WHEN NOT MATCHED THEN INSERT BY NAME
```

--------------------------------

### Configure Custom Merge Clauses in dbt_project.yml

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Define explicit update and delete logic for incremental models using merge clauses.

```yaml
models:
  my_project:
    incremental_model:
      +materialized: incremental
      +incremental_strategy: merge
      +unique_key: id
      +merge_clauses:
        when_matched:
          - action: update
            mode: explicit
            condition: "DBT_INTERNAL_SOURCE.status = 'active'"
            update:
              include: ['name', 'email', 'status']
              set_expressions:
                updated_at: "CURRENT_TIMESTAMP"
          - action: delete
            condition: "DBT_INTERNAL_SOURCE.status = 'deleted'"
        when_not_matched:
          - action: insert
            mode: by_name
```

--------------------------------

### Enhanced Merge Strategy Configuration with Custom Conditions

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Utilize enhanced merge options to control update and insert conditions, specify columns for update, exclude columns, and define custom set expressions for incremental models.

```yaml
models:
  - name: my_incremental_model
    config:
      materialized: incremental
      incremental_strategy: merge
      unique_key: id
      merge_update_condition: "DBT_INTERNAL_DEST.age < DBT_INTERNAL_SOURCE.age"
      merge_insert_condition: "DBT_INTERNAL_SOURCE.status != 'inactive'"
      merge_update_columns: ['name', 'age', 'status']
      merge_exclude_columns: ['created_at']
      merge_update_set_expressions:
        updated_at: "CURRENT_TIMESTAMP"
        version: "COALESCE(DBT_INTERNAL_DEST.version, 0) + 1"
```

--------------------------------

### Configure External Sources in dbt

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Define external data sources in your dbt project using `external_location` to reference CSV, JSON, or Parquet files. Supports custom read options for CSV files.

```yaml
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/source2a.parquet', 's3://my-bucket/source2b.parquet'])"
```

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

--------------------------------

### Reference External Source in dbt Model

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Query data from an external source defined in your dbt project within a SQL model.

```sql
SELECT *
FROM {{ source('external_source', 'source1') }}
WHERE created_at > '2024-01-01'
```

--------------------------------

### Use CREDENTIAL_CHAIN secret provider

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Automatically fetch AWS credentials using the credential_chain provider.

```yaml
default:
  outputs:
    dev:
      type: duckdb
      path: /tmp/dbt.duckdb
      extensions:
        - httpfs
        - parquet
      secrets:
        - type: s3
          provider: credential_chain
  target: dev
```

--------------------------------

### Configure S3 secrets for DuckDB

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Use the secrets field to provide AWS credentials for S3 access.

```yaml
default:
  outputs:
    dev:
      type: duckdb
      path: /tmp/dbt.duckdb
      extensions:
        - httpfs
        - parquet
      secrets:
        - type: s3
          region: my-aws-region
          key_id: "{{ env_var('S3_ACCESS_KEY_ID') }}"
          secret: "{{ env_var('S3_SECRET_ACCESS_KEY') }}"
  target: dev
```

--------------------------------

### Compiled SQL for Table with Specific External Location

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

The SQL query compiled for a table with a specific `external_location` configuration, which can be a function call.

```sql
SELECT *
FROM read_parquet(['s3://my-bucket/my-sources/source2a.parquet', 's3://my-bucket/my-sources/source2b.parquet'])
```

--------------------------------

### Advanced Incremental Merge Strategy

Source: https://context7.com/duckdb/dbt-duckdb/llms.txt

Implement an advanced incremental merge strategy with custom conditions for updates and inserts, specified columns for updates, excluded columns, and custom update expressions for specific fields.

```sql
{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='id',
    merge_update_condition="DBT_INTERNAL_DEST.age < DBT_INTERNAL_SOURCE.age",
    merge_insert_condition="DBT_INTERNAL_SOURCE.status != 'inactive'",
    merge_update_columns=['name', 'age', 'status'],
    merge_exclude_columns=['created_at'],
    merge_update_set_expressions={
        'updated_at': 'CURRENT_TIMESTAMP',
        'version': 'COALESCE(DBT_INTERNAL_DEST.version, 0) + 1'
    }
) }}

SELECT id, name, age, status, created_at, updated_at, version
FROM {{ source('raw', 'users') }}
```

--------------------------------

### Invoke Table Function

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Call a table function materialization, requiring parentheses even when no parameters are provided.

```sql
select * from {{ ref("my_table_function") }}()
```

```sql
select * from {{ ref("my_table_function_with_parameters") }}(1, 2)
```

--------------------------------

### Override External Location for a Specific Table

Source: https://github.com/duckdb/dbt-duckdb/blob/master/README.md

Configure `external_location` at the table level to override the source-level setting or handle special cases. This setting is not propagated to documentation.

```yaml
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'])"
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.