Snowflake SQL Tutorial 102 - A TEMPORARY Table Can Hide a PERMANENT One (Qualifying Won't Save You)

Snowflake SQL · Episode 102

In this post we are going to cover the three kinds of table in Snowflake: PERMANENT, TRANSIENT and TEMPORARY.

They are three words you can put in front of CREATE TABLE. A permanent table is what you get when you say nothing. A transient table keeps a short recovery window. A temporary table lives in your session and goes away with it.

We will start with one of each and ask the catalog what it thinks they are. Then we will do the thing that gives this post its title: put a temporary table's name on top of a permanent table's name, in the same session, and watch where a perfectly ordinary INSERT lands.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 102 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's create one table of each kind
2. Now let's ask the catalog what they are
3. Let's ask the transient table for a longer window
4. Can we get a dropped transient table back?
5. Can a transient table become permanent?
6. Now let's give a temporary table a permanent table's name
7. What happens when we spell the name out in full?
8. So where does an INSERT land?
9. Let's ask the catalog which table is which
10. Now let's drop the temporary one
11. One last thing: give temporary tables names you would never use

Let's create one table of each kind

Three tables with the same two columns. The only difference between them is one word in front of TABLE, and one of them has no word at all.

Copy this into a worksheet and run it. Run it in a single session and keep that session open, because the temporary table only exists for as long as the session does.

Sample data

-- Sample data for this post.
-- Three tables that differ by exactly one word, and one row in each.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE PERM_T102 (ID INT, NOTE VARCHAR);
CREATE OR REPLACE TRANSIENT TABLE TRANS_T102 (ID INT, NOTE VARCHAR);
CREATE OR REPLACE TEMPORARY TABLE TEMP_T102 (ID INT, NOTE VARCHAR);

INSERT INTO PERM_T102  (ID, NOTE) SELECT 1, 'permanent';
INSERT INTO TRANS_T102 (ID, NOTE) SELECT 2, 'transient';
INSERT INTO TEMP_T102  (ID, NOTE) SELECT 3, 'temporary';

Now let's check that all three of them are working the same way.

Query

SELECT (SELECT COUNT(*) FROM PERM_T102)  AS PERMANENT_ROWS,
       (SELECT COUNT(*) FROM TRANS_T102) AS TRANSIENT_ROWS,
       (SELECT COUNT(*) FROM TEMP_T102)  AS TEMPORARY_ROWS;

Result1 row · 3 columns

PERMANENT_ROWS	TRANSIENT_ROWS	TEMPORARY_ROWS
1	1	1

One row in each. Nothing so far tells us these are different kinds of table at all. They accept rows and they give them back.

Now let's ask the catalog what they are

The difference is recorded, not guessed at. INFORMATION_SCHEMA.TABLES has a TABLE_TYPE, an IS_TRANSIENT flag and a RETENTION_TIME for every table.

Query

SELECT TABLE_NAME, TABLE_TYPE, IS_TRANSIENT, RETENTION_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND RIGHT(TABLE_NAME, 5) = '_T102'
ORDER BY TABLE_NAME;

Result3 rows · 4 columns

TABLE_NAME	TABLE_TYPE	IS_TRANSIENT	RETENTION_TIME
PERM_T102	BASE TABLE	NO	1
TEMP_T102	LOCAL TEMPORARY	NULL	0
TRANS_T102	BASE TABLE	YES	1

Three rows, and three different answers.

• PERM_T102 is a BASE TABLE, IS_TRANSIENT is NO.
• TRANS_T102 is also a BASE TABLE, and IS_TRANSIENT is YES.
• TEMP_T102 gets a table type of its own, LOCAL TEMPORARY, and its IS_TRANSIENT is NULL.

So a transient table is an ordinary base table with a flag set on it. A temporary table is something the catalog files separately.

Have a look at the RETENTION_TIME column while we are here. The permanent and the transient table both say one. The temporary table says zero, and zero means no Time Travel.

Now let's ask the same question with SHOW TABLES

SHOW TABLES answers from a different place, and it is worth running both.

Query

SHOW TABLES LIKE '%_T102';

Result3 rows · 27 columns · scroll →

Scroll sideways for the retention_time column.

created_on	name	database_name	schema_name	kind	comment	cluster_by	rows	bytes	owner	retention_time	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-09-28 13:03:51.357000-07:00	PERM_T102	DEMO_DB	PUBLIC	TABLE			1	1024	ACCOUNTADMIN	1	OFF	OFF	OFF	NULL	NULL	N	N	ROLE	N	N	N	N	N	N	OFF	OFF
2026-09-28 13:03:51.916000-07:00	TEMP_T102	DEMO_DB	PUBLIC	TEMPORARY			1	1024	ACCOUNTADMIN	1	OFF	OFF	OFF	NULL	NULL	N	N	ROLE	N	N	N	N	N	N	OFF	OFF
2026-09-28 13:03:51.617000-07:00	TRANS_T102	DEMO_DB	PUBLIC	TRANSIENT			1	1024	ACCOUNTADMIN	1	OFF	OFF	OFF	NULL	NULL	N	N	ROLE	N	N	N	N	N	N	OFF	OFF

The kind column here reads TABLE, TEMPORARY and TRANSIENT, which is friendlier than the two columns we needed in the view. Twenty-seven columns come back, so most of this grid is other business.

Two details in that grid are easy to misread. The comment and cluster_by cells are empty, which is not the same thing as NULL, and the two search_optimization cells really are NULL. The grid above marks them differently, so you can tell which is which.

The two catalogs disagree here

Now compare retention_time for TEMP_T102 in the two grids. The view said zero. SHOW TABLES says one. Those are the same table, read seconds apart, and the two catalogs do not agree about it.
Nothing captured on this page settles which number the engine acts on, so I am not going to tell you. What the page does settle is further down, and it does not depend on either number: a row written into a temporary table goes when the table goes.

Let's ask the transient table for a longer window

Before we ask for anything, let's see what this account allows by default.

Query

SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS' IN ACCOUNT;

Result1 row · 6 columns · scroll →

key	value	default	level	description	type
DATA_RETENTION_TIME_IN_DAYS	1	1		number of days to retain the old version of deleted/updated data	NUMBER

The value and the default are the same, and the level cell came back empty, which is how SHOW PARAMETERS marks a parameter nobody has overridden.

Now let's ask the transient table to keep two days instead.

Query

ALTER TABLE TRANS_T102 SET DATA_RETENTION_TIME_IN_DAYS = 2;

Query failed — Snowflake returned an error

001008 (22023): SQL compilation error:
invalid value [2] for parameter 'DATA_RETENTION_TIME_IN_DAYS'

Refused, at compile time, and the message is about the value we passed rather than about the table.

So let's ask for the number it will take.

Query

ALTER TABLE TRANS_T102 SET DATA_RETENTION_TIME_IN_DAYS = 1;

Result1 row · 1 column

status
Statement executed successfully.

Accepted. One day is the ceiling for a transient table.

That on its own does not prove much, because the account default is also one day. So let's make exactly the same request of the permanent table.

Query

ALTER TABLE PERM_T102 SET DATA_RETENTION_TIME_IN_DAYS = 2;

Result1 row · 1 column

status
Statement executed successfully.

Result1 row · 3 columns

TABLE_NAME	IS_TRANSIENT	RETENTION_TIME
PERM_T102	NO	2

Accepted, and the catalog records it. So this account can do more than one day, which means the refusal really was about the table being transient.

Here are the two of them next to each other.

Query

SELECT TABLE_NAME, IS_TRANSIENT, RETENTION_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME IN ('PERM_T102', 'TRANS_T102')
ORDER BY TABLE_NAME;

Result2 rows · 3 columns

TABLE_NAME	IS_TRANSIENT	RETENTION_TIME
PERM_T102	NO	2
TRANS_T102	YES	1

A transient table is capped at one day whatever the account allows. That is the first real consequence of the word.

Can we get a dropped transient table back?

One day of Time Travel is still Time Travel. Let's drop the transient table and see.

Query

DROP TABLE TRANS_T102;

Result1 row · 1 column

status
TRANS_T102 successfully dropped.

Now let's try to read from it.

Query

SELECT COUNT(*) FROM TRANS_T102;

Query failed — Snowflake returned an error

002003 (42S02): SQL compilation error:
Object 'TRANS_T102' does not exist or not authorized.

Gone, as you would expect. Now let's ask for it back.

Query

UNDROP TABLE TRANS_T102;

Result1 row · 1 column

status
Table TRANS_T102 successfully restored.

Result1 row · 2 columns

ID	NOTE
2	transient

The row came back. So UNDROP works on a transient table. The window is short, but it is there, and that is the difference between transient and temporary that actually matters.

Not storage. Recovery.

People reach for transient tables to save money on storage. The thing to think about first is recovery. A transient table gives you one day to notice a mistake. A temporary table gives you none.

Can a transient table become permanent?

This is the one I did not know the answer to. A clone takes the kind of table you ask for, so let's clone the transient table without saying TRANSIENT, which should give us a permanent copy.

Query

CREATE OR REPLACE TABLE LAUNDERED_T102 CLONE TRANS_T102;

Snowflake refused that one: “Transient object cannot be cloned to a permanent object.” This is the one statement in the episode whose own output is not in the capture file, so instead of the error pane, here is the catalog asking whether the table exists.

Query

SELECT TABLE_NAME, IS_TRANSIENT, RETENTION_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'LAUNDERED_T102';

Result0 rows · 3 columns

TABLE_NAME	IS_TRANSIENT	RETENTION_TIME

No rows. The table was never created, so there is nothing to describe.

Now let's go the other way and ask for a transient clone of the permanent table.

Query

CREATE OR REPLACE TRANSIENT TABLE DOWNGRADED_T102 CLONE PERM_T102;

Result1 row · 1 column

status
Table DOWNGRADED_T102 successfully created.

Result1 row · 3 columns

TABLE_NAME	IS_TRANSIENT	RETENTION_TIME
DOWNGRADED_T102	YES	1

Accepted, and the clone is transient with a window of one day. So you can downgrade durability with a clone. You cannot upgrade it.

Now let's give a temporary table a permanent table's name

This is the part of the post that matters. Everything above was catalog detail. What follows is a way to lose a day's work and be told everything went fine.

Let's start with an ordinary permanent table with an ordinary name, and three rows in it.

Setup

CREATE OR REPLACE TABLE SHADOW_T102 (ID INT, NOTE VARCHAR);

INSERT INTO SHADOW_T102 (ID, NOTE)
SELECT 1, 'the real row one'
UNION ALL SELECT 2, 'the real row two'
UNION ALL SELECT 3, 'the real row three';

Query

SELECT ID, NOTE FROM SHADOW_T102 ORDER BY ID;

Result3 rows · 2 columns

ID	NOTE
1	the real row one
2	the real row two
3	the real row three

Three rows. This is the table your reports read. It is real and it is permanent.

Now, in the same session, let's create a temporary table and give it that same name.

Query

CREATE TEMPORARY TABLE SHADOW_T102 (ID INT, NOTE VARCHAR);

Result1 row · 1 column

status
Table SHADOW_T102 successfully created.

Read that statement again before you read the result. It says CREATE TEMPORARY TABLE. It does not say CREATE OR REPLACE. There is no replacing going on here, because as far as this statement is concerned the name is free.

It ran. No warning, no error, and no mention of the table that is already sitting under that name.

So which table does a plain SELECT find now?

Query

SELECT COUNT(*) AS ROWS_A_PLAIN_SELECT_SEES FROM SHADOW_T102;

Result1 row · 1 column

ROWS_A_PLAIN_SELECT_SEES
0

Zero. It found the temporary one, the empty one.

What happens when we spell the name out in full?

This is where I expected to show you the safe version. Database, schema, table, everything written out. In Episode 99 the advice was to qualify names in anything you save, and that advice is correct.

Query

SELECT COUNT(*) AS ROWS_THE_QUALIFIED_NAME_SEES
FROM DEMO_DB.PUBLIC.SHADOW_T102;

Result1 row · 1 column

ROWS_THE_QUALIFIED_NAME_SEES
0

Zero again. The fully qualified name found the temporary table too.

I did not expect that, and it is worse than the thing I built the demo to show.

What that actually means

Qualifying a name protects you from reading the wrong schema. It does not protect you from this, and nothing in the query text does.
While a temporary table wears that name, the permanent table is unreachable. Not by a plain name, not by a qualified one, not in that session.

So where does an INSERT land?

Now let's do what a load script does next. It writes a row into the table by name.

Query

INSERT INTO SHADOW_T102 (ID, NOTE) SELECT 4, 'a row a load script just wrote';

Result1 row · 1 column

number of rows inserted
1

One row inserted. Success, reported cleanly.

Let's find out where it went. This query counts the rows under the plain name and under the qualified name, and asks each of them for the highest ID.

Query

SELECT (SELECT COUNT(*) FROM SHADOW_T102)                 AS ROWS_UNQUALIFIED,
       (SELECT COUNT(*) FROM DEMO_DB.PUBLIC.SHADOW_T102)  AS ROWS_QUALIFIED,
       (SELECT MAX(ID)  FROM SHADOW_T102)                 AS HIGHEST_UNQUALIFIED,
       (SELECT MAX(ID)  FROM DEMO_DB.PUBLIC.SHADOW_T102)  AS HIGHEST_QUALIFIED;

Result1 row · 4 columns

ROWS_UNQUALIFIED	ROWS_QUALIFIED	HIGHEST_UNQUALIFIED	HIGHEST_QUALIFIED
1	1	4	4

Both names see one row, and both of them say the highest ID is four. They are looking at the same temporary table. The permanent table's three rows are not in either count.

Let's ask the catalog which table is which

Both tables are real and both are recorded. Let's ask INFORMATION_SCHEMA.TABLES for that one name.

Query

SELECT TABLE_NAME, TABLE_TYPE, IS_TRANSIENT, ROW_COUNT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'SHADOW_T102';

Result2 rows · 4 columns

TABLE_NAME	TABLE_TYPE	IS_TRANSIENT	ROW_COUNT
SHADOW_T102	LOCAL TEMPORARY	NULL	1
SHADOW_T102	BASE TABLE	NO	3

Two rows for one name. The LOCAL TEMPORARY one with the row our script wrote, and the BASE TABLE one with the three rows that were there all along.

So the view will tell you. Now let's ask SHOW TABLES the same question.

Query

SHOW TABLES LIKE 'SHADOW_T102';

Result1 row · 27 columns · scroll →

created_on	name	database_name	schema_name	kind	comment	cluster_by	rows	bytes	owner	retention_time	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-09-28 13:04:03.125000-07:00	SHADOW_T102	DEMO_DB	PUBLIC	TEMPORARY			1	1024	ACCOUNTADMIN	1	OFF	OFF	OFF	NULL	NULL	N	N	ROLE	N	N	N	N	N	N	OFF	OFF

One row, and it is the temporary one. SHOW TABLES does not mention the permanent table at all.

That is worth remembering, because SHOW TABLES is what most people reach for when something looks wrong. The query that shows you both is the one against INFORMATION_SCHEMA.TABLES.

Now let's drop the temporary one

Dropping the temporary table has the same effect as ending the session, and it is quicker to demonstrate.

Query

DROP TABLE SHADOW_T102;

Result1 row · 1 column

status
SHADOW_T102 successfully dropped.

Now let's read from that name again.

Query

SELECT ID, NOTE FROM SHADOW_T102 ORDER BY ID;

Result3 rows · 2 columns

ID	NOTE
1	the real row one
2	the real row two
3	the real row three

The real rows are back. They were never touched. They were unreachable and untouched at the same time.

And the row our load script wrote?

Query

SELECT COUNT(*) AS ROWS_NOW,
       MAX(ID)  AS HIGHEST_ID,
       COUNT(CASE WHEN NOTE LIKE '%load script%' THEN 1 END) AS THE_WRITTEN_ROW
FROM SHADOW_T102;

Result1 row · 3 columns

ROWS_NOW	HIGHEST_ID	THE_WRITTEN_ROW
3	3	0

Three rows, the highest ID is three, and the row the script wrote is nowhere. It went with the temporary table.

The script reported success, because it was told success. Nothing lied to it. It asked for a table by name, it got a table, and it wrote a row into it.

One last thing: give temporary tables names you would never use

There is no setting to turn this off and no way to write the query differently. The fix is in the naming, and it costs nothing.

• Put a prefix or a suffix on every temporary table. TMP_, _SCRATCH, anything that could not be a real table.
• Never let a temporary table borrow the name of something your reports read.
• If a table you trust suddenly looks empty, check for a temporary table of the same name before you check anything else.

That last one is not an obvious thing to check, which is exactly why it is in this post.

Finally, let's clean up after ourselves.

Cleanup

-- Put the schema back the way we found it.
DROP TABLE IF EXISTS PERM_T102;
DROP TABLE IF EXISTS TRANS_T102;
DROP TABLE IF EXISTS TEMP_T102;
DROP TABLE IF EXISTS LAUNDERED_T102;
DROP TABLE IF EXISTS DOWNGRADED_T102;
DROP TABLE IF EXISTS SHADOW_T102;

Query

SELECT CURRENT_DATABASE()                 AS THE_DATABASE,
       CURRENT_SCHEMA()                   AS THE_SCHEMA,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
        WHERE RIGHT(TABLE_NAME, 5) = '_T102') AS PROBE_TABLES_LEFT,
       (SELECT COUNT(*) FROM CUSTOMERS)   AS DEMO_DB_IS_READABLE;

