Swap table in Snowflake, simply explained

Swapping a table in Snowflake is a common and easy task to undertake by using the alter table command, here is the simplest example of how to swap a table:

alter table table_1 swap with table_2

Swap table in Snowflake if it exists already

We can build upon the simple example we showed previously by adding an if exists constraint, which checks first if the table exists before running swap table. This is helpful as it stops potential errors being returned.

alter table if exists table_1 swap with table_2

Snowflake swap table, what does it do?

The swap with command in Snowflake takes two tables and swaps all content, metadata, and user rights between them. Behind the scenes it’s essentially a series of renaming commands which rename both tables simultaneously.

Swapping a table in Snowflake, things to watch out for

When swapping a table, there are three things to keep in mind:

• You cannot swap a permanent table with a temporary or transient table
• You must have the ownership privilege on the tables you are swapping to run this command
• Swapping the tables does not update references to those tables in other objects (such as view definitions), so this must be updated to the new names manually

Related articles

Drop table in Snowflake
Delete table in Snowflake
Create table in Snowflake
Create or replace table in Snowflake

References

Snowflake alter table documentation