Snowflake SQL Tutorial 133 - The Catalog Listed My Dropped Table And UNDROP Said It Was Purged

Snowflake SQL · Episode 133

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

A small table with a few rows in it, so we can tell whether the data comes back along with the name.

Sample data
-- 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);
Query
SELECT COUNT(*)    AS ROWS_IN_THE_TABLE,
       SUM(AMOUNT) AS TOTAL_AMOUNT
FROM SALES_T133;
Result1 row · 2 columns
ROWS_IN_THE_TABLETOTAL_AMOUNT
4735.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.

Query
DROP TABLE SALES_T133;
Result1 row · 1 column
status
SALES_T133 successfully dropped.

Now let's try to select from it.

Query
SELECT COUNT(*) AS ROWS_IN_THE_TABLE FROM SALES_T133;
Query failed — Snowflake returned an error
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.

Query
SHOW TABLES HISTORY LIKE '%\\_T133' IN SCHEMA PUBLIC;
Result1 row · 28 columns · scroll →
created_onnamedatabase_nameschema_namekindcommentcluster_byrowsbytesownerretention_timedropped_onautomatic_clusteringchange_trackingsearch_optimizationsearch_optimization_progresssearch_optimization_bytesis_externalenable_schema_evolutionowner_role_typeis_eventis_hybridis_icebergis_dynamicis_immutableis_interactiverow_timestamperror_logging
2026-10-01 13:20:16.127000-07:00SALES_T133DEMO_DBPUBLICTABLE41024ACCOUNTADMIN12026-10-01 13:20:18.110000-07:00OFFOFFOFFNULLNULLNNROLENNNNNNOFFOFF

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.

The same result, as a query
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;
Result1 row · 4 columns
THE_TABLEHOW_MANY_ROWSDROPPED_ONTHE_KIND
SALES_T13342026-10-01 13:20:18.110000-07:00TABLE

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.

Query
UNDROP TABLE SALES_T133;
Result1 row · 1 column
status
Table SALES_T133 successfully restored.

successfully restored. Let's count it.

Query
SELECT COUNT(*)    AS ROWS_IN_THE_TABLE,
       SUM(AMOUNT) AS TOTAL_AMOUNT
FROM SALES_T133;
Result1 row · 2 columns
ROWS_IN_THE_TABLETOTAL_AMOUNT
4735.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.

Query
SHOW TABLES HISTORY LIKE '%\\_T133' IN SCHEMA PUBLIC;
Result1 row · 28 columns · scroll →
created_onnamedatabase_nameschema_namekindcommentcluster_byrowsbytesownerretention_timedropped_onautomatic_clusteringchange_trackingsearch_optimizationsearch_optimization_progresssearch_optimization_bytesis_externalenable_schema_evolutionowner_role_typeis_eventis_hybridis_icebergis_dynamicis_immutableis_interactiverow_timestamperror_logging
2026-10-01 13:20:16.127000-07:00SALES_T133DEMO_DBPUBLICTABLE41024ACCOUNTADMIN1NULLOFFOFFOFFNULLNULLNNROLENNNNNNOFFOFF
Counted
SELECT COUNT(*) AS ROWS_IN_THE_HISTORY_LISTING
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Result1 row · 1 column
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.

Query
UPDATE SALES_T133 SET AMOUNT = 1.00;
Result1 row · 2 columns
number of rows updatednumber of multi-joined rows updated
40

Every amount is now one. Let's drop it, and call that version A.

Query
DROP TABLE SALES_T133;
Result1 row · 1 column
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.

Query
CREATE TABLE SALES_T133 (SALE_ID INT, REGION VARCHAR(10), AMOUNT NUMBER(10,2));
Result1 row · 1 column
status
Table SALES_T133 successfully created.
Query
INSERT INTO SALES_T133 (SALE_ID, REGION, AMOUNT) VALUES (99, 'nowhere', 0.01);
Result1 row · 1 column
number of rows inserted
1

And let's drop that one too. Call it version B.

Query
DROP TABLE SALES_T133;
Result1 row · 1 column
status
SALES_T133 successfully dropped.

So now there are two dropped versions of one name. Let's look.

Query
SHOW TABLES HISTORY LIKE '%\\_T133' IN SCHEMA PUBLIC;
Result2 rows · 28 columns · scroll →
created_onnamedatabase_nameschema_namekindcommentcluster_byrowsbytesownerretention_timedropped_onautomatic_clusteringchange_trackingsearch_optimizationsearch_optimization_progresssearch_optimization_bytesis_externalenable_schema_evolutionowner_role_typeis_eventis_hybridis_icebergis_dynamicis_immutableis_interactiverow_timestamperror_logging
2026-10-01 13:20:16.127000-07:00SALES_T133DEMO_DBPUBLICTABLE41024ACCOUNTADMIN12026-10-01 13:20:21.894000-07:00OFFOFFOFFNULLNULLNNROLENNNNNNOFFOFF
2026-10-01 13:20:22.008000-07:00SALES_T133DEMO_DBPUBLICTABLE11024ACCOUNTADMIN12026-10-01 13:20:23.327000-07:00OFFOFFOFFNULLNULLNNROLENNNNNNOFFOFF
Counted
SELECT COUNT(*) AS DROPPED_VERSIONS_OF_THIS_NAME
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Result1 row · 1 column
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.

