Undrop for recovering dropped objects in Snowflake - Commands & Configuration

Introduction

Sometimes you accidentally delete a table or other database object. Snowflake lets you recover these dropped objects quickly using the undrop command, so you don't lose important data.

When you accidentally drop a table but realize you still need the data.

When a colleague drops a view by mistake and you want to restore it.

When you want to recover a schema or database that was dropped recently.

When you want to restore an object to its exact state before deletion.

When you want to avoid recreating complex objects from scratch after accidental deletion.

Commands

This command lists all tables that were dropped recently in the specified schema. It helps you find the exact name of the table you want to recover.

Terminal

SHOW DROPPED TABLES IN SCHEMA my_database.public;

Expected Output

name           | dropped_on          | dropped_by

my_table       | 2024-06-01 10:15:00 | [e-mail]

This command recovers the dropped table named 'my_table' in the specified schema. It restores the table with all its data and structure.

Terminal

UNDROP TABLE my_database.public.my_table;

Expected Output

Statement executed successfully.

This command verifies that the table has been restored and is available again in the schema.

Terminal

SHOW TABLES IN SCHEMA my_database.public;

Expected Output

name           | created_on          | rows

my_table       | 2024-06-01 10:15:00 | 1000

Key Concept

If you remember nothing else, remember: Snowflake keeps dropped objects for a time so you can easily undrop and recover them.

Common Mistakes

Trying to undrop an object without specifying the full name including database and schema.

Snowflake needs the full path to find the correct dropped object to restore.

Always use the full object name like database.schema.object when running undrop.

Waiting too long to undrop, after the retention period expires.

Dropped objects are only kept for a limited time before permanent deletion.

Run undrop as soon as possible after accidental drop to ensure recovery.

Summary

Use SHOW DROPPED TABLES to find recently dropped tables in a schema.

Run UNDROP TABLE with full object name to recover the dropped table.

Verify recovery by listing tables again with SHOW TABLES.