Snowflake AI Data Cloud Features & Architecture · Topic 12 of 17

Table Types - Permanent, Temporary and Transient

Compare permanent, temporary and transient Snowflake tables by lifetime, visibility, Time Travel retention and Fail-safe, and pick the right one for a job.

  • 5 min
In a nutshell
  • Permanent: Time Travel 0 to 1 day on Standard, up to 90 days on Enterprise or higher, plus 7 days of Fail-safe.
  • Transient: persists, Time Travel 0 or 1 day, no Fail-safe, cheaper for data you can rebuild.
  • Temporary: session only, invisible to other sessions, Time Travel 0 or 1 day, no Fail-safe.

What it is

Snowflake has several table types. They differ in who manages the data, how long it lives and how much recovery protection it gets. The exam groups them as permanent, temporary, transient, Apache Iceberg, external and dynamic.

This page covers the three standard Snowflake tables, which all store data in Snowflake-managed micro-partitions. The next page covers Iceberg, external and dynamic tables.

Table typeWhere the data livesMain idea
PermanentSnowflake storageThe default, full protection
TemporarySnowflake storageExists only for one session
TransientSnowflake storagePersists, but no Fail-safe
Apache IcebergExternal volume or Snowflake storageOpen Iceberg format
ExternalFiles in an external stageRead-only view over files
DynamicSnowflake storageResult of a query, refreshed automatically

How it works

The three protection levels

Data protection in Snowflake has two stages. Time Travel lets you query, clone or undrop data as it was in the past, for the retention period set by DATA_RETENTION_TIME_IN_DAYS. Fail-safe follows Time Travel and gives 7 more days in which only Snowflake Support can recover the data.

Data changedor droppedkept inTime Travelyou recover itthenFail safe7 days, Support onlythenPurgedgone
Table typePersists untilVisible toTime Travel retentionFail-safe
Permanent, Standard EditionExplicitly droppedAny role with privileges0 or 1 day (default 1)7 days
Permanent, Enterprise Edition or higherExplicitly droppedAny role with privileges0 to 90 days (default 1)7 days
TransientExplicitly droppedAny role with privileges0 or 1 day (default 1)None
TemporaryEnd of the sessionOnly the creating session0 or 1 day (default 1), ends with the sessionNone

Permanent tables

CREATE TABLE with no keyword creates a permanent table. It is the right choice for any data that must be recoverable after an accident, such as production fact and dimension tables.

Permanent tables cost the most to store, because their storage includes historical micro-partitions held for Time Travel plus 7 days of Fail-safe. On Enterprise Edition or higher you can extend Time Travel up to 90 days.

Temporary tables

CREATE TEMPORARY TABLE creates a table that belongs to the current session. Other users and other sessions cannot see it, even with the same role. When the session ends, Snowflake drops it and it cannot be recovered.

A temporary table can have the same name as a permanent or transient table in the same schema. Inside that session the temporary table takes precedence and hides the other table, so every query, DROP or CREATE OR REPLACE affects the temporary one.

Use temporary tables for intermediate results inside one ETL session or script that no one else needs.

Transient tables

CREATE TRANSIENT TABLE creates a table that persists until dropped and is visible to anyone with privileges, like a permanent table. The difference is protection: Time Travel is at most 1 day and there is no Fail-safe.

That makes transient tables cheaper to keep for data that changes a lot and can be rebuilt from its source, such as staging tables reloaded every night. You save the Fail-safe storage, but if the data is lost after Time Travel ends, Snowflake cannot recover it.

You can also create a transient database or schema. Every schema in a transient database is transient, and every table created in a transient schema is transient.

Raw filesstageCOPY INTOTransient tablestagingtransformPermanent tablereportingETL sessionscriptwritesTemporary tablesession onlyfeedsPermanent tablefinal load

Changing the type

You cannot convert a table to another type after creation. Temporary and transient tables can never be turned into another type with an ALTER. To change type, create a new table of the type you want and copy the data, for example with CREATE TABLE ... AS SELECT.

Cloning also follows the type rules. A temporary table can be cloned only to a temporary or transient table, never to a permanent one.

Watch out

Fail-safe is not a user feature. You cannot query or restore Fail-safe data yourself. Only Snowflake Support can recover it, and only for permanent tables.

How the exam asks about this

  • "Reduce storage costs for staging data that is reloaded daily and can be recreated" -> transient table, because it has no Fail-safe.
  • "Intermediate results must not be visible to other users and should disappear after the job" -> temporary table.
  • "Data must be recoverable for 30 days after an accidental delete" -> permanent table on Enterprise Edition or higher with DATA_RETENTION_TIME_IN_DAYS = 30.

Common mistakes

Thinking transient tables vanish at session end. Only temporary tables do. Transient tables persist until dropped; they just lack Fail-safe.

Expecting 90 days of Time Travel on transient tables. Transient and temporary tables allow only 0 or 1 day, on any edition.

Forgetting the name shadowing rule. A temporary table with the same name as a permanent table hides it in that session, so a DROP TABLE there drops the temporary table, not the permanent one.

Trying to ALTER a table into another type. There is no such command; create a new table and copy the data.

What to remember for the exam

  • Permanent: Time Travel 0 to 1 day on Standard, up to 90 days on Enterprise or higher, plus 7 days of Fail-safe.
  • Transient: persists, Time Travel 0 or 1 day, no Fail-safe, cheaper for data you can rebuild.
  • Temporary: session only, invisible to other sessions, Time Travel 0 or 1 day, no Fail-safe.
  • Tables in a transient schema or database are transient.
  • Table type cannot be changed after creation.
Exam tip

When a question mentions lowering storage cost for data that can be regenerated, think transient. When it mentions session-only or private scratch data, think temporary.

Check yourself

  • Which table type persists until dropped but has no Fail-safe?

    A transient table. It keeps up to 1 day of Time Travel and has no Fail-safe period.

  • What happens to a temporary table when the session ends?

    Snowflake drops it, and it cannot be recovered with Time Travel or Fail-safe.

  • What is the maximum Time Travel retention for a permanent table on Enterprise Edition?

    90 days, set with DATA_RETENTION_TIME_IN_DAYS, followed by 7 days of Fail-safe.

Topic 12 of 17 in Snowflake AI Data Cloud Features & Architecture

Test yourself on Snowflake AI Data Cloud Features & Architecture

The full Snowflake SnowPro Core COF-C03 question bank has 845 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.