Snowflake schema change

QbS 0
Viewed 41

I'm learning SQL and Snowflake and I'd like to change some metadata of a table. I'm trying to understand what is a good practice in the following case.

I want to change properties for 4 columns (but not all in the Table):

1. COLUMN_DEFAULT for 2 columns
2. IS_NULLABLE  for 2 columns

Additionally, the table already is filled with values. I need to preserve data in the table. Only those 4 columns that need to have properties changed will have different values.

What would be the best approach?

  1. Create new (proper) schema
  2. Clone table and assigned it to the new schema and add some prefix
  3. Drop an old table
  4. Rename the new to the name of the old one

Is that the correct way of doing it?

Or maybe:

  1. Create a copy of 4 columns that need to have changed properties inside the table. And create them with already poroper properties
  2. Drop old columns
  3. Rename freshly created columns.

Or maybe somet other way is better? I'd appreciate your suggestions.

database-schema snowflake-schema snowflake-cloud-data-platform
1 Answers

Default Column: Unfortunately, we can't add a default for an existing column or modify it unless the default value is the sequence. The feature to add a default value for a column using an alter statement is not supported in Snowflake as documented here. The workaround is to recreate the table with the default value and reload the data.

Reference: https://docs.snowflake.com/en/sql-reference/sql/alter-table-column.html

Not Null:

alter table t1 alter column c1 set not null;

When setting a column to NOT NULL, if the column contains NULL values, an error is returned and no changes are applied to the column.

HamzaShakeel0
Related