Snowflake Time Travel is an amazing feature that allows you to query a historical snapshot up to 90 days in the past. There are a number of business use cases for administrators, data engineers, and analytics professionals. In this guide, we will explore practical use cases where we use Time Travel to aid us in meta data management and governance. In this document we cover broad strokes and code examples to start using Time Travel. A complete Snowflake Time Travel guide is available on the Snowflake website
What is Snowflake Time Travel?
Snowflakes Time Travel feature allows users to access historical data that has been modified or deleted, enabling the restoration of tables, schemas, and databases to specific points within a defined retention period. This retention period is configurable, with a standard duration of 1 day for Standard Edition and up to 90 days for Enterprise Edition, after which the data moves into Fail-safe for an additional 7 days.
How to use Snowflake Time Travel Example?
To use Snowflake time travel, you simply use an “AT” clause that will accept a parameter for “OFFSET” that indicates in seconds how far back in time you would like to go. I have listed some general time offset figures.
SELECT * FROM TABLE AT (OFFSET => -10*60); -- 10 minutes ago in seconds.SELECT * FROM TABLE AT (OFFSET => -1*60*60); -- 1 hour ago in seconds.SELECT * FROM TABLE AT (OFFSET => -1*24*60*60); -- 1 day ago in seconds.SELECT * FROM TABLE AT (OFFSET => -7*24*60*60); -- 7 days ago in seconds.How to restore deleted data in Snowflake?
Using Snowflake Time Travel, you can easily restore data that may have been truncated or overwritten. In the code below, you can insert into a table the results of time travel.
INSERT INTO TABLE.SCHEMA.TABLE
SELECT * FROM TABLE.SCHEMA.RYAN AT (OFFSET => -10 * 60);What are other use cases for using Snowflake Time Travel
Snowflake’s Time Travel feature offers several advanced capabilities beyond basic data recovery:
Backup and Recovery: Beyond restoring dropped tables, Time Travel allows for the duplication and backup of data from key points in the past, enhancing data protection strategies.
Auditing and Compliance: By querying historical data, organizations can track changes over time, facilitating audits and ensuring compliance with data governance policies.
Data Analysis and Reporting: Time Travel enables analysis of data as it existed at specific points, allowing for trend analysis and comparison of historical and current datasets. Snowflake Documentation
Testing and Development: Developers can create zero-copy clones of databases or tables at particular moments, providing consistent datasets for testing without affecting production environments. Snowflake Documentation
Rapid recovery for Daily ETL fallback: When we build data pipelines for daily data refresh where we truncate and replace, time travel is a simple and risk free method to restore tables to previous state.
Our recommendations
1. Always use enterprise for your Snowflake instance if you are running production processes
2. When you setup an organization, increase the Time Travel period to 90 days. You can also set the period at the database and shema level in the event your data retention policies differ from the a retention period that you set.
ALTER ACCOUNT SET DATA_RETENTION_TIME_IN_DAYS = 90;
Where can I learn more about Snowflake Time Travel?
You can always view Snowflake Time Travel Documentation or contact us if you need any assistance.
