Temporary vs Transient Tables in Snowflake

Snowflake supports three table types, Permanent table, Temporary table, and Transient table. Here are the differences between them,

Permanent table

It is the regular database table. Consumes space, Time-travel and fail-safe period can be enabled. All tables in snowflake by default are micro-partitioned, compressed, encrypted and stored in columnar format

Temporary table

A temporary table is used for storing non-permanent, transitory data (e.g. ETL data, session-specific data). Temporary tables only exist within the session in which they were created and persist only for the remainder of the session. As such, they are not visible to other users or sessions. Once the session ends, data stored in the table is purged completely from the system and, therefore, is not recoverable, either by the user who created the table or Snowflake. You can create a temporary table that has the same name as an existing table in the same schema, effectively hiding the existing table. To create a temporary table,

create temporary table mytemptable (id number, creation_date date);

Transient Tables

Snowflake supports creating transient tables that persist until explicitly dropped and are available to all users with the appropriate privileges. Transient tables are similar to permanent tables with the key difference that they do not have a Fail-safe period. As a result, transient tables are specifically designed for transitory data that needs to be maintained beyond each session (in contrast to temporary tables) but does not need the same level of data protection and recovery provided by permanent tables. to create a transient table:

create transient table mytranstable (id number, creation_date date);

Comparison of Permanent vs Temporary vs Transient tables

victor

posted on 20 Oct 18