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