Chapter 8: Back to the Future with Time Travel

Dealing with data issues is never a pleasant job and especially so when you cannot determine when the data was changed or whether the data has been lost altogether. Snowflake provides an incredibly unique way of going back in time through the Time Travel feature. This chapter explores the various applications of the Time Travel feature and combines it with cloning to tackle common data loss and debugging issues.

The following topics will be covered in this chapter:

• Using Time Travel to return to the state of data at a particular time
• Using Time Travel to recover from the accidental loss of table data
• Identifying dropped databases, tables, and other objects and restoring them using Time Travel
• Using Time Travel in conjunction with cloning to improve debugging
• Using cloning to set up new environments based on the production environment rapidly

Technical requirements

This chapter requires access to a modern internet browser (Chrome, Edge, Firefox, and so on) and access to the internet to connect to your Snowflake instance in the cloud.

The code for this chapter can be found at the following GitHub URL: https://github.com/PacktPublishing/Snowflake-Cookbook/tree/master/Chapter08.

Using Time Travel to return to the state of data at a particular time

In this recipe, we will go back to a point in time for a table or a set of tables and query the data at that time using the Time Travel functionality.

Getting ready

You will need to be connected to your Snowflake instance via the web UI or the SnowSQL client to execute this recipe.

How to do it…

We will be creating a table and populating that table with some sample data. Further on in the recipe, we will run an update on this table and demonstrate how to recover to a point before the data was updated. The steps are as follows:

1. We will create a new database, followed by creating a table containing some sample customer data. We will be using sample data provided by Snowflake to populate this table. To do so, run the following SQL:

   CREATE DATABASE C8_R1;
   CREATE TABLE CUSTOMER AS 
   SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.  CUSTOMER
   LIMIT 100000;

2. Let's validate that...

Using Time Travel to recover from the accidental loss of table data

This recipe will go through a scenario of accidental data loss after a delete query has been executed, affecting data in a table. We will demonstrate how we can recover data after it has been deleted accidentally.

Getting ready

You will need to be connected to your Snowflake instance via the web UI or the SnowSQL client to execute this recipe.

How to do it…

We will be creating a table and populating that table with some sample data. Further on in the recipe, we will run DELETE on this table and demonstrate how to recover to a point before the data was deleted. The steps for this recipe are as follows:

1. We will create a new database, followed by creating a table containing some sample customer data. We will be using sample data provided by Snowflake to populate this table. To do so, run the following SQL:

   CREATE DATABASE C8_R2;
   CREATE TABLE CUSTOMER AS 
   SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL...

Identifying dropped databases, tables, and other objects and restoring them using Time Travel

Time Travel can be used to recover tables, schemas, and even complete databases. In this recipe, we are given a scenario where a database and other objects have been deleted. We will identify what has been deleted and recover them back to the previous state.

Getting ready

You will need to be connected to your Snowflake instance via the web UI or the SnowSQL client to execute this recipe.

How to do it…

We will first create a database, then two schemas in that database, and some tables within those schemas. We will then gradually delete the tables, schemas, and eventually the complete database. Then we will try to recover them through the Time Travel feature:

1. We will start by creating a new database, followed by the creation of a schema. To do so, run the following SQL:

   CREATE DATABASE C8_R3;
   CREATE SCHEMA SCHEMA1;

2. Next, we will create a test table called CUSTOMER...

Using Time Travel in conjunction with cloning to improve debugging

Often, the debugging of data issues requires restoring data to a point and rerunning the process. It is typically required in traditional databases to make an actual copy of your production system's data. Additionally, if you require data as it existed at a specific time or in a specific state, it is almost impossible to achieve without significant effort. The Snowflake Time Travel feature combined with cloning simplifies that process. In this recipe, we will create a clone of a complete database at a point in time so that the development team can use the cloned version to validate and debug their code.

Getting ready

You will need to be connected to your Snowflake instance via the web UI or the SnowSQL client to execute this recipe.

How to do it…

We will be creating a database, two schemas, and then some tables within those schemas. We will then add some sample data in these tables, followed...

Using cloning to set up new environments based on the production environment rapidly

In traditional database and data warehousing, setting up a new development environment can be a lengthy and costly process. The process typically requires replicating the production structures in a new development database. Then some sample data in the production databases is copied over into the new development database. The process is time-consuming and error-prone since it involves running table DDLs in a new environment and then inserting the sample data. Creating one or several new environments based on an existing production environment is a straightforward process in Snowflake. In this recipe, we will explore how we can use the cloning capabilities to create new development environments in a matter of minutes.

Getting ready

You will need to be connected to your Snowflake instance via the web UI to execute this recipe.

How to do it…

We will be creating a database, two schemas...