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:
- Staging data extracted from an external source before loading into permanent tables
- Intermediate data sets used within a single session or query
- Session-specific application data
- Test or sandbox data used for trying queries or testing logic
- 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, ... );
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.
-- 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
|Snowflake Temp Tables||Snowflake Transient Tables|
|Snowflake Temp table exists only within the current session. Automatically dropped when the session ends||Persist beyond the current session and are available to all users with the necessary credentials until they are dropped|
|Snowflake Temp Table is only visible during the current session and not to other users or sessions||Visible to all users with necessary credentials, not session-based|
|Data is not recoverable once the session ends||No Fail-safe period, but data is recoverable during the Time Travel Retention Period. After this period, data cannot be recovered|
|Snowflake Temp Table is ideal for storing non-permanent, session-specific data like ETL data||Suitable for temporary data that needs to be retained post-session but does not require regular/permanent table-level data protection and recovery|
|No fail-safe period||No fail-safe period, thereby reducing certain storage-related costs|
|Can have the same name as a permanent table in the same schema. The permanent table gets hidden during the session||Governed by the same unique name restrictions as permanent tables; cannot have the same name as a permanent table|
|Features like Cloning are not supported. Data is not recoverable post-session||Some standard features like Cloning are not supported. Data cannot be recovered after the Time Travel retention term expires|
|Not designed for data requiring high levels of protection or long-term storage||Snowflake Transient Tables are viable options for data that does not require the high level of protection offered by permanent tables but needs to be stored beyond a single session|
Difference between Snowflake Temp Tables and Common Table Expression (CTEs)?
|Snowflake Temp Tables||Snowflake CTEs|
|Snowflake Temporary tables create an object in the instance that can be queried repeatedly during the session. They are primarily used when you have a large dataset that you want to refer to multiple times for a short period.||Snowflake CTEs are named subqueries defined within the WITH clause of a Snowflake SQL statement. They are used to break up complex queries into smaller, more manageable chunks and do not create any physical objects.|
|Snowflake Temporary tables exist only within the current session and are automatically dropped when the session ends||Exist only for the duration of the query and are not materialized. They are virtual views that have little overhead but can impact performance for complex queries.|
|Snowflake Temporary tables are visible and reusable within the session in which they are created||Can be referenced multiple times within the single SQL statement in which they are defined but are not accessible outside that statement.|
|Snowflake Temporary tables are suitable for handling larger datasets that need to be accessed multiple times within a session||Efficient for organizing complex queries, especially those involving hierarchical data structures like management hierarchies or component-subcomponent relationships.|
|Snowflake Temporary tables can be used to improve the performance of a query by avoiding redundant data processing||While they add modularity + simplify maintenance, they can impact performance, especially if they are complex or nested multiple times in a query. Recursive CTEs also have the potential to create infinite loops.|
|Snowflake Temporary tables act as standalone objects within a session and can be queried multiple times like regular tables||Integrated within a single SQL statement and act as a part of the query's execution plan. They increase modularity and simplify the maintenance of complex SQL queries.|
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!
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.