Modifying Tables with Snowflake’s ALTER TABLE: Step-by-Step Instructions

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

Snowflake Alter Table

The Snowflake alter table add column feature enables you to modify existing tables in your data warehouse by adding another column. This is useful when you need to add in new measures/values, categorical values based on other columns in the table, or to capture additional information. 

SYNTAX:

ALTER TABLE [ IF EXISTS ] <name> UNSET {
                                       DATA_RETENTION_TIME_IN_DAYS |
                                       DEFAULT_DDL_COLLATION       |
                                       COMMENT
                                       }
                                       [ , ... ]

Where:

columnAction ::=
  {
     ADD COLUMN <col_name> <col_type>
        [ { DEFAULT <expr> | { AUTOINCREMENT | IDENTITY } [ { ( <start_num> , <step_num> ) | START <num> INCREMENT <num> } ] } ]
                            /* AUTOINCREMENT (or IDENTITY) supported only for columns with numeric data types (NUMBER, INT, FLOAT, etc.). */
                            /* Also, if the table is not empty (i.e. rows exist in the table), only DEFAULT can be altered.               */
        [ inlineConstraint ]
   | RENAME COLUMN <col_name> TO <new_col_name>

How to Alter RENAME tables in Snowflake?

The Alter Rename clause can be used to change or modify the table’s name.

Syntax

-- Alter SQL Syntax Command  : 
 
ALTER TABLE [OLD_TABLE_NAME] RENAME TO [NEW_TABLE_NAME]

Example

Let’s start by creating a table called Snowflake Practice, and then renaming it Snowflake Rename Practice.

-- Create Table Query  : 
 
create or replace table Snowflake_Practice (sid int, department_name text, fees int)
 
-- Output
 
+------------------------------------------------+
| status                                         |
|------------------------------------------------|
| Table SNOWFLAKE_PRACTICE successfully created. |
+------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.452s
 
 
-- Now RENAME the table to  Snowflkae_Rename_Practice
 
ALter table Snowflake_Practice Rename To Snowflake_Practice_Rename;
 
 
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.392s

Snowflake Rename Table Example using GUI

--Example : 
ALTER TABLE EMP RENAME TO EMP_ONE

The old table’s name was EMP, and the new table’s name was EMP ONE. As a result, the name of the table will be changed from EMP to EMP ONE.

Select the table that needs to be renamed here. I chose EMP as the name of the table.

Now run the command that will rename the table’s name. As a result, the name of the table will be changed from EMP to EMP ONE.

The name has been changed to EMP ONE successfully.

How to RENAME Columns using Alter Table in Snowflake?

Alter Table Snowflake RENAME COLUMN Example :

--SQL Command Syntax : 
ALTER TABLE [TABLE_NAME] RENAME COLUMN [OLD_COLUMN] TO [NEW_COLUMN]

--Example : 
ALTER TABLE EMP RENAME COLUMN INFO TO NEW_INFO

The column will be renamed NEW INFO instead of INFO. As a result, the column’s new name is NEW INFO.

Rename the table and column by selecting them. EMP table and INFO column were chosen.

The column name will be changed to NEW INFO after running the following command.

We can now verify that the column name has been updated.

How to DROP Columns using Alter Table in Snowflake?

Alter table Snowflake DROP COLUMN Example :

--SQL Command Syntax : 
ALTER TABLE [TABLE_NAME] DROP COLUMN [COLUMN_NAME]

--Example : 
ALTER TABLE EMP DROP COLUMN NEW_INFO

The column will now be completely removed from the EMP table.

Choose the table and column that should be removed.

Execute the following statement. NEW INFO will be removed from the table.

The column NEW INFO was successfully removed.

How to DROP Constraints using Alter Table in Snowflake?

Alter table Snowflake DROP CONSTRAINTS Example :

--SQL Command Syntax : 
ALTER TABLE [TABLE_NAME] ALTER COLUMN [COLUMN_NAME] DROP [CONSTRAINT]

--Example : 
ALTER TABLE EMP ALTER COLUMN INFO DROP NOT NULL

We were previously unable to add null values, but after removing the constraint, we will be able to do so.

Choose the table and column where the constraint should be removed. Check if the INFO column has a not null constraint.

Run the following command and check the status.

Check the INFO null column. The NOT NULL constraint has been removed.

How to ADD COMMENT using Alter Table in Snowflake?

Adding comments to the table serves as a description of the column and provides classification information.

--SQL Command Syntax : 
ALTER TABLE [TABEL_NAME] ALTER [COLUMN_NAME] COMMENT ‘[COMMENT]’

--Example : 
ALTER TABLE EMP ALTER NAME COMMENT ‘NAME OF THE EMPLOYEE’

In the comment section, the column NAME will be changed to ‘NAME OF THE EMPLOYEE.’

As you can see, the comment section in the table’s corner is empty for NAME.

Run the SQL statement below and check the results.

Now we can look at the NAME-related comment column. NAME OF THE EMPLOYEE has been added to the comment.

This will be used to determine the contents of a particular column.

How to Increase size of data type using Alter table in Snowflake?

Alter Table Snowflake Change the size of data type :

--SQL Command Syntax : 
ALTER TABLE [TABLE_NAME] ALTER [COLUMN_NAME] SET DATA TYPE [DATATYPE_WITH_SIZE]

	
--Example : 
ALTER TABLE EMP ALTER NAME SET DATA TYPE VARCHAR(30)

The data type size was previously 20 and will now be updated to 30.

Note that we can increase the size here but not decrease it because that is not supported.

Select the datatype for which the dataset’s size needs to be increased. Check the INFO datatype size at the bottom left of the image. Its dimensions are 20. Now I’m attempting to expand the size of column NAME.

Check whether the size has increased by running the command below.

The size of the name has been changed to 30 in the picture on the left bottom. The size was successfully increased.

The capacity has been increased to 30 people. It will be impossible to reduce the datatype size.

How to alter the temp table in Snowflake?

The rename table or swap table query clauses can be used to change or replace a temporary table with a permanent table.

Alter Temp table using Swap in Snowflake Example

	
-- Alter table Customer_Temp Swap with Customer

Alter Temp table using Rename in Snowflake Example

	
-- Alter table Customer_Temp Rename to Customer

How to SWAP tables using Alter Table in Snowflake?

In Snowflake, the SWAP TABLE function will be used to swap table names.

SWAP TABLE Example In Snowflake :

--SQL Command Syntax : 
ALTER TABLE [TABLE_ONE] SWAP WITH [TABLE_TWO]

--Example : 
ALTER TABLE EMP SWAP WITH EMP_ONE

The table names are frequently interchanged. The EMP table will be called EMP ONE, and the EMP ONE table will be called EMP.

To begin, choose which tables need to be swapped. To swap the tables, I chose EMP and EMP ONE.

Now run the command that will swap the table names.

Check the data in the tables for verification. As you can see, the table names were successfully swapped.

How to SET/UNSET TAG using Alter Table in Snowflake?

Syntax:

ALTER TABLE [ IF EXISTS ] <name> SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]

