The ability to time travel through data in addition to delivery of zero copy clones delivers two remarkably powerful and flexible data handling tools.
- Time Travel: Is the ability to query data in the past. This gives the ability to re-execute any query on data as it was, at any point in time, over the past 90 days. Furthermore it provides the ability to recover from a mistake and un-drop a table or even an entire database within seconds.
- Zero Copy Clones: Provides the ability to clone a table, all tables in a schema or even an entire database within seconds at zero additional cost. To put this in context, a test clone of a 1.3 Tb table took just 5.3 seconds, as no physical data was copied. While holding the data clone online will eventually add to the total storage cost, it does mean you can secure a transactionally consistent 10 terabyte database backup in under a minute, and once created, a clone exists until dropped.
Combining both of these features provides a powerful but flexible data management toolkit which means: -
- Database Backups: Are effectively eliminated. The ability to produce a transactionally consistent clone of an entire database at a given point in time, means backup copies to external storage are unnecessary.
- Database Restores: Are no longer needed. On a legacy database, re-running a report against archived data typically involves restoring the data from a backup medium to an existing server. On snowflake, this simply involves creating a virtual warehouse, and querying the data from the clone. Unlike the legacy solution which can take days to complete, this can be completed within minutes, with zero disruption to existing users.
- Agile DevOps: Are a reality. Instead of copying the production database or restoring a backup for testing, it’s possible to take a full clone of the database at a point in time, then simply reload the data using the new code version. The flexibility to resize the virtual warehouse means a full size, full volume performance testing environment can be built within minutes, and then removed when no longer needed.
- Database Recovery: Is a breeze. Imagine a situation where the weekend batch data load routine has corrupted hundreds of tables in the production database, and it’s only been detected on a Monday morning. It’s possible to quickly identify the initial SQL statement, and clone the entire database at the instant beforehand. Executing a simple switch statement, replaces the corrupt database with the clean copy.
- Table Recovery: Is almost instant. It’s easy to accidentally drop a table, a schema or even an entire database, and Snowflake includes an undrop command for each.
To demonstrate just how easy it is to recover an entire multi-terabyte database to a point in time, the following SQL statements take a clone copy, and then switch it with the existing database, effectively restoring it to the situation an hour ago.