Snowflake ALTER TABLE for Table Modifications Explained(with code)

Does your organization use Snowflake to store its data for analytics and reporting purposes? If so, then the Snowflake alter table add column feature is one of many useful features that help you make changes to your data once it is ingested into the data warehouse by ensuring that you can modify your data to capture additional business situations or use cases. 

This blog post will introduce the syntax and provide some examples that will help you understand how it can be used in your Snowflake database.

What is Snowflake?

• Snowflake is a cloud-hosted data warehouse that performs data analytics and storage functions as a service. It works across different cloud platforms and allows you to mix and match between them.
• Its cutting-edge architecture that separates storage and computations allows independent scaling and also enables competitive pricing. You only pay for what you use and when you use it. It also provides a high level of security.
• Snowflake stores data in the form of tables, which are collections of rows and columns. Sometimes, the data type or attributes of a table may change. This would require you to make changes to the structure of the table. You can do this easily by using the Snowflake alter table command. Let’s take a deeper look into this.

Key Features of Snowflake

Here are some of the benefits of using Snowflake as a Software as a Service (SaaS) solution:

• Snowflake enables you to enhance your Analytics Pipeline by transitioning from nightly Batch Loads to Real-time Data Streams, allowing you to improve the quality and speed of your analytics. By enabling secure, concurrent, and monitoring access to your Data Warehouse across your organization, you can improve the quality of analytics at your company.
• Snowflake uses the Caching Paradigm to swiftly deliver the results from the cache. To avoid re-generation of the report when nothing has changed, Snowflake employs Persistent (within the session) Query results.
• Snowflake allows you to get rid of silos and ensure access to meaningful insights across the enterprise, resulting in better Data-driven Decision-Making. This is a crucial first step toward bettering partner relationships, optimizing pricing, lowering operational expenses, increasing sales effectiveness, and more.
• Snowflake allows you to better analyze Customer Behaviour and Product Usage. You can also use the whole scope of data to ensure customer satisfaction, drastically improve product offers, and foster Data Science innovation.
• Snowflake allows you to create your own Data Exchange, which allows you to securely communicate live, controlled data. It also encourages you to improve data relationships throughout your business units, as well as with your partners and customers.

What is Snowflake Alter Table?

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.

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 RENAME Tables using Alter Table 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?

Alter table Snowflake ADD COMMENT Example

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

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

• Adding comments to the table serves as a description of the column and provides classification information.
• 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 Example

--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 Temporary 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?

• 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.

Key Takeaways

• You have now learned the basics of using the Snowflake Alter Table command. 
• 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!
• Want an easier way to load data into your Snowflake database? This is where a simpler alternative like Hevo can save your day! 

Hevo Data is a No-Code Data Pipeline that offers a faster way to move data from 150+ Data Sources, including 60+ Free Sources, into your Data Warehouse to be visualized in a BI tool. Hevo is fully automated and, hence, does not require you to code.

Take Hevo’s 14-day free trial to experience a better way to manage your data pipelines. You can also check out the unbeatable pricing, which will help you choose the right plan for your business needs.

Frequently Asked Questions