[CT-2178] Handling column names having / while altering the table

[preethi8p]

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

We had few columns with "/" in the column names. During incremental runs, when dbt runs the alter statement to either add or drop a column it is failing with the below error in the global macro columns.sql
ERROR_MESSAGE
SQL compilation error:
syntax error line 3 at position 12 unexpected '/'.
image

Fix
Modified global macro to add proper quoting to the column names during alter table statement.

Expected Behavior

We expect that the alter statements to add or drop columns, must run successfully without any failure. Basically if we have added a column in the previous layer(say load layer), and during incremental load in the next layer(raw layer) it should run the alter statement and add the column to the raw table. Please note it failed because it has a character "/" in the column name.

Steps To Reproduce

1. Add a column to a table in load layer with a name like "KOND/CC" (with a /)
2. Do an incremental run so that this column should be added in the next layer which uses this table as source.
3. Alter statement to add the column is triggered and it fails with error mentioned above
4. As a fix, we need to include the column within double quotes in the macro "snowflake__alter_relation_add_remove_columns" as we are using Snowflake.
5. Upon fix it will run the alter statement successfully and add that column to the table.

Relevant log output

Log output with error:
2023-02-16T17:23:47.3708425Z �[0m17:23:47  �[33mDatabase Error in (models/raw/cust.sql �[0m 
2023-02-16T17:23:47.3714555Z �[0m17:23:47    001003 (42000): SQL compilation error:
2023-02-16T17:23:47.3720917Z �[0m17:23:47    syntax error line 3 at position 12 unexpected '/'.
2023-02-16T17:23:47.3727341Z �[0m17:23:47    syntax error line 3 at position 16 unexpected '/'.

Log output after fix:
2023-02-20T12:39:08.9562443Z �[0m12:39:08  121 of 1023 OK created incremental model qa_raw.cust .......... [�[32mSUCCESS 0�[0m in 193.62s]

Environment

- OS: Windows 10
- Python: 3.8.6
- dbt-core: 1.3.1
- dbt-snowflake:1.3.0

Additional Context

No response

[jtcohen6]

Linking:

• Older dbt-core issue that's related (but was closed as stale): [Bug] Incremental compilation error not handling column with quotes dbt-core#4422
• Proposed fix to the global macro in dbt-core: Handling column names having / while altering the table dbt-core#7025

[preethi8p]

Have raised a Pull Request for the issue - Update columns.sql dbt-labs/dbt-core#7025

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[preethi8p]

I hope this was accepted as a valid issue, so is this PR completed? How do I track? If yes, then please provide me the link to verify the same else please let me know the next step.

[dbeatty10]

This was reported multiple times and covering a plurality of adapters:

• dbt-snowflake: [CT-2178] Handling column names having / while altering the table #63
• dbt-postgres: [Bug] Incremental compilation error alter queries not using quoted columns #160
  • postgres
  • redshift
  • snowflake
  • sqlserver

Acceptance criteria

Implementation detail was originally described here: dbt-labs/dbt-core#7025 (comment)

In short:

1. Use {{ adapter.quote() }} as described in dbt-labs/dbt-adapters#160 (comment)
2. Add test case(s) to the on_schema_change tests for a column that contains a special character (/, ", etc.) or is a reserved keyword, e.g. from.
3. Inherit these test cases within each adapter (dbt-snowflake, etc).

More detail

After doing a git grep "column\.name" dbt/, here are three places I noticed in the code base to update:

1. dbt-adapters/dbt/include/global_project/macros/adapters/columns.sql

   Line 126 in 154ca61

   add column {{ column.name }} {{ column.data_type }}{{ ',' if not loop.last }}

2. dbt-adapters/dbt/include/global_project/macros/adapters/columns.sql

   Line 130 in 154ca61

   drop column {{ column.name }}{{ ',' if not loop.last }}

3. dbt-adapters/dbt/include/global_project/macros/materializations/snapshots/helpers.sql

   Line 11 in 154ca61

   alter table {{ relation }} add column "{{ column.name }}" {{ column.data_type }};

Since the 3rd one is related to snapshots, ideally there would be at least one snapshot-specific functional test that needs a quoted identifier to handle a special character or reserved keyword.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[preethi8p]

Its been too long I have opened this issue, now I don't even remember where to track back. Now I am not clear on all the comments, so please let me know if this can be published or not ? And what should be my next step towards this? TIA.