Converting Permanent table to Transient Table in Snowflake

Snowflake Transient tables are similar to that of permanent tables except they don't have fail-safe period. Therefore, cost associated with fail-safe is not applicable to transient tables.

To convert the Permanent table to Transient table in Snowflake, we have to follow the hot swap method.

• First create the new Transient table with the data
• Provide grants to the newly created Transient table
• drop the old permanent table
• Rename the Transient table to the right name

CREATE TRANSIENT TABLE TR_Employee AS 
SELECT * FROM Employee;

GRANT SELECT, TRUNCATE, DELETE ON TR_Employee TO SYSUSER1;

DROP TABLE Employee;

ALTER TABLE TR_Employee RENAME TO Employee;

nVector

posted on 26 Jan 21