[Bug] Appending new columns to an existing incremental does not respect the quote column property.

[jeremyyeo]

Is this a new bug in dbt-snowflake?

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

Current Behavior

If we introduce a new column into an incremental that has a reserved keyword and we quote it - the append column DML still adds the columns without the explicit quote.

Expected Behavior

Append column DML should quote the name of the column.

Steps To Reproduce

Project setup:

# dbt_project.yml
name: my_dbt_project
profile: all
config-version: 2
version: "1.0.0"

models:
   my_dbt_project:
      +materialized: incremental

# models/schema.yml

models:
  - name: bar
    columns:
      - name: id
        data_tests:
          - not_null

-- models/bar.sql
{{ config(on_schema_change='append_new_columns') }}

select 1 id

Do an initial build

$ dbt --debug build
01:55:07  1 of 2 START sql incremental model dbt_jyeo.bar ................................ [RUN]
01:55:07  Re-using an available connection from the pool (formerly list_development_jyeo_dbt_jyeo, now model.my_dbt_project.bar)
01:55:07  Began compiling node model.my_dbt_project.bar
01:55:07  Writing injected SQL for node "model.my_dbt_project.bar"
01:55:07  Began executing node model.my_dbt_project.bar
01:55:07  Writing runtime sql for node "model.my_dbt_project.bar"
01:55:07  Using snowflake connection "model.my_dbt_project.bar"
01:55:07  On model.my_dbt_project.bar: /* {"app": "dbt", "dbt_version": "1.8.4", "profile_name": "all", "target_name": "sf", "node_id": "model.my_dbt_project.bar"} */
create or replace transient table development_jyeo.dbt_jyeo.bar
         as
        (

select 1 id
        );
01:55:07  Opening a new connection, currently in state closed
01:55:09  SQL status: SUCCESS 1 in 2.050 seconds
01:55:09  Applying DROP to: development_jyeo.dbt_jyeo.bar__dbt_tmp
01:55:09  Using snowflake connection "model.my_dbt_project.bar"
01:55:09  On model.my_dbt_project.bar: /* {"app": "dbt", "dbt_version": "1.8.4", "profile_name": "all", "target_name": "sf", "node_id": "model.my_dbt_project.bar"} */
drop view if exists development_jyeo.dbt_jyeo.bar__dbt_tmp cascade
01:55:09  SQL status: SUCCESS 1 in 0.299 seconds
01:55:09  On model.my_dbt_project.bar: Close
01:55:10  Sending event: {'category': 'dbt', 'action': 'run_model', 'label': '506eba38-1952-45c6-ab60-e8d5601a1f51', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x109bfe890>]}
01:55:10  1 of 2 OK created sql incremental model dbt_jyeo.bar ........................... [SUCCESS 1 in 3.02s]
01:55:10  Finished running node model.my_dbt_project.bar
01:55:10  Began running node test.my_dbt_project.not_null_bar_id.ece651d55f
01:55:10  2 of 2 START test not_null_bar_id .............................................. [RUN]
01:55:10  Re-using an available connection from the pool (formerly model.my_dbt_project.bar, now test.my_dbt_project.not_null_bar_id.ece651d55f)
01:55:10  Began compiling node test.my_dbt_project.not_null_bar_id.ece651d55f
01:55:10  Writing injected SQL for node "test.my_dbt_project.not_null_bar_id.ece651d55f"
01:55:10  Began executing node test.my_dbt_project.not_null_bar_id.ece651d55f
01:55:10  Writing runtime sql for node "test.my_dbt_project.not_null_bar_id.ece651d55f"
01:55:10  Using snowflake connection "test.my_dbt_project.not_null_bar_id.ece651d55f"
01:55:10  On test.my_dbt_project.not_null_bar_id.ece651d55f: /* {"app": "dbt", "dbt_version": "1.8.4", "profile_name": "all", "target_name": "sf", "node_id": "test.my_dbt_project.not_null_bar_id.ece651d55f"} */
select
      count(*) as failures,
      count(*) != 0 as should_warn,
      count(*) != 0 as should_error
    from (
      
    
    



select id
from development_jyeo.dbt_jyeo.bar
where id is null



      
    ) dbt_internal_test
01:55:10  Opening a new connection, currently in state closed
01:55:12  SQL status: SUCCESS 1 in 1.599 seconds
01:55:12  On test.my_dbt_project.not_null_bar_id.ece651d55f: Close
01:55:12  2 of 2 PASS not_null_bar_id .................................................... [PASS in 2.22s]

Add a new column to bar:

-- models/bar.sql
{{ config(on_schema_change='append_new_columns') }}

