Delete table in Snowflake

Delete table in Snowflake (explained with simple examples)

It is straight forward to delete a table in Snowflake. In the simplest form all you need to do is run the command drop table table_name.

Stephen Allwright
Stephen Allwright

It is straight forward to delete a table in Snowflake. In the simplest form all you need to do is run the command drop table table_name . In this post I will look further into how to delete a table, some other helpful commands, and common issues that users have.

Delete table in Snowflake

On first glance Snowflake does not have a specific delete table command, this is because it goes by another name. They have a command called drop table which effectively deletes the table, if by deleting we mean remove the table from a given schema and all of the data contained in that table.

In order to delete a table called products in the database analytics and schema production we would run the following command:

drop table analytics.production.products;

-- Will return "products successfully dropped" if successful 

Delete table if exists in Snowflake

There is a helpful adjustment to the standard drop table command which is recommend to add, this addition checks first if the table exists before it is deleted. The reason we would want to add this is because if we run drop table table_name and the table doesnโ€™t exist then an error will be raised, which we donโ€™t want.

Running this command on the same table as before:

drop table if exists analytics.production.products;

Undrop table to restore after being deleted in Snowflake

Once you have deleted a table in Snowflake it is possible to restore the table, in order to do so you need to run the following:

undrop table analytics.production.products;

-- Will return "Table products successfully restored" if successful

There are however some caveats to this restoration being possible, they are:

  • Snowflake Time Travel feature needs to be possible on your instance
  • The table can only be restored if itโ€™s within the data retention period, which by default is 24 hours. This default can be changed by adjusting the parameter DATA_RETENTION_TIME_IN_DAYS

Delete table for a defined database and schema in Snowflake

A common mistake that users make when running commands in Snowflake is that they donโ€™t define the database and schema. This is especially dangerous when you are deleting a table, as you could end up deleting production data when you only meant to delete the table in development. To avoid this costly mistake, make sure that you always define the schema and database in the command. An example of this can be seen below:

Snowflake delete table

In this example you see a screenshot from the Snowflake UI where I want to delete a table called products in the database analytics and the schema analytics . Note that the default database and schema for this worksheet is analytics_archive.analytics .

The first command in the example will result in the table products being deleted from the default schema and database in the worksheet, which is not where I want to delete the data from, as I have not explicitly said otherwise. In order to be sure that the correct data is deleted, the second command needs to be run, where the schema and database are defined in the command.

Create table in Snowflake
Create or replace table in Snowflake
Drop table in Snowflake
Rename Snowflake table
Swap Snowflake tables

References

undrop table documentation
drop table documentation

Snowflake

Stephen Allwright Twitter

I'm a Data Scientist currently working for Oda, an online grocery retailer, in Oslo, Norway. These posts are my way of sharing some of the tips and tricks I've picked up along the way.

Comments