[CT-736] [CT-735] varchar column reduces in size on an incremental run which leads to "Value too long for character type" error

[stnguyen]

Describe the bug

I have an incremental model with the following properties:

materialized="incremental",
sort="date_col",
dist="date_col",
unique_key="user_id_date",
incremental_strategy="merge",
on_schema_change="sync_all_columns"

The first fresh run worked fine. One of the column named platform (which is built from a case-when statement) was detected automatically by dbt to be of type varchar(20).

On the next incremental run, platform column of the new portion of data only had strings of maximum 17 characters. This led to dbt trying to perform a column type change on redshift from varchar(20) to varchar(17), and got this error:

Value too long for character type

Steps To Reproduce

• There's a platform column build from case-when in the incremental model
• First fresh run works fine
• First incremental run hits the error

Expected behavior

The new column type should be big enough to hold both new and old values

Screenshots and log output

From dbt log file:

In "tbl":
  Schema changed: True
  Source columns not in target: []
  Target columns not in source: []
  New column types: [{'column_name': 'platform', 'new_type': 'character varying(17)'}]

alter table "tbl" add column "platform__dbt_alter" character varying(17);
update "tbl" set "platform__dbt_alter" = "platform";
alter table "tbl" drop column "platform" cascade;
alter table "tbl" rename column "platform__dbt_alter" to "platform"

15:43:07.968169 [debug] [Thread-1  ]: Postgres adapter: Postgres error: Value too long for character type
DETAIL:  
  -----------------------------------------------
  error:  Value too long for character type
  code:      8001
  context:   Value too long for type character varying(17)
  query:     13418344
  location:  string.cpp:247
  process:   query2_263_13418344 [pid=11812]
  -----------------------------------------------

System information

The output of dbt --version:

Core:
  - installed: 1.1.0
  - latest:    1.1.0 - Up to date!

Plugins:
  - bigquery:  1.1.0 - Up to date!
  - snowflake: 1.1.0 - Up to date!
  - redshift:  1.1.0 - Up to date!
  - postgres:  1.1.0 - Up to date!

The operating system you're using: MacOS 12.3.1

The output of python --version: Python 3.8.9

[jtcohen6]

@stnguyen Thanks for opening!

It sounds like on_schema_change logic here, or more likely the logic in diff_column_data_types here, is missing logic to the effect of: If the type is the same, and only the precision differs, don't treat that as a data type change. There are some Column methods we might be able to use to this effect.

If the precision has increased from the older data to the newer data, dbt should already be handling that during expand_target_column_types, which occurs right before schema changes are detected and processed.

I'm going to transfer this issue to the dbt-core repo, since that's actually where the code change should happen, in the default/global project macros.

[epapineau]

I'd like to contribute to this issue if it's not already being worked on :)

[leahwicz]

@epapineau I'm good with you contributing if you are up for this! @jtcohen6 is this good with you as well?

[jtcohen6]

very much so! excited to have you back for round 2, @epapineau :)

[leahwicz]

@epapineau I'm going to assign this to you just so we know you are working on it. If for any reason you have to drop it, just remove yourself so we can keep track. Thanks again for the help here!