Time travel would be awesome!  Think about going back in time and buying a winning lottery ticket or buying stock in an up and coming company, just before it makes it.  It would be so great.  However, many time travel movies and shows always warn us about the dangers of changing timelines.  Maybe time travel isn’t a good thing.  Well, I have a few examples of time travel that would always be a good thing!

As a business user, do you wish you could go back in time and see how your data has changed over time   without laborious tasks of restoring backups or deploying full-fledged data warehouse? Don’t get me wrong, I’m not saying you don’t need a data warehouse, simply stating that sometimes the data you need to look at is not in your current data warehouse or maybe you never had a data warehouse and wish you did, Dunn Solutions can help you with that, but that’s for another blog.

The Importance of Time Travel for Developers and Database Administrators

As a developer or database administrator, have you ever dropped or truncated a table or deleted rows by accident?  Hopefully, you have backups you can restore from!  It is important to be extra careful about your update and delete statements. From my personal development experience, running 1000 lines of SQL code every day, I sometimes find myself in situations where I would love to just run a single line of code and go back to the snapshot before my last test SQL execution.

These are situations where database time travel would really come in handy.  I have some exciting news for you, Snowflake offers database time travel! 

Snowflake is a cloud-based data platform that enables time travel to access historical data at any point in time within a defined period. In this blog, I will talk about the time travel functionality in Snowflake and how it works. I’ll wrap up the blog by discussing when you can use it, why is this extremely important and how Dunn Solutions, a certified Snowflake partner, can help with your organization’s data infrastructure.

Understanding Time Travel in Snowflake

I call it the lifesaver functionality. In other words, time travel in Snowflake gives you the power and flexibility to restore tables, schemas, and databases that might have been deleted for some reason. It can also be used to perform data backups, data comparisons and analyzing data usage in the past over a certain period. Hence, it serves as a continuous data protection lifecycle based on the data retention periods defined for any object. It is important to understand different data retention periods that can be applied in different editions. The standard retention period is 1 day (24 hours) and its automatically enabled for all Snowflake accounts. For Snowflake Standard Edition, the retention period can be set to 0 (or unset back to the default of 1 day) at the account and object level. For Snowflake Enterprise Edition and higher, the retention period for permanent databases, schemas, and tables can be set to any value from 0 up to 90 days (excluding transient tables).

Why is Time Travel Important?

Coming back to the point where I said this functionality is a lifesaver, I want to stress on some of the most important tasks that can be achieved; I can query the data from the past that has been updated or deleted. This can be helpful in data comparison, data backup, and data restoration. I can also create clones of tables from the past, for different teams, for purposes of testing and reporting. I agree that there may be other ways of achieving this task and different processes that could be in place for automation but that requires time and effort, and I feel that time travel allows the flexibility and speed necessary for developing new, fast-paced solutions.

Let’s take an example of how easy it is to compare data between two points in time:

1. Create a weather table and insert data for two cities.
2. Wait for approximately five minutes and insert data for two more cities
3. Use ‘at’ keyword in the Select statement to query data from Weather table that existed 5 minutes ago
4. Only 2 newly added rows are returned when performing a comparison

Achieving Flexibility and Speed in Developing Data Solutions

Considering flexibility and speed, Time Travel comes in handy when developing data solutions. Dunn Solutions’ data analytics consultants have successfully created complex data warehouses and data lakes for numerous clients. As an architect, I am always looking for flexibility and speed in the overall development process of a data warehouse. This allows enough time to be allocated in designing the model, rather than getting stuck in the ETL development. Time Travel in Snowflake helps in maintaining data integrity while testing different solutions. As a result of this, you can not only build solutions with much greater flexibility but also ensure data accuracy and protection.

Restoring Data Without Deleting Data or Restoring Backups

Based on the scenario above, let’s look at how we can restore the first instance of our weather table without deleting data or restoring database backups:

1. Rename the latest version of Weather table to a temp table Weather 1
2. Create new Weather table from data that existed 5 minutes ago using the temp table
3. Drop the temp table

I was fascinated by this functionality when I first saw it in action. Running a drop or an update statement accidentally does not give me nightmares anymore. Moreover, I can look at data at any point in time in the past by executing a single line of code.

If you want to see some of this in action, please reach out to Dunn Solutions’ certified snowflake experts to discuss your data analytics, data lake or data warehouse challenges! There are many benefits that Snowflake offers. I am confident that Dunn Solutions’ Data Analytics team can deliver a solution that will address your organization’s data infrastructure.