ALTER TABLE [ IF EXISTS ] <name> UNSET TAG <tag_name> [ , <tag_name> ... ]

WHERE:

clusteringAction ::=
  {
     CLUSTER BY ( <expr> [ , <expr> , ... ] )
   | RECLUSTER [ MAX_SIZE = <budget_in_bytes> ] [ WHERE <condition> ]
     /* RECLUSTER is deprecated */
   | { SUSPEND | RESUME } RECLUSTER
   | DROP CLUSTERING KEY
  }

tableColumnAction ::=
| ALTER | MODIFY
                      [ COLUMN ] <col1_name> SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]
                    , [ COLUMN ] <col2_name> SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]
                    ...

   | ALTER | MODIFY
                      COLUMN <col1_name> UNSET TAG <tag_name> [ , <tag_name> ... ]
                    , COLUMN <col2_name> UNSET TAG <tag_name> [ , <tag_name> ... ]
                    ...
 inlineConstraint ::=
    [ NOT NULL ]
    [ CONSTRAINT <constraint_name> ]
    { UNIQUE | PRIMARY KEY | { [ FOREIGN KEY ] REFERENCES <ref_table_name> [ ( <ref_col_name> ) ] } }
    [ <constraint_properties> ]

Parameters:

name:

SET …: The table to change’s identifier. If the identifier contains spaces or special characters, double quotes must be used to enclose the entire string. Case is also important for identifiers enclosed in double quotes. Sets one or more table properties or parameters (separated by blank spaces, commas, or new lines):

