Snowflake Alter Table: Hevo’s Guide for Beginners

Introduction

Is Snowflake being used by your company to store data for analytics and reporting? If that’s the case, the Snowflake Alter Table add column tool is one of several beneficial capabilities that allows you to adjust your data after it’s been ingested into the data warehouse, allowing you to capture additional business situations or use cases. 

Also, scripting, a full shortcuts capability, and a high level of security are all features of SnowSQL, Snowflake’s query language.

This article will provide you with an introduction to Snowflake Alter Table and will also explain how to Alter RENAME Tables in Snowflake, Swap tables using Alter Table in Snowflake, and Alter the Temp Table in Snowflake.

Snowflake Alter Table

The Snowflake Alter Table add column functionality allows you to add a new column to an existing table in your data warehouse. This is helpful when you need to add new values/measures, categorical values dependent on other table columns, or to capture more data.

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?

To replace or edit the table’s name, use the Alter Rename clause.

Syntax:

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

Example:

To start, make a table called Snowflake Practice, then rename 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


EMP was the old table's name, while EMP ONE was the new table's name. As a result, the table will be renamed EMP ONE instead of EMP.

Choose the table that needs to be renamed from the drop-down menu. I selected EMP as the name of the table.

Execute the command to rename the table's name. As a result, the table will be renamed EMP ONE instead of EMP.

The name was successfully changed to EMP ONE.

SWAP Tables Using Alter Table in Snowflake

To switch table names in Snowflake, use the SWAP TABLE function.

In Snowflake, an example of a SWAP TABLE is as follows:

--SQL Command Syntax : 

ALTER TABLE [TABLE_ONE] SWAP WITH [TABLE_TWO]

--Example : 

ALTER TABLE EMP SWAP WITH EMP_ONE


The names of the tables are regularly swapped. EMP ONE will be the name of the EMP table, and, EMP will be the name of the EMP ONE table.

To begin, decide which tables should be switched. I chose EMP and EMP ONE to switch the tables.

After that, run the command to exchange the table names.

Check the data in the tables to make sure it's correct. The table names were successfully swapped, as you can see.

Alter the Temp Table in Snowflake

To modify or replace a temporary table with a permanent table, use the rename table or swap table query clauses.

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

Also, Read – Snowflake Data Types

Conclusion

The Snowflake Alter Table add column command is a very useful command that you should learn because you will almost definitely need it when running your data warehouse. Hopefully, you’re now ready to start incorporating it into your own Snowflake database code!

---