26 Jan 21 · npack · #Snowflake ·   Bookmark   ×

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;

npack

posted on 26 Jan 21

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds