Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
2 tasks done
skadyan opened this issue Nov 6, 2024 · 0 comments · May be fixed by #1232
Open
2 tasks done

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

skadyan opened this issue Nov 6, 2024 · 0 comments · May be fixed by #1232
Labels
bug Something isn't working

Comments

@skadyan
Copy link

skadyan commented Nov 6, 2024 •
edited
Loading

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 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
@skadyan skadyan added bug Something isn't working triage labels Nov 6, 2024
@skadyan skadyan linked a pull request Nov 6, 2024 that will close this issue
Open
2 tasks
@skadyan skadyan changed the title [Bug] Adding a column with on_schema_change=(append, sync_all_columns) fails when the column name contains whitespace [Bug] Adding a column with on_schema_change=(append_new_columns, sync_all_columns) fails when the column name contains whitespace Nov 6, 2024
@adrianburusdbt adrianburusdbt mentioned this issue Nov 7, 2024
Open
2 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working
Projects
None yet
Milestone
No milestone
2 participants
@skadyan @amychen1776