Working with Temporary and Transient Tables¶
In addition to permanent tables, Snowflake supports defining temporary and transient tables. These types of tables are especially useful for storing transitory data.
In this Topic:
Snowflake supports creating temporary tables for storing non-permanent, transitory data (e.g. ETL data). Temporary tables only persist for the duration of the session in which they are created and are not visible to other users. Once the session ends, data stored in the table is purged and is not recoverable, either by the user who created the table or Snowflake.
Data stored in temporary tables contributes to the overall storage charges that Snowflake bills your account. To prevent any unexpected storage changes, Snowflake recommends dropping large temporary tables after they are no longer needed. You can also explicitly exit the session in which the table was created to ensure no additional charges are accrued.
To create a temporary table, specify the TEMPORARY keyword in CREATE TABLE.
After creation, temporary tables cannot be converted to permanent or transient tables.
Snowflake supports creating transient tables that persist until explicitly dropped and are available to any users with the appropriate privileges. 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.
Snowflake also supports creating transient databases and schemas. All tables created in a transient schema, as well as all schemas created in a transient database, are transient by definition.
To create a transient table, schema, or database, specify the TRANSIENT keyword when creating the object:
After creation, transient tables cannot be converted to permanent or temporary tables.
Comparison of Table Types¶
The following table summarizes the differences between the three table types, particularly with regard to Time Travel and Fail-safe:
|Type||Persistence||Time Travel Retention Period (Days)||Fail-safe Period (Days)|
|Temporary||Duration of session||0 or 1 (default is 1)||0|
|Transient||Until explicitly dropped||0 or 1 (default is 1)||0|
|Permanent (Standard Edition)||Until explicitly dropped||0 or 1 (default is 1)||7|
|Permanent (Enterprise Edition and higher)||Until explicitly dropped||0 to 90 (default is configurable)||7|
Time Travel notes:
- The Time Travel retention period for a table can be specified when the table is created or any time afterwards. Within the retention period, all Time Travel operations can be performed on data in the table (e.g. queries) and the table itself (e.g. cloning and restoration).
- Temporary tables can have a Time Travel retention period of 1 day; however, a temporary table is purged once the session (in which the table was created) ends so the actual retention period is for 24 hours or the duration of the session, whichever is shorter.
- The Fail-safe period is not configurable for any table type.
- Transient and temporary tables have no Fail-safe period. As a result, no additional data storage charges are incurred beyond the Time Travel retention period.
Because transient tables do not have a Fail-safe period, they provide a good option for managing the cost of very large tables used to store transitory data; however, the data in these tables cannot be recovered after the Time Travel retention period passes.
For example, if a system failure occurs in which a transient table is dropped or lost, after 1 day, the data is not recoverable by you or Snowflake. As such, we recommend using transient tables only for data that does not need to be protected against failures or can be reconstructed outside of Snowflake.