In this post we are going to cover UNDROP in Snowflake.
UNDROP brings a dropped table back. It is one statement with no options, and it is not a recycle bin you can rummage through. It gives you back one thing, and there are three separate ways for it to tell you no.
We will start by dropping a table on purpose and build it up step by step. Every query below was run on a real Snowflake account, and every result you see is the output it gave back.
Episode 133 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.
- Let's create some sample data
- Let's drop it and see what is left
- But the catalog remembers it
- Now let's undrop it
- Which one comes back when a name has been dropped twice?
- So can we have the older one as well?
- Does UNDROP ever overwrite the live table?
- Let's move the live one out of the way
- What happens when the table had no retention at all?
- And a name that was never dropped at all?
- Can we undrop a whole schema?
- One last thing: the last refusals, and the teardown
Let's create some sample data
A small table with a few rows in it, so we can tell whether the data comes back along with the name.
-- Sample data for this post.
-- A small table we are going to lose on purpose.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;
CREATE OR REPLACE TABLE SALES_T133 (
SALE_ID INT,
REGION VARCHAR(10),
AMOUNT NUMBER(10,2)
);
INSERT INTO SALES_T133 (SALE_ID, REGION, AMOUNT)
VALUES (1, 'north', 100.00), (2, 'south', 250.50),
(3, 'east', 75.25), (4, 'west', 310.00);SELECT COUNT(*) AS ROWS_IN_THE_TABLE,
SUM(AMOUNT) AS TOTAL_AMOUNT
FROM SALES_T133;| ROWS_IN_THE_TABLE | TOTAL_AMOUNT |
|---|---|
| 4 | 735.75 |
Four rows and 735.75. Those are the two numbers to watch.
Let's drop it and see what is left
DROP TABLE. No warning and no confirmation.
DROP TABLE SALES_T133;| status |
|---|
| SALES_T133 successfully dropped. |
Now let's try to select from it.
SELECT COUNT(*) AS ROWS_IN_THE_TABLE FROM SALES_T133;002003 (42S02): SQL compilation error: Object 'SALES_T133' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on TABLE SALES_T133.
Object 'SALES_T133' does not exist or not authorized. As far as a query is concerned, the table is gone.
But the catalog remembers it
SHOW TABLES HISTORY is the same as SHOW TABLES except that it also lists the tables that have been dropped.
SHOW TABLES HISTORY LIKE '%\\_T133' IN SCHEMA PUBLIC;| created_on | name | database_name | schema_name | kind | comment | cluster_by | rows | bytes | owner | retention_time | dropped_on | automatic_clustering | change_tracking | search_optimization | search_optimization_progress | search_optimization_bytes | is_external | enable_schema_evolution | owner_role_type | is_event | is_hybrid | is_iceberg | is_dynamic | is_immutable | is_interactive | row_timestamp | error_logging |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2026-10-01 13:20:16.127000-07:00 | SALES_T133 | DEMO_DB | PUBLIC | TABLE | 4 | 1024 | ACCOUNTADMIN | 1 | 2026-10-01 13:20:18.110000-07:00 | OFF | OFF | OFF | NULL | NULL | N | N | ROLE | N | N | N | N | N | N | OFF | OFF |
Twenty eight columns, which is a lot to read at once. The four that matter here are the name, the row count, the moment it was dropped, and the kind.
While that grid is in front of you, have a look at the difference between its blank-looking cells. The comment and cluster_by cells are empty strings and the grid marks them as empty. search_optimization_progress and search_optimization_bytes are real NULLs. Both look like nothing and they are not the same thing.
Let's pull the four columns we care about out of that result.
SELECT "name" AS THE_TABLE,
"rows" AS HOW_MANY_ROWS,
"dropped_on" AS DROPPED_ON,
"kind" AS THE_KIND
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
ORDER BY THE_TABLE;| THE_TABLE | HOW_MANY_ROWS | DROPPED_ON | THE_KIND |
|---|---|---|---|
| SALES_T133 | 4 | 2026-10-01 13:20:18.110000-07:00 | TABLE |
There it is. The name, four rows, the exact moment it was dropped, and the word TABLE.
So the catalog is still holding a record of the table, including how many rows were in it when it went.
Now let's undrop it
One statement, and no options to get wrong.
UNDROP TABLE SALES_T133;| status |
|---|
| Table SALES_T133 successfully restored. |
successfully restored. Let's count it.
SELECT COUNT(*) AS ROWS_IN_THE_TABLE,
SUM(AMOUNT) AS TOTAL_AMOUNT
FROM SALES_T133;| ROWS_IN_THE_TABLE | TOTAL_AMOUNT |
|---|---|
| 4 | 735.75 |
Four rows and 735.75, which is exactly what it held before we dropped it. The rows came with it. This is not a new empty table wearing the old name.
And the history listing is worth asking again.
SHOW TABLES HISTORY LIKE '%\\_T133' IN SCHEMA PUBLIC;| created_on | name | database_name | schema_name | kind | comment | cluster_by | rows | bytes | owner | retention_time | dropped_on | automatic_clustering | change_tracking | search_optimization | search_optimization_progress | search_optimization_bytes | is_external | enable_schema_evolution | owner_role_type | is_event | is_hybrid | is_iceberg | is_dynamic | is_immutable | is_interactive | row_timestamp | error_logging |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2026-10-01 13:20:16.127000-07:00 | SALES_T133 | DEMO_DB | PUBLIC | TABLE | 4 | 1024 | ACCOUNTADMIN | 1 | NULL | OFF | OFF | OFF | NULL | NULL | N | N | ROLE | N | N | N | N | N | N | OFF | OFF |
SELECT COUNT(*) AS ROWS_IN_THE_HISTORY_LISTING
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));| ROWS_IN_THE_HISTORY_LISTING |
|---|
| 1 |
One row, and its dropped_on cell is a real NULL now. That is the live table again rather than a record of a dropped one.
Which one comes back when a name has been dropped twice?
This is the question we did not know the answer to. First let's change the table, so the two versions will be easy to tell apart.
UPDATE SALES_T133 SET AMOUNT = 1.00;| number of rows updated | number of multi-joined rows updated |
|---|---|
| 4 | 0 |
Every amount is now one. Let's drop it, and call that version A.
DROP TABLE SALES_T133;| status |
|---|
| SALES_T133 successfully dropped. |
Now a brand new table with exactly the same name, and one row in it that could not be mistaken for anything else.
CREATE TABLE SALES_T133 (SALE_ID INT, REGION VARCHAR(10), AMOUNT NUMBER(10,2));| status |
|---|
| Table SALES_T133 successfully created. |
INSERT INTO SALES_T133 (SALE_ID, REGION, AMOUNT) VALUES (99, 'nowhere', 0.01);| number of rows inserted |
|---|
| 1 |
And let's drop that one too. Call it version B.
DROP TABLE SALES_T133;| status |
|---|
| SALES_T133 successfully dropped. |
So now there are two dropped versions of one name. Let's look.
SHOW TABLES HISTORY LIKE '%\\_T133' IN SCHEMA PUBLIC;| created_on | name | database_name | schema_name | kind | comment | cluster_by | rows | bytes | owner | retention_time | dropped_on | automatic_clustering | change_tracking | search_optimization | search_optimization_progress | search_optimization_bytes | is_external | enable_schema_evolution | owner_role_type | is_event | is_hybrid | is_iceberg | is_dynamic | is_immutable | is_interactive | row_timestamp | error_logging |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2026-10-01 13:20:16.127000-07:00 | SALES_T133 | DEMO_DB | PUBLIC | TABLE | 4 | 1024 | ACCOUNTADMIN | 1 | 2026-10-01 13:20:21.894000-07:00 | OFF | OFF | OFF | NULL | NULL | N | N | ROLE | N | N | N | N | N | N | OFF | OFF | ||
| 2026-10-01 13:20:22.008000-07:00 | SALES_T133 | DEMO_DB | PUBLIC | TABLE | 1 | 1024 | ACCOUNTADMIN | 1 | 2026-10-01 13:20:23.327000-07:00 | OFF | OFF | OFF | NULL | NULL | N | N | ROLE | N | N | N | N | N | N | OFF | OFF |
SELECT COUNT(*) AS DROPPED_VERSIONS_OF_THIS_NAME
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));| DROPPED_VERSIONS_OF_THIS_NAME |
|---|
| 2 |
Two rows in the listing, both called SALES_T133, each with its own dropped_on. One of them says four rows and the other says one row, which is how we can tell version A from version B.
Now let's undrop, and see which one arrives.
UNDROP TABLE SALES_T133;| status |
|---|
| Table SALES_T133 successfully restored. |
SELECT SALE_ID AS SALE_ID, REGION AS REGION, AMOUNT AS AMOUNT
FROM SALES_T133
ORDER BY SALE_ID;| SALE_ID | REGION | AMOUNT |
|---|---|---|
| 99 | nowhere | 0.01 |
One row, 99, nowhere, 0.01. That is version B, the most recent one. Not the four row one.
So can we have the older one as well?
Version A is still listed in the history. Let's ask for it.
UNDROP TABLE SALES_T133;002002 (42710): SQL compilation error: Object 'SALES_T133' already exists.
Object 'SALES_T133' already exists. And it does, because we just restored version B into that name.
So version A is in the history and unreachable, for as long as the name is taken.
UNDROP is not a list you choose from. There is no clause for naming which version you want. It is one step back, and only one.
Does UNDROP ever overwrite the live table?
That is the condition worth being completely sure about, so let's set it up on its own with a table whose rows say which version they are.
DROP TABLE IF EXISTS SALES_T133;| status |
|---|
| SALES_T133 successfully dropped. |
CREATE OR REPLACE TABLE TAKEN_T133 (SALE_ID INT, NOTE VARCHAR(20));| status |
|---|
| Table TAKEN_T133 successfully created. |
INSERT INTO TAKEN_T133 (SALE_ID, NOTE) VALUES (1, 'the old one');| number of rows inserted |
|---|
| 1 |
DROP TABLE TAKEN_T133;| status |
|---|
| TAKEN_T133 successfully dropped. |
Now a new table with the same name, and a row that says it is the new one.
CREATE TABLE TAKEN_T133 (SALE_ID INT, NOTE VARCHAR(20));| status |
|---|
| Table TAKEN_T133 successfully created. |
INSERT INTO TAKEN_T133 (SALE_ID, NOTE) VALUES (2, 'the new one');| number of rows inserted |
|---|
| 1 |
So there is a dropped version waiting and the name is in use. Let's undrop.
UNDROP TABLE TAKEN_T133;002002 (42710): SQL compilation error: Object 'TAKEN_T133' already exists.
Refused, with the same already-exists error as before. Now the part that matters. Is the live table alright?
SELECT SALE_ID AS SALE_ID, NOTE AS NOTE FROM TAKEN_T133 ORDER BY SALE_ID;| SALE_ID | NOTE |
|---|---|
| 2 | the new one |
There is the new row, exactly as we left it. UNDROP refused and changed nothing. It does not overwrite anything.
Let's move the live one out of the way
If the only problem is that the name is taken, then the way out is to free the name.
ALTER TABLE TAKEN_T133 RENAME TO TAKEN_NEW_T133;| status |
|---|
| Statement executed successfully. |
Now the name is free. Let's try again.
UNDROP TABLE TAKEN_T133;| status |
|---|
| Table TAKEN_T133 successfully restored. |
Restored. And now both of them should exist.
SELECT (SELECT NOTE FROM TAKEN_T133) AS WHAT_THE_OLD_ONE_SAYS,
(SELECT NOTE FROM TAKEN_NEW_T133) AS WHAT_THE_NEW_ONE_SAYS;| WHAT_THE_OLD_ONE_SAYS | WHAT_THE_NEW_ONE_SAYS |
|---|---|
| the old one | the new one |
The old one and the new one, side by side, under two different names. That is the recovery you actually want: nothing lost, and a chance to compare them before you decide anything.
What happens when the table had no retention at all?
Episode 132 showed that a retention of zero turns time travel off. The question here is whether it turns UNDROP off too.
CREATE OR REPLACE TABLE NOGOINGBACK_T133 (SALE_ID INT, NOTE VARCHAR(20))
DATA_RETENTION_TIME_IN_DAYS = 0;| status |
|---|
| Table NOGOINGBACK_T133 successfully created. |
INSERT INTO NOGOINGBACK_T133 (SALE_ID, NOTE) VALUES (1, 'gone forever?');| number of rows inserted |
|---|
| 1 |
SELECT TABLE_NAME AS THE_TABLE,
RETENTION_TIME AS RETENTION_DAYS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'NOGOINGBACK_T133';| THE_TABLE | RETENTION_DAYS |
|---|---|
| NOGOINGBACK_T133 | 0 |
Zero. Let's drop it and find out.
DROP TABLE NOGOINGBACK_T133;| status |
|---|
| NOGOINGBACK_T133 successfully dropped. |
First question: is it even in the history?
SHOW TABLES HISTORY LIKE 'NOGOINGBACK%' IN SCHEMA PUBLIC;| created_on | name | database_name | schema_name | kind | comment | cluster_by | rows | bytes | owner | retention_time | dropped_on | automatic_clustering | change_tracking | search_optimization | search_optimization_progress | search_optimization_bytes | is_external | enable_schema_evolution | owner_role_type | is_event | is_hybrid | is_iceberg | is_dynamic | is_immutable | is_interactive | row_timestamp | error_logging |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2026-10-01 13:20:29.653000-07:00 | NOGOINGBACK_T133 | DEMO_DB | PUBLIC | TABLE | 1 | 1024 | ACCOUNTADMIN | 0 | 2026-10-01 13:20:31.743000-07:00 | OFF | OFF | OFF | NULL | NULL | N | N | ROLE | N | N | N | N | N | N | OFF | OFF |
SELECT COUNT(*) AS ROWS_IN_THE_HISTORY_LISTING
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));| ROWS_IN_THE_HISTORY_LISTING |
|---|
| 1 |
One row. It is listed, with a row count, a retention of zero and the moment it was dropped. Which looks like good news.
So let's undrop it.
UNDROP TABLE NOGOINGBACK_T133;090025 (02000): Table NOGOINGBACK_T133 did not exist or was purged.
Table NOGOINGBACK_T133 did not exist or was purged.
Read those two results together. The catalog lists the table, and the table is already gone. That listing is not a promise that you can have it back. It is a record that something was there.
On their own, each of those two grids is misleading.
And a name that was never dropped at all?
Let's undrop a name we have never used for anything.
UNDROP TABLE NEVER_EXISTED_T133;090025 (02000): Table NEVER_EXISTED_T133 did not exist or was purged.
The same code, and the same wording, with a different name in it. did not exist or was purged.
So a table you destroyed on purpose and a name you have never used give you the same answer. The error cannot tell you which of the two you are looking at, and the history listing cannot either: it showed the purged one and it would show nothing at all for the name that never existed.
Can we undrop a whole schema?
We can, and the interesting part is what comes back with it. Let's make a schema with a table inside it.
CREATE OR REPLACE SCHEMA DROPZONE_T133;| status |
|---|
| Schema DROPZONE_T133 successfully created. |
CREATE OR REPLACE TABLE DROPZONE_T133.INSIDE_T133 (SALE_ID INT, NOTE VARCHAR(20));| status |
|---|
| Table INSIDE_T133 successfully created. |
INSERT INTO DROPZONE_T133.INSIDE_T133 (SALE_ID, NOTE)
VALUES (1, 'inside'), (2, 'also inside');| number of rows inserted |
|---|
| 2 |
Now let's drop the whole schema, table and all.
DROP SCHEMA DROPZONE_T133;| status |
|---|
| DROPZONE_T133 successfully dropped. |
SHOW SCHEMAS HISTORY LIKE 'DROPZONE%' IN DATABASE DEMO_DB;| created_on | name | is_default | is_current | database_name | owner | comment | options | retention_time | dropped_on | owner_role_type | classification_profile_database | classification_profile_schema | classification_profile | object_visibility | is_nested |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2026-10-01 13:20:32.947000-07:00 | DROPZONE_T133 | N | N | DEMO_DB | ACCOUNTADMIN | 1 | 2026-10-01 13:20:34.635000-07:00 | ROLE | NULL | NULL | NULL | NULL | false |
Sixteen columns this time rather than twenty eight, and there is our schema with the moment it was dropped.
UNDROP SCHEMA is the same shape of statement as the table one.
UNDROP SCHEMA DROPZONE_T133;| status |
|---|
| Schema DROPZONE_T133 successfully restored. |
Restored. And here is the question that matters. Did the table inside come back with it?
SELECT COUNT(*) AS ROWS_INSIDE FROM DROPZONE_T133.INSIDE_T133;| ROWS_INSIDE |
|---|
| 2 |
Two rows. It did.
SELECT TABLE_SCHEMA AS THE_SCHEMA, TABLE_NAME AS THE_TABLE, ROW_COUNT AS HOW_MANY
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DROPZONE_T133';| THE_SCHEMA | THE_TABLE | HOW_MANY |
|---|---|---|
| DROPZONE_T133 | INSIDE_T133 | 2 |
The schema, the table inside it, and its row count. So undropping a schema does not hand you an empty shell. Everything that was in it comes back.
One last thing: the last refusals, and the teardown
A schema that was never dropped behaves like a table that was never dropped.
UNDROP SCHEMA NEVER_EXISTED_SCHEMA_T133;090025 (02000): Schema NEVER_EXISTED_SCHEMA_T133 did not exist or was purged.
The same code again, with the word Schema where the table version said Table.
And dropping something that is not there is a different error altogether.
DROP TABLE NEVER_EXISTED_T133;002003 (42S02): SQL compilation error: Table 'DEMO_DB.PUBLIC.NEVER_EXISTED_T133' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on TABLE DEMO_DB.PUBLIC.NEVER_EXISTED_T133.
A plain compilation error about an object that does not exist. DROP and UNDROP do not share a message for the same missing name.
Then everything goes.
DROP SCHEMA IF EXISTS DROPZONE_T133;| status |
|---|
| DROPZONE_T133 successfully dropped. |
DROP TABLE IF EXISTS TAKEN_T133;| status |
|---|
| TAKEN_T133 successfully dropped. |
DROP TABLE IF EXISTS TAKEN_NEW_T133;| status |
|---|
| TAKEN_NEW_T133 successfully dropped. |
DROP TABLE IF EXISTS NOGOINGBACK_T133;| status |
|---|
| Drop statement executed successfully (NOGOINGBACK_T133 already dropped). |
DROP TABLE IF EXISTS SALES_T133;| status |
|---|
| Drop statement executed successfully (SALES_T133 already dropped). |
This episode created and dropped a schema, so it is worth checking where the session ended up.
SELECT CURRENT_DATABASE() AS THE_DATABASE, CURRENT_SCHEMA() AS THE_SCHEMA;| THE_DATABASE | THE_SCHEMA |
|---|---|
| DEMO_DB | PUBLIC |
SELECT CURRENT_DATABASE() AS THE_DATABASE,
CURRENT_SCHEMA() AS THE_SCHEMA,
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
WHERE RIGHT(TABLE_NAME, 5) = '_T133') AS PROBE_TABLES_LEFT,
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME LIKE '%_T133') AS PROBE_SCHEMAS_LEFT,
(SELECT COUNT(*) FROM CUSTOMERS) AS DEMO_DB_IS_READABLE;| THE_DATABASE | THE_SCHEMA | PROBE_TABLES_LEFT | PROBE_SCHEMAS_LEFT | DEMO_DB_IS_READABLE |
|---|---|---|---|---|
| DEMO_DB | PUBLIC | 0 | 0 | 10 |
Still in PUBLIC, no probe tables left, no probe schemas left, and the demo database still reads. Back where we started.
Key points
SHOW TABLES HISTORYkeeps a dropped table's row count and the exact moment it was dropped.UNDROPrestores the rows, not just the name. Four rows and735.75went, and four rows and735.75came back.- After the restore the history listing has a real
NULLindropped_on, which is how you tell a live table from a record of a dropped one. - A blank cell in a
SHOWresult is not always aNULL. In the history listing the comment is an empty string and the search optimization columns are realNULLs. - When a name has been dropped twice,
UNDROPgives you the most recent version. - The older version stays listed and unreachable, because the name is taken. There is no clause for choosing a version.
UNDROPonto a name in use is refused with002002, and the live table is left completely untouched.- Rename the live table first and then
UNDROPworks, leaving both versions side by side. - A table created with
DATA_RETENTION_TIME_IN_DAYS = 0still appears inSHOW TABLES HISTORYafter it is dropped. UNDROPon that table says090025, did not exist or was purged. The listing is a record, not a promise.- A name that was never dropped gives the identical code and wording, so purged and never-existed are indistinguishable.
UNDROP SCHEMAbrings the tables inside it back, with their rows.
Episode 134 — zero copy cloning. A second table that shares its storage with the first one until one of them changes, what that means for a clone of a clone, and what gets copied along with the rows.
Every query and every result grid on this page was captured from a live Snowflake account (version 10.35.101) running the demo script for this episode. This episode creates and drops a schema, and the last two grids prove the session is back in PUBLIC with nothing of its own left behind. Every count in the text is read off one of the grids on this page. Nothing is timed and no performance claims are made anywhere in this series. Behaviour follows the Snowflake UNDROP TABLE documentation.



No comments:
Post a Comment
Note: Only a member of this blog may post a comment.