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 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.
Related articles
Editing tables
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