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

[nguyensinhtu]

Is this a new bug?

• [X] I believe this is a new bug
• [X] 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: https://github.com/dbt-labs/dbt-adapters/blob/b414668cd046ce9b68c03b68ca7fc94f190b4e99/dbt/include/global_project/macros/adapters/columns.sql#L89, 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?