[v2 Bug] DuckDB: on_schema_change: 'fail' reports every column as a type change on the second incremental run

Author: cnstlunguCreated Sep 18, 2026Updated Sep 18, 2026

Labelstriage

I have searched the existing issues and could not find one covering this on DuckDB. This looks like the DuckDB sibling of #14363, which was closed as fixed on Databricks — see Additional Context at the bottom.

Current Behavior

Any incremental model with on_schema_change: 'fail' on the built-in DuckDB adapter succeeds on its first run and then fails on every subsequent run, with no change to the model SQL, the data, or the schema.

The error contradicts itself — the column sets match exactly, yet nearly every column is reported as a type change:

The source and target schemas on this incremental model are out of sync!
...
Additional troubleshooting context:
   Source columns not in target: []
   Target columns not in source: []
   New column types: [{'column_name': 'id', 'new_type': 'integer'},
                      {'column_name': 'big', 'new_type': 'bigint'},
                      {'column_name': 'dbl', 'new_type': 'double'},
                      {'column_name': 'dec', 'new_type': 'decimal(18, 3)'},
                      {'column_name': 'dt', 'new_type': 'date'},
                      {'column_name': 'ts', 'new_type': 'datetime'},
                      {'column_name': 'flg', 'new_type': 'boolean'}]

Root cause

The two sides of the comparison are produced in different naming conventions and then compared with exact string equality.

I pulled the target side out with a run-operation macro calling adapter.get_columns_in_relation:

column	target (`get_columns_in_relation`)	source (v2 static analysis)	equal?
`id`	`INTEGER`	`integer`	no — case
`big`	`BIGINT`	`bigint`	no — case
`dbl`	`DOUBLE`	`double`	no — case
`dec`	`DECIMAL(18,3)`	`decimal(18, 3)`	no — case and whitespace
`txt`	`VARCHAR`	`VARCHAR`	yes
`dt`	`DATE`	`date`	no — case
`ts`	`TIMESTAMP`	`datetime`	no — case and type alias
`flg`	`BOOLEAN`	`boolean`	no — case

txt is the tell. VARCHAR is the one type v2 emits identically on both sides, and it is the one column that is not flagged. So this is not a DuckDB type-system quirk — it is three separate normalisation gaps in a comparison that should normalise before comparing:

case — integer vs INTEGER
type alias — datetime vs TIMESTAMP
whitespace in parameterised types — decimal(18, 3) vs DECIMAL(18,3)

Failure path

dbt_internal_packages/dbt-duckdb/macros/materializations/incremental.sql:75
  → dbt_internal_packages/dbt-adapters/macros/materializations/models/incremental/on_schema_change.sql:117
  → dbt_internal_packages/dbt-adapters/macros/materializations/models/incremental/on_schema_change.sql:148

Behaviour of the other `on_schema_change` values

value	result
`fail`	broken — errors on every run after the first
`sync_all_columns`	runs, but drops and re-adds the 7 "changed" columns on every incremental run, which reorders the table's columns (`txt` migrates to position 1). Values and row counts are preserved — I checked explicitly, including historical rows that the incremental filter does not re-emit.
`append_new_columns`	fine
`ignore`	fine

Interaction with contracts — this is the part with no workaround

With contract: enforced: true, v2 rejects two of those four outright:

[error] [InvalidConfig (dbt1005)]: Invalid value for on_schema_change: ignore.
Models materialized as incremental with contracts enabled must set on_schema_change
to 'append_new_columns' or 'fail'

So for a contracted incremental the only permitted values are append_new_columns and fail. fail is broken by this bug, which leaves append_new_columns as the only option that runs — and it does not guard against type changes at all. There is currently no configuration of a contracted incremental model on DuckDB that both runs and detects schema drift.

Expected Behavior

A second run of an unchanged incremental model should succeed. Column types should be normalised to a common vocabulary before source and target are compared, so that INTEGER/integer, TIMESTAMP/datetime and DECIMAL(18,3)/decimal(18, 3) are each recognised as the same type.

dbt-core 1.12.5 with dbt-duckdb 1.11.0 does exactly this on the identical project — see below.

Steps To Reproduce

Three files, no packages.

dbt_project.yml

yaml

name: 'v2_osc_repro'
version: '1.0.0'
profile: 'v2_osc_repro'
models:
  v2_osc_repro:
    +materialized: table

profiles.yml

yaml

v2_osc_repro:
  target: local
  outputs:
    local:
      type: duckdb
      path: "repro.duckdb"
      schema: main

models/m_incremental.sql

sql

{{ config(materialized='incremental', unique_key=['id'], on_schema_change='fail') }}

select
    1::INTEGER              as id,
    2::BIGINT               as big,
    3.5::DOUBLE             as dbl,
    4.25::DECIMAL(18,3)     as dec,
    'x'::VARCHAR            as txt,
    DATE '2020-01-01'       as dt,
    TIMESTAMP '2020-01-01'  as ts,
    true::BOOLEAN           as flg

Then:

bash