STAGE_FILE_FORMAT = ( FORMAT_NAME = 'file_format_name' ) or STAGE_FILE_FORMAT = ( TYPE = CSV | JSON | AVRO | ORC | PARQUET | XML [ ... ] ): Modifies the table’s file format (for data loading and unloading), which can be one of the following:

FORMAT_NAME = file_format_name:

To use for loading/unloading data, specify an existing file format object. The format type (CSV, JSON, etc.) and other format options for data files are determined by the specified file format object.

There are no other format options specified in the string. The named file format object, on the other hand, specifies the other file format options for loading and unloading data.

TYPE = CSV | JSON | AVRO | ORC | PARQUET | XML [ … ]: The type of files to load/unload is specified. The string can contain additional format-specific options.

TAG tag_name = ‘tag_value‘ [ , tag_name = ‘tag_value‘ , … ]:

The tag name (i.e. the key) and the tag value are specified.

The tag value is always a string, and the tag value can have up to 256 characters. A maximum of 20 distinct tag keys can be assigned to an object.

UNSET …:Unsets one or more properties/parameters for the table, restoring them to their default values:

• DATA_RETENTION_TIME_IN_DAYS
• MAX_DATA_EXTENSION_TIME_IN_DAYS
• CHANGE_TRACKING
• DEFAULT_DDL_COLLATION
• TAG tag_name [ , tag_name ... ]
• COMMENT

Snowflake Alter Table Example

The following examples will demonstrate how the to alter table add column is applied to add three new columns to the table t1, along with the result. 

-- Add a new column to table T1
alter table t1 add column a2 number;
-- Add another column with NOT NULL constraint
alter table t1 add column a3 number not null;
-- Add another column with a default value and a NOT NULL constraint
alter table t1 add column a4 number default 0 not null;
desc table t1;

What is the Masking Policy in Snowflake?

Masking Policy is a schema-level object that is used to protect sensitive data from unauthorized access while allowing authorized users access.

How to CREATE MASKING POLICY in Snowflake?

Snowflake CREATING MASKING POLICY :

To create a masking policy for email, use the statement below. As a result, only the ANALYST will be able to see the value, while others will not.

--SQL Command Syntax : 
CREATE [ OR REPLACE ] MASKING POLICY [ IF NOT EXISTS ] <name> AS
( <arg_name_to_mask> <arg_type_to_mask> [ , <arg_1> <arg_type_1> ... ] )
RETURNS <arg_type_to_mask> -> <expression_on_arg_name>
[ COMMENT = '<string_literal>' ];

--Example :
create or replace masking policy email_mask as (val string) returns string ->
  case
    when current_role() in ('ANALYST') then val
    else '*********'
  end;

Following the creation of the MASKING POLICY. We employ this policy, which will be applied to a single column.

Conclusion

The alter table add column feature is a very helpful command that is important to master as it will almost certainly be required when operating your data warehouse. Hopefully, you are now ready to start using it in your own code in your Snowflake database!

Hevo is a No-code Data Pipeline product that will help you move data from multiple data sources to your destination. It is consistent and reliable. It has pre-built integrations from 100+ sources.

So, give it a try! Sign Up here for a 14-day free trial.

Have any further queries? Get in touch with us in the comments section below.

Rashid Y Technical Content Writer, Hevo Data

Rashid is a technical content writer with a passion for the data industry. Leveraging his problem-solving skills, he delivers informative and engaging content on data science. With a deep understanding of complex data concepts and a talent for clear, compelling communication, Rashid creates content that informs and captivates his audience.