Technology

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!




Recent Posts

HAOQI’s Valentine’s Day Gift-Buying Guide

Valentine's Day is approaching, and it's time to start thinking about that perfect gift for… Read More

14 hours ago

What to Know About Quality Control Inspection for Pipe Products

Introduction Pipes are tubes often used in conveying water, gas, oil, or other fluid substances… Read More

15 hours ago

The Best Museums in Newcastle

Also known as Newcastle upon Tyne, this city in North East England is a large… Read More

15 hours ago

SnapTik’ is Video Downloader that Makes It Simple to Save

One of the best downloaders is, without a doubt, this application and web-based downloader. This… Read More

15 hours ago

Vietnam is an Emerging Software Outsourcing Destination

In the software development field these days, IT outsourcing is more and more becoming popular… Read More

2 days ago

What are the Best Options for Making Calls from the Computer?

It's hard sometimes to manage calls by just using mobile devices only. People managing large… Read More

2 days ago