Welcome back. In this lesson, I'll review time travel, a unique and powerful feature that Snowflake provides. Time travel enables access to historical data which was either deleted or updated at any point within a defined period. Let's begin. Time travel is a very cool feature that can be used to recover data that has either been deleted, modified, or dropped, either intentionally or unintentionally. With time travel, you can confidently restore data from a pass point in time within a defined retention period in near real-time. There's no special requirements to preconfigure periodic database snapshots or backups etc.

Time travel is a feature that is turned on and available by default. With time travel, you can confidently experiment on your data sets knowing that if things go wrong, you can always recover quickly and easily. As already mentioned, because of Snowflake's unique storage architecture, recovery times are near instant. Time travel data recovery is only available within a defined retention period. The retention period being either 24 hours for the standard edition of Snowflake or 90 days for the enterprise edition. Regardless, when the retention period expires, a further 7-day failsafe period is available for you during which you can consult with the Snowflake support team who can recover data on your behalf. Out beyond this period, deleted data and/or objects dropped will be gone for good.

Note: it is only the sys admin role that can execute time travel queries within the 24-hour or 90 days retention period. Snowflake provides simple to use time travel SQL extensions, which make it super easy to either query deleted data from the past or restore dropped objects. The following slide presents a number of time travel select queries which highlights some of the ways to query deleted table data.

In the first example, the select query leverages the at and offset keywords to query all historical data stored in the cloudacademydb.training.coursestable as of 10 minutes ago. The second example leverages the at and timestamp keywords to query all historical data stored in the cloudacademydb.training.coursestable at the specified date time. The third and final query leverages the before and statement keywords to query all historical data stored in the cloudacademydb.training.coursestable up to but not including any changes made by the specified statement.

The following slide now presents a number of time travel cloning type queries, highlighting some of the ways to use time travel to clone objects. In the first example, the create schema command, in tandem with the clone keyword, is used to create a clone of the cloudacademydb.trainingschema and all of its objects as they existed one hour before the current time, renaming it cloudacademydb.training_cloned. The second example is similar in intention to the first, but this time clones an actual table at a defined point in time as specified within the provided timestamp. And the third and final example clones an entire database and all of its objects as they existed prior to the completion of the specified statement.

The following slide now presents a number of time travel undrop-based queries, demonstrating to you some of the ways to use time travel to restore various dropped objects, noting that when a table, schema, or database is dropped, it is not permanently erased, rather it can be quickly restored using the undrop command within the retention period applicable to the object. In the first example, the undrop keyword is used to restore a specific database table. In this case, the cloudacademydb.training.coursestable. The second example uses the undrop keyword to restore a specific schema and all of its child objects. In this case, the cloudacademydb.trainingschema. And finally, the third example undrops an entire database. In this case, the Cloud Academy DB database.