Query
UNDROP TABLE SALES_T133;
Result1 row · 1 column
status
Table SALES_T133 successfully restored.
And what is in it
SELECT SALE_ID AS SALE_ID, REGION AS REGION, AMOUNT AS AMOUNT
FROM SALES_T133
ORDER BY SALE_ID;
Result1 row · 3 columns
SALE_IDREGIONAMOUNT
99nowhere0.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.

Query
UNDROP TABLE SALES_T133;
Query failed — Snowflake returned an error
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.

One step back, and only one

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.

Clear the old name
DROP TABLE IF EXISTS SALES_T133;
Result1 row · 1 column
status
SALES_T133 successfully dropped.
A table
CREATE OR REPLACE TABLE TAKEN_T133 (SALE_ID INT, NOTE VARCHAR(20));
Result1 row · 1 column
status
Table TAKEN_T133 successfully created.
The old row
INSERT INTO TAKEN_T133 (SALE_ID, NOTE) VALUES (1, 'the old one');
Result1 row · 1 column
number of rows inserted
1
Drop it
DROP TABLE TAKEN_T133;
Result1 row · 1 column
status
TAKEN_T133 successfully dropped.

Now a new table with the same name, and a row that says it is the new one.

Query
CREATE TABLE TAKEN_T133 (SALE_ID INT, NOTE VARCHAR(20));
Result1 row · 1 column
status
Table TAKEN_T133 successfully created.
The new row
INSERT INTO TAKEN_T133 (SALE_ID, NOTE) VALUES (2, 'the new one');
Result1 row · 1 column
number of rows inserted
1

So there is a dropped version waiting and the name is in use. Let's undrop.

Query
UNDROP TABLE TAKEN_T133;
Query failed — Snowflake returned an error
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?

Query
SELECT SALE_ID AS SALE_ID, NOTE AS NOTE FROM TAKEN_T133 ORDER BY SALE_ID;
Result1 row · 2 columns
SALE_IDNOTE
2the 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.

Query
ALTER TABLE TAKEN_T133 RENAME TO TAKEN_NEW_T133;
Result1 row · 1 column
status
Statement executed successfully.

Now the name is free. Let's try again.

Query
UNDROP TABLE TAKEN_T133;
Result1 row · 1 column
status
Table TAKEN_T133 successfully restored.

Restored. And now both of them should exist.

Query
SELECT (SELECT NOTE FROM TAKEN_T133)     AS WHAT_THE_OLD_ONE_SAYS,
       (SELECT NOTE FROM TAKEN_NEW_T133) AS WHAT_THE_NEW_ONE_SAYS;
Result1 row · 2 columns
WHAT_THE_OLD_ONE_SAYSWHAT_THE_NEW_ONE_SAYS
the old onethe 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.

Query
CREATE OR REPLACE TABLE NOGOINGBACK_T133 (SALE_ID INT, NOTE VARCHAR(20))
DATA_RETENTION_TIME_IN_DAYS = 0;
Result1 row · 1 column
status
Table NOGOINGBACK_T133 successfully created.
One row in it
INSERT INTO NOGOINGBACK_T133 (SALE_ID, NOTE) VALUES (1, 'gone forever?');
Result1 row · 1 column
number of rows inserted
1
And its retention
SELECT TABLE_NAME     AS THE_TABLE,
       RETENTION_TIME AS RETENTION_DAYS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'NOGOINGBACK_T133';
Result1 row · 2 columns
THE_TABLERETENTION_DAYS
NOGOINGBACK_T1330

Zero. Let's drop it and find out.

Query
DROP TABLE NOGOINGBACK_T133;
Result1 row · 1 column
status
NOGOINGBACK_T133 successfully dropped.

First question: is it even in the history?

Query
SHOW TABLES HISTORY LIKE 'NOGOINGBACK%' IN SCHEMA PUBLIC;
Result1 row · 28 columns · scroll →
created_onnamedatabase_nameschema_namekindcommentcluster_byrowsbytesownerretention_timedropped_onautomatic_clusteringchange_trackingsearch_optimizationsearch_optimization_progresssearch_optimization_bytesis_externalenable_schema_evolutionowner_role_typeis_eventis_hybridis_icebergis_dynamicis_immutableis_interactiverow_timestamperror_logging
2026-10-01 13:20:29.653000-07:00NOGOINGBACK_T133DEMO_DBPUBLICTABLE11024ACCOUNTADMIN02026-10-01 13:20:31.743000-07:00OFFOFFOFFNULLNULLNNROLENNNNNNOFFOFF
Counted
SELECT COUNT(*) AS ROWS_IN_THE_HISTORY_LISTING
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Result1 row · 1 column
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.

Query
UNDROP TABLE NOGOINGBACK_T133;
Query failed — Snowflake returned an error
090025 (02000): Table NOGOINGBACK_T133 did not exist or was purged.

Table NOGOINGBACK_T133 did not exist or was purged.

A listing is a record, not a promise

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.

Query
UNDROP TABLE NEVER_EXISTED_T133;
Query failed — Snowflake returned an error
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.

A schema
CREATE OR REPLACE SCHEMA DROPZONE_T133;
Result1 row · 1 column
status
Schema DROPZONE_T133 successfully created.
A table in it
CREATE OR REPLACE TABLE DROPZONE_T133.INSIDE_T133 (SALE_ID INT, NOTE VARCHAR(20));
Result1 row · 1 column
status
Table INSIDE_T133 successfully created.
Two rows in that
INSERT INTO DROPZONE_T133.INSIDE_T133 (SALE_ID, NOTE)
VALUES (1, 'inside'), (2, 'also inside');
Result1 row · 1 column
number of rows inserted
2

Now let's drop the whole schema, table and all.

Query
DROP SCHEMA DROPZONE_T133;
Result1 row · 1 column
status
DROPZONE_T133 successfully dropped.
SHOW SCHEMAS HISTORY
SHOW SCHEMAS HISTORY LIKE 'DROPZONE%' IN DATABASE DEMO_DB;
Result1 row · 16 columns · scroll →
created_onnameis_defaultis_currentdatabase_nameownercommentoptionsretention_timedropped_onowner_role_typeclassification_profile_databaseclassification_profile_schemaclassification_profileobject_visibilityis_nested
2026-10-01 13:20:32.947000-07:00DROPZONE_T133NNDEMO_DBACCOUNTADMIN12026-10-01 13:20:34.635000-07:00ROLENULLNULLNULLNULLfalse

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.

Query
UNDROP SCHEMA DROPZONE_T133;
Result1 row · 1 column
status
Schema DROPZONE_T133 successfully restored.

Restored. And here is the question that matters. Did the table inside come back with it?

Query
SELECT COUNT(*) AS ROWS_INSIDE FROM DROPZONE_T133.INSIDE_T133;
Result1 row · 1 column
ROWS_INSIDE
2

Two rows. It did.

And the catalog agrees
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';
Result1 row · 3 columns
THE_SCHEMATHE_TABLEHOW_MANY
DROPZONE_T133INSIDE_T1332

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.

Query
UNDROP SCHEMA NEVER_EXISTED_SCHEMA_T133;
Query failed — Snowflake returned an error
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.

Query
DROP TABLE NEVER_EXISTED_T133;
Query failed — Snowflake returned an error
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.

Teardown
DROP SCHEMA IF EXISTS DROPZONE_T133;
Result1 row · 1 column
status
DROPZONE_T133 successfully dropped.
Teardown
DROP TABLE IF EXISTS TAKEN_T133;
Result1 row · 1 column
status
TAKEN_T133 successfully dropped.
Teardown
DROP TABLE IF EXISTS TAKEN_NEW_T133;
Result1 row · 1 column
status
TAKEN_NEW_T133 successfully dropped.
Teardown
DROP TABLE IF EXISTS NOGOINGBACK_T133;
Result1 row · 1 column
status
Drop statement executed successfully (NOGOINGBACK_T133 already dropped).
Teardown
DROP TABLE IF EXISTS SALES_T133;
Result1 row · 1 column
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.

Query
SELECT CURRENT_DATABASE() AS THE_DATABASE, CURRENT_SCHEMA() AS THE_SCHEMA;
Result1 row · 2 columns
THE_DATABASETHE_SCHEMA
DEMO_DBPUBLIC
And the check
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;
Result1 row · 5 columns
THE_DATABASETHE_SCHEMAPROBE_TABLES_LEFTPROBE_SCHEMAS_LEFTDEMO_DB_IS_READABLE
DEMO_DBPUBLIC0010

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 HISTORY keeps a dropped table's row count and the exact moment it was dropped.
  • UNDROP restores the rows, not just the name. Four rows and 735.75 went, and four rows and 735.75 came back.
  • After the restore the history listing has a real NULL in dropped_on, which is how you tell a live table from a record of a dropped one.
  • A blank cell in a SHOW result is not always a NULL. In the history listing the comment is an empty string and the search optimization columns are real NULLs.
  • When a name has been dropped twice, UNDROP gives 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.
  • UNDROP onto a name in use is refused with 002002, and the live table is left completely untouched.
  • Rename the live table first and then UNDROP works, leaving both versions side by side.
  • A table created with DATA_RETENTION_TIME_IN_DAYS = 0 still appears in SHOW TABLES HISTORY after it is dropped.
  • UNDROP on that table says 090025, 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 SCHEMA brings the tables inside it back, with their rows.
Next in this series

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.