Table Types

Overview

Snowflake has four table types: permanent, temporary, transient and external. The type is chosen when the table is created and cannot be inferred from its name afterwards — you read it back with SHOW TABLES.

What separates them is not what they can store but how much recovery Snowflake keeps behind them — and therefore how much they cost to keep. That is the whole distinction; everything else follows from it.

The four types at a glance

Read the bottom two rows of the figure first. The columns describe what each type is for; the two bands describe what you can get back after a mistake.

Figure 6 — Table Types in SnowflakeOpen full size ↗

Permanent tables

Persists inside the account until you physically drop it. Nothing else removes it.
Designed for data that requires the highest level of data protection and recovery.
The default table type — a CREATE TABLE with no type keyword produces a permanent table.
Time Travel up to 90 days on Enterprise edition, which is what lets you read the table as it was at an earlier point and recover from the micro-partitions Snowflake still holds.
Fail-Safe: a further 7 days after the Time Travel window has closed.

Temporary tables

Persists, but tied to a single session — think one user. When the session ends the table is gone, and it cannot be retrieved.
Used for transitory data. The canonical case is an ETL or ELT script that needs somewhere to hold an intermediate calculation between source and target: create it inside the script, and it disappears when the script finishes.
Time Travel of 0 or 1 days, and no Fail-Safe.

A name trap: a temporary table can carry the same name as a permanent table in the same schema, and for the life of that session the name resolves to the temporary one. The permanent table is untouched and reappears when the session ends — which makes this a hard bug to see from outside the session.

Transient tables

Persists until dropped, exactly like a permanent table, and is not tied to a session — multiple users can use it.
For data that has to persist but does not need the same level of retention as a permanent table.
Time Travel of 0 or 1 days, and no Fail-Safe.

The reason to reach for one is cost. Retention is storage you pay for: a permanent table on a 90-day window keeps 90 days of change history, plus 7 days of Fail-Safe you cannot even query. If the data is reproducible, that is money spent on recovery you would never use — and a transient table is the alternative to creating yet another permanent table for it.

External tables

Persists until removed, like the other two non-temporary types.
The data stays outside Snowflake — in a cloud data lake such as AWS S3, Azure Blob Storage or a GCP bucket. Snowflake sits “over” it and presents it in tabular form; nothing is physically moved.
Reached through an external stage.
Read-only — you cannot manipulate the data through the table.
No Time Travel and no Fail-Safe, because Snowflake is not the system of record for those bytes.

Time Travel and Fail-Safe, precisely

Type	Time Travel	Fail-Safe
Permanent	0–90 days on Enterprise and above; 0 or 1 day on Standard	7 days after the Time Travel window closes
Temporary	0 or 1 day, and never outlives the session	None
Transient	0 or 1 day	None
External	None	None

Two things about that table are easy to get wrong. First, the default retention is 1 day for every type, permanent included — 90 days is the ceiling you may raise it to, not the value you get. Second, Fail-Safe is not a feature you operate: it is a window during which Snowflake Support may be able to recover data on a best-effort basis. There is no SQL for it.

Creating each one

Set the context first. A table needs a role to be created under, a warehouse to run the statement, and — because tables live in a schema — a database and schema to land in.

sql

USE ROLE ACCOUNTADMIN;
USE WAREHOUSE COMPUTE_WH;
USE SCHEMA MY_DB.MY_SCHEMA;   -- tables live in a schema, so name one

-- Permanent: no keyword at all. This is the default.
CREATE OR REPLACE TABLE MY_PERMANENT_TABLE (
  ID   INT,
  NAME VARCHAR(50)
);

-- Transient: the keyword is required.
CREATE OR REPLACE TRANSIENT TABLE MY_TRANSIENT_TABLE (
  ID   INT,
  NAME VARCHAR(50)
);

-- Temporary: the keyword is required, and the table dies with the session.
CREATE OR REPLACE TEMPORARY TABLE MY_TEMPORARY_TABLE (
  ID   INT,
  NAME VARCHAR(50)
);

Once the context is set, the object browser shows the new tables under the schema's Tables folder — after a page reload, as with databases and schemas, since the tree does not always refresh on its own.

Reading the type back

The names above say what each table is, but real tables are not named that way. The type is a property, and SHOW TABLES is how you see it — for every table in the current database and schema:

sql

SHOW TABLES;

The kind column carries the answer:

`kind`	The table is
`TABLE`	Permanent — note that the default type has no distinguishing label
`TEMPORARY`	Temporary, and visible only to the session that created it
`TRANSIENT`	Transient

Scroll the same output to the right and you also get retention_time — 1 day on every freshly created table, whatever its type.

Changing the retention window

sql

