Wow! Instantly UNDROP Databases, Schemas, and Tables Using IDs

In this blog, I will discuss how to restore a dropped database, table, or schema using the UNDROPcommand. Typically, when you DROPa table in Snowflake, you can use the UNDROPcommand followed by the table name to bring it back to life. For example, consider this scenario:

Fig 1 — DEMO_TBL1 with Employee Column (First Instance)

In this scenario, the DEMO_TBL1 table is located within the DEMO_DB1 database and DEMO_SCH1 schema. After dropping the DEMO_TBL1 table, I created a new table with the same name (DEMO_TBL1) but with different columns.

Fig 2— DEMO_TBL1 with Store Column (Second Instance)

In the second instance, I dropped the table, thinking it was no longer needed. However, I later realized that I needed the original table related to Employee data. Normally, in this situation, we would use Time Travel in snowflake to retrieve the table, depending on the Time Travel settings in the account. This involves querying the table at a specific point in time, which can be cumbersome and require significant analysis to pinpoint the exact moment before the table was dropped and then restore it.

However, with Snowflake’s latest release, the process has become much easier thanks to the enhanced UNDROPfeature. This feature allows us to UNDROPa database, schema, or table using the ID, simplifying the recovery process.

Now, let’s dive deeper into how to UNDROPa database, schema, or table using the ID.

Scenario 1: UNDROP the Table Using ID

Step 1: Create DEMO_TABLE with the column COL1_TBL1.

CREATE TABLE DEMO_DB1.DEMO_SCH1.DEMO_TABLE (COL1_TBL1 INT); -- TABLE CREATION SUCCESSFULL

INSERT INTO DEMO_DB1.DEMO_SCH1.DEMO_TABLE (COL1_TBL1) VALUES (1);

SELECT * FROM DEMO_DB1.DEMO_SCH1.DEMO_TABLE;

Fig 3 — Creation of DEMO_TABLE (First Instance)

Step 2: Retrieve the ID of the table (DEMO_TABLE):

SELECT TABLE_NAME,TABLE_ID FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES WHERE TABLE_NAME = 'DEMO_TABLE' AND DELETED IS NULL -- 6146

Fig 4 — Retriving TABLE_ID for the DEMO_TABLE (First Instance)

Step 3: Drop the table (DEMO_TABLE):

DROP TABLE DEMO_DB1.DEMO_SCH1.DEMO_TABLE;

Step 4: Create a new table with the same name (DEMO_TABLE):

CREATE TABLE DEMO_DB1.DEMO_SCH1.DEMO_TABLE (COL1_TBL2 INT);

INSERT INTO DEMO_DB1.DEMO_SCH1.DEMO_TABLE (COL1_TBL2) VALUES (2);

SELECT * FROM DEMO_DB1.DEMO_SCH1.DEMO_TABLE;

Fig 5— Creation of DEMO_TABLE (Second Instance)

Step 5: Retrieve the ID of the new table (DEMO_TABLE):

SELECT TABLE_NAME,TABLE_ID FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES WHERE TABLE_NAME = 'DEMO_TABLE' AND DELETED IS NULL 

Fig 6 — Retrieving TABLE_ID for the DEMO_TABLE (Second Instance)

Step 6: Drop the second table (DEMO_TABLE):

DROP TABLE DEMO_DB1.DEMO_SCH1.DEMO_TABLE;

Step 7: We have now removed two tables with the same name, which can be verified in the metadata table.

SELECT TABLE_NAME,TABLE_ID,DELETED FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES WHERE TABLE_NAME = 'DEMO_TABLE';

Fig 7 — Metadata Table Showing Deleted Details

Result: To UNDROPthe first table, use the ID 6146. This will restore the table with the columns that were defined when the table was first created.

UNDROP TABLE IDENTIFIER (6146)

Fig 8 — UNDROP table using Table_ID Identifier

Now, we will see how the first table is restored using the ID with column name is COL1_TBL1.

Figure 9 — Restored Table DEMO_TABLE (Created on First Instance)

We will follow a similar process for restoring a database and schema, but the approach will involve using the respective metadata tables and columns.

Below is a table that provides more details, along with scripts for your testing.

Fig 9 — Meta Data Table Details (To restore Table, Schema and Database)

How to UNDROP the Schema using the ID:

CREATE OR REPLACE DATABASE UNDROP_DEMO_DB_1;

CREATE OR REPLACE SCHEMA UNDROP_DEMO_SCH_1;

CREATE OR REPLACE TABLE UNDROP_DEMO_TBL_1 (COL1 STRING);

SELECT SCHEMA_ID,SCHEMA_NAME,DELETED FROM SNOWFLAKE.ACCOUNT_USAGE.SCHEMATA  WHERE SCHEMA_NAME = 'UNDROP_DEMO_SCH_1'; --28

DROP SCHEMA UNDROP_DEMO_DB_1.UNDROP_DEMO_SCH_1;

CREATE OR REPLACE SCHEMA UNDROP_DEMO_SCH_1;

CREATE OR REPLACE TABLE UNDROP_DEMO_TBL_2 (COL2 STRING);

DROP SCHEMA UNDROP_DEMO_DB_1.UNDROP_DEMO_SCH_1;

SELECT SCHEMA_ID,SCHEMA_NAME,DELETED FROM SNOWFLAKE.ACCOUNT_USAGE.SCHEMATA  WHERE SCHEMA_NAME = 'UNDROP_DEMO_SCH_1'; --33

UNDROP SCHEMA IDENTIFIER(28);

How to UNDROP the Database using the ID:

CREATE OR REPLACE DATABASE UNDROP_DEMO_1;

CREATE OR REPLACE SCHEMA UNDROP_SCH_1;

SELECT DATABASE_ID, DATABASE_NAME,DELETED FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASES  WHERE DATABASE_NAME = 'UNDROP_DEMO_1'; --7

DROP DATABASE UNDROP_DEMO_1;

CREATE OR REPLACE DATABASE UNDROP_DEMO_1;

CREATE OR REPLACE SCHEMA UNDROP_SCH_2;

SELECT DATABASE_ID, DATABASE_NAME,DELETED FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASES  WHERE DATABASE_NAME = 'UNDROP_DEMO_1'; --13 

DROP DATABASE UNDROP_DEMO_1;

SELECT DATABASE_ID, DATABASE_NAME,DELETED FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASES  WHERE DATABASE_NAME = 'UNDROP_DEMO_1';

UNDROP DATABASE IDENTIFIER (7)

SELECT * FROM UNDROP_DEMO_1;

Please feel free to share any additional features that you find relevant in this scenario and how this feature can help save time in your projects in the comments section. If you found this article useful, kindly click the like button.

References: -

• https://www.snowflake.com/

About me:

I am a Cloud Data Architect at EY New Zealand. Throughout my career, I have worked on numerous projects involving legacy data warehouses, big data implementations, cloud platforms, and migrations. If you require assistance with certification, data solutions, or implementations, please feel free to connect with me on LinkedIn.