HOW TO: Create and Use Snowflake Temporary Table (2024)

Snowflake temporary tables provide a useful way to store transient data that only needs to persist for the duration of a session. Compared to Snowflake regular/permanent tables, temporary tables offer more flexibility and lower costs for managing short-lived data.

In this article, we will cover everything you need to know about creating, using, and optimizing Snowflake temporary tables. We’ll provide an overview of temporary tables—their key characteristics, costs, performance aspects, use cases, real-world applications, and how they differ from other Snowflake table types.

What Are the 3 Types of Tables in Snowflake?

Before we dive into understanding Snowflake temporary tables, let's first note that Snowflake offers three different table types:

• Permanent: Persist indefinitely unless explicitly dropped
• Transient: Persist past session until explicitly dropped
• Temporary: Only exist for duration of the session.

What are Permanent Snowflake Tables?

Permanent tables are the default table type created via the Snowflake CREATE TABLE command. It provides lasting storage solutions for crucial business data. These tables remain in the system until explicitly removed with the DROP TABLE command, and they are accessible to all users with appropriate privileges. Although permanent tables have a larger overhead than temporary or transient tables, they allow for long-term data storage and provide essential features such as Time Travel and fail-safe mechanisms for data recovery.

How to create Permanent Snowflake Tables?

To create a Permanent table, use the CREATE TABLE syntax:

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype, 
  ...
);

What Are Snowflake Transient Tables?

Snowflake Transient Tables provide a flexible data storage solution for temporary use cases, striking an ideal mix between the durability of permanent tables and the volatile nature of temporary tables. These tables exist beyond the session in which they were created, allowing data to be shared across multiple sessions. They can also be manually dropped as needed using DROP TABLE command.

Transient tables are similar to permanent tables with the key difference that they do not have a Fail-safe period.

How to create Transient Snowflake Tables?

To create a transient table, use the CREATE TRANSIENT TABLE syntax:

CREATE TRANSIENT TABLE table_name (
  column1 datatype,
  column2 datatype, 
  column3 datatype, 
  column4 datatype, 
  column5 datatype, 
  ...
);

For example,

This table persists outside the session but can be dropped at any time.

What Are the Use Cases of Snowflake Transient Table?

Common use cases for Snowflake transient tables include:

• Snowflake Transient tables can be used to temporarily store data during complicated data processing operations where a permanent table is not required.
• Snowflake Transient Tables are a useful solution for data that needs to be retained for more than one session but does not require data recovery.
• Snowflake Transient tables are useful for performing complex data transformations and aggregations during the data analysis process.
• Snowflake Transient tables can be used to stage backup or archival data before loading into permanent tables or external systems.
• Snowflake Transient Tables can act as temporary staging areas for loading and transforming data before moving it to permanent tables.
• Snowflake Transient Table provides data engineers/DS/ML/AI teams with disposable workspace without cluttering shared permanent tables.

What is Snowflake Temporary Table?

Snowflake Temporary tables offer a lightweight option for storing transient, non-critical data that only needs to persist for a single session. Once the session ends, the temporary table is dropped automatically and the entire data gets deleted.

Some of the main key characteristics of Snowflake temporary tables are:

• Only visible and accessible within the session where created
• Data not recoverable after the session ends
• No fail-safe period—data immediately gets deleted
• Storage costs only apply while the table exists

Snowflake Temporary tables are extremely useful for:

• Storing intermediate or temporary data needed for a specific task or analysis
• Testing queries or logic before applying changes to permanent tables
• Improving performance by caching commonly accessed data
• Reducing processing for one-time or infrequently used data

Compared to Snowflake regular tables, Snowflake temporary tables incur lower overhead and operational costs since data protection and recovery mechanisms do not apply.

Watch this video to learn more about the differences and similarities between Snowflake transient tables and Snowflake temporary tables.

What Are Temporary Tables Used For?

Snowflake temporary tables shine for storing short-lived, temporary data that only need to persist for a certain period of time. Here are some Snowflake temporary table example:

1. Staging data extracted from an external source before loading into permanent tables
2. Intermediate data sets used within a single session or query
3. Session-specific application data
4. Test or sandbox data used for trying queries or testing logic
5. Caching commonly accessed data to improve query performance

Essentially, any data that is short-lived and non-critical is a good fit for Snowflake temporary tables. They can help reduce storage and processing costs compared to loading all data into permanent tables.

Snowflake Temporary tables are also useful during ETL workflows for expedited staging without the overhead of permanent storage. For instance, you could extract data from an upstream source, land it into a temporary table, transform and cleanse it, and then insert the final output into a permanent table for longer-term persistence, as we will cover in depth in a later example.

