|
Time travel seems to be a good part that enables users to access data from any point in the past. For instance, if you already have an Employee table and unintentionally delete it, users could use time travel to go back 5 minutes and fetch the information. Snowflake Time Travel enables users to access historical data (information which has been altered or removed) at whatever moment in time. It is an effective tool for completing the necessary tasks:
Query data which has been updated or deleted in the past.
Clone entire tables, schemas, and databases at or before specific dates in the past.
Restore previously deleted tables, schemas, and databases
Become a Snowflake Certified professional by learning this HKR Snowflake Training !
See data from a table as of 5 minutes ago:
select * from my_table at(offset => -60*5);
To check the data as of timestamp:
select * from my_table
at(timestamp => 'Mon, 01 May 2015 16:20:00 -0700'::timestamp);
The data retention period is an important aspect of Snowflake Time Travel.
When information in a table is customised, such as deletion or dropping an object containing data, Snowflake saves the data's previous state. The data retention period identifies the amount of days that this history data is kept and, as a result, Time Travel operations (SELECT, CREATE... CLONE, UNDROP) can be performed on it.
Get ahead in your career with our Snowflake Tutorial !
Users with the ACCOUNT ADMIN role can set the default retention period for your account by using the DATA RETENTION TIME IN DAYS object parameter.
When entering data, schema, or individual table, its same parameter can be used to expressly overpower the default.
A database, schema, or table's data retention period could be modified.
With the standard, 1-day retention period, time travel is enabled by default. However, you may want to consider upgrading to Snowflake Enterprise Edition in order to configure longer data retention periods of up to 90 days for databases, schemas, and tables.
The example below generates a table with 90 days of retention for time travel.
create table mytable(col1 number, col2 date) data_retention_time_in_days=90;
To reduce the retention period for a particular table
alter table mytable set data_retention_time_in_days=30;
Time Travel cannot be turned off for an account, but it can be turned off for individual databases, schemas, and tables by specifying DATA RETENTION TIME IN DAYS with a value of 0 for the object.
alter table mytable set data_retention_time_in_days=0;
If you accidentally dropped an object, you can recover it by using the undrop command.
UNDROP TABLE MyTable;
UNDROP SCHEMA MySchema;
UNDROP DATABASE MyDatabase;
Storage fees are levied for keeping historical data during both the Time Travel and Fail-safe periods.
Snowflake reduces the amount of storage needed for historical data by keeping only the information needed to restore the individual table rows that were updated or deleted. As a result, storage usage is calculated as a percentage of the changed table. Table full copies are only kept when tables are dropped or truncated.
Storage fees are calculated for each 24-hour period (i.e. one day) beginning with the time the data was changed. The number of days historical data is kept is determined by the table type and the table's Time Travel retention period.
Top 30 frequently asked snowflake interview questions & answers for freshers & experienced professionals
Cloud storage files (S3 / Cloud buckets / blob) are immutable. That is, we cannot change the files; whenever a change occurs to a file (such as an insert / delete / update), Snowflake cannot adjust the file; instead, it must open the file. Snowflake saves the previous version of the file for a set period of time to allow for time travel.
This is a restriction of the cloud, and Snowflake has brilliantly transformed this limitation into an appealing characteristic for its clients.
The limitations of time travel are outlined below (these were taken from Snowflake's website, which has more information on Time Travel).
The default retention period is one day (24 hours) and is fully implemented for all Snowflake accounts. The retention period for Snowflake Standard Edition can be set to 0 at the account and object levels (for databases, schemas and tables).
In the above blog post we discussed the importance of time travel in snowflake, how time travel works, limits of time travel, etc in a more detailed way. Had any doubts, drop your queries in the comments section to get them resolved.
Related Articles:
Batch starts on 3rd Jun 2023, Weekend batch
Batch starts on 7th Jun 2023, Weekday batch
Batch starts on 11th Jun 2023, Weekend batch
©2023 HKR Trainings. All rights Reserved.
On Our Website all Courses, Technologies, logos, and certification titles we use are their respective owners' property, Trademarks & their intellectual Property belong to them. All the firm, service, or product names on our website are solely for identification purposes. We do not own, endorse or have the copyright of any brand/logo/name in any manner. Few graphics on our website are freely available on public domains. we use all these just for the purpose of training only.
