Unit tests do not apply profiles.yml settings (TimeZone) to test connections

[shauneccles]

Description

dbt unit tests on dbt-duckdb do not apply the settings configuration from profiles.yml to the connection used for unit test execution. This causes TIMESTAMPTZ columns to behave differently in unit tests vs production queries when a non-UTC TimeZone is configured.

Minimal reproduction

profiles.yml:

my_project:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: data/warehouse/my_db.duckdb
      settings:
        TimeZone: 'Australia/Melbourne'

models/my_model.sql:

select
    cast(strftime(my_timestamp, '%Y%m%d') as integer) as date_key,
    my_timestamp::date as date_val
from {{ ref('my_source') }}

Unit test (in a _unit_tests.yml):

unit_tests:
  - name: test_date_key
    model: my_model
    given:
      - input: ref('my_source')
        rows:
          - {my_timestamp: "2025-06-01 23:00:00"}
    expect:
      rows:
        - {date_key: 20250601, date_val: "2025-06-01"}

Expected behaviour

Per DuckDB's documented TIMESTAMPTZ semantics:

When strings without UTC offsets or time zone names are converted to a WITH TIME ZONE type, the string is interpreted in the configured time zone.

With TimeZone = 'Australia/Melbourne' (UTC+10 in June):

• "2025-06-01 23:00:00" should be interpreted as 2025-06-01 23:00:00 AEST = 2025-06-01 13:00:00 UTC
• strftime(...) should return 20250601 (Melbourne date)
• ::date should return 2025-06-01

This is confirmed by running the same SQL via dbt show --inline against the production connection.

Actual behaviour

The unit test fails:

• strftime(...) returns 20250602 (the naive string was parsed as UTC, then displayed in Melbourne: 2025-06-02 09:00:00 AEST)
• ::date returns 2025-06-02

The fixture CTE in compiled SQL correctly shows cast('2025-06-01 23:00:00' as TIMESTAMP WITH TIME ZONE), but the cast interprets the string as UTC rather than Melbourne time.

Additional failure mode

When the model uses gap_start_datetime::date >= some_date_column in a join, the unit test produces a DuckDB internal assertion error:

INTERNAL Error: Failed to bind column reference "gap_start_datetime" [87.1]:
inequal types (TIMESTAMP WITH TIME ZONE != DATE)

This does not occur on the production connection.

Root cause analysis

DuckDB's TimeZone setting is per-connection, not per-database (duckdb/duckdb-web#2315). The dbt-duckdb adapter applies settings from profiles.yml when opening a connection for dbt run, dbt show, etc. However, unit tests appear to use a connection (or in-memory instance) that does not receive the same settings dict.

This creates a split within the same compiled query:

1. Fixture CTE (mock data): naive strings cast to TIMESTAMPTZ are interpreted as UTC (no timezone set yet)
2. Model SQL: strftime/::date apply Melbourne timezone for output formatting

Providing explicit offsets ("2025-06-01 23:00:00+10:00") does not resolve the issue, which may relate to duckdb/duckdb#18767.

Environment

• dbt-core: 1.11.7
• dbt-duckdb: 1.10.1
• DuckDB: 1.2.x (bundled)
• OS: Windows 11
• Python: 3.13

Related issues

• duckdb/duckdb-web#2315 -- TimeZone is per-connection, not per-database
• duckdb/duckdb#18767 -- TIMESTAMP parsing with timezone produces unexpected results
• duckdb/duckdb#11279 -- TIMESTAMP not consistently detected as TIMESTAMPTZ
• duckdb/duckdb#16839 -- Proposal to disallow implicit TIMESTAMP/TIMESTAMPTZ casting
• dbt-labs/dbt-core#11868 -- Feature: unit tests to set column types in fixtures

Suggested fix

Ensure that settings from the profile configuration are applied to the DuckDB connection used for unit test execution, the same way they are applied for dbt run and dbt show.