Save up to 30% on your Snowflake spend in a few minutes!

Request a Demo

Enter your work email

Enter your work email

Request a Demo

How to Create a Temporary Table in Snowflake?

Creating a Snowflake temporary table uses the same CREATE TABLE syntax as regular Snowflake tables, with the addition of the TEMPORARY keyword:

So, to create a Temporary table, use Snowflake CREATE TEMPORARY TABLE syntax:

CREATE TEMPORARY TABLE table_name (
   column1 DATA_TYPE,
   column2 DATA_TYPE,
   ...
);

For example :

CREATE TEMPORARY TABLE temp_students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    major VARCHAR(100)
);

You can define columns, data types, constraints, indexes, etc. just as with Snowflake permanent tables.

After creating the empty temporary table, you can insert data into it either:

1) Row-by-row using INSERT statements:

INSERT INTO temp_students VALUES (1, 'Chaos', 'A');
INSERT INTO temp_students VALUES (2, 'Genius', 'B');

2) By selecting data from another existing table:

INSERT INTO temp_students
SELECT student_id, name, grade
FROM students
WHERE grade = 'A';

As you can see, this approach of creating Snowflake temporary table first provides more control to customize the structure as needed before loading data.

3) Create from SELECT Query

The other common approach is to create Snowflake temporary table and load data in one step using the results of a SELECT query:

CREATE TEMPORARY TABLE temp_students AS
    SELECT student_id, name, grade
    FROM students
    WHERE grade = 'A';

You can see that the temporary table gets created and populated instantly based on the SELECT statement results.

You can also make use of CREATE OR REPLACE to overwrite an existing temporary table with new query results:

CREATE OR REPLACE TEMPORARY TABLE temp_students AS
    SELECT student_id, name, grade 
    FROM students;

This particular technique is great for the one-step creation of Snowflake temporary tables from large datasets without needing to pre-define the table.

TLDR; The two main approaches are creating the empty temporary table then inserting data, or creating the populated temporary table directly from a SELECT query. Both are useful depending on the use case.

How to Drop a Temporary Table in Snowflake?

Snowflake temporary tables are automatically dropped when the session ends. but, if you need to drop a temporary table before the session ends, you can use the DROP TABLE command.

The syntax for dropping a temporary table in Snowflake is as follows:

DROP TABLE IF EXISTS temp_table_name;

Note: Dropping a temporary table is an irreversible operation, and any data stored in the table will be lost

Naming Conflicts and Session-Specific Behavior of Temporary Tables

As you already know, one main unique aspect of Snowflake temporary tables is that they are isolated to the specific session in which they are created.

Snowflake Temporary tables can have the same name as existing permanent tables, but this does NOT cause the permanent table to be hidden or inaccessible. Permanent tables are still visible and can be queried as normal.

BUT, any queries that reference just the table name without a schema will hit the temporary table first instead of the permanent table.

For example:

-- Permanent table 
CREATE TABLE demo_schema.students (id INTEGER);

-- Temporary table with same name
CREATE TEMPORARY TABLE students (id INTEGER); 

-- This will query the TEMPORARY table 
SELECT * FROM students;

-- This will query the PERMANENT table
SELECT * FROM demo_schema.students;

So temporary tables take precedence over permanent tables with the same name when just the table name is used in the query. The permanent table is not hidden or replaced.

Some best practices to follow to avoid this kind of conflict are:

• Name temporary tables with a prefix e.g. temp_students, tmp_students
• Avoid Snowflake temporary table names matching permanent tables
• Double check you are querying the intended table

Also, dropping or replacing a regular/permanent table only affects the temporary version visible in that session. The actual permanent table will remain unchanged until the session ends.

Can Temporary Tables be Shared in Snowflake?

Temporary tables in Snowflake cannot be shared between sessions or users. Temporary tables are session-level objects, which means they are only visible and available during the session in which they were created. When the session ends, the temporary table is automatically deleted and no longer exists.

If you need to transfer data between sessions or users, use permanent tables rather than temporary tables.

Can We Clone a Temporary Table in Snowflake?

Yes, it is possible to clone a temporary table in Snowflake within the same session. Cloning a temporary table creates a new temporary table with the same structure and data as the original table.

To clone a temporary table in Snowflake, you can use the CREATE TABLE statement with the LIKE clause, followed by the name of the temporary table you want to clone. Here's the syntax:

CREATE TEMPORARY TABLE new_temp_table LIKE existing_temp_table;

OR