dbt run    # succeeds — builds the table
dbt run    # fails — nothing changed in between

To reproduce the contract variant, add a models/schema.yml with contract: enforced: true and the eight columns typed as above.

Confirming it is a v2-only discrepancy

The same three files on dbt-core 1.12.5 + dbt-duckdb 1.11.0 run cleanly three times in a row, on_schema_change: 'fail' and contract: enforced: true both in place:

Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 TOTAL=1
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 TOTAL=1
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 TOTAL=1

Relevant log output

[error] [JinjaError (dbt1501)]: Error executing materialization macro
'dbt_duckdb.materialization_incremental_duckdb' for model model.v2_osc_repro.m_incremental:
Failed to eval the compiled Jinja expression invalid operation: Compilation Error for
model.v2_osc_repro.m_incremental from models/m_incremental.sql:

              The source and target schemas on this incremental model are out of sync!
              They can be reconciled in several ways:
                - set the `on_schema_change` config to either append_new_columns or
                  sync_all_columns, depending on your situation.
                - Re-run the incremental model with `full_refresh: True` to update the
                  target schema.
                - update the schema manually and re-run the process.

              Additional troubleshooting context:
                 Source columns not in target: []
                 Target columns not in source: []
                 New column types: [{'column_name': 'id', 'new_type': 'integer'},
                                    {'column_name': 'big', 'new_type': 'bigint'},
                                    {'column_name': 'dbl', 'new_type': 'double'},
                                    {'column_name': 'dec', 'new_type': 'decimal(18, 3)'},
                                    {'column_name': 'dt', 'new_type': 'date'},
                                    {'column_name': 'ts', 'new_type': 'datetime'},
                                    {'column_name': 'flg', 'new_type': 'boolean'}]

(in run/v2_osc_repro/models/m_incremental.sql)
(in dbt_internal_packages/dbt-duckdb/macros/materializations/incremental.sql:75:27)
(in dbt_internal_packages/dbt-adapters/macros/materializations/models/incremental/on_schema_change.sql:117:29)
(in dbt_internal_packages/dbt-adapters/macros/materializations/models/incremental/on_schema_change.sql:148:27)

The target-side types were obtained with:

jinja

{% macro show_cols() %}
  {% set rel = adapter.get_relation(database=target.database, schema=target.schema,
                                    identifier='m_incremental') %}
  {% for c in adapter.get_columns_in_relation(rel) %}
    {{ log("TARGET " ~ c.name ~ " dtype=" ~ c.dtype ~ " data_type=" ~ c.data_type, info=True) }}
  {% endfor %}
{% endmacro %}

TARGET id   dtype=INTEGER        data_type=INTEGER
TARGET big  dtype=BIGINT         data_type=BIGINT
TARGET dbl  dtype=DOUBLE         data_type=DOUBLE
TARGET dec  dtype=DECIMAL(18,3)  data_type=DECIMAL(18,3)
TARGET txt  dtype=VARCHAR        data_type=character varying(256)
TARGET dt   dtype=DATE           data_type=DATE
TARGET ts   dtype=TIMESTAMP      data_type=TIMESTAMP
TARGET flg  dtype=BOOLEAN        data_type=BOOLEAN

Environment

- OS: macOS 27.0.0 (arm64)
- dbt: 2.0.4 (built-in DuckDB adapter)
- compared against: dbt-core 1.12.5 + dbt-duckdb 1.11.0 + duckdb 1.4.4

Which database adapter are you using?

DuckDB

Is this a discrepancy vs. dbt 1.x?

Yes — this works correctly in dbt 1.x but not in dbt v2.x

Additional Context

Relationship to #14363

This looks like the same defect that #14363 reported on Databricks ("Fusion Databricks incremental false-positive schema drift (on_schema_change: fail) on string column", also with contract: enforced: true). That issue was reported against 2.0.0-preview.149, confirmed fixed on 2.0.0-preview.206 by two commenters, and closed "Unable to repro this" on 2026-08-25.

I am reproducing it on 2.0.4, released well after that fix, on DuckDB. So either the fix was adapter-specific and did not cover the DuckDB path, or it has regressed. #14186 is another closed Databricks on_schema_change="fail" failure that may be related.

What I have not established

I only have DuckDB available locally, so I cannot say whether the source-side type vocabulary is specific to the DuckDB adapter or comes from shared v2 static analysis. If it is the latter, every adapter whose warehouse reports uppercase type names is affected and the Databricks fix may have addressed only a symptom. That seems like the first thing worth checking.

Possibly related open issues

#16350 — snapshot ADD COLUMN emits BigQuery API type names (RECORD) instead of standard SQL types
#15961 — BigQuery unit-test fixtures rendered with incorrect column types
#15894 — TIMESTAMP and DATETIME columns never compared in unit tests

All three are the same underlying theme: v2 carrying a different type vocabulary from the one the warehouse reports. #15894 in particular concerns the same TIMESTAMP/DATETIME distinction that this bug trips on.

Source: dbt-labs/dbt-core

View original on GitHub View discussion on GitHub