dbeatty10
commented
1 year ago Thanks for reporting this @crystalro0 🤩
Adding an override to alter_relation_add_remove_columns like you mentioned worked for me as well.
### Reprex
`models/src_artists.sql` ```sql {{ config( materialized='table', ) }} {% if var("version", 0) == 0 %} select {{ dbt.current_timestamp() }} as inserted_at, 'taylor' as name {% else %} -- add a non-zero version to the end of the command to get a different version: -- --vars "{'version': 1}" select {{ dbt.current_timestamp() }} as inserted_at, 'taylor' as Name, 'eras' as "Tour" {% endif %} ``` `models/dim_artists.sql` ```sql {{ config( materialized='incremental', on_schema_change='append_new_columns', ) }} select * from {{ ref("src_artists") }} ``` Add a case-sensitive column to an incremental model and look at the results afterwards ```shell dbt run --full-refresh dbt show --inline "select * from {{ ref('dim_artists') }}" dbt run --vars "{'version': 1}" dbt show --inline "select * from {{ ref('dim_artists') }}" ```### Root cause
Ultimately, I believe the root cause is that the current implementation does not match the quoting being applied in this trace of code (listed in reverse order): - https://github.com/dbt-labs/dbt-core/blob/5310d3715cdaa109eea03995731425a85cd6cd33/core/dbt/include/global_project/macros/materializations/models/incremental/column_helpers.sql#L11 - https://github.com/dbt-labs/dbt-core/blob/5310d3715cdaa109eea03995731425a85cd6cd33/core/dbt/include/global_project/macros/materializations/models/incremental/strategies.sql#L71 - https://github.com/dbt-labs/dbt-core/blob/5310d3715cdaa109eea03995731425a85cd6cd33/core/dbt/include/global_project/macros/materializations/models/incremental/strategies.sql#L10Solution
If we update the implementation of default__alter_relation_add_remove_columns, I'd suggest using adapter.quote like elsewhere in that file to achieve the same effect:
{% for column in add_columns %}
add column {{ adapter.quote(column.name) }} {{ column.data_type }}{{ ',' if not loop.last }}
{% endfor %}{{ ',' if remove_columns | length > 0 }}
{% for column in remove_columns %}
drop column {{ adapter.quote(column.name) }} {{ ',' if not loop.last }}
{% endfor %}Acceptance criteria
- Add applicable test cases within dbt-core and inherit within dbt-snowflake (confirming that an error is raised in dbt-snowflake)
- Solve the reported issue, optionally using an implementation like above
alexnikitchuk
colin-rogers-dbt
jeremyyeo
Is this a new bug in dbt-core?
Current Behavior
While working with DBT incremental config:
on_schema_change='append_new_columns'The append new columns flag is not able to capture the correct case-sensitive column name and add it to the incremental table causing the run to fail.Expected Behavior
Be able to apply to add the new columns to an incremental table with the
'append_new_columns'config.Steps To Reproduce
Relevant log output
Environment
Which database adapter are you using with dbt?
snowflake
Additional Context
suggested the following as a workaround:
saved in project macro folder.