Snowflake Alter Table: Hevo’s Guide for Beginners

| Updated on February 28, 2024


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.


                                       DATA_RETENTION_TIME_IN_DAYS |
                                       DEFAULT_DDL_COLLATION       |
                                       [ , ... ]


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.


-- Alter SQL Syntax Command  : 


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 : 


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 : 


--Example : 


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


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!

Adam Green

With more than 3 years of experience as a software and tech writer on Adam has been writing articles, blogs, and featured stories centered around the software and tech niche since he graduated from Virginia Tech University. He writes savvy articles, tutorials, and reviews that explain difficult concepts to readers of all levels. His expertise includes software development, cybersecurity, artificial intelligence, and emerging technologies. Through simple and engaging writing, Adam constantly delivers useful insights that enable readers to feel at ease in the ever-changing technological scene.

Related Posts