Snowddl - Alter statement Issue

[chintan-episource]

ALTER TABLE "DB_NAME"."SCHEMA_NAME"."TABLE_NAME" MODIFY COLUMN "PARTITION" COMMENT 'Project Key /Id';
This statement does not work as expected. So subsequent alter statements on any other table are not executed as well. Do we see any alternative.

[chintan-episource]

As part of this above exercise, we have to add new column to existing table. But since above issue was a blocker, new column dint got added to other table . Now , I altered table externally but it does not identify the table. instead it gives me below command -

CREATE OR REPLACE TABLE "DB_NAME"."SCHEMA"."TABLE_NAME"
( )
Let me know your perspective to this.

[littleK0i]

This statement does not work as expected

So what is the specific error message or incorrect state? What goes wrong exactly?

Regarding to "CREATE OR REPLACE TABLE", Snowflake supports very limited number of table transformations via ALTER TABLE: https://docs.snowflake.com/en/sql-reference/sql/alter-table-column

Everything else requires full table rewrite, which takes form of "CREATE OR REPLACE TABLE" statement.

I suspect, manual alter changed the state of table, which can only be repaired by full rewrite.

[littleK0i]

If full table rewrite is required, SnowDDL will not suggest to execute individual ALTER TABLE ... commands for small things like comments, since the whole object must replaced with CREATE OR REPLACE TABLE ... anyway.

Also, all objects are resolved in separate threads. So any problems occurred during processing of one table should not affect processing of other tables by design.

I can help you to investigate if you wish. I'll need:

• Current structure of affected tables in SQL format (can be obtained in GUI by using View definition option);
• SnowDDL config for affected tables;
• Logs and Suggested SQL queries produced by SnowDDL during execution;

Thank you.

[chintan-episource]

Alter is for different table.
followed by
Create or replace ... select from same table
for which column was added from outside snowddl since alter statements did not work for
ALTER TABLE "db_name"."Schema"."DOS" MODIFY COLUMN "PAGE" COMMENT 'Start-end pages of chart';
subsequent alter statement did not work .

Now , it shows when i ran -
Alter statment
followed by create or replace ... select * from table name gets ignored.
No errors shown. it says 0 ddl executed.

[littleK0i]

Maybe you did not add CLI options --apply-unsafe and --apply-replace-table to the command.

"Unsafe" changes are not applied by default, but only suggested in the form of raw SQL.

Full list of CLI options: https://docs.snowddl.com/basic/cli
More about "unsafe" queries: https://docs.snowddl.com/guides/other/safe-unsafe

[chintan-episource]

Ohk.. I'll shall try this option . What about Alter stmt- ALTER TABLE "db_name"."Schema"."DOS" MODIFY COLUMN "PAGE" COMMENT 'Start-end pages of chart'; will this as well work?

[chintan-episource]

SInce you mentioned if there are mutliple Alter statements, and each will be executed by different thread, but it did not behave as you mentioned.
There were several alter statements -
ALTER TABLE "db_name"."Schema"."DOS" MODIFY COLUMN "PAGE" COMMENT 'Start-end pages of chart'
.....
followed by
Alter table "db_name"."Schema"."DOS" add column "new column name "

ideally add column should have worked as expected, but it did not work.
So had to add column outside snowddl and now i see this option --apply-replace-table to rectify the same.