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