Snowflake Transient Tables, Usage and Examples

Post author:Vithal S
Post last modified:May 2, 2020
Post category:Snowflake
Reading time:4 mins read

Snowflake Transient tables are similar to permanent tables with the key difference that they do not have a Fail-safe period. The transient tables are similar to temporary tables, but, you have to explicitly drop transient tables at the end of the session.

Snowflake Transient Tables

Snowflake transient tables persist until explicitly dropped and are available to all users with the appropriate privileges. The transient tables are designed for transitory data that needs to be maintained beyond the current session.

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, you or Snowflake cannot recover the data after the Time Travel retention period passes.

Snowflake Transient Table Syntax

To create a temporary table, simply specify the TRANSIENT keyword in your CREATE TABLE DDL.

For example,

create transient table trantablename (col1 type1, col2 type2, ... coln typen,);

Snowflake Transient Table Example

Following is the example of create transient table in Snowflake.

create TRANSIENT table TEST1 (col1 int, col2 int);
+-----------------------------------+
| status                            |
|-----------------------------------|
| Table TEST1 successfully created. |
+-----------------------------------+

Create Snowflake Transient Table with same name as Permanent Table

Just like other table types, transient tables belong to a specified database and schema. However, because they are not session-based, they are bound by the same unique name requirements. This means you cannot create transient tables with the same name as permanent table.

Foe example,

--Permanent Table
select * from test;
+------+------+
| COL1 | COL2 |
|------+------|
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+

-- Transient Table
create TRANSIENT table TEST (col1 int, col2 int);
002002 (42710): SQL compilation error:
Object 'TEST' already exists.

As you can see, you cannot create a transient table with the same name as permanent table.

Snowflake Transient Table Restrictions

Following are some if the restriction on the transient tables.

Cannot create transient table with the same name as permanent tables.
Temporary tables do not support some standard features such as cloning.
The data in the transient tables cannot be recovered after the Time Travel retention period passes.
Transient tables have no Fail-safe period