Drop column from a Snowflake table

Learn how to drop a column from a table using the alter table command in Snowflake.

Stephen Allwright
Stephen Allwright

The examples in this post will use the following table, which is called orders

order_id customer_id order_value number_products date
1528 4920 98 4 2023-01-06
1529 6590 156 7 2023-01-06
1530 2408 25 2 2023-01-07

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

alter table orders
drop column number_products;

The result would be:

order_id customer_id order_value date
1528 4920 98 2023-01-06
1529 6590 156 2023-01-06
1530 2408 25 2023-01-07

Drop multiple columns from a table

Now, let's drop both number_products and order_value from the orders table.

alter table orders
drop column number_products, order_value;

The result would be this:

order_id customer_id date
1528 4920 2023-01-06
1529 6590 2023-01-06
1530 2408 2023-01-07

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.


Editing tables

Drop table in Snowflake

Editing columns

Add multiple columns to Snowflake table
Add column to Snowflake table
Rename Snowflake column

References

Snowflake alter table documentation
Snowflake Time Travel documentation
Snowflake community issue regarding undropping columns

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.