Working with Temporary and Transient Tables¶

Data Storage Usage for Temporary Tables¶

For the duration of the existence of a temporary table, the data stored in the table contributes to the overall storage charges that Snowflake bills your account. To prevent any unexpected storage changes, particularly if you create large temporary tables in sessions that you maintain for periods longer than 24 hours, Snowflake recommends explicitly dropping these tables once 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.

For more information, see Comparison of Table Types (in this topic).

Potential Naming Conflicts with Other Table Types¶

Similar to the other table types (transient and permanent), temporary tables belong to a specified database and schema; however, because they are session-based, they aren’t bound by the same uniqueness requirements. This means you can create temporary and non-temporary tables with the same name within the same schema.

However, note that the temporary table takes precedence in the session over any other table with the same name in the same schema. This can lead to potential conflicts and unexpected behavior, particularly when performing DDL on both temporary and non-temporary tables. For example:

• You can create a temporary table that has the same name as an existing table in the same schema, effectively hiding the existing table.

• You can create a table that has the same name as an existing temporary table in the same schema; however, the newly-created table is hidden by the temporary table.

Subsequently, all queries and other operations performed in the session on the table affect only the temporary table.

Creating a Temporary Table¶

To create a temporary table, simply specify the TEMPORARY keyword (or TEMP abbreviation) in CREATE TABLE.

Note that creating a temporary table does not require the CREATE TABLE privilege on the schema in which the object is created.

For example:

CREATE TEMPORARY TABLE mytemptable (id NUMBER, creation_date DATE);

Copy

Data Storage Usage for Transient Tables¶

Similar to permanent tables, transient tables contribute to the overall storage charges that Snowflake bills your account; however, because transient tables do not utilize Fail-safe, there are no Fail-safe costs (i.e. the costs associated with maintaining the data required for Fail-safe disaster recovery).

For more information, see Comparison of Table Types (in this topic).

Transient Tables Created as Clones of Permanent Tables¶

When you create a transient table as a clone of a permanent table, Snowflake creates a zero-copy clone. This means when the transient table is created, it utilizes no data storage because it shares all of the existing micro-partitions of the original permanent table. When rows are added, deleted, or updated in the clone, it results in new micro-partitions that belong exclusively to the clone (in this case, the transient table).

When a permanent table is deleted, it enters Fail-safe for a 7-day period. Fail-safe bytes incur storage costs. If a transient table is created as a clone of a permanent table, this might delay the time between when the permanent table is deleted and when all of its bytes enter Fail-safe. If the transient table clone shares any micro-partitions with the permanent table when it is deleted, those shared bytes will only enter Fail-safe when the transient table is deleted.

Transient Databases and Schemas¶

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.

Creating a Transient Table, Schema, or Database¶

To create a transient table, schema, database, simply specify the TRANSIENT keyword when creating the object:

• CREATE TABLE

• CREATE SCHEMA

• CREATE DATABASE

For example, to create a transient table:

CREATE TRANSIENT TABLE mytranstable (id NUMBER, creation_date DATE);

Copy

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).

• If the Time Travel retention period for a permanent table is set to 0, it will immediately enter the Fail-safe period when it is dropped.

• 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 remainder of the session, whichever is shorter.

• A long-running Time Travel query will delay the purging of temporary and transient tables until the query completes.

Fail-safe Notes¶

• 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.