How to DROP COLUMN in Snowflake

Galaxy Glossary

How do I drop a column in Snowflake without breaking dependencies?

Removes an existing column from a Snowflake table, permanently deleting its data and metadata.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does DROP COLUMN do in Snowflake?

DROP COLUMN removes a column’s data, metadata, constraints, and lineage from a table. After execution, the column and its history are unrecoverable unless you restore the table from Time Travel.

What is the exact syntax?

Use ALTER TABLE <table_name> DROP [ COLUMN ] <column_name> [ , <column_name> ... ] [ CASCADE | RESTRICT ]. Add IF EXISTS to avoid errors when the column might not exist.

How do I drop multiple columns at once?

List columns comma-separated: ALTER TABLE Orders DROP COLUMN discount, promo_code;. Snowflake applies the statement atomically—either all columns are removed or none if an error occurs.

Will dependent objects break?

If a view or foreign key references the column, use CASCADE to drop those objects automatically. Use RESTRICT (default) to abort when dependencies exist, protecting production data.

Example – remove `discount` from Orders

-- Backup with transient clone CREATE OR REPLACE TABLE Orders_backup CLONE Orders; -- Drop the obsolete column ALTER TABLE Orders DROP COLUMN IF EXISTS discount RESTRICT;

The statement fails if any view still selects discount, keeping your model consistent.

Best practices for DROP COLUMN

Back up the table with CLONE, confirm dependencies by querying INFORMATION_SCHEMA.COLUMNS, and run in a transaction on critical tables to keep rollbacks simple.

Why How to DROP COLUMN in Snowflake is important

How to DROP COLUMN in Snowflake Example Usage


-- Drop the temporary "temp_total" column after migration
ALTER TABLE Orders DROP COLUMN IF EXISTS temp_total RESTRICT;

How to DROP COLUMN in Snowflake Syntax


ALTER TABLE [ IF EXISTS ] <table_name>
    DROP { COLUMN | COLUMN IF EXISTS } <column_name> [ , <column_name> ... ]
    [ CASCADE | RESTRICT ];

-- Ecommerce context examples
-- Remove obsolete "discount" column from Orders
ALTER TABLE Orders DROP COLUMN discount RESTRICT;

-- Remove multiple staging columns at once
ALTER TABLE Customers DROP COLUMN temp_flag, old_email CASCADE;

Common Mistakes

Forgetting IF EXISTS. Running ALTER TABLE … DROP COLUMN without the clause raises an error if the column is already gone. Always add IF EXISTS in automated scripts.
Using CASCADE blindly. CASCADE removes dependent views and constraints, which may be hard to recreate. Prefer RESTRICT, resolve dependencies manually, then drop the column.

Frequently Asked Questions (FAQs)

Can I undo a DROP COLUMN in Snowflake?

Yes – if Time Travel retention has not expired, you can restore the whole table to a point before the drop or clone it from historical data.

Does DROP COLUMN reclaim storage instantly?

Logical removal is immediate, but physical storage is reclaimed asynchronously by Snowflake’s background services.

Is DROP COLUMN blocking?

No. Snowflake runs the operation as a metadata change, so it completes quickly and does not lock the table for reads.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.