select 1 id, 'premium' as "group"

Specify quote: true for the column in the model schema file:

# models/schema.yml
models:
  - name: bar
    columns:
      - name: id
        data_tests:
          - not_null
      - name: "group"
        quote: true
        data_tests:
          - not_null

Do an incremental run:

$ dbt --debug build

01:57:42  1 of 3 START sql incremental model dbt_jyeo.bar ................................ [RUN]
01:57:42  Re-using an available connection from the pool (formerly list_development_jyeo_dbt_jyeo, now model.my_dbt_project.bar)
01:57:42  Began compiling node model.my_dbt_project.bar
01:57:42  Writing injected SQL for node "model.my_dbt_project.bar"
01:57:42  Began executing node model.my_dbt_project.bar
01:57:42  Using snowflake connection "model.my_dbt_project.bar"
01:57:42  On model.my_dbt_project.bar: /* {"app": "dbt", "dbt_version": "1.8.4", "profile_name": "all", "target_name": "sf", "node_id": "model.my_dbt_project.bar"} */
create or replace  temporary view development_jyeo.dbt_jyeo.bar__dbt_tmp
  
   as (
    

select 1 id, 'premium' as "group"
  );
01:57:42  Opening a new connection, currently in state closed
01:57:44  SQL status: SUCCESS 1 in 1.693 seconds
01:57:44  Using snowflake connection "model.my_dbt_project.bar"
01:57:44  On model.my_dbt_project.bar: /* {"app": "dbt", "dbt_version": "1.8.4", "profile_name": "all", "target_name": "sf", "node_id": "model.my_dbt_project.bar"} */
describe table development_jyeo.dbt_jyeo.bar__dbt_tmp
01:57:44  SQL status: SUCCESS 2 in 0.348 seconds
01:57:44  Using snowflake connection "model.my_dbt_project.bar"
01:57:44  On model.my_dbt_project.bar: /* {"app": "dbt", "dbt_version": "1.8.4", "profile_name": "all", "target_name": "sf", "node_id": "model.my_dbt_project.bar"} */
describe table development_jyeo.dbt_jyeo.bar
01:57:45  SQL status: SUCCESS 1 in 0.360 seconds
01:57:45  Using snowflake connection "model.my_dbt_project.bar"
01:57:45  On model.my_dbt_project.bar: /* {"app": "dbt", "dbt_version": "1.8.4", "profile_name": "all", "target_name": "sf", "node_id": "model.my_dbt_project.bar"} */
describe table development_jyeo.dbt_jyeo.bar__dbt_tmp
01:57:45  SQL status: SUCCESS 2 in 0.334 seconds
01:57:45  Using snowflake connection "model.my_dbt_project.bar"
01:57:45  On model.my_dbt_project.bar: /* {"app": "dbt", "dbt_version": "1.8.4", "profile_name": "all", "target_name": "sf", "node_id": "model.my_dbt_project.bar"} */
describe table "DEVELOPMENT_JYEO"."DBT_JYEO"."BAR"
01:57:45  SQL status: SUCCESS 1 in 0.311 seconds
01:57:45  
    In "DEVELOPMENT_JYEO"."DBT_JYEO"."BAR":
        Schema changed: True
        Source columns not in target: [SnowflakeColumn(column='group', dtype='VARCHAR', char_size=7, numeric_precision=None, numeric_scale=None)]
        Target columns not in source: []
        New column types: []
  
01:57:45  Using snowflake connection "model.my_dbt_project.bar"
01:57:45  On model.my_dbt_project.bar: /* {"app": "dbt", "dbt_version": "1.8.4", "profile_name": "all", "target_name": "sf", "node_id": "model.my_dbt_project.bar"} */
alter table "DEVELOPMENT_JYEO"."DBT_JYEO"."BAR" add column
          
            group character varying(7)
01:57:46  Snowflake adapter: Snowflake query id: 01b5d435-0905-18b6-000d-37833c1ad132
01:57:46  Snowflake adapter: Snowflake error: 001003 (42000): SQL compilation error:
syntax error line 3 at position 12 unexpected 'group'.
01:57:46  On model.my_dbt_project.bar: Close
01:57:46  Database Error in model bar (models/bar.sql)
  001003 (42000): SQL compilation error:
  syntax error line 3 at position 12 unexpected 'group'.
01:57:46  Sending event: {'category': 'dbt', 'action': 'run_model', 'label': '5caec81e-9663-4ad2-bac4-62efe4d03bb8', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x1094bb450>]}
01:57:46  1 of 3 ERROR creating sql incremental model dbt_jyeo.bar ....................... [ERROR in 4.17s]
01:57:46  Finished running node model.my_dbt_project.bar
01:57:46  Began running node test.my_dbt_project.not_null_bar__group_.5b47d4ccdd
01:57:46  2 of 3 SKIP test not_null_bar__group_ .......................................... [SKIP]
01:57:46  Finished running node test.my_dbt_project.not_null_bar__group_.5b47d4ccdd
01:57:46  Began running node test.my_dbt_project.not_null_bar_id.ece651d55f
01:57:46  3 of 3 SKIP test not_null_bar_id ............................................... [SKIP]

^ We tried to add a column group without the quotes:

alter table "DEVELOPMENT_JYEO"."DBT_JYEO"."BAR" add column
          
            group character varying(7)

Instead, we should have done a:

alter table "DEVELOPMENT_JYEO"."DBT_JYEO"."BAR" add column
          
            "group"" character varying(7)

To check that the quote config actually works somewhat - do a full-refresh instead:

$ dbt --debug build --full-refresh
01:59:49  On model.my_dbt_project.bar: /* {"app": "dbt", "dbt_version": "1.8.4", "profile_name": "all", "target_name": "sf", "node_id": "model.my_dbt_project.bar"} */
create or replace transient table development_jyeo.dbt_jyeo.bar
         as
        (

select 1 id, 'premium' as "group"
        );
01:59:49  Opening a new connection, currently in state closed
01:59:51  SQL status: SUCCESS 1 in 2.335 seconds
01:59:51  Applying DROP to: development_jyeo.dbt_jyeo.bar__dbt_tmp
01:59:51  Using snowflake connection "model.my_dbt_project.bar"
01:59:51  On model.my_dbt_project.bar: /* {"app": "dbt", "dbt_version": "1.8.4", "profile_name": "all", "target_name": "sf", "node_id": "model.my_dbt_project.bar"} */
drop view if exists development_jyeo.dbt_jyeo.bar__dbt_tmp cascade
01:59:51  SQL status: SUCCESS 1 in 0.351 seconds
01:59:51  On model.my_dbt_project.bar: Close
01:59:52  Sending event: {'category': 'dbt', 'action': 'run_model', 'label': '12901028-470a-4875-a92c-adf3b5d1baf7', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x1139ab310>]}
01:59:52  1 of 3 OK created sql incremental model dbt_jyeo.bar ........................... [SUCCESS 1 in 3.34s]
01:59:52  Finished running node model.my_dbt_project.bar
01:59:52  Began running node test.my_dbt_project.not_null_bar__group_.5b47d4ccdd
01:59:52  2 of 3 START test not_null_bar__group_ ......................................... [RUN]
01:59:52  Re-using an available connection from the pool (formerly model.my_dbt_project.bar, now test.my_dbt_project.not_null_bar__group_.5b47d4ccdd)
01:59:52  Began compiling node test.my_dbt_project.not_null_bar__group_.5b47d4ccdd
01:59:52  Writing injected SQL for node "test.my_dbt_project.not_null_bar__group_.5b47d4ccdd"
01:59:52  Began executing node test.my_dbt_project.not_null_bar__group_.5b47d4ccdd
01:59:52  Writing runtime sql for node "test.my_dbt_project.not_null_bar__group_.5b47d4ccdd"
01:59:52  Using snowflake connection "test.my_dbt_project.not_null_bar__group_.5b47d4ccdd"
01:59:52  On test.my_dbt_project.not_null_bar__group_.5b47d4ccdd: /* {"app": "dbt", "dbt_version": "1.8.4", "profile_name": "all", "target_name": "sf", "node_id": "test.my_dbt_project.not_null_bar__group_.5b47d4ccdd"} */
select
      count(*) as failures,
      count(*) != 0 as should_warn,
      count(*) != 0 as should_error
    from (
select "group"
from development_jyeo.dbt_jyeo.bar
where "group" is null

    ) dbt_internal_test
01:59:52  Opening a new connection, currently in state closed
01:59:54  SQL status: SUCCESS 1 in 1.739 seconds
01:59:54  On test.my_dbt_project.not_null_bar__group_.5b47d4ccdd: Close
01:59:54  2 of 3 PASS not_null_bar__group_ ............................................... [PASS in 2.36s]
...

^ We see that the group column is quoted in the test which shows that this config does work in this case - testing - but not when appending new columns.

Relevant log output

No response

Environment

- OS: macOS
- Python: 3.11.9
- dbt-core: 1.8.4
- dbt-snowflake: 1.8.3

Additional Context

No response

[colin-rogers-dbt]

This will be addressed in: dbt-labs/dbt-adapters#256