Do you wish you could travel across time and observe the evolution of your data over time without having to restore backups or implement a fully functional data warehouse as a business user? Please don't misunderstand what I'm saying: I'm not stating that a data warehouse isn't necessary; I'm simply stating that the data you require may not be available in your current data warehouse, or you may never have had one and wish you had.
Have you ever accidentally dropped, truncated, or erased rows from a table? Hopefully, you've prepared backups to fall back on! It's critical to be extra cautious when using update and remove statements. Developers or Database administrators occasionally find themselves in situations where they would like to execute a single piece of code and revert back to the snapshot before their last test SQL execution.
These are the kinds of circumstances where database time travel might be extremely useful. Snowflake supports database time travel. Snowflake Time Travel allows you to go back in time and view past data, i.e. data that has been modified or removed). As the data storage layer, Snowflake employs immutable cloud storage. This indicates that it makes a new version of the file rather than changing it. Time travel is one of the new and fascinating possibilities that this manner of functioning opens up.
It is an effective tool for doing the following tasks:
- Restoring data-related objects (tables, schemas, and databases) that may have been removed by accident or on purpose.
- Duplicating and backing up data from previous periods of time.
- Analyzing data manipulation and consumption over a set period of time.
Introduction to Snowflake Time Travel
Time travel is an interesting feature that allows you to access data from any point in the past. If you have an Employee table, for example, and you unintentionally delete it, you can utilize time travel to go back 5 minutes and retrieve the data.
It also can be used to back up data, compare data, and examine previous data usage over a period of time. As a result, it acts as a continuous data protection lifecycle that is dependent on the data retention durations set for each item. It's critical to comprehend the various data retention periods which can be used in distinct versions. All Snowflake accounts have a standard retention duration of one day (24 hours), which is enabled by default. At the account and object level, the retention period in Snowflake Standard Edition can be adjusted to 0 (or unset to the default of 1 day). The retention time for everlasting databases, schemas, and tables in Snowflake Enterprise Edition and higher can be specified to any value between 0 and 90 days (with the exception of transient tables).
Consider the following scenario: You're working on a bug patch and are connected to the PROD database, where you executed an update statement on a table that updated billions of records. On that table, you also conducted additional delete and update logic. You later realized that when using update and delete statements in your SQL query, you forgot to use the necessary where clause.
Time Travel SQL Extensions
We use the Time Travel SQL Extensions AT or BEFORE clause in SELECT queries and CREATE... CLONE commands in Snowflake Time Travel to retrieve or clone historical data. We use the SQL Extensions with the following arguments to locate the particular historical data that you want to access:
- TIMESTAMP
- OFFSET (time difference from the current time in seconds).
- STATEMENT (statement's identifier, e.g. query ID).
The characteristic of time travel relies heavily on the data retention duration. When a user makes changes to a table, Snowflake saves the current state of the data before making any alterations. And this state of data from the past will last for a set amount of time, which is known as the Time travel data retention period.
For all Snowflake accounts, the default data retention duration is one day. It is 1 day by default for standard aims, and it ranges from 0 - 90 days for enterprise edition and higher accounts.
How to Specify the data retention period for Time Travel?
Steps to specify the data retention duration are as follows:
- DATA_RETENTION_TIME_IN_DAYS object parameter can be used by users with the ACCOUNTADMIN role to set the data retention time for your account.
- When building a schema, database, or individual table, the DATA_RETENTION_TIME_IN_DAYS object option is also used to alter the default.
- At any moment, users can change the data retention term for a schema, database, or table.
You can either increase or decrease the data retention duration.
Increasing Retention: It extends the data retention time for time travel data.
Decreasing Retention: It reduces the data retention duration for time travel data.
No comments:
Post a Comment