Incremental model with on_schema_change: append_new_columns causes column misalignment when new column is not last

[mlobet-ros]

Describe the bug

In a "replace_where" incremental model, adding a new column anywhere else than the end of a delta table, and using "on_schema_change: append_new_columns" results in a mismatch of columns:

dbt sees the new column and effectively adds this column as the last column in the delta table.

But the inserts triggered by dbt use column position, rather than column names, which causes the mismatch.
dbt starts by creating a view containing the data to insert. It uses the model definition to do so, hence with the columns order as in the model.
it then does the following:
"
INSERT INTO catalog.schema.incremental_table --new column is the last column
REPLACE WHERE tf_snapshot_date = '2025-12-20'
TABLE newly_created_view__dbt_tmp --new column is somewhere in the middle of the view
"
thus, for example:
updating a model from
old_col1, old_col2, old_col3
to
old_col1, new_col1, old_col2, old_col3

will trigger the update of the table to
old_col1, old_col2, old_col3, new_col1

but the subsequent inserts will put values of new_col1 in old_col2, the values from old_col2 to old_col3 etc.

Steps To Reproduce

config used:
{{
config(
materialized='incremental',
incremental_strategy="replace_where",
alias='my_alias',
partition_by=['tf_snapshot_date'],
incremental_predicates= 'tf_snapshot_date = ' ~ "'" ~ var("snapshot_date") ~ "'"
on_schema_change= 'append_new_columns'
)
}}

Expected behavior

3 options:

• either make sure to add the column in the right position in the table
• or make sure that the insert is done using column name rather than position
• leave it as is because "append" means append, not "add in the middle"

Screenshots and log output

--

System information

The output of dbt --version:

dbt-fusion 2.0.0-preview.86

The operating system you're using:
linux
The output of python --version:
Python 3.12.3

Additional context

Add any other context about the problem here.