Drop table in Snowflake (explained with simple examples)

It is straight forward to execute drop 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 drop a table, some other helpful commands, and common issues that users have.

Drop table in Snowflake

When we run drop table in Snowflake we remove the table from the default, or specified, schema and database.

In order to drop 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 

Drop 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 dropped. 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 dropped in Snowflake

Once you have dropped 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

Drop 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 running drop table, as you could end up dropping production data when you only meant to drop 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:

In this example you see a screenshot from the Snowflake UI where I want to run drop table on 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 dropped from the default schema and database in the worksheet, which is not where I want to drop the data from, as I have not explicitly said otherwise. In order to be sure that the correct data is dropped, the second command needs to be run, where the schema and database are defined in the command.

Related articles

Create tables

Create table in Snowflake
Create or replace table in Snowflake
Create or replace temporary table in Snowflake

Edit tables

Delete table in Snowflake
Drop column from table
Rename Snowflake table
Swap Snowflake tables

References

undrop table documentation
drop table documentation