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 is Snowflake Temporary Table?

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

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.

What Are Snowflake 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 examples where Snowflake temporary tables can be useful:

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.

How to Create a Snowflake Temporary Table?

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

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.

Naming Conflicts and Session-Specific Behavior of Snowflake Temp 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.

Practical Use Cases and Performance Benefits of Snowflake Temp 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, 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 Snowflake Temporary Tables

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 Snowflake 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 transient tables instead of temporary if the data needs to persist for an extended period past the session. 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.

Difference between Snowflake Temp Tables and Snowflake Transient Tables

Difference between Snowflake Temp Tables and Common Table Expression (CTEs)?

Conclusion

And that's a wrap!! Snowflake Temporary tables are extremely powerful for efficiently handling and storing non-permanent, transitory 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 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.

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 temporary and transient tables?

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

Can Snowflake Temporary Tables be converted to other table types?

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