Snowflake DROP TABLE: How to Safely Delete Tables
The DROP TABLE command is used to delete a table from your database schema. This means that the table and all its data will be removed from the schema, but not immediately from the system. Snowflake allows you to recover the deleted table for a certain period using its Time Travel feature (opens in a new tab).
Syntax
The syntax for dropping a table is:
_10DROP TABLE [IF EXISTS] <table_name> [CASCADE | RESTRICT];
where:
IF EXISTS: This option prevents an error from popping up if the table doesn’t exist.CASCADE|RESTRICT: UseCASCADEto drop the table even if it’s referenced by other tables via foreign keys;RESTRICTwill stop you if there are such dependencies.
Usage Notes
- Time Travel: When you drop a table, it’s not gone forever—at least not immediately. Snowflake keeps a version of the table available for recovery through Time Travel (opens in a new tab) for a set period defined by your data retention settings.
- If it’s a permanent table, after Time Travel, it moves into a seven-day Fail-safe period during which it can still be recovered by Snowflake support.
- Transient and temporary tables don’t have Fail-safe and are purged after the Time Travel period expires.
Examples
Drop a Table
To drop a table named sales_data:
_10DROP TABLE sales_data;
Drop a Table with the IF EXISTS Option
If you’re not sure the table exists and want to avoid an error:
_10DROP TABLE IF EXISTS sales_data;
Drop a Table with the CASCADE Option
To ensure no dependencies block the operation, use:
_10DROP TABLE sales_data CASCADE;
