[Bug] Incremental failed for on_schema_change "sync_all_columns" when there's only a type change.

[nguyensinhtu]

Is this a new bug?

• I believe this is a new bug
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

For incremental model config:

    config(
        materialized='incremental',
       on_schema_change='sync_all_columns'
    )
}}

select ...

This fails when a column type changes. Following the logic here:

dbt-adapters/dbt/include/global_project/macros/adapters/columns.sql

Line 89 in b414668

{% macro default__alter_column_type(relation, column_name, new_column_type) -%}

,
in step 2, DBT is trying to copy data with an old type to a temporary column with a new type.

Expected Behavior

It's hard to decide what to do in this case:
Here a few options I can think of:

• Add casting type when copying data over.
• Better error msg.
• Or you should discard support for data type changes.

Steps To Reproduce

1. with config

config(
        materialized='incremental',
       on_schema_change='sync_all_columns'
    )

2. Failed on incremental run not full-refresh.

Relevant log output

log error: column "id__dbt_alter" is of type bigint but expression is of type character

Environment

- OS: Ubuntu 20.04
- Python:3.11.6 
- dbt-adapters: 1.5.0

Additional Context

No response

[amychen1776]

Hi @nguyensinhtu - I'm curious what adapter are you using this on and what version?