Undrop for recovering dropped objects in Snowflake - Mini Project: Build & Apply

Undrop for recovering dropped objects

📖 Scenario: You are managing a Snowflake data warehouse. Sometimes, tables or other database objects get dropped by mistake. Snowflake allows you to recover these dropped objects using the UNDROP command within a certain time window.In this project, you will practice how to undrop a dropped table to recover your data.

🎯 Goal: Build a Snowflake script that creates a table, drops it, and then recovers it using the UNDROP command.

📋 What You'll Learn

Create a table named employees with columns id (integer) and name (string).

Drop the employees table.

Use the UNDROP TABLE employees command to recover the dropped table.

💡 Why This Matters

🌍 Real World

In real data warehouse management, accidental drops happen. Knowing how to recover dropped objects quickly prevents data loss and downtime.

💼 Career

Cloud data engineers and database administrators often need to recover dropped objects to maintain data integrity and availability.

1

Create the employees table

Write a SQL statement to create a table called employees with two columns: id as INTEGER and name as STRING.

Snowflake

-- Your code here to create the employees table

Need a hint?

Use CREATE TABLE employees (id INTEGER, name STRING); to create the table.

2

Drop the employees table

Write a SQL statement to drop the table named employees.

Snowflake

CREATE TABLE employees (id INTEGER, name STRING);
-- Your code here to drop the employees table

Need a hint?

Use DROP TABLE employees; to remove the table.

3

Recover the dropped employees table

Write a SQL statement to recover the dropped table employees using the UNDROP command.

Snowflake

CREATE TABLE employees (id INTEGER, name STRING);
DROP TABLE employees;
-- Your code here to undrop the employees table

Need a hint?

Use UNDROP TABLE employees; to recover the dropped table.

4

Verify the recovery of the employees table

Write a SQL statement to show the structure of the employees table to confirm it has been recovered.

Snowflake

CREATE TABLE employees (id INTEGER, name STRING);
DROP TABLE employees;
UNDROP TABLE employees;
-- Your code here to describe the employees table

Need a hint?

Use DESCRIBE TABLE employees; to check the table structure.