-- Permanent tables: anything from 0 to 90 days on Enterprise.
ALTER TABLE MY_PERMANENT_TABLE SET DATA_RETENTION_TIME_IN_DAYS = 3;
ALTER TABLE MY_PERMANENT_TABLE SET DATA_RETENTION_TIME_IN_DAYS = 90;

-- 91 is refused, out of range:
--   "Maximum allowed retention time is 90 days"
ALTER TABLE MY_PERMANENT_TABLE SET DATA_RETENTION_TIME_IN_DAYS = 91;

-- Transient and temporary tables cap at 1 day, so 2 is refused as an
-- INVALID PROPERTY rather than as an out-of-range number — the property
-- is simply not settable above 1 on these types:
ALTER TABLE MY_TRANSIENT_TABLE SET DATA_RETENTION_TIME_IN_DAYS = 2;
ALTER TABLE MY_TEMPORARY_TABLE SET DATA_RETENTION_TIME_IN_DAYS = 2;

The difference in those two errors is worth noticing. Raising a permanent table past 90 is a value that is too large; raising a transient table past 1 is a property that does not apply. The second is the type refusing the whole idea of longer retention — which is exactly what you chose the type for.

Choosing a type

If the data is	Use	Because
The system of record — you would have to explain its loss	Permanent	It is the only type with Fail-Safe behind it, and the only one whose Time Travel can reach past yesterday
Scratch space inside one script or session	Temporary	Nothing has to clean it up; the session ending does that
Shared and durable, but reproducible from somewhere else	Transient	You keep the sharing and the persistence, and stop paying for recovery you would never use
Already in a data lake and you want to query it in place	External	No copy, no load, no second source of truth — at the price of being read-only

What this means for a PostgreSQL migration

PostgreSQL has its own vocabulary for the same trade-offs, and the mapping is not one-to-one.

PostgreSQL	Lands as	Watch for
`CREATE TEMPORARY TABLE`	Temporary	Both are session-scoped, but PostgreSQL's ON COMMIT DROP and ON COMMIT DELETE ROWS have no Snowflake equivalent — transaction-scoped lifetimes have to be written out by hand
`CREATE UNLOGGED TABLE`	Transient	The closest match in intent — durable, cheap, not crash-recoverable — but not a lossless mapping, so it belongs in review rather than in the automatic set
Staging and work tables in an ETL job	Transient	Landing these as permanent tables is the commonest silent cost in a migration: every rebuilt staging table drags 90 days of history and 7 days of Fail-Safe behind it
Foreign tables (postgres_fdw, file_fdw) and data already in object storage	External	Read-only in Snowflake, so any write path through the foreign table has to be redesigned rather than translated

Two places in this platform turn on temporary tables specifically.

Assessment counts them as complexity. Procedure analysis scans each body for #name, temp and temporary alongside cursors, loops and dynamic SQL — a procedure that builds temp tables needs the target to permit session-scoped DDL, which is not a given.
Translated procedures are deployed as EXECUTE AS CALLER for this reason. Snowflake's default is owner's rights, which forbids session-scoped DDL inside a procedure body: session.sql("CREATE TEMPORARY TABLE …") fails with Unsupported statement type 'temporary TABLE'. Caller's rights runs the body in the invoker's session, where temp tables are allowed and are session-scoped — which is what the T-SQL #temp semantics of the original procedures assume.

That second one is a good illustration of the general shape of this work: the table type translated cleanly, and the privilege model around it is what actually broke.

Three slips in the source

Recorded rather than repeated, because a reader following the walkthrough will hit all three.

The narration says that if you miss the Time Travel window you can "retrieve the data through a fail-safe option". You cannot. Fail-Safe is not user-accessible — there is no option, no command and no self-service recovery. It is a 7-day period in which Snowflake Support may be able to recover the data, on a best-effort basis, and only for permanent tables.
The 90-day ceiling is edition-dependent, not universal. On Standard edition the maximum is 1 day even for a permanent table. The demo account is Enterprise, which is the only reason 90 was accepted.
The narration numbers transient as "the third table" and then introduces external as "the third" as well. The slide's order — permanent, temporary, transient, external — is the one used here.

Where this page comes from

The four types, the Time Travel and Fail-Safe figures, and the SQL walkthrough are Snowflake's own introductory material on table types, with the three misstatements above corrected. External tables are deferred in that source to a later session; what is here is the summary from the comparison slide.

The migration section is this platform's own. The complexity signal is in app/ai/agents/migration_assessment/utils/analysis/procedure_analysis.py, and the caller's-rights decision, with the live error text, is commented in app/ai/agents/agent_control/utils/procedure/execution/normal_procedure/snowflake.py.