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
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
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.