Snowflake Cheatsheet: Working with Permanent, Transient and Temporary Tables

Snowflake offers three types of tables: permanent, transient, and temporary. Each type of table has its own characteristics and is suited for different purposes.

Permanent tables are the most common type of table in Snowflake. They persist indefinitely until they are explicitly dropped. Permanent tables are fully managed by Snowflake, which means that they are automatically backed up and recovered in the event of a failure.

Transient tables are similar to permanent tables in that they persist until they are explicitly dropped. However, transient tables do not have a fail-safe period, which means that they are not recoverable in the event of a failure. Transient tables are also not visible to all users, but only to those with the appropriate privileges.

Temporary tables are only visible to the current session and are dropped automatically when the session ends. Temporary tables are useful for storing data that is only needed for the current session.

The following table summarizes the key differences between permanent, transient, and temporary tables in Snowflake:

FeaturePermanentTransientTemporary
PersistenceIndefinitelyUntil explicitly droppedUntil current session ends
Fail-safeYesNoNo
VisibilityAll usersUsers with appropriate privilegesCurrent session only
Time TravelYesYes (up to 1 day on Snowflake Standard Edition & 90 days in Enterprise Edition)No

Note:

  • The default time travel retention period for permanent tables in Snowflake Standard Edition is 1 day. This means that you can query historical data for up to 1 day in the past. You can increase the time travel retention period to up to 90 days if you have a Snowflake Enterprise Edition subscription.
  • The fail-safe period for permanent tables is 7 days. This means that if a permanent table is dropped, its data will be retained for 7 days in case of a failure.
  • Transient and temporary tables do not have a fail-safe period. This means that if a transient or temporary table is dropped, its data will be lost permanently.


Which type of table should you use?

The type of table you should use depends on your specific needs. If you need a table that will persist indefinitely and be recoverable in the event of a failure, then you should use a permanent table. If you need a table that will persist until it is explicitly dropped, but you do not need it to be recoverable in the event of a failure, then you should use a transient table. If you only need a table for the current session, then you should use a temporary table.

Here are some examples of when you might use each type of table:

  • Permanent tables: You would use a permanent table to store data that needs to be retained for the long term, such as historical data or master data.
  • Transient tables: You would use a transient table to store data that is only needed for a short period of time, such as intermediate results or data that is used for testing.
  • Temporary tables: You would use a temporary table to store data that is only needed for the current session, such as data that is used in a single query or a set of related queries.

We can create temporary, transient and permanent tables easily:

USE DATABASE KODEY;
USE SCHEMA KODEY_CO_UK;
CREATE TEMPORARY TABLE TEMP AS
SELECT * FROM KODEY.KODEY_CO_UK.ARTICLES
CREATE TRANSIENT TABLE TRANSIENT AS
SELECT * FROM KODEY.KODEY_CO_UK.ARTICLES
SELECT * FROM KODEY.KODEY_CO_UK.TEMP
SELECT * FROM KODEY.KODEY_CO_UK.TRANSIENT

We can manage the time travel period using the below statement.

ALTER TABLE KODEY.KODEY_CO_UK.ARTICLES SET DATA_RETENTION_TIME_IN_DAYS = 3;

I hope this blog post has helped you to understand the difference between permanent, transient, and temporary tables in Snowflake. If you have any questions, please feel free to leave a comment below.

I have used Google Bard to help generate much of the text to articulate the article topic quickly.

Share the Post:

Related Posts