Snowflake is a cloud-based data warehousing platform that offers a variety of advanced features, making it a popular choice among data professionals. Two such features are Time Travel and Fail-safe, which provide powerful data recovery capabilities. In this article, we will explore the differences between Time Travel and Fail-safe, along with their use cases and examples using tables .
-
Time Travel
Time Travel is a feature in Snowflake that allows you to query historical data at any point in time within a defined retention period. This feature is useful for scenarios such as data recovery, auditing, and data analysis.
Retention Period:
The retention period for Time Travel depends on the Snowflake edition you are using:
- Standard Edition: 1 day
- Enterprise Edition: 7 days (configurable up to 90 days)
- Business Critical Edition: 7 days (configurable up to 90 days)
Example:
Let’s assume you have a table called ‘freshers_in_employees’ containing employee information. You accidentally delete a few records and want to recover them using Time Travel.
First, note the current timestamp:
SELECT CURRENT_TIMESTAMP();
Next, delete the records:
DELETE FROM freshers_in_employees WHERE employee_id IN (1001, 1002, 1003);
To recover the deleted records, use the ‘AT’ clause with the timestamp you noted earlier:
SELECT * FROM freshers_in_employees AT(TIMESTAMP => '<noted_timestamp>') WHERE employee_id IN (1001, 1002, 1003);
-
Fail-safe
Fail-safe is a Snowflake feature that provides an additional layer of data protection after the Time Travel retention period has expired. Fail-safe retains the data for an additional 7 days, during which the data is not directly accessible for querying. In case of data loss or corruption, you can contact Snowflake Support to recover the data within this period.
Example:
Let’s assume the Time Travel retention period for the ‘freshers_in_employees’ table has expired, and you still need to recover some lost data. In this case, you will need to contact Snowflake Support and provide them with the necessary details, such as the table name, the timestamp when the data was lost, and the specific records you want to recover.
Use Cases:
Time Travel:
- Recovering accidentally deleted or modified data
- Auditing historical data for compliance purposes
- Analyzing data trends over time
Fail-safe:
- Recovering data after the Time Travel retention period has expired
- Protecting against data corruption or loss
- Ensuring data durability and reliability
Time Travel and Fail-safe are powerful Snowflake features that provide robust data recovery capabilities. Time Travel allows you to query and restore data within a specific retention period, while Fail-safe serves as an additional layer of protection beyond that period. Understanding these features and their use cases will help you to better manage and secure your data in Snowflake.
Snowflake important urls to refer
