[Bug] Adding a column with on_schema_change=(append_new_columns, sync_all_columns) fails when the column name contains whitespace

[skadyan]

Is this a new bug in dbt-snowflake?

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

Current Behavior

If the source query produces a result set with a column name that includes whitespace, the generated ALTER TABLE...ADD statement fails because the column name is not quoted properly.

Expected Behavior

column name in the ALTER TABLE... ADD/DROP should be quoted properly.

Steps To Reproduce

Assume we have the model built already

{{config(
    materialized="incremental",
    on_schema_change="append_new_columns",
)
}}
SELECT 1 as "Id",
       'dbt-snowflake' as "Adapter Name",

Now modify the model to have a new column:

{{config(
    materialized="incremental",
    on_schema_change="append_new_columns",
)
}}
SELECT 1 as "Id",
       'dbt-snowflake' as "Adapter Name",
       'dbt snowflake integration' AS "Adapter Summary"

It fails with the below syntax error which it should not. Expected behavior is that my model should have added a new column.

13:41:30  Completed with 1 error and 0 warnings:
13:41:30
13:41:30    Database Error in model my_model (models\test_model\my_model.sql)
  001003 (42000): SQL compilation error:
  syntax error line 3 at position 28 unexpected 'character'.
13:41:30
13:41:30  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Behind the scene it generate below statement which is incorrect.

alter table "DW"."DW_ETL"."MY_MODEL" add column

            Adapter Summary character varying(25)

see log out put for verbose output.

Relevant log output

describe table DW_ETL.my_model__dbt_tmp
13:45:15  SQL status: SUCCESS 3 in 0.0 seconds
13:45:15  Using snowflake connection "model.dbt_dw.my_model"
13:45:15  On model.dbt_dw.my_model: /* {"app": "dbt", "dbt_version": "1.7.1", "profile_name": "dbt_dw", "target_name": "platform_qa", "node_id": "model.dbt_dw.my_model"} */
describe table "DW"."DW_ETL"."MY_MODEL"
13:45:15  SQL status: SUCCESS 2 in 0.0 seconds
13:45:15
    In "DW"."DW_ETL"."MY_MODEL":
        Schema changed: True
        Source columns not in target: [SnowflakeColumn(column='Adapter Summary', dtype='VARCHAR', char_size=25, numeric_precision=None, numeric_scale=None)]
        Target columns not in source: []
        New column types: []

13:45:15  Using snowflake connection "model.dbt_dw.my_model"
13:45:15  On model.dbt_dw.my_model: /* {"app": "dbt", "dbt_version": "1.7.1", "profile_name": "dbt_dw", "target_name": "platform_qa", "node_id": "model.dbt_dw.my_model"} */
alter table "DW"."DW_ETL"."MY_MODEL" add column

            Adapter Summary character varying(25)
13:45:15  Snowflake adapter: Snowflake query id: 01b830d9-0002-2c45-0000-12e15b3dc93a
13:45:15  Snowflake adapter: Snowflake error: 001003 (42000): SQL compilation error:
syntax error line 3 at position 28 unexpected 'character'.
13:45:15  Timing info for model.dbt_dw.my_model (execute): 19:15:13.708253 => 19:15:15.623516
13:45:15  On model.dbt_dw.my_model: Close
13:45:15  Database Error in model my_model (models\test_model\my_model.sql)
  001003 (42000): SQL compilation error:
  syntax error line 3 at position 28 unexpected 'character'.
13:45:15  1 of 1 ERROR creating sql incremental model DW_ETL.my_model ............................................................ [ERROR in 2.08s]
13:45:15  Finished running node model.dbt_dw.my_model

Environment

- OS:
- Python:
- dbt-core:
- dbt-snowflake:

Additional Context

No response