Snowflake Transient tables are similar to that of permanent tables only difference is that they don not support fail-safe period. Therefore, cost associated with fail-safe is not applicable to transient tables. You can use transient tables in an ETL design to hold temporary data. In this article, we will check how to convert permanent table to transient table in Snowflake.
Transient Table in Snowflake
As mentioned earlier, transition tables are similar to managed tables with key difference such as fail-safe is not available. The transient tables are designed for transitory data that needs to be maintained beyond the current session, which is not possible in case of temporary tables.
Because transient tables do not have a Fail-safe period, they provide a good option for managing the cost of very large tables used to store transitory data. However, you or Snowflake cannot recover the data after the Time Travel retention period passes.
Related Articles,
Convert Permanent table to Transient Table in Snowflake
There may be a situation where you may want to convert large permanent or managed table to transient table. The conversion may be due to reduce the cost of storage.
There is no direct command that allows you to migrate permanent tables to transient tables. However, there is a work around for the same.
Migrating data from permanent tables to transient tables involves performing the following tasks:
- Use CREATE TRANSIENT TABLE … AS SELECT (CTAS) to create and populate the transient tables with the data from the original, permanent table.
CREATE TRANSIENT TABLE TR_TEST1 AS
SELECT * FROM TEST1;- Grant all access control privileges granted on the original table to the new transient table.
GRANT SELECT, TRUNCATE, DELETE ON TR_TEST1 TO SYSUSER1;- Drop original, permanent Table
DROP TABLE TEST1;- You can rename the transient table to original table so that existing applications pointing to original table should not fail. For example,
ALTER TABLE TR_TEST1 RENAME TO TEST1;Hope this helps 🙂