Result1 row · 4 columns

THE_DATABASE	THE_SCHEMA	PROBE_TABLES_LEFT	DEMO_DB_IS_READABLE
DEMO_DB	PUBLIC	0	10

No probe tables left, and the demo database still reads. Back where we started.

Key points

• A transient table is a BASE TABLE with IS_TRANSIENT set to YES. A temporary table gets its own TABLE_TYPE, LOCAL TEMPORARY.
• INFORMATION_SCHEMA.TABLES reports a RETENTION_TIME of zero for the temporary table while SHOW TABLES reports one. The two catalogs disagree.
• A transient table is capped at one day of Time Travel even on an account that allows more. The permanent table accepted two days.
• UNDROP works on a transient table and the rows come back.
• A transient table cannot be cloned into a permanent one. A permanent table clones into a transient one happily. Downgrade yes, upgrade no.
• CREATE TEMPORARY TABLE will take the name of an existing permanent table without a warning, because it is CREATE, not CREATE OR REPLACE.
• After that, every SELECT in the session finds the temporary table — including the fully qualified DATABASE.SCHEMA.TABLE name.
• INFORMATION_SCHEMA.TABLES lists both tables under the one name. SHOW TABLES lists only the temporary one.
• An INSERT lands in the temporary table and reports success. Drop it and the permanent table reappears intact, with the written row nowhere at all.
• Give temporary tables names you would never give a real table.

Next in this series

Episode 103 — ALTER TABLE: ADD, DROP, RENAME and SWAP WITH. Changing a schema under other people's queries, which of those changes break loudly, and the one statement that publishes a rebuilt table with no gap in between.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode. One statement has no grid and the text says so where it appears: the transient-to-permanent clone, whose refusal is quoted from the demo script rather than shown, with the zero-row catalog check beside it as the evidence. The _T102 tables are created and dropped by the script above. 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. Table kinds follow the Snowflake CREATE TABLE documentation.