If you google the word ‘snowflake‘ for the first time, you will likely get references to the ‘it insult‘ word,  Taiwanese dessert, a crystal of snow, etc.  Once you find that Snowflake is the hot new data warehouse in the cloud, your subsequent searches are likely to be more productive in terms of digging up documentation. However, complex topics such as “Snowflake database recovery” have likely not made it into Stackoverflow or your favorite internet group yet. 

In this article, I will tell you a story about a day I sweated and thought I would lose my job and how Snowflake saved it!

Cloud Big Data Architecture

The project I am working on involved building a data warehouse from scratch on AWS. It is quite complex, with myriads of data sources, ETL pipelines, integration with multiple systems, etc. A high level architecture is shown in the diagram below:

Figure 1: Cloud Big Data Architecture

Cloud Data Warehouse

We chose Snowflake to serve as the Cloud Data Warehouse. It is a scalable, performant and resilient data warehouse explicitly built for the cloud. It is available on AWS and Azure with GCP in the works.

Setting up and using Snowflake is a breeze; I won’t go into details as you can read about it in their documentation.

Cloning in Snowflake

One of the cool features of Snowflake is the ability to clone an object. The object can be a table, an entire schema or even an entire database! The best part is that this is a super fast operation as no physical copying of data is involved (until either database starts to change).

As part of our regular operations, we maintain multiple databases and schemas – for different aspects of development, testing, etc. At some specific points in time, we clone objects from one database or schema to another. 

The Sandbox Clone

As users were on-boarded, we created a sandbox database as a playground for them to start playing with the data. As part of my duties, I re-clone this database at the start of every week. This way, users have a fairly stable environment to work with while also periodically getting the latest data. 

Creating this clone database is super simple using a SQL statement like this:

CREATE OR REPLACE DATABASE sandbox CLONE devdb;

This statement will delete the old database named sandbox and create a new version of it as a clone of the current state of the devdb database. How simple is that? 

Well, too simple to cause mistakes … as I found out!

One Monday morning, this is what I typed into the Worksheet and without thinking, hit RUN:

CREATE OR REPLACE DATABASE devdb CLONE sandbox;

After I do the clone, I do spot checks on the sandbox to make sure that “new” tables that were created show up in it. This time, not only did the new tables not show up, but worse – they disappeared from the devdb!

When I realized my mistake, I went into panic mode. We were still in development and so had no backups yet – the traditional way to recover from such errors.   

Snowflake Time Travel

I needn’t have panicked. Snowflake has this very cool feature (definitely even cooler than cloning) called Time Travel. (If you ever were a Dr. Who fan, you will be thrilled!)

Figure2: Time Travel

Think of Time Travel has a way to access the state of an object at a particular point in time in the past … up to 90 days back for the Enterprise Edition. For example, if you want to check the status of an order from a day ago:

SELECT status FROM orders AT(offset => -24*60*60);

It is as though you have a live backup of all the data at your fingertips … sweet!

Note that the default data retention period is one (1) day. You must explicitly enable a larger retention period at table creation time (or change the default setting for your database) to avail of the full 90 days. 

The UNDROP command

I bet every database admin has wished for this command more than once in their career. And yes – Snowflake has an UNDROP command. If you drop a table, schema or even an entire database – you can undrop it. 

UNDROP works in conjunction with Time Travel to help restore the state of an object as it was at a particular time. 

Recovering the Snowflake Database

Back to my story … In our Snowflake system, we had not changed the default retention period – so it remained at the default  value of 1 day. However, since I detected my mistaken within the hour, it was still possible to recover. 

Here are the steps that helped with Snowflake database recovery of the original devdb database:

1. Since the devdb is now garbage, move it away. DO NOT  delete it – that comes later:
   • ALTER DATABASE devdb RENAME TO devdb_old;
2. The original devdb was dropped when I did the clone. Running the UNDROP command will restore the original:
   • UNDROP DATABASE devdb;
3. We no longer need the devdb_old database; so we can drop it:
   • DROP DATABASE devdb_old;
     

That’s it! The database was restored in no time and I could now re-clone it. However, I learned my lesson. So instead of typing away the clone command in a Snowflake worksheet, I created a script to execute it.

Conclusion

Snowflake is a very user-friendly data warehouse, allowing developers to do a lot of the functions that typically require a database administrator. However, it is still possible to make mistakes as this article on “Snowflake Database Recovery” shows.