Snowflake Temporary Tables vs. Transient Tables

By: Sergey Gigoyan   |   Updated: 2023-03-01   |   Comments   |   Related: More > Snowflake

Problem

When working with Snowflake, it is important to understand the different types of tables available and when to use each one. In this tutorial, we will compare Snowflake temporary and transient tables, highlight their similarities and differences, and provide examples of when to use each type of table.

Solution

In Snowflake, there are two types of temporary tables: temporary tables and transient tables.

Temporary tables are only visible to the current session and are dropped automatically when the session ends. On the other hand, transient tables have a wider scope of visibility and persist beyond the current session unless explicitly dropped. Once created, it is impossible to convert a temporary or transient table into any other type of table.

When working with temporary and transient tables in Snowflake, it's important to consider their integration with the Time Travel and Fail-safe features. Time Travel allows you to query data as it existed at a specific point in time. At the same time, Fail-safe protects historical data in the event of unexpected system failures or security breaches. Both temporary and transient tables can have Time Travel enabled with a retention period of up to one day. This means you can query the table data as it existed at a specific point in time within a 1-day window.

Regarding Fail-safe, neither temporary nor transient tables support this feature.

Snowflake Temporary Tables

Temporary tables are useful for storing data needed for a session-related specific task or operation, such as query optimization or managing staging data. When a temporary table is created in Snowflake, the data stored in the table will add to the overall storage charges for your account. To avoid any unexpected changes in storage costs, it is best practice to drop the table when it is no longer needed. To prevent additional charges, you can also end the session where the table was created. Here is an example of creating a temporary table in Snowflake:

CREATE DATABASE TestDB;

CREATE SCHEMA TestSchema;

CREATE TEMPORARY TABLE tmpTestTable (ID INT, Val VARCHAR(50));

SELECT * FROM TestDB.TestSchema.tmpTestTable;

The code above creates a database called TestDB and TestSchema schema within it. A temporary table is created in TestSchema. In the UI, we can see that tmpTestTable appears under the TestDB.TestSchema:

If we open a new worksheet and try to query our temporary table, we will receive an error as it is visible only in the scope of the session that created it:

Now, let's close the worksheet where the temporary table is created. After doing that, we can see that the temporary table disappears under the TestDB database:

Closing the worksheet ends the session that created the tmpTestTable temporary table. Therefore, tmpTestTable is automatically dropped.

Snowflake Transient Tables

In contrast, transient tables are intended for data that needs to be retained for longer than a single session but do not require the same level of data protection and recovery provided by permanent tables. Unlike permanent tables, transient tables do not have a Fail-safe period, a feature unique to permanent tables. Also, the maximum available Time Travel can be set up to one day. This makes them ideal for temporary or transitory data that will not be needed in the long term. However, it's important to note that due to the absence of Fail-safe and supported maximum 1-day Time Travel, transient tables are not recommended for data that requires data protection and recovery.

Transient tables are created using the CREATE TRANSIENT TABLE command and persist after the session unless explicitly dropped. Here is an example of creating a transient table in Snowflake:

CREATE TRANSIENT TABLE transientTestTable(ID INT, Val VARCHAR(50));

If we query transientTestTable from another worksheet, we can see that the transient table is available from other sessions:

Also, if we close the session, the transient tables will exist, unlike temporary tables. Let's close the worksheet where the transient table is created and locate the transientTestTable under TestDB:

The data stored in transient tables, like permanent tables, will incur storage charges on your Snowflake account. However, since transient tables do not have a Fail-safe feature, there will be no additional costs for maintaining data for disaster recovery purposes, unlike the costs associated with permanent tables.

Conclusion

In conclusion, understanding the differences between temporary and transient tables in Snowflake can help you make more informed decisions on how to store and manipulate temporary data. By carefully considering the use cases and costs associated with each type of table, you can optimize data management and reduce costs in Snowflake.

Next Steps

For additional information, please follow the links below:

• Working with Temporary and Transient Tables — Snowflake Documentation
• Comparison of Table Types - Snowflake Documentation
• Understanding & Viewing Fail-safe — Snowflake Documentation
• Understanding & Using Time Travel — Snowflake Documentation

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips