Drop column from a Snowflake table
Learn how to drop a column from a table using the alter table command in Snowflake.
1. What does drop column mean?
2. Drop one column from a table
3. Drop multiple columns from a table
4. Drop column only if it exists
5. Undrop a column from a table
The examples in this post will use the following table, which is called
What does drop column mean?
When you drop a column, it means the column is deleted from the table along with the data it contained.
Drop one column from a table
In order to drop a column from a table, you need to use Snowflake's
alter table command.
Let's drop just one column,
number_products, from the
alter table orders drop column number_products;
The result would be:
Drop multiple columns from a table
Now, let's drop both
order_value from the
alter table orders drop column number_products, order_value;
The result would be this:
Drop column only if it exists
When undertaking operations in Snowflake, it is common to add the
if exists constraint which stops unwanted errors from being returned when the defined object can't be found.
This is unfortunately not possible to use when dropping columns in Snowflake, therefore if you try to drop a column which doesn't exist then an error will be returned.
Undrop a column from a table in Snowflake
If you accidentally drop a column and want to roll back the change, it's possible to get it back, however, it's not straightforward. In order to do this, you will need to utilise Time Travel in Snowflake to access your backups.
Each situation will be different, so it's difficult to provide an example here. Instead, I recommend reading the documentation for Time Travel on Snowflake's website.
Add multiple columns to Snowflake table
Add column to Snowflake table
Rename Snowflake column
Snowflake alter table documentation
Snowflake Time Travel documentation
Snowflake community issue regarding undropping columns