very useful session. can you explain more on exact befits using transient tables, if we want to extend retention period of a table, how to change the values, where to change.
If we use Transient table , then we can reduce the storage cost incase , you dont want to time travel more than 1 day. We can extend retention period for the permanent table by using data_retention_time_in_days clause at the time of creation or later by using alter command. For example, 1) CREATE OR REPLACE TABLE mytable_30(col1 number, col2 date) data_retention_time_in_days=30; 2) alter table if exists mytable set data_retention_time_in_days=90;
if you play on the EU data market, you should support GDPR requirements. One of them- delete customer info by request. you can't do it in any way 7 days if you use regular tables, because you can't change file safety settings. It's main reason to use transient tables. second reason: if you have huge amount of DMLs on the big tables- standard backup(file safety feature) isn't optimal way. transient tables + old fashion CTAS by schedule is "the way". PS data_retention_time_in_days = 90 for regular tables can be used only on EE+ versions.
No option to create a temp database and temp schema in snowflake. we can create snowflake permanent/transient database we can create snowflake permanent/transient/managed schemas
Dear Vickey, I tried creating with same table for permanent, transient and temporary tables and it creates, however table get overridden with either permanent or transient. so latest created get reflected. -- CREATING TABLES WITH SAME NAME; CREATE OR REPLACE TABLE CUSTOMER (ID NUMBER, DATES DATE); -- PERMANENT TABLE CREATE OR REPLACE TRANSIENT TABLE CUSTOMER (ID NUMBER, DATES DATE); -- TEMPORARY TABLE CREATE OR REPLACE TEMPORARY TABLE CUSTOMER (ID NUMBER, DATES DATE); -- TEMPORARY TABLE SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG ='DEV_DB' AND TABLE_SCHEMA='TABLE_TYPES'; DROP TABLE CUSTOMER;
thanks for sharing info
My pleasure
Nice, Thanks
Thank you Rajesh.
I assume, that it's good to add info about external tables to this video.
Nice
very useful session. can you explain more on exact befits using transient tables, if we want to extend retention period of a table, how to change the values, where to change.
If we use Transient table , then we can reduce the storage cost incase , you dont want to time travel more than 1 day. We can extend retention period for the permanent table by using data_retention_time_in_days clause at the time of creation or later by using alter command.
For example, 1) CREATE OR REPLACE TABLE mytable_30(col1 number, col2 date) data_retention_time_in_days=30;
2) alter table if exists mytable set data_retention_time_in_days=90;
@@VCKLYTech Thank you vey much.
if you play on the EU data market, you should support GDPR requirements. One of them- delete customer info by request. you can't do it in any way 7 days if you use regular tables, because you can't change file safety settings. It's main reason to use transient tables. second reason: if you have huge amount of DMLs on the big tables- standard backup(file safety feature) isn't optimal way. transient tables + old fashion CTAS by schedule is "the way". PS data_retention_time_in_days = 90 for regular tables can be used only on EE+ versions.
what would be scenario or use or die advantage if we can create both db and schema as temp and inside of this if we create permanent tables
No option to create a temp database and temp schema in snowflake.
we can create snowflake permanent/transient database
we can create snowflake permanent/transient/managed schemas
@@VCKLYTechwhat would be the data protection if we create transient db and created permanent table inside it .
Can we use clustering in temporary table?
I think so
bro how to create a procedure that monitor changes in table plz send code
no need to create procedure , you just create a steam on your table so ur stream will capture the DML changes
Dear Vickey, I tried creating with same table for permanent, transient and temporary tables and it creates, however table get overridden with either permanent or transient. so latest created get reflected. -- CREATING TABLES WITH SAME NAME;
CREATE OR REPLACE TABLE CUSTOMER (ID NUMBER, DATES DATE); -- PERMANENT TABLE
CREATE OR REPLACE TRANSIENT TABLE CUSTOMER (ID NUMBER, DATES DATE); -- TEMPORARY TABLE
CREATE OR REPLACE TEMPORARY TABLE CUSTOMER (ID NUMBER, DATES DATE); -- TEMPORARY TABLE
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG ='DEV_DB' AND TABLE_SCHEMA='TABLE_TYPES';
DROP TABLE CUSTOMER;
When user dont use or replace, then it throws error. otherwise it allows user to crate
Please try with out replace while creating tables so that we will get to know what scenario possible with same table name with different table types.