Skip to main content

Time Travel Made Easy with Snowflake

By Gennaio 28, 2024Marzo 19th, 2024No Comments
Image by vecstock on Freepik

Snowflake’s Time Travel feature is an incredibly useful tool that enables you to access historical data effortlessly. For instance, consider a scenario where you have a Transaction table containing details of transactions from last years at your store. If you accidentally delete this table, Time Travel allows you to revert back to a point in time, say five minutes earlier, and recover your data.

Key Features of Snowflake Time Travel

Let’s dive into details and see what makes this function unique:

  1. Retrieve Altered or Deleted Data: Easily query data that has been modified or removed in the past. This function eliminates concerns about losing crucial information.
  2. Create Historical Clones: You can create replicas of entire Tables, Schemas, or even Databases at specific points in time. This is vital for conducting historical analyses or data comparisons.
  3. Restore Deleted Tables, Schemas, and Databases: In the event of accidental deletions, Time Travel enables you to recover lost Tables, Schemas, and more, providing a safety net for your data.

Snowflake Time Travel is more than a tool for accessing historical data; it’s a comprehensive solution for managing your data’s history, performing insightful analysis, and safeguarding against unexpected data loss.

Data Retention Period in Snowflake Time Travel

The data retention period is crucial in Snowflake Time Travel. It determines how long your historical data is preserved for Time Travel operations like SELECT, CREATE, CLONE, and UNDROP. Here’s how it works across different Snowflake editions:

  • Snowflake Standard Edition: Users can set the retention period to 0 (or revert to the default of 1 day) at both the account and object levels, including databases, schemas, and tables.
  • Snowflake Enterprise Edition (and higher): For transient and temporary databases, schemas, and tables, the retention period can be set to 0 (or reverted to 1 day). For permanent databases, schemas, or tables, the retention period can extend from 0 up to 90 days.

Post-Retention: Moving to Snowflake Fail-Safe

Once the retention period ends, historical data is transferred to Snowflake Fail-Safe with the following consequences:

  • Inaccessibility of historical data for querying.
  • Inability to clone past objects.
  • Restoring previously dropped objects becomes impossible.

Enabling Extended Snowflake Time Travel

To extend your Data Retention Period beyond the standard 1-day setting, an upgrade to Snowflake Enterprise Edition is required. Note that a longer Data Retention Period implies increased storage needs, which will be reflected in your monthly Storage Fees. For detailed information on storage costs associated with Time Travel and Fail-Safe, refer to the Snowflake documentation.


Leave a Reply