Articles
Transient table? That was a new one for me. In ancient SQL times, I often used temporary tables, whether for performance gains, code readability and debugging, or just to drive crazy the developer who would see the code next — all good reasons.
Oxford Language defines “transient” and “temporary” as:
- Transient: Lasting only for a short time; impermanent.
- Temporary: Lasting for only a limited period of time; not permanent.
Very helpful. So, what is the difference between these two objects, and when is it practical to use one over the other, or neither?
In my last post, I highlighted the different types of tables in Snowflake and provided a brief overview of each. Let’s have a quick review and explore a few characteristics and features. Remember, transient and temporary tables are quite similar — their key differences lie in their purpose, scope, and duration.
What are the shared characteristics?
- Both transient and temporary tables are typically used for intermediate storage;
- They provide an opportunity for cost savings;
- They are suitable for data that does not require data recovery (can be easily reloaded/regenerated)
- They are useful for performing complex data manipulations when a permanent table is not required.
- They have no Fail-safe period and a limited Time Travel period.
What are the key differences?
|
Transient Tables |
Temporary Tables |
|
Persist until explicitly dropped |
Exist for the duration of the session* |
|
Can be accessed by others with the needed permissions |
Cannot be accessed by others |
|
Must have a unique name |
Can have the same name as an existing table** |
Understanding key concepts and features
*Sessions vs. Connections
A connection and a session are separate concepts within Snowflake. A Snowflake session is terminated only when the user explicitly terminates the session or the session times out due to inactivity after four hours.
Disconnecting from Snowflake does not terminate the active sessions. Thus, a Snowflake session may be very long-lived, and any temporary tables created within that session will continue to exist until they are dropped or the session is terminated. To avoid unexpected storage costs for temporary tables, explicitly drop the temporary tables when no longer needed.
**Temporary Table Naming Considerations
You can create temporary and non-temporary tables with the same name and within the same schema. The temporary table takes precedence in the session over any other table with the same name in the same schema. This can lead to potential conflicts and unexpected behavior, particularly when performing DDL on both temporary and non-temporary tables. Unless you have a specific reason, it is good practice to name temporary tables with an obvious identifier, such as a prefix or suffix indicating that it is temporary.
In the example below, I create two tables with the same name. After running the commands, I can see they are both created using the Info Schema, one marked temporary.
CREATE OR REPLACE TABLE BballCoaches AS SELECT DISTINCT TEAMNAME, CURRENTCOACH FROM MARCHMADNESS2024 WHERE SEASON = 2024; CREATE OR REPLACE TEMPORARY TABLE BballCoaches AS SELECT DISTINCT TEAMNAME, CURRENTCOACH FROM MARCHMADNESS2024 WHERE SEASON = 2024;
If I now begin a statement in the same session, auto-complete defaults to the temporary table, not to the permanent table.
Within the same session, I add a new column to the table:
Which table did I just alter? Only the temporary table.
If I create a new session and query, I get the result below, because the temporary table exists only in the other session. The new column was added only to the temporary table, as expected.
The moral of this story: unless you have a compelling reason, always name temporary tables with an obvious identifier that clearly differentiates them from permanent tables.
Table Access
Temporary tables cannot be accessed by others; this is seen in the above example. In fact, though I created it I cannot access the table if I am in a different session. Transient tables, on the other hand, can be accessed by others as long as they have permission.
Below I am creating a transient table, using my sysadmin role.
Now, I log in as a different user in the public role, which has the usage privilege on the database and read rights on the transient table.
As seen below, the sample user can select data from the transient table.
Cloning Considerations
If you find yourself needing or wanting to clone temporary and transient objects, you should know the limitations. To see this in action, below I’ve created a permanent date dimension table, then used the same SQL to create it as a temporary and transient table. Then I started cloning.
| Temporary | //Clone Temp table to Perm table
CREATE TABLE DATES_TEMP_CLONE CLONE DATES; SQL compilation error: Temp table cannot be cloned to a permanent table; clone to a transient table instead |
| //Clone Temp table to Temp table
CREATE TEMPORARY TABLE DATES_TEMP_CLONE_TOTEMP CLONE DATES_TEMP; Table DATES_TEMP_CLONE_TO_TEMP successfully created. |
|
| //Clone Temp table to Transient table
CREATE TRANSIENT TABLE DATES_TEMP_CLONE_TO_TRANSIENT CLONE DATES_TEMP; Table DATES_TEMP_CLONE_TO_TRANSIENT successfully created. |
|
| Transient | //Clone Transient table to Perm table
CREATE TABLE DATES_TRANSIENT_CLONE_TO_PERM CLONE DATES_TRANSIENT; SQL compilation error: Transient object cannot be cloned to a permanent object. |
| //Clone Transient table to Temp table
CREATE TEMPORARY TABLE DATES_TRANSIENT_CLONE_TO_TEMP CLONE DATES_TRANSIENT; Table DATES_TRANSIENT_CLONE_TO_TEMP successfully created. |
|
| //Clone Transient table to Transient table
CREATE TRANSIENT TABLE DATES_TRANSIENT_CLONE_TO_TRANSIENT CLONE DATES_TRANSIENT; Table DATES_TRANSIENT_CLONE_TO_TRANSIENT successfully created. |
To summarize, you cannot clone a temporary or transient table into a permanent table. Snowflake’s documentation explains this and it is a point we’ve confirmed with the above code.
Keep in mind that, if you create a transient table as a clone of a permanent table, Snowflake creates a zero-copy clone. This means that no additional data storage is used because it shares all of the existing micro-partitions of the original permanent table. However, when rows are modified in the clone, it results in new micro-partitions that belong exclusively to the clone (in this case, the transient table).
Storage
Transient tables do not have a Fail-safe period, making them a cost-effective option for managing very large tables used to store transient data. Just remember that data in these tables cannot be recovered once the Time Travel retention period expires.
Summary
- Temporary tables are dropped when the session (not the connection) in which they were created ends. Transient tables exist until explicitly dropped.
- Data stored in temporary tables is not recoverable after the table is dropped.
- Once created, temporary tables cannot be converted to any other table type.
- Data in transient tables cannot be recovered by Snowflake after the Time Travel retention period ends.
- Fail-safe costs can be eliminated by utilizing transient tables for short-lived and easily re-built tables such as work tables and tally tables.
- As there is no Fail-safe period, no additional data storage charges are incurred beyond the Time Travel retention period.
- In both temporary and transient tables, a long-running Time Travel query will delay the purging of temporary and transient tables until the query completes.
- If the table needs to be accessed by another user, use transient tables.
- Explicitly dropping temporary objects is best practice.
| Transient Table | Temporary Table | |
| Life | Explicitly dropped | End of Session or Explicitly Dropped |
| Access | Users with Permissions | Current User Only |
| Time-Travel | 24 hours or None | [24 hours or the remainder of the session] or None |
| Fail-Safe | No | No |
| Cloning (SRC > Target) | Transient > Transient Transient > Temporary |
Temporary > Transient Temporary > Temporary |
Snowflake’s official documentation can be found here. Snowflake’s documentation always should be the primary source for any architecting and development decisions.