[ADAP-885] [Bug] Dynamic table alter syntax and comment syntax is wrong

[kaarthik108]

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

DBT tries to comment on the model with a wrong syntax statement

comment on dynamic_table model_name AS

Instead of

comment on dynamic table model_name AS

Also, during the alter process

it tries to do

alter dynamic_table sandbox.procs.customer alter

instead of

alter dynamic table sandbox.procs.customer alter

Expected Behavior

comment on dynamic table model_name AS

and

alter dynamic table sandbox.procs.customer alter

Steps To Reproduce

To be able to change the 'dynamic_table' keyword to 'dynamic table'

Relevant log output

No response

Environment

- OS:Mac os
- Python: 3.8.16
- dbt-core: 1.6.2
- dbt-snowflake: 1.6.2

Additional Context

No response

[mikealfare]

@kaarthik108, when you say "during the alter process", are you referring to altering comments?

Nevermind, I found what you're talking about. You already have a PR open for the first fix, would you be willing to finish this off by updating the second macro as well?

[kaarthik108]

@mikealfare
Yes I have fixed it now

[Gyllsdorff]

alter dynamic table sandbox.procs.customer alter

Is that the correct syntax for a dynamic table? alter table sandbox.procs.customer alter works fine for a dynamic table.

https://docs.snowflake.com/en/sql-reference/sql/alter-table-column
vs
https://docs.snowflake.com/en/sql-reference/sql/alter-dynamic-table

It looks like ALTER DYNAMIC TABLE is only used to change the dynamic table properties, the columns is treated as a regular table and should be changed using either ALTER TABLE x ALTER COLUMN y COMMENT $$z$$; or COMMENT ON.

SHOW DYNAMIC TABLES LIKE 'x';
> [1 line]

alter dynamic table dev.test_schema.x alter "column_1" COMMENT $$test comment$$;
> Syntax error: unexpected 'alter'. (line 1)

alter table dev.test_schema.x alter "column_1" COMMENT $$test comment$$;
> Statement executed successfully.

[mikealfare]

Nice catch @Gyllsdorff. I'm surprised that's the syntax, but it looks like you're correct. If you want to submit a PR I can review it. Otherwise I'll take a look next week.

[kaarthik108]

Alright, thanks for pointing that @Gyllsdorff .
A weird syntax

[patkearns10]

@mikealfare I might have done the fork / PR wrong, but the above change should do it.
kaarthik108@edbf910

[dearhari]

Hi @kaarthik108
I upgraded to Snowflake adapter 1.6.4 and tested it with a dynamic table. My model yml has column descriptions and when run a dbt build, I get an error and this to do with the alter statement..

In Snowflake the syntax that works for dynamic table is as shown below:

alter table dim_airport alter airport_code comment 'Airport code (IATA 3 letter code)',
airport_description comment 'Airport Description',
city comment 'City';

You don't need the word "dynamic" in the alter statement.

Can you confirm if your fix covers the above?

[kaarthik108]

Hi @dearhari , yes that's the fix I have corrected in the latest PR.
for the alter table comment.

It doesn't work on 1.6.4, currently it is alter dynamic table table_name alter
This is now fixed on #790

[dearhari]

Thanks, when would #790 be made available?

…

On Mon, Oct 2, 2023 at 2:43 PM Kaarthik Andavar ***@***.***> wrote: Hi @dearhari <https://github.com/dearhari> , yes that's the fix I have corrected in the latest PR. for the alter table comment. It doesn't work on 1.6.4, currently it is alter dynamic table table_name alter This is now fixed on #790 <#790> — Reply to this email directly, view it on GitHub <#769 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AUKVD2YZRLOGI57YQ46MNKLX5IL4TANCNFSM6AAAAAA4XFVBZE> . You are receiving this because you were mentioned.Message ID: ***@***.***>

-- Regards, *Hari Velayuthan*