Snowflake Temporary Tables, Usage and Examples

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

Similar to other relational databases, Snowflake support creating temp or temporary tables to hold non-permanent data. i.e. Data which is used in the current session. In this article, we will check how to create Snowflake temp tables, syntax, usage and restrictions with some examples.

Snowflake Temporary Tables

The temporary table in Snowflake is visible only within the current session. Temporary tables only exist within the session in which they were created and persist only for the remainder of the session. Once the session ends, the system will purge the data stored in a temporary table and it is not recoverable.

Snowflake Create Temporary Table Syntax

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

For example,

create temporary table temptablename (col1 type1, col2 type2, ... coln typen,);

Snowflake Temporary Table Example

Following is the example of create temp table in Snowflake.

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

Create Snowflake Temporary Table with same name as Permanent Table

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

For example,

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

-- Temporary Table
create TEMPORARY table TEST (col1 int, col2 int);
+----------------------------------+
| status                           |
|----------------------------------|
| Table TEST successfully created. |
+----------------------------------+

However, note that the temporary table takes precedence in the session over any other table with the same name in the same schema. The system will hide permanent table when you create a temporary table with the same name.

Snowflake Temporary Table Restrictions

Following are some if the restriction on the temp tables.

Temporary tables are not visible to other users or sessions.
Temporary tables do not support some standard features such as cloning.
Data is not recoverable. The system will purge the temporary table data at the end of the session. User or Snowflake will not be able to recover the data.
Temporary tables have no Fail-safe period