You can use the CTAS (Create Table As Select) statement to clone a temporary table by selecting all the data from the existing temporary table:

CREATE TEMPORARY TABLE new_temp_table AS SELECT * FROM existing_temp_table;

Practical Use Cases and Performance Benefits of Temporary Tables

Snowflake temporary tables provide several distinct advantages that make them well-suited for specific use cases involving transient, non-critical data. When leveraged appropriately, temporary tables can significantly enhance query performance, scalability, and cost-efficiency.

1) Quick Access—Faster Queries

One of the biggest performance benefits of Snowflake temporary tables is using ‘em to cache frequently accessed data. This data caching can drastically improve query speeds in situations where the underlying data is expensive to repeatedly process or access.

For example, extracting raw data from External Stage (like Amazon S3) into a Snowflake stage incurs high latency. So by loading the S3 data into a Snowflake temporary table first, subsequent queries can simply access the cached table instead of hitting S3 every time. This avoids redundant data processing and extraction.

The main key is to identify data that is reused often, but requires heavy processing or pulls from slow sources. Hence, by pulling these types of data into the Snowflake temporary table, you keep a rapid access copy in local memory/storage. Temporary tables are ideal for cache use cases since the data can be easily rebuilt and reloaded after the session.

2) Managing Concurrency and Scalability

Snowflake Temporary tables can also help overcome the scalability limitations of regular/permanent tables by providing session-isolated data stores. When many users or sessions try to concurrently access the same table, performance can degrade rapidly.

With the help of Snowflake temporary tables, each session can create a local copy of the necessary data instead of hitting the regular/permanent table every time. This takes load off the permanent table and allows much higher concurrency.

For example, an analytics dashboard requiring frequent queries could cache necessary data in each user's temporary session table. So by distributing the data access in this way, overall scalability is increased.

3) Cost-Effective Data Storage

Since temporary tables automatically expire after the session, they provide very cost-effective storage for transient, short-lived data sets. The overhead of data protection and recovery mechanisms present in permanent tables is avoided with temporary tables.

BUT, it is still crucial to explicitly drop temporary tables when no longer needed and tightly control session duration. Even though temporary, the storage costs do apply for the table's lifetime. For larger data sets, Snowflake transient tables may provide a more cost-optimized persistent option.

Note: These benefits only apply when Snowflake temporary tables are used appropriately. They should not be used for data that exceeds the local memory or needs to be widely accessed.

Practical Examples of Using Temporary Tables in Snowflake

To illustrate how Snowflake temporary tables are used in practice, let’s walk through some common examples.

Example 1—Temporary Holding Table for Data Processing

When processing large data sets, we can use the Snowflake temporary table as a holding area for intermediate results before loading into the final table.

-- Snowflake Temp Table Example
-- Extract raw data from an external source 
CREATE TEMPORARY TABLE temp_raw_data AS
SELECT * FROM external_table;

-- Cleaning bad records from temp raw data
DELETE FROM temp_raw_data
WHERE column IS NULL;

-- Do initial data transformations
INSERT INTO temp_processed AS
SELECT col1, col2, SUBSTRING(col3, 1, 100) 
FROM temp_raw_data;

-- Final pruning and transformations
INSERT INTO permanent_table
SELECT col1, UPPER(col2), SUBSTRING(col3, 1, 50)
FROM temp_processed
WHERE LENGTH(col3) > 20;

-- Dropping Snowflake temporary tables
DROP TABLE temp_raw_data;
DROP TABLE temp_processed;

As you can see, here we use Snowflake temporary tables to stage the data at different processing phases, applying transformations incrementally before the final load process.

Example 2—Staging Data During ETL Process

Say you need to load data from an upstream database into Snowflake. Rather than loading directly into the final table, you stage the extracted data into a temporary table first. This provides a buffer to cleanse and transform the data before insertion into the permanent table.

-- Snowflake Temp Table Example
-- Extract data from upstream DB into staging
CREATE TEMPORARY TABLE tmp_stage AS
SELECT * FROM upstream_db.table;

-- Cleansing bad records  
DELETE FROM tmp_stage
WHERE column IS NULL;

-- Insert only cleaned data into permanent table
INSERT INTO employees 
SELECT * FROM tmp_stage;

-- Drop temp table after data is loaded
DROP TABLE tmp_stage;

Example 3—Local Cache for Frequent Queries

For an analytics dashboard that runs frequent queries on a large Snowflake table, we can optimize performance by caching this data in a temporary table.

-- Snowflake Temp Table Example
-- Create Snowflake temp table for dashboard metrics
CREATE TEMPORARY TABLE tmp_metrics AS
SELECT col1, col2, col3 FROM large_table;

-- Refresh cache if underlying data changes
CREATE OR REPLACE TEMPORARY TABLE tmp_metrics AS  
SELECT col1, col2, col3 FROM large_table;

-- Dashboard can now easily query Snowflake temp table
SELECT * FROM tmp_metrics

Now the dashboard can simply query the much smaller cached table instead of hitting the large table repeatedly.

Example 4—Temporary Copy of Table for Testing

Before modifying a regular/permanent table, we can create a temporary copy to safely test queries first.

-- Snowflake Temp Table Example
-- Create temporary copy of permanent table 
CREATE TEMPORARY TABLE temp_table AS
SELECT * FROM permanent_table;

-- Test transformation logic on copy
SELECT col1, UPPER(col2)
FROM temp_table; 

-- Apply change to regular/permanent table after testing
ALTER TABLE permanent_table 
ADD COLUMN new_col VARCHAR;

As you can see, the Snowflake temporary table provides an isolated copy for testing without impacting the real permanent table.

Managing Costs of Temporary Tables

While Snowflake temporary tables provide more flexibility and lower overhead than permanent tables, they still do incur storage costs that count towards your total Snowflake usage.

Snowflake temporary tables utilize on-demand storage resources just like permanent tables while they exist. Although the storage is released at the end of the session when the temporary table is dropped, any long-running sessions with large temporary tables can result in higher-than-expected storage costs.

For instance, if you create a Snowflake temporary table and insert ~1TB of data, that will add ~1TB of storage to your Snowflake usage even if the table only exists for a certain period. Multiply this by additional concurrent users and sessions—and costs can quickly scale.

Hence, proper management of Snowflake temporary tables is important to control storage costs:

• Be sure to explicitly drop temporary tables with the DROP TABLE command when they are no longer needed rather than waiting for the session to end. This immediately releases the storage resources.
• Limit the duration of sessions with large temporary tables to minimize the timeframe they are incurring storage charges.
• Monitor the storage usage of temporary tables during a session and trim or truncate data if possible. Optimize queries to reduce cached data volumes.
• Consider using Snowflake transient tables instead of temporary if the data needs to persist for an extended period past the session. Snowflake transient tables remain until explicitly dropped and can therefore be more cost-effective for long-term temporary data storage needs.

So if you carefully plan and optimize it, you can easily make the most of Snowflake temporary tables in a cost-effective way. They're great for handling short-term data needs flexibly. Just remember to keep an eye on their storage costs and factor that into your day-to-day practices.

What Is the Difference Between Temporary and Transient Tables in Snowflake?

What Is the Difference Between CTE and Temp Table in Snowflake?

How to Create Temporary Table in Snowflake Using CTE?

Common Table Expressions (CTEs) are a valuable SQL feature that allows you to provide a temporary result set within a query. In Snowflake, you can also create a temporary table using a Common Table Expression (CTE) with the help of the CREATE TEMPORARY TABLE statement. To create a temporary table from a CTE, use the following syntax:

CREATE TEMPORARY TABLE temp_table_name
AS
WITH cte_name AS (
    -- CTE query
)
SELECT * 
FROM cte_name;

Here's a step-by-step example:

Let's create a sample table orders to work with:

CREATE OR REPLACE TABLE orders (
  order_id INT,
  cust_name VARCHAR(50),
  order_date DATE,
  order_amount FLOAT
);

INSERT INTO orders VALUES
  (1, 'Elon Musk', '2024-05-01', 110.50),
  (2, 'Jeff Bezos', '2024-05-02', 175.25),
  (3, 'Mark Zuck', '2024-05-03', 210.00),
  (4, 'Larry Page', '2024-05-04', 250.75);

Now, let's create a temporary table temp_orders using a CTE that filters orders with an amount greater than 150:

CREATE TEMPORARY TABLE temp_orders
AS
WITH cte_orders AS (
  SELECT order_id, cust_name, order_date, order_amount
  FROM orders
  WHERE order_amount > 150
)
SELECT *
FROM cte_orders;

You can query the temporary table temp_orders like any other table:

SELECT * FROM temp_orders;

This will output:

Snowflake temporary tables are session-scoped, which means they can only be accessed during the current session and are automatically dropped when it ends. The key benefit of using CTE within the Snowflake temporary table is that it allows you to define a complex query and store the result set temporarily. This method can be handy when you need to perform many operations on the same result set or when you wish to split a complex query into smaller, more understandable chunks.

Want to take Chaos Genius for a spin?

It takes less than 5 minutes.

Start Free Trial

Enter your work email

Enter your work email

Start Free Trial

Conclusion

And that's a wrap!! Snowflake Temporary tables are extremely powerful for efficiently handling and storing non-permanent, temporary data in Snowflake. They can improve performance, reduce costs, and provide more flexibility compared to regular/permanent tables. If it is leveraged properly, Snowflake temporary tables allow you to only persist data at the highest necessary level, while keeping short-lived datasets lightweight.

In this article, we covered:

• What Is Snowflake Temporary Tables?
• What Are Snowflake Temp Tables Used For?
• Storage Cost Implications of Snowflake Temp Tables
• How to Create Snowflake Temporary Table?
• Practical Use Cases and Performance Benefits of Snowflake Temporary Tables
• Practical Examples of Using Snowflake Temporary Tables
• Comparisons Between Snowflake Temp Tables vs Transient Tables vs CTEs

…and so much more!

FAQs

What are the 3 types of tables in Snowflake?

The 3 types of tables in Snowflake are: Permanent, Transient, and Temporary.

How long do permanent tables last in Snowflake?

Permanent tables persist indefinitely in Snowflake unless explicitly dropped.

How long do transient tables last in Snowflake?

Transient tables persist beyond the current session until explicitly dropped.

What are Snowflake temporary tables?

Snowflake temporary tables only exist for the duration of the session created and provide a lightweight option for non-critical, short-term data.

How long do temporary tables last in Snowflake?

Snowflake temporary tables automatically expire when the session ends. The data is completely deleted and not recoverable afterward.

What are some use cases for Snowflake temporary tables?

Use cases include staging transient ETL data, data visulaization, caching frequently accessed results, storing session-specific application data, and testing queries.

Do Snowflake temporary tables incur storage costs?

Yes, storage used by temporary tables counts towards total usage and costs while the table exists.

Is the CREATE TABLE privilege required to create a Snowflake Temporary Table?

No, creating a Temporary Table does not require the CREATE TABLE privilege on the schema in which the object is created​

How can I manage costs with Snowflake temporary tables?

Drop tables when no longer needed, limit session times, monitor usage, and use transient tables for longer-term data.

What is the syntax to create a Snowflake temporary table?

Use CREATE TEMPORARY TABLE followed by the table name, columns, and data types.

Can a Snowflake temporary table have the same name as a regular/permanent table?

Yes, but the temporary table takes precedence in that session.

When should you use a temporary over permanent table in Snowflake?

For transient, non-critical data needing short-term storage.

What permissions do you need to create Snowflake temporary tables?

No special permissions, any user can create temporary tables.

Are Snowflake temporary tables visible across different sessions?

No, Snowflake temporary tables are only visible and accessible within the session created.

Is it possible to clone Snowflake temporary tables?

Yes, it is possible to clone a temporary table within the same session using CREATE TABLE LIKE or CREATE TABLE AS SELECT (CTAS).

What happens if you drop a permanent table that a temporary table is based on?

The temporary table remains unchanged for the session until it is dropped.

What is the difference between a view and a transient table?

A view is a virtual table based on a query, while a transient table is an actual database table that stores data, though not permanently like regular tables.

What is the difference between temporary and transient tables?

Transient tables persist past the session until explicitly dropped, unlike Snowflake  temporary tables which are automatically dropped after the session

How do temporary tables help manage concurrency and scalability in Snowflake?

By allowing each session to create a local copy of data in a temporary table, rather than all sessions hitting the same permanent table.

Can Snowflake Temporary Tables be converted to other table types?

No, after creation, Temporary Tables cannot be converted to any other table type.

How do you drop a transient table in Snowflake?

You can drop a transient table in Snowflake using the DROP TABLE command, just like dropping a permanent table.

Is Snowflake temporary table fail safe?

No, Snowflake temporary tables do not have a fail-safe period - the data is immediately deleted once the session ends.

Can temporary tables take advantage of Snowflake features like Time Travel?

No, temporary tables do not support features like Time Travel or Fail-safe which are designed for permanent tables.

What are some common use cases for temporary tables in Snowflake?

Common use cases include staging data, storing intermediate results, caching frequently accessed data, testing queries, and storing short-lived non-critical data.

Do temporary tables incur storage costs in Snowflake?

Yes, temporary tables utilize on-demand storage resources and incur costs while they exist, though lower than permanent tables.

How can you optimize storage costs for temporary tables in Snowflake?

Drop temporary tables when no longer needed, limit session duration for large temp tables, monitor usage, and consider transient tables for longer-lived data.