what is time travel in snowflake

Time Travel snowflake: The Ultimate Guide to Understand, Use & Get Started 101

By: Harsh Varshney Published: January 13, 2022

Related Articles

To empower your business decisions with data, you need Real-Time High-Quality data from all of your data sources in a central repository. Traditional On-Premise Data Warehouse solutions have limited Scalability and Performance , and they require constant maintenance. Snowflake is a more Cost-Effective and Instantly Scalable solution with industry-leading Query Performance. It’s a one-stop-shop for Cloud Data Warehousing and Analytics, with full SQL support for Data Analysis and Transformations. One of the highlighting features of Snowflake is Snowflake Time Travel.

Table of Contents

Snowflake Time Travel allows you to access Historical Data (that is, data that has been updated or removed) at any point in time. It is an effective tool for doing the following tasks:

• Restoring Data-Related Objects (Tables, Schemas, and Databases) that may have been removed by accident or on purpose.
• Duplicating and Backing up Data from previous periods of time.
• Analyzing Data Manipulation and Consumption over a set period of time.

In this article, you will learn everything about Snowflake Time Travel along with the process which you might want to carry out while using it with simple SQL code to make the process run smoothly.

What is Snowflake?

Snowflake is the world’s first Cloud Data Warehouse solution, built on the customer’s preferred Cloud Provider’s infrastructure (AWS, Azure, or GCP) . Snowflake (SnowSQL) adheres to the ANSI Standard and includes typical Analytics and Windowing Capabilities. There are some differences in Snowflake’s syntax, but there are also some parallels. 

Snowflake’s integrated development environment (IDE) is totally Web-based . Visit XXXXXXXX.us-east-1.snowflakecomputing.com. You’ll be sent to the primary Online GUI , which works as an IDE, where you can begin interacting with your Data Assets after logging in. Each query tab in the Snowflake interface is referred to as a “ Worksheet ” for simplicity. These “ Worksheets ,” like the tab history function, are automatically saved and can be viewed at any time.

Key Features of Snowflake

• Query Optimization: By using Clustering and Partitioning, Snowflake may optimize a query on its own. With Snowflake, Query Optimization isn’t something to be concerned about.
• Secure Data Sharing: Data can be exchanged securely from one account to another using Snowflake Database Tables, Views, and UDFs.
• Support for File Formats: JSON, Avro, ORC, Parquet, and XML are all Semi-Structured data formats that Snowflake can import. It has a VARIANT column type that lets you store Semi-Structured data.
• Caching: Snowflake has a caching strategy that allows the results of the same query to be quickly returned from the cache when the query is repeated. Snowflake uses permanent (during the session) query results to avoid regenerating the report when nothing has changed.
• SQL and Standard Support: Snowflake offers both standard and extended SQL support, as well as Advanced SQL features such as Merge, Lateral View, Statistical Functions, and many others.
• Fault Resistant: Snowflake provides exceptional fault-tolerant capabilities to recover the Snowflake object in the event of a failure (tables, views, database, schema, and so on).

To get further information check out the official website here . 

What is Snowflake Time Travel Feature?

Snowflake Time Travel is an interesting tool that allows you to access data from any point in the past. For example, if you have an Employee table, and you inadvertently delete it, you can utilize Time Travel to go back 5 minutes and retrieve the data. Snowflake Time Travel allows you to Access Historical Data (that is, data that has been updated or removed) at any point in time. It is an effective tool for doing the following tasks:

• Query Data that has been changed or deleted in the past.
• Make clones of complete Tables, Schemas, and Databases at or before certain dates.
• Tables, Schemas, and Databases that have been deleted should be restored.

As the ability of businesses to collect data explodes, data teams have a crucial role to play in fueling data-driven decisions. Yet, they struggle to consolidate the data scattered across sources into their warehouse to build a single source of truth. Broken pipelines, data quality issues, bugs and errors, and lack of control and visibility over the data flow make data integration a nightmare.

1000+ data teams rely on Hevo’s Data Pipeline Platform to integrate data from over 150+ sources in a matter of minutes. Billions of data events from sources as varied as SaaS apps, Databases, File Storage and Streaming sources can be replicated in near real-time with Hevo’s fault-tolerant architecture. What’s more – Hevo puts complete control in the hands of data teams with intuitive dashboards for pipeline monitoring, auto-schema management, custom ingestion/loading schedules. 

All of this combined with transparent pricing and 24×7 support makes us the most loved data pipeline software on review sites.

Take our 14-day free trial to experience a better way to manage data pipelines.

How to Enable & Disable Snowflake Time Travel Feature? 

1) enable snowflake time travel.

To enable Snowflake Time Travel, no chores are necessary. It is turned on by default, with a one-day retention period . However, if you want to configure Longer Data Retention Periods of up to 90 days for Databases, Schemas, and Tables, you’ll need to upgrade to Snowflake Enterprise Edition. Please keep in mind that lengthier Data Retention necessitates more storage, which will be reflected in your monthly Storage Fees. See Storage Costs for Time Travel and Fail-safe for further information on storage fees.

For Snowflake Time Travel, the example below builds a table with 90 days of retention.

To shorten the retention term for a certain table, the below query can be used.

2) Disable Snowflake Time Travel

Snowflake Time Travel cannot be turned off for an account, but it can be turned off for individual Databases, Schemas, and Tables by setting the object’s DATA_RETENTION_TIME_IN_DAYS to 0.

Users with the ACCOUNTADMIN role can also set DATA_RETENTION_TIME_IN_DAYS to 0 at the account level, which means that by default, all Databases (and, by extension, all Schemas and Tables) created in the account have no retention period. However, this default can be overridden at any time for any Database, Schema, or Table.

3) What are Data Retention Periods?

Data Retention Time is an important part of Snowflake Time Travel. Snowflake preserves the state of the data before the update when data in a table is modified, such as deletion of data or removing an object containing data. The Data Retention Period sets the number of days that this historical data will be stored, allowing Time Travel operations ( SELECT, CREATE… CLONE, UNDROP ) to be performed on it.

All Snowflake Accounts have a standard retention duration of one day (24 hours) , which is automatically enabled:

• At the account and object level in Snowflake Standard Edition , the Retention Period can be adjusted to 0 (or unset to the default of 1 day) (i.e. Databases, Schemas, and Tables).
• The Retention Period can be set to 0 for temporary Databases, Schemas, and Tables (or unset back to the default of 1 day ). The same can be said of Temporary Tables.
• The Retention Time for permanent Databases, Schemas, and Tables can be configured to any number between 0 and 90 days .

4) What are Snowflake Time Travel SQL Extensions?

The following SQL extensions have been added to facilitate Snowflake Time Travel:

• OFFSET (time difference in seconds from the present time)
• STATEMENT (identifier for statement, e.g. query ID)
• For Tables, Schemas, and Databases, use the UNDROP command.

How Many Days Does Snowflake Time Travel Work? 

How to specify a custom data retention period for snowflake time travel .

The maximum Retention Time in Standard Edition is set to 1 day by default (i.e. one 24 hour period). The default for your account in Snowflake Enterprise Edition (and higher) can be set to any value up to 90 days :

• The account default can be modified using the DATA_RETENTION_TIME IN_DAYS argument in the command when creating a Table, Schema, or Database.
• If a Database or Schema has a Retention Period , that duration is inherited by default for all objects created in the Database/Schema.

The Data Retention Time can be set in the way it has been set in the example below. 

Using manual scripts and custom code to move data into the warehouse is cumbersome. Frequent breakages, pipeline errors and lack of data flow monitoring makes scaling such a system a nightmare. Hevo’s reliable data pipeline platform enables you to set up zero-code and zero-maintenance data pipelines that just work.

• Reliability at Scale : With Hevo, you get a world-class fault-tolerant architecture that scales with zero data loss and low latency. 
• Monitoring and Observability : Monitor pipeline health with intuitive dashboards that reveal every stat of pipeline and data flow. Bring real-time visibility into your ELT with Alerts and Activity Logs  
• Stay in Total Control : When automation isn’t enough, Hevo offers flexibility – data ingestion modes, ingestion, and load frequency, JSON parsing, destination workbench, custom schema management, and much more – for you to have total control.    
• Auto-Schema Management : Correcting improper schema after the data is loaded into your warehouse is challenging. Hevo automatically maps source schema with destination warehouse so that you don’t face the pain of schema errors.
• 24×7 Customer Support : With Hevo you get more than just a platform, you get a partner for your pipelines. Discover peace with round the clock “Live Chat” within the platform. What’s more, you get 24×7 support even during the 14-day full-feature free trial.
• Transparent Pricing : Say goodbye to complex and hidden pricing models. Hevo’s Transparent Pricing brings complete visibility to your ELT spend. Choose a plan based on your business needs. Stay in control with spend alerts and configurable credit limits for unforeseen spikes in data flow. 

How to Modify Data Retention Period for Snowflake Objects?

When you alter a Table’s Data Retention Period, the new Retention Period affects all active data as well as any data in Time Travel. Whether you lengthen or shorten the period has an impact:

1) Increasing Retention 

This causes the data in Snowflake Time Travel to be saved for a longer amount of time.

For example, if you increase the retention time from 10 to 20 days on a Table, data that would have been destroyed after 10 days is now kept for an additional 10 days before being moved to Fail-Safe. This does not apply to data that is more than 10 days old and has previously been put to Fail-Safe mode .

2) Decreasing Retention

• Temporal Travel reduces the quantity of time data stored.
• The new Shorter Retention Period applies to active data updated after the Retention Period was trimmed.
• If the data is still inside the new Shorter Period , it will stay in Time Travel.
• If the data is not inside the new Timeframe, it is placed in Fail-Safe Mode.

For example, If you have a table with a 10-day Retention Term and reduce it to one day, data from days 2 through 10 will be moved to Fail-Safe, leaving just data from day 1 accessible through Time Travel.

However, since the data is moved from Snowflake Time Travel to Fail-Safe via a background operation, the change is not immediately obvious. Snowflake ensures that the data will be migrated, but does not say when the process will be completed; the data is still accessible using Time Travel until the background operation is completed.

Use the appropriate ALTER <object> Command to adjust an object’s Retention duration. For example, the below command is used to adjust the Retention duration for a table:

How to Query Snowflake Time Travel Data?

When you make any DML actions on a table, Snowflake saves prior versions of the Table data for a set amount of time. Using the AT | BEFORE Clause, you can Query previous versions of the data.

This Clause allows you to query data at or immediately before a certain point in the Table’s history throughout the Retention Period . The supplied point can be either a time-based (e.g., a Timestamp or a Time Offset from the present) or a Statement ID (e.g. SELECT or INSERT ).

• The query below selects Historical Data from a Table as of the Date and Time indicated by the Timestamp:
• The following Query pulls Data from a Table that was last updated 5 minutes ago:
• The following Query collects Historical Data from a Table up to the specified statement’s Modifications, but not including them:

How to Clone Historical Data in Snowflake? 

The AT | BEFORE Clause, in addition to queries, can be combined with the CLONE keyword in the Construct command for a Table, Schema, or Database to create a logical duplicate of the object at a specific point in its history.

Consider the following scenario:

• The CREATE TABLE command below generates a Clone of a Table as of the Date and Time indicated by the Timestamp:
• The following CREATE SCHEMA command produces a Clone of a Schema and all of its Objects as they were an hour ago:
• The CREATE DATABASE command produces a Clone of a Database and all of its Objects as they were before the specified statement was completed:

Using UNDROP Command with Snowflake Time Travel: How to Restore Objects? 

The following commands can be used to restore a dropped object that has not been purged from the system (i.e. the item is still visible in the SHOW object type> HISTORY output):

• UNDROP DATABASE
• UNDROP TABLE
• UNDROP SCHEMA

UNDROP returns the object to its previous state before the DROP command is issued.

A Database can be dropped using the UNDROP command. For example,

Similarly, you can UNDROP Tables and Schemas . 

Snowflake Fail-Safe vs Snowflake Time Travel: What is the Difference?

In the event of a System Failure or other Catastrophic Events , such as a Hardware Failure or a Security Incident, Fail-Safe ensures that Historical Data is preserved . While Snowflake Time Travel allows you to Access Historical Data (that is, data that has been updated or removed) at any point in time. 

Fail-Safe mode allows Snowflake to recover Historical Data for a (non-configurable) 7-day period . This time begins as soon as the Snowflake Time Travel Retention Period expires.

This article has exposed you to the various Snowflake Time Travel to help you improve your overall decision-making and experience when trying to make the most out of your data. In case you want to export data from a source of your choice into your desired Database/destination like Snowflake , then Hevo is the right choice for you! 

However, as a Developer, extracting complex data from a diverse set of data sources like Databases, CRMs, Project management Tools, Streaming Services, and Marketing Platforms to your Database can seem to be quite challenging. If you are from non-technical background or are new in the game of data warehouse and analytics, Hevo can help!

Hevo will automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. Hevo provides a wide range of sources – 150+ Data Sources (including 40+ Free Sources) – that connect with over 15+ Destinations. It will provide you with a seamless experience and make your work life much easier.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!

Harsh comes with experience in performing research analysis who has a passion for data, software architecture, and writing technical content. He has written more than 100 articles on data integration and infrastructure.

No-code Data Pipeline for Snowflake

• Snowflake Commands

Hevo - No Code Data Pipeline

Continue Reading

Suraj Kumar Joshi

Athena vs Redshift Serverless: The Ultimate Guide on Data Query Service

Sarthak Bhardwaj

Snowflake vs Redshift: 6 Critical Differences

Veeresh Biradar

Redshift vs BigQuery: 7 Critical Differences

Leave a reply cancel reply.

Your email address will not be published. Required fields are marked *

Save my name, email, and website in this browser for the next time I comment.

I want to read this e-book

Panic hits when you mistakenly delete data. Problems can come from a mistake that disrupts a process, or worse, the whole database was deleted. Thoughts of how recent was the last backup and how much time will be lost might have you wishing for a rewind button. Straightening out your database isn't a disaster to recover from with Snowflake's Time Travel. A few SQL commands allow you to go back in time and reclaim the past, saving you from the time and stress of a more extensive restore.

We'll get started in the Snowflake web console, configure data retention, and use Time Travel to retrieve historic data. Before querying for your previous database states, let's review the prerequisites for this guide.

Prerequisites

• Quick Video Introduction to Snowflake
• Snowflake Data Loading Basics Video

What You'll Learn

• Snowflake account and user permissions
• Make database objects
• Set data retention timelines for Time Travel
• Query Time Travel data
• Clone past database states
• Remove database objects
• Next options for data protection

What You'll Need

• A Snowflake Account

What You'll Build

• Create database objects with Time Travel data retention

First things first, let's get your Snowflake account and user permissions primed to use Time Travel features.

Create a Snowflake Account

Snowflake lets you try out their services for free with a trial account . A Standard account allows for one day of Time Travel data retention, and an Enterprise account allows for 90 days of data retention. An Enterprise account is necessary to practice some commands in this tutorial.

Login and Setup Lab

Log into your Snowflake account. You can access the SQL commands we will execute throughout this lab directly in your Snowflake account by setting up your environment below:

Setup Lab Environment

This will create worksheets containing the lab SQL that can be executed as we step through this lab.

Once the lab has been setup, it can be continued by revisiting the lab details page and clicking Continue Lab

or by navigating to Worksheets and selecting the Getting Started with Time Travel folder.

Increase Your Account Permission

Snowflake's web interface has a lot to offer, but for now, switch the account role from the default SYSADMIN to ACCOUNTADMIN . You'll need this increase in permissions later.

Now that you have the account and user permissions needed, let's create the required database objects to test drive Time Travel.

Within the Snowflake web console, navigate to Worksheets and use the ‘Getting Started with Time Travel' Worksheets we created earlier.

Create Database

Use the above command to make a database called ‘timeTravel_db'. The Results output will show a status message of Database TIMETRAVEL_DB successfully created .

Create Table

This command creates a table named ‘timeTravel_table' on the timeTravel_db database. The Results output should show a status message of Table TIMETRAVEL_TABLE successfully created .

With the Snowflake account and database ready, let's get down to business by configuring Time Travel.

Be ready for anything by setting up data retention beforehand. The default setting is one day of data retention. However, if your one day mark passes and you need the previous database state back, you can't retroactively extend the data retention period. This section teaches you how to be prepared by preconfiguring Time Travel retention.

Alter Table

The command above changes the table's data retention period to 55 days. If you opted for a Standard account, your data retention period is limited to the default of one day. An Enterprise account allows for 90 days of preservation in Time Travel.

Now you know how easy it is to alter your data retention, let's bend the rules of time by querying an old database state with Time Travel.

With your data retention period specified, let's turn back the clock with the AT and BEFORE clauses .

Use timestamp to summon the database state at a specific date and time.

Employ offset to call the database state at a time difference of the current time. Calculate the offset in seconds with math expressions. The example above states, -60*5 , which translates to five minutes ago.

If you're looking to restore a database state just before a transaction occurred, grab the transaction's statement id. Use the command above with your statement id to get the database state right before the transaction statement was executed.

By practicing these queries, you'll be confident in how to find a previous database state. After locating the desired database state, you'll need to get a copy by cloning in the next step.

With the past at your fingertips, make a copy of the old database state you need with the clone keyword.

Clone Table

The command above creates a new table named restoredTimeTravel_table that is an exact copy of the table timeTravel_table from five minutes prior.

Cloning will allow you to maintain the current database while getting a copy of a past database state. After practicing the steps in this guide, remove the practice database objects in the next section.

You've created a Snowflake account, made database objects, configured data retention, query old table states, and generate a copy of the old table state. Pat yourself on the back! Complete the steps to this tutorial by deleting the objects created.

By dropping the table before the database, the retention period previously specified on the object is honored. If a parent object(e.g., database) is removed without the child object(e.g., table) being dropped prior, the child's data retention period is null.

Drop Database

With the database now removed, you've completed learning how to call, copy, and erase the past.

Overview of Snowflake Time Travel

Consider a scenario where instead of dropping a backup table you have accidentally dropped the actual table (or) instead of updating a set of records, you accidentally updated all the records present in the table (because you didn’t use the Where clause in your update statement).

What would be your next action after realizing your mistake? You must be thinking to go back in time to a period where you didn’t execute your incorrect statement so that you can undo your mistake.

Snowflake provides this exact feature where you could get back to the data present at a particular period of time. This feature in Snowflake is called Time Travel .

Let us understand more about Snowflake Time Travel in this article with examples.

1. What is Snowflake Time Travel?

Snowflake Time Travel enables accessing historical data that has been changed or deleted at any point within a defined period. It is a powerful CDP (Continuous Data Protection) feature which ensures the maintenance and availability of your historical data.

Below actions can be performed using Snowflake Time Travel within a defined period of time:

• Restore tables, schemas, and databases that have been dropped.
• Query data in the past that has since been updated or deleted.
• Create clones of entire tables, schemas, and databases at or before specific points in the past.

Once the defined period of time has elapsed, the data is moved into Snowflake Fail-Safe and these actions can no longer be performed.

2. Restoring Dropped Objects

A dropped object can be restored within the Snowflake Time Travel retention period using the “UNDROP” command.

Consider we have a table ‘Employee’ and it has been dropped accidentally instead of a backup table.

It can be easily restored using the Snowflake UNDROP command as shown below.

Databases and Schemas can also be restored using the UNDROP command.

Calling UNDROP restores the object to its most recent state before the DROP command was issued.

3. Querying Historical Objects

When unwanted DML operations are performed on a table, the Snowflake Time Travel feature enables querying earlier versions of the data using the  AT | BEFORE  clause.

The AT | BEFORE clause is specified in the FROM clause immediately after the table name and it determines the point in the past from which historical data is requested for the object.

Let us understand with an example. Consider the table Employee. The table has a field IS_ACTIVE which indicates whether an employee is currently working in the Organization.

The employee ‘Michael’ has left the organization and the field IS_ACTIVE needs to be updated as FALSE. But instead you have updated IS_ACTIVE as FALSE for all the records present in the table.

There are three different ways you could query the historical data using AT | BEFORE Clause.

3.1. OFFSET

“ OFFSET” is the time difference in seconds from the present time.

The following query selects historical data from a table as of 5 minutes ago.

3.2. TIMESTAMP

Use “TIMESTAMP” to get the data at or before a particular date and time.

The following query selects historical data from a table as of the date and time represented by the specified timestamp.

3.3. STATEMENT

Identifier for statement, e.g. query ID

The following query selects historical data from a table up to, but not including any changes made by the specified statement.

The Query ID used in the statement belongs to Update statement we executed earlier. The query ID can be obtained from “Open History”.

4. Cloning Historical Objects

We have seen how to query the historical data. In addition, the AT | BEFORE clause can be used with the CLONE keyword in the CREATE command to create a logical duplicate of the object at a specified point in the object’s history.

The following queries show how to clone a table using AT | BEFORE clause in three different ways using OFFSET, TIMESTAMP and STATEMENT.

To restore the data in the table to a historical state, create a clone using AT | BEFORE clause, drop the actual table and rename the cloned table to the actual table name.

5. Data Retention Period

A key component of Snowflake Time Travel is the data retention period.

When data in a table is modified, deleted or the object containing data is dropped, Snowflake preserves the state of the data before the update. The data retention period specifies the number of days for which this historical data is preserved.

Time Travel operations can be performed on the data during this data retention period of the object. When the retention period ends for an object, the historical data is moved into Snowflake Fail-safe.

6. How to find the Time Travel Data Retention period of Snowflake Objects?

SHOW PARAMETERS command can be used to find the Time Travel retention period of Snowflake objects.

Below commands can be used to find the data retention period of data bases, schemas and tables.

The DATA_RETENTION_TIME_IN_DAYS parameters specifies the number of days to retain the old version of deleted/updated data.

The below image shows that the table Employee has the DATA_RETENTION_TIME_IN_DAYS value set as 1.

7. How to set custom Time-Travel Data Retention period for Snowflake Objects?

Time travel is automatically enabled with the standard, 1-day retention period. However, you may wish to upgrade to Snowflake Enterprise Edition or higher to enable configuring longer data retention periods of up to 90 days for databases, schemas, and tables.

You can configure the data retention period of a table while creating the table as shown below.

To modify the data retention period of an existing table, use below syntax

The below image shows that the data retention period of table is altered to 30 days.

A retention period of 0 days for an object effectively disables Time Travel for the object.

8. Data Retention Period Rules and Inheritance

Changing the retention period for your account or individual objects changes the value for all lower-level objects that do not have a retention period explicitly set. For example:

• If you change the retention period at the account level, all databases, schemas, and tables that do not have an explicit retention period automatically inherit the new retention period.
• If you change the retention period at the schema level, all tables in the schema that do not have an explicit retention period inherit the new retention period.

Currently, when a database is dropped, the data retention period for child schemas or tables, if explicitly set to be different from the retention of the database, is not honored. The child schemas or tables are retained for the same period of time as the database.

• To honor the data retention period for these child objects (schemas or tables), drop them explicitly before you drop the database or schema.

Related Articles:

Leave a Comment Cancel reply

Save my name, email, and website in this browser for the next time I comment.

Related Posts

QUALIFY in Snowflake: Filter Window Functions

GROUP BY ALL in Snowflake

Rank Transformation in Informatica Cloud (IICS)

Get Snowflake Projects Live Faster. Reliably.

What is Snowflake Time Travel?

Snowflake Time Travel allows you to access previous snapshots of your data objects, such as tables, schemas, and databases. You can access these snapshots at any point within the specified data retention period.

Time travel in Snowflake, combined with the UNDROP SQL command helps you recover from running unintended, accidental operations on the database, such as DELETE FROM , TRUNCATE TABLE , or DROP TABLE commands.

In this article, we're going to kick things off by diving into Snowflake's Time Travel functionality and see how pairing it with the CLONE command can help us recover dropped objects. We'll also explore setting the data retention period for data objects, what fail-safe is, and understand how it works. In the end, we will explore how we can extend these concepts to code, by coupling code and data together in a stateful data system . We'll do so just by using Git, so you can safely restore any previous snapshot of your objects from your data warehouse based on a Git commit.

How to restore a dropped object?

If you're anxiously Googling because you dropped your production table: you can use the Snowflake UNDROP TABLE command. Calling UNDROP restores the latest version of a table before issuing the DROP command:

You can call UNDROP on schema or database objects as well:

Viewing dropped objects

The SHOW TABLES Snowflake command lists all current tables within a schema. To also see the dropped variations of a table, you can use the SHOW TABLES HISTORY command. This command also works with schemas or database objects:

The output of these commands includes all dropped objects, along with an additional DROPPED_ON column that indicates the timestamp when each object was dropped. If an object was dropped more than once, the output will list each instance of the object as a separate row.

It's important to note that while tables, schemas, and databases can be restored in Snowflake, views dropped through a DROP VIEW command cannot be restored and must be recreated. To prevent the loss of your view objects, we recommend saving the definition of your views in a version control system like Git.

Time Travel syntax

To query previous versions of an object, Snowflake provides several methods, including:

• AT(TIMESTAMP => '<TIMESTAMP>')
• AT(OFFSET => -<NUMBER_OF_SECONDS>)
• BEFORE(STATEMENT => '<STATEMENT_ID>')

You can find the STATEMENT_ID of a particular query from the Query History tab.

For example, to retrieve data from 10 minutes ago, you can use:

You can also calculate the TIMESTAMP for a specific point in the past using SQL timestamp functions. For instance, to access data from 10 minutes ago, you can use:

To use the BEFORE(STATEMENT => '<STATEMENT_ID>') method for querying a previous version of an object before a specific operation, you can retrieve the STATEMENT_ID from the Query History view of that particular query that changed the state of the object. This functionality is particularly useful for pinpointing and restoring data to its state prior to a specific change or operation.

Cloning past snapshots of an object

You can materialize any previous snapshot in the database by using the CLONE keyword in the CREATE command. For example:

This feature is especially useful after using the Snowflake truncate table or Snowflake delete rows commands (i.e., DELETE FROM my_table WHERE <condition> ).

Conversely, you can restore schemas or databases using the CLONE command:

The command might fail for databases and schemas if they contain child objects (schemas or tables) whose Time travel period has expired. To mitigate this issue, use the IGNORE TABLES WITH INSUFFICIENT DATA RETENTION parameter:

Data retention period

The standard retention period is 1 day and is automatically applied to all objects within an account. For Snowflake Enterprise or higher tiers, this retention period can be increased up to 90 days (excluding transient or temporary tables). To modify the retention period of an object, the DATA_RETENTION_TIME_IN_DAYS configuration can be used:

Only users with the ACCOUNTADMIN role can specify the DATA_RETENTION_TIME_IN_DAYS at the account level, which will then act as the default value for all objects within the account.

A user with the ACCOUNTADMIN role can also set a minimum data retention time at the account level with the MIN_DATA_RETENTION_TIME_IN_DAYS parameter. This setting enforces a minimum data retention period across all databases, schemas, and tables within the account. When MIN_DATA_RETENTION_TIME_IN_DAYS is configured at the account level, the data retention period for an object within the account becomes the maximum of DATA_RETENTION_TIME_IN_DAYS and MIN_DATA_RETENTION_TIME_IN_DAYS parameters.

To override the account default when creating a new table, schema, or database, include the DATA_RETENTION_TIME_IN_DAYS parameter in the creation command:

It's also possible to disable Time Travel for an object by setting the retention period to 0:

Once an object's retention period comes to an end, its historical data enters the Snowflake Fail-safe phase. During this phase:

• You won't be able to query historical data anymore.
• Cloning past objects is off the table.
• Restoring objects that were previously dropped becomes impossible.

Fail-safe acts as a safety net, offering a fixed 7-day window where Snowflake might still be able to recover historical data. This critical period kicks in right after the Time Travel retention period ends, giving you one last chance at data recovery, albeit with Snowflake's assistance and under specific conditions. You can read more about the steps required here .

Time Travel example

Here's a Snowflake Time Travel query you can use to restore a previous version of a table where some rows were accidentally updated and deleted:

Output: id_squared: [0, 1, 4, 9, 16]

Output: id_squared: [1, 1, 1, 1, 1]

You'll need to find the QUERY_ID of the UPDATE statement from the QUERY HISTORY view:

Once you have the snapshot of your table cloned, you can proceed to swap the two tables if needed:

Here's another Snowflake Time Travel example for when a table is accidentally dropped or renamed:

In these examples, Time Travel allows you to "go back in time" and to correct mistakes such as accidental updates, deletions, or even structural changes to your tables.

Snowflake Time Travel alternative: Git-like version control for data

Time travel is a powerful feature for restoring data objects based on a timestamp value or statement ID. In real-world scenarios, it might be difficult to reconcile to which statement or timestamp you want to revert your schema. Multiple people can issue statements concurrently on the same schema, or even on the same table in a short timeframe. This complicates time travel.

A better solution would be to couple code and data together, restoring the state of your schema based on a commit ID. Y42 provides such a stateful solution that allows you to version control code and manage both data and code together using just Git. This means if you want to roll back your database to a specific version of your code, the data from that point in time is also automatically restored alongside your code changes. This is achieved by maintaining two separate layers in the database, with pointers to all previous snapshots of an object attached to individual commit IDs.

If you want to learn more about what powers this mechanism, you can read about Virtual Data Builds here .

Conclusions

Snowflake offers robust tools to protect your data objects against accidental operations. I still remember the days when you had to perform daily backups, and restoring such a backup would take minutes or hours, depending on the S3 storage class the backup resided in. Then, you'd have to backfill the missing data from when the backup was taken to the current timestamp. Snowflake Time Travel puts all that in the rearview mirror with simple, intuitive SQL commands that allow restoring objects from any time (within a specific timeframe). However, it does so without the ability to tie the object's version from the database to a change in your codebase that caused it. This is where Y42 comes into play. Y42 enables you to maintain your data warehouse and codebase in sync at all times. With Y42, you can deploy or revert code changes, and the state of the data warehouse is automatically synced in a frictionless, zero-copy operation, empowering everyone to make changes to their production system with confidence .

In this article

Share this article

More articles

Be the first to know

Subscribe to our newsletter to get the latest news and insights from the dataverse, curated by fellow data practitioners.

Can't find what you're looking for? Ask The Community  

Using Time Travel to validate correctness of a query or a recent DML operation

Validating a DML based on Time Travel to check the effects on the underlying tables. This article is mainly intended to address misinterpretations about data or table incorrectness after a DML.

At times users can misinterpret the "effect" of a recent DML operation. For example, for a table that undergoes multiple DMLs from various workloads, users, or sources. In such cases, oversight about other workloads may lead the users to misinterpret the outcome of some recent DML operations. Typical examples include:

• An unexpected result from a SQL query because of oversight about changed data
• an UPDATE/MERGE statement not updating the "expected" data as the data at the time of DML might have been changed temporarily from another transaction or workload
• A DELETE statement not deleting rows, because the filter columns might have changed data at the time of the DELETE

This misinterpretation may happen for users who lack insights into what other workloads are updating the tables. Or in general due to an oversight of the effects of other DMLs around the time of the misinterpreted DML. In this article, we will list the Snowflake features that can aid in troubleshooting or validation for DML correctness. The most common and helpful feature is Time Travel if the DML timeframe was still within the Time Travel retention period of the underlying table(s).

Scope: Any DML operations that are recent and the underlying table(s) are still within the time travel retention period.

Use a mix of the below features to effectively identify the expected behaviour. It may help determine what exactly happened to the underlying table, what effect the DML had, and most importantly clarifies that the DML did indeed work as expected.

• If the underlying table is still within time travel retention it is quite easy to query the table using Time Travel feature both before and after the DML and compare both the table/data states. Please refer to the links in the "Additional Information" section for appropriate syntax/usage examples.
• While it can be common to review which other DML operations were performed on the table "after" the DML of interest, it may also be useful to check which other DMLs were performed recently (in a short span of time) "before" the DML. For example, any prior DMLs might have just updated the table data, due to which the under-review DML may not have performed the "intended/expected" action.
• Use the UI, Result_Scan() table function, or Query History views to gather the output (Status) of the DML operation, as it may help to know how many rows were inserted, updated, or deleted from the table actually as a result of the DML being reviewed.

Refer to links for more details about the related topics

Docs: Time Travel AT|Before

Docs: query_history

Docs: result_scan

Article: How-to-query-time-travel-with-a-time-different-than-the-default-UTC

Article: Querying-time-travel-data-in-a-session-with-a-non-UTC-timezone

Was this article helpful? Yes No

MOST VIEWED

• Client Release History (Prior to January 2022)
• 4.39 Release Notes - November 16-23, 2020
• How To: Test Azure OAuth Connection To Snowflake End To End Using Python (Client Credentials Flow)
• How to configure Azure to issue OAuth tokens on behalf of a client to access Snowflake
• 5.12 Behavior Change Release Notes - April 12-13, 2021

© 2024 Snowflake Inc. All Rights Reserved | If you'd rather not receive future emails from Snowflake, unsubscribe here or customize your communication preferences

How to Leverage the Time Travel Feature on Snowflake

Welcome to Time Travel in the Snowflake Data Cloud . You may be tempted to think “only superheroes can Time Travel,” and you would be right. But Snowflake gives you the ability to be your own real-life superhero. 

Have you ever feared deleting the wrong data in your production database? Or that your carefully written script might accidentally remove the wrong records? Never fear, you are here – with Snowflake Time Travel!

What’s The Big Deal?

Snowflake Time Travel, when properly configured, allows for any Snowflake user with the proper permissions to recover and query data that has been changed or deleted up to the last 90 days (though this recovery period is dependent on the Snowflake version, as we’ll see later.) 

This provides comprehensive, robust, and configurable data history in Snowflake that your team doesn’t have to manage! It includes the following advantages:

• Data (or even entire databases and schemas) can be restored that may have been lost due to a deletion, no matter if that deletion was on purpose or not
• The ability to maintain backup copies of your data for all past versions of it for a period of time
• Allowing for inspection of changes made over specific periods of time

To further investigate these features, we will look at:

• How Time Travel works
• How to configure Time Travel in your account
• How to use Time Travel
• How Time Travel impacts Snowflake cost
• Some Time Travel best practices

How Time Travel Works

Before we learn how to use it, let’s understand a little more about why Snowflake can offer this feature. 

Snowflake stores the records in each table in immutable objects called micro-partitions that contain a subset of the records in a given table. 

Each time a record is changed (created/updated/deleted), a brand new micro-partition is created, preserving the previous micro-partitions to create an immutable historical record of the data in the table at any given moment in time. 

Time Travel is simply accessing the micro-partitions that were current for the table at a particular moment in time.  

How To Configure Time Travel In Your Account

Time Travel is available and enabled in all account types.

However, the extent to which it is available is dependent on the type of Snowflake account, the object type, and the access granted to your user.  

Default Retention Period

The retention period is the amount of time you can travel back and recover the state of a table at a given point and time. It is variable per account type. The default Time Travel retention period is 1 day (24 hours).

PRO TIP: Snowflake does have an additional layer of data protection called fail-safe , which is only accessible by Snowflake to restore customer data past the time travel window.  However, unlike time travel, it should not be considered as a part of your organization’s backup strategy.

Account/Object Type Considerations

All Snowflake accounts have Time Travel for permanent databases, schemas, and tables enabled for the default retention period.

Snowflake Standard accounts (and above) can remove Time Travel retention altogether by setting the retention period to 0 days, effectively disabling Time Travel. 

Snowflake Enterprise accounts (and above) can set the Time Travel retention period for transient databases, schemas, tables, and temporary tables to either 0 or 1 day. The retention period can also be increased to 0-90 days for permanent databases, schemas, and tables.

The following table summarizes the above considerations:

Changing Retention Period

For the Snowflake Enterprise accounts; two account level parameters can be used to change the default account level retention time.  

• DATA_RETENTION_TIME_IN_DAYS: How many days that Snowflake stores historical data for the purpose of Time Travel.
• MIN_DATA_RETENTION_TIME_IN_DAYS: How many days at a minimum that Snowflake stores historical data for the purpose of Time Travel.

The parameter DATA_RETENTION_TIME_IN_DAYS can also be used at an object level to override the default retention time for an object and its children. Example: 

How To Use Time Travel

Using Time Travel is easy! There are two sets of SQL commands that can invoke Time Travel capabilities:

• AT or BEFORE : clauses for both SELECT and CREATE .. CLONE statements.  AT is inclusive and BEFORE is exclusive
• UNDROP : command for restoring a deleted table/schema/database

The following graphic from the Snowflake documentation summarizes this visually:

Query Historical Data

You can query historical data using the AT or BEFORE clauses and one of three parameters:

• TIMESTAMP :  A specific historical timestamp at which to query data from a particular object.  Example: SELECT * FROM my_table AT (TIMESTAMP => ‘Fri, 01 May 2015 15:00:00 -0700’::TIMESTAMP_TZ);
• OFFSET : The difference in seconds from the current time at which to query data from a particular object.  Example: CREATE SCHEMA restored_schema CLONE my_schema AT (OFFSET => -4800);
• STATEMENT : The query ID of a statement that is used as a reference point from which to query data from a particular object.  Example: CREATE DATABASE restored_db CLONE my_db BEFORE (STATEMENT => ‘8e5d0ca9-005e-44e6-b858-a8f5b37c5726’);

The one thing to understand is that these commands will work only within the retention period for the object that you are querying against. So, if your retention time is set to the default one day, and you try to UNDROP a table two days after deleting it, you receive an error and be out of luck! 

PRO TIP: Snowflake does have an additional layer of data protection called fail-safe , which is only accessible by Snowflake to restore customer data past the time travel window. However, unlike time travel, it should not be considered as a part of your organization’s backup strategy.

Restore Deleted Objects

You can also restore objects that have been deleted by using the UNDROP command.  To use this command, another table with the same fully qualified name (database.schema.table) cannot exist.  

Example: UNDROP TABLE my_table

How Time Travel Impacts Snowflake Cost

Snowflake accounts are billed for the number of 24-hour periods that Time Travel data (the micro-partitions) is necessary to be maintained for the data that is being retained. 

Every time there is a change in a table’s data, the historical version of that changed data will be retained (and charged in addition) for the entire retention period. This may not be an entire second copy of the table. Snowflake will try to optimize to maintain only the minimal amount of historical data needed but will incur additional costs. 

As an example, if every row of a 100 GB table were changed ten times a day, the storage consumed (and charged) for this data per day would be 100GB x 10 changes = 1 TB.  

What can you do to optimize cost to ensure your ops team does not wake up to an unnecessarily large Time Travel bill?  Below are a couple of suggestions.

Use Transient and Temporary Tables When Possible

If data does not need to be protected using Time Travel, or there is data only being used as an intermediate stage in an ETL process, then take advantage of using transient and temporary tables with the DATA_RETENTION_TIME_IN_DAYS parameter set to 0. This will essentially disable Time Travel and make sure there are no extra costs because of it. 

Copy Large High-Churn Tables

If you have large permanent tables where a high percentage of records are often changed every day, it might be a good idea to change your storage strategy for these tables based on the cost implications mentioned above.  

One way of dealing with such a table would be to create it as a transient table with 0 Time Travel retention (DATA_RETENTION_TIME_IN_DAYS=0) and copy it over to a permanent table on a periodic basis.  

This would allow you to control the number of copies of this data you maintain without worrying about ballooning Time Travel costs in the background. 

Time Travel is an incredibly useful tool that removes the need for your team to maintain backups/snapshots/complex restoration processes/etc… as with a traditional database.  Specifically, it enables the following advantages:

• Data recovery/restoration : use the ability to query historical data to restore old versions of a particular dataset, or recover databases/schemas/tables that have been deleted
• Backups : If not explicitly disabled, time travel automatically is maintaining backup copies of all past versions of your data for at least 1 day, and up to 90 days
• Change Auditing : The queryable nature of time travel allows for inspection of changes made to your data over specific periods of time

Final Thoughts

Hopefully, this has helped understand how to use Snowflake Time Travel and the context around how it works, and some of the cost implications.  

If your organization needs help using or configuring Time Travel, or any other Snowflake feature, phData is a certified Elite Snowflake partner, and we would love to hear from you so that our team can help drive the value of your organization’s data forward!

More to explore

Top 5 Fivetran Connectors for Healthcare

How to Migrate Hive Tables From Hadoop Environment to Snowflake Using Spark Job

Beyond the Data: Franco Borgiani, Data Engineer

Join our team

• About phData
• Leadership Team
• All Technology Partners
• Case Studies
• phData Toolkit

Subscribe to our newsletter

• © 2023 phData
• Privacy Policy
• Accesibility Policy
• Website Terms of Use
• Data Processing Agreement
• End User License Agreement

Data Coach is our premium analytics training program with one-on-one coaching from renowned experts.

• Data Coach Overview
• Course Collection

Accelerate and automate your data projects with the phData Toolkit

• Get Started
• Financial Services
• Manufacturing
• Retail and CPG
• Healthcare and Life Sciences
• Call Center Analytics Services
• Snowflake Native Streaming of HL7 Data
• Snowflake Retail & CPG Supply Chain Forecasting
• Snowflake Plant Intelligence For Manufacturing
• Snowflake Demand Forecasting For Manufacturing
• Snowflake Data Collaboration For Manufacturing

• MLOps Framework
• Teradata to Snowflake
• Cloudera CDP Migration

Technology Partners

Other technology partners.

Check out our latest insights

• Dashboard Library
• Whitepapers and eBooks

Data Engineering

Consulting, migrations, data pipelines, dataops, change management, enablement & learning, coe, coaching, pmo, data science and machine learning services, mlops enablement, prototyping, model development and deployment, strategy services, data, analytics, and ai strategy, architecture and assessments, reporting, analytics, and visualization services, self-service, integrated analytics, dashboards, automation, elastic operations, data platforms, data pipelines, and machine learning.

Use Kanaries Cloud for free as students and educators

Snowflake Time Travel: Clearly Explained

Published on 7/24/2023

In the realm of cloud computing and big data analytics , Snowflake has emerged as a leading data warehouse solution. Its architecture, designed for the cloud, provides a flexible, scalable, and easy-to-use platform for managing and analyzing data. One of the standout features of Snowflake is Time Travel . This feature is not just a novelty; it's a powerful tool that can significantly enhance your data management capabilities.

Snowflake Time Travel is a feature that allows you to access historical data within a specified time frame. This means you can query data as it existed at any point in the past, making it an invaluable tool for data retention and data governance . Whether you're auditing data changes, complying with data regulations, or recovering from a data disaster, Time Travel has you covered.

Want to quickly visualize your snowflake data? Use RATH (opens in a new tab) to easily turn your Snowflake database into interactive visualizations! RATH is an AI-powered, automated data analysis and data visualization tool that is supported by a passionate Open Source community. check out RATH GitHub (opens in a new tab) for more. Here is how you can visualize Snowflake data in RATH:

Learn more about how to visualize Snowflake Data in RATH Docs .

Understanding Snowflake Time Travel

Snowflake Time Travel is a feature that allows you to "travel back in time" to view and operate on historical data. This is achieved by retaining all changes made to the data in your Snowflake database for a specified period, known as the Time Travel retention period . This period can be set anywhere from 1 to 90 days, depending on your needs and the edition of Snowflake you're using.

The way Time Travel works is simple yet ingenious. Whenever a change is made to a table in Snowflake, instead of overwriting the existing data, Snowflake keeps a copy of the old data. This allows you to query the table as it was at any point within the retention period, effectively giving you a time machine for your data.

The role of Snowflake Time Travel extends beyond just viewing historical data. It plays a crucial role in data retention and data governance . With Time Travel, you can easily comply with data regulations that require you to keep a history of data changes. It also allows you to recover data that was accidentally deleted or modified, making it an essential tool for disaster recovery.

Benefits of Using Snowflake Time Travel

The benefits of using Snowflake Time Travel are manifold. First and foremost, it provides a robust solution for data auditing . By keeping a history of all data changes, you can easily track who changed what and when. This is invaluable in industries where data integrity and traceability are paramount, such as finance and healthcare.

Time Travel also plays a crucial role in data compliance . Many data regulations require businesses to retain a history of data changes for a certain period. With Time Travel, complying with these regulations becomes a breeze. You can easily demonstrate to auditors that you have a full history of data changes at your fingertips.

Another key benefit of Time Travel is in disaster recovery . Accidental data deletion or modification can be disastrous, especially if the data is critical for your business. With Time Travel, you can quickly restore the data to its previous state, minimizing downtime and data loss.

To illustrate the power of Time Travel, consider a scenario where a critical table in your database is accidentally dropped. Without Time Travel, recovering this table would require a restore from backup, which could take hours or even days, depending on the size of the table and the speed of your backup system. With Time Travel, you can simply query the table as it

existed just before it was dropped, and then recreate it with a single command. This can be done in minutes, regardless of the size of the table.

How to Enable and Use Snowflake Time Travel

Enabling Time Travel in Snowflake is straightforward. When you create a table, you can specify the Time Travel retention period by setting the DATA_RETENTION_TIME_IN_DAYS parameter. For example, the following command creates a table with a Time Travel retention period of 14 days:

Once Time Travel is enabled, you can query historical data using the AT or BEFORE keywords in your SQL queries. For example, the following query retrieves the state of my_table as it was 1 hour ago:

Restoring deleted data is just as easy. If you accidentally drop my_table , you can restore it using the UNDROP TABLE command:

This will restore my_table to its state just before it was dropped, effectively undoing the DROP TABLE command.

In conclusion, Snowflake Time Travel is a powerful feature that can greatly enhance your data management capabilities. Whether you're auditing data changes, complying with data regulations, or recovering from a data disaster, Time Travel has you covered. So why not take it for a spin and see how it can benefit your business?

Best Practices and Limitations of Snowflake Time Travel

While Snowflake Time Travel is a powerful tool, it's essential to understand its best practices and limitations to use it effectively.

One best practice is to set an appropriate Time Travel retention period for each table. The retention period should be long enough to allow for data recovery in case of accidental deletion, but not so long that it unnecessarily increases storage costs.

Another best practice is to use Time Travel in conjunction with a robust data backup strategy. While Time Travel can recover data within the retention period, it's not a substitute for regular backups, especially for data that needs to be retained for a long time.

As for limitations, one key limitation of Time Travel is that it can't recover data beyond the retention period. Once the retention period has passed, the historical data is permanently deleted. Therefore, it's crucial to ensure that your retention period is adequate for your needs.

Another limitation is that Time Travel can increase storage costs, as it requires keeping multiple versions of your data. However, Snowflake uses advanced compression techniques to minimize the storage impact.

Snowflake Time Travel for Data Compliance and Disaster Recovery

Snowflake Time Travel can play a crucial role in data compliance and disaster recovery. For data compliance, Time Travel allows you to keep a history of data changes, which is often required by data regulations. You can easily demonstrate to auditors that you have a full history of data changes, helping you comply with regulations and avoid penalties.

For disaster recovery, Time Travel can be a lifesaver. If critical data is accidentally deleted or modified, you can quickly restore it to its previous state using Time Travel. This can significantly reduce downtime and data loss, helping your business recover quickly from data disasters.

In conclusion, Snowflake Time Travel is a powerful tool that can significantly enhance your data management capabilities. It allows you to access historical data, comply with data regulations, recover from data disasters, and more. However, it's essential to understand its best practices and limitations to use it effectively.

We encourage you to explore Snowflake Time Travel further. Check out tutorials and documentation to learn more about this powerful feature and how it can benefit your business.

1. What is Snowflake Time Travel?

Snowflake Time Travel is a feature that allows you to access historical data within a specified time frame. This means you can query data as it existed at any point in the past.

2. How does Snowflake Time Travel work?

Whenever a change is made to a table in Snowflake, instead of overwriting the existing data, Snowflake keeps a copy of the old data. This allows you to query the table as it was at any point within the Time Travel retention period.

3. What are the benefits of using Snowflake Time Travel?

The benefits of using Snowflake Time Travel include data auditing, data compliance, and disaster recovery. It allows you to track data changes, comply with data regulations, and recover data that was accidentally deleted or modified.

Snowflake Time Travel

By: koen verbeeck.

Snowflake works with immutable cloud storage as the storage layer for the data. This means it doesn’t change a file, it rather creates a new version of the file. This way of working opens new and exciting possibilities and one of them is time travel.

Introduction to Time Travel

Imagine that every time you make a change to a table, a new version of the table is created. Each time you run an INSERT, UPDATE or DELETE (or any other DML statement), a new version of the table is stored alongside all previous versions of the table. This would mean you could read previous versions of the data in your table. Time travel in Snowflake is exactly that.

You can compare it with temporal tables in SQL Server . In fact, you’ll see the syntax to query with time travel is fairly the same as in SQL Server. The biggest difference however is SQL Server stores all the versions indefinitely, while Snowflake only maintains the different versions of your table for a specific period in time. Depending on your edition, this is either one day or up to 90 days. After those 90 days, the versions are lost. Well, actually they are kept for 7 more days, but only Snowflake support can access those. An overview:

To query the current version of your table, you use the standard SQL you’ve been using all along. To query the previous versions, you need specific syntax, which we’ll cover in the next paragraph. The fail-safe cannot be queried. By default, time travel is enabled on every table. You can however shorten the data retention period or you can turn it completely off for a table. Those tables are called transient tables . Good candidates for transient tables are staging tables or tables which are truncated and re-loaded every day. Keep in mind storing all of those different versions of your table actually consumes storage for which you have to pay.

Currently, Snowflake doesn’t have a back-up mechanism. It relies on the underlying cloud to make sure the data is safe and replicated. However, if you do something wrong, like accidentally deleting some data, you can use time travel to fetch the data before you made the change.

Querying Time Travel Data

Let us first create a new table:

With some sample data:

Wait for a couple of minutes, then insert some extra sample data:

We now have 4 rows in the table, and 3 versions (1 with no rows, 1 with 2 rows and 1 with 4 rows).

Using the query history, we can fetch the query ID of our last INSERT statement.

Using the BEFORE clause, we can fetch the version of the table before our last INSERT:

This returns two rows:

Using the OFFSET clause, we can go back a specific period in time.

In the example here, we retrieved the version of the table before any row was inserted.

If you go too far back in time, you’ll get an error because the table didn’t exist yet.

If you truncate a table, time travel still works. If you drop a table however, you cannot query the data anymore. You can restore the table though using the UNDROP command. Once the table is restored, time travel works again.

Additional Information

• You can also query time travel data for a specific time stamp.
• Introduction to SQL Server Temporal Tables
• Options to Retrieve SQL Server Temporal Table and History Data
• SQL Server 2016 T-SQL Syntax to Query Temporal Table
• More temporal table tips .

Next >>

Comments for this article.

Snowflake Time Travel - A Detailed Guide

• Snowflake Interview Questions and Answers
• Snowflake Tutorial
• Snowflake Architecture
• What is Snowflake Data Warehouse
• Star schema and Snowflake schema in QlikView
• Snowflake vs Redshift - Which is the Best Data Warehousing Tool
• Snowflake vs BigQuery
• What is a Snowflake Table & Types of Tables
• Snowflake vs Databricks
• Snowflake vs Azure
• Snowflake Vs Hadoop: What's the Difference?
• How to Insert Data in Snowflake
• Snowflake Connectors
• Snowflake Documentation - A Complete Guide
• How to Update Data in Snowflake?
• How to Delete Data in Snowflake
• How to Add a Default Value to a Column in Snowflake
• How to Add a Column in Snowflake
• How to Add a NOT NULL Constraint in Snowflake
• How to Alter a Sequence in Snowflake
• How to Create a Table in Snowflake
• How to Create a View in Snowflake
• How to create an Index in Snowflake
• How to Drop a Column in Snowflake
• How to Drop a Table in Snowflake - MindMajix
• How to Drop a View in Snowflake?
• How to Drop the Index in Snowflake
• How to Duplicate a Table in Snowflake
• How to Remove a NOT NULL Constraint in Snowflake
• How to Remove a Default Value to a Column in Snowflake
• How to Rename a Table in Snowflake
• How to Rename a Column in Snowflake
• How to write a Common Table Expression(CTE) in Snowflake
• How to Write a Case Statement in Snowflake
• How to Use Coalesce in Snowflake
• How to Query a JSON Object in Snowflake
• How to Truncate a Table in Snowflake
• How to Group by Time in Snowflake
• How to Import a CSV in Snowflake
• How to Query Date and Time in Snowflake
• How to Convert UTC to Local Time Zone in Snowflake
• How to Avoid Gaps in Data in Snowflake
• How to Have Multiple Counts in Snowflake
• How do we Calculate the Running Total or Cumulative Sum in Snowflake
• How to Round Timestamps in Snowflake
• How to Calculate Percentiles in Snowflake
• How to Compare Two Values When One Value is Null in Snowflake
• How to do Type Casting in Snowflake
• How to Get First Row Per Group in Snowflake
• Snowflake Cloning - Zero Copy Clone
• Explore real-time issues getting addressed by experts
• Test and Explore your knowledge
• Introduction to Time Travel

Time Travel SQL Extensions

• Data Retention Period
• How to Specify the data retention period for Time Travel?
• What is fail-safe in Snowflake?
• Which Snowflake Edition Provides Time Travel?

How to Restore Objects?

Enabling and disabling time travel.

• Snowflake Time Travel Storage cost
• Difference between Time Travel and Fail-Safe in Snowflake

Traveling across time would be fantastic! There are a lot of time travel movies and series out there, on the other hand, consistently caution us about the risks of altering timelines. Perhaps time travel isn't a good idea. I can think of a few scenarios in which time travel would be helpful!

Snowflake Time Travel - Table of Contents

Do you wish you could travel across time and observe the evolution of your data over time without having to restore backups or implement a fully functional data warehouse as a business user? Please don't misunderstand what I'm saying: I'm not stating that a data warehouse isn't necessary; I'm simply stating that the data you require may not be available in your current data warehouse, or you may never have had one and wish you had.

Have you ever accidentally dropped, truncated, or erased rows from a table? Hopefully, you've prepared backups to fall back on! It's critical to be extra cautious when using update and remove statements. Developers or Database administrators occasionally find themselves in situations where they would like to execute a single piece of code and revert back to the snapshot before their last test SQL execution.

These are the kinds of circumstances where database time travel might be extremely useful. Snowflake supports database time travel. Snowflake Time Travel allows you to go back in time and view past data, i.e. data that has been modified or removed). As the data storage layer, Snowflake employs immutable cloud storage. This indicates that it makes a new version of the file rather than changing it. Time travel is one of the new and fascinating possibilities that this manner of functioning opens up.

It is an effective tool for doing the following tasks:

• Restoring data-related objects (tables, schemas, and databases) that may have been removed by accident or on purpose.
• Duplicating and backing up data from previous periods of time.
• Analyzing data manipulation and consumption over a set time.

Introduction to Snowflake Time Travel

Time travel is an interesting feature that allows you to access data from any point in the past. If you have an Employee table, for example, and you unintentionally delete it, you can utilize time travel to go back 5 minutes and retrieve the data.

It also can be used to back up data, compare data, and examine previous data usage over a period of time. As a result, it acts as a continuous data protection lifecycle that is dependent on the data retention durations set for each item. It's critical to comprehend the various data retention periods which can be used in distinct versions. All Snowflake accounts have a standard retention duration of one day (24 hours), which is enabled by default.  At the account and object level, the retention period in Snowflake Standard Edition can be adjusted to 0 (or unset to the default of 1 day). The retention time for everlasting databases, schemas, and tables in Snowflake Enterprise Edition and higher can be specified to any value between 0 and 90 days (with the exception of transient tables).

Consider the following scenario: You're working on a bug patch and are connected to the PROD database, where you executed an update statement on a table that updated billions of records. On that table, you also conducted additional delete and update logic. You later realized that when using update and delete statements in your SQL query, you forgot to use the necessary where clause.

So, what are you going to do now? Perhaps you'll consider retrieving the database's most recent backup copy and restoring it. Alternatively, you might try to truncate the table and load new data from the source. Alternatively, you will inform your boss.

If you're working in a PROD environment, you won't have enough time to backup your data and load new data. When it comes to restoring a backup copy, there is a potential that data will be lost between the last backup and the current data. If you want to do a fresh data load, it will take anything from hours to days, depending on the amount of data you have.

Then you're probably considering going back in time and restoring things to their previous state. What I mean is that I want to go back to that point in time and get the data as it was before I made a mistake with my initial update/Delete statement.

And Snowflake has a capability that allows you to go back in time and retrieve your data. This property of a snowflake is also known as its time-traveling property.

We use the Time Travel SQL Extensions AT or BEFORE clause in SELECT queries and CREATE... CLONE commands in Snowflake Time Travel to retrieve or clone historical data. We use the SQL Extensions with the following arguments to locate the particular historical data that you want to access:

• OFFSET (time difference from the current time in seconds).
• STATEMENT (statement's identifier, e.g. query ID).

What is Data Retention Period For Snowflake Time Travel?

The characteristic of time travel relies heavily on the data retention duration. When a user makes changes to a table, Snowflake saves the current state of the data before making any alterations. And this state of data from the past will last for a set amount of time, which is known as the Time travel data retention period.

For all Snowflake accounts, the default data retention duration is one day. It is 1 day by default for standard aims, and it ranges from 0 - 90 days for enterprise edition and higher accounts.

How to Specify the Data Retention Period For Time Travel?

Steps to specify the data retention duration are as follows:

• DATA_RETENTION_TIME_IN_DAYS object parameter can be used by users with the ACCOUNTADMIN role to set the data retention time for your account.
• When building a schema, database, or individual table, the DATA_RETENTION_TIME_IN_DAYS object option is also used to alter the default.
• At any moment, users can change the data retention term for a schema, database, or table.

You can either increase or decrease the data retention duration.

• Increasing Retention: It extends the data retention time for time travel data.
• Decreasing Retention: It reduces the data retention duration for time travel data.

What is Fail-safe in Snowflake?

Fail-safe gives a (non-configurable) 7-day timeframe during which Snowflake may be able to retrieve prior data. This time begins as soon as the Time Travel retention period expires. 

It is a data recovery service that is offered with the best effort and should only be used after all other options have been exhausted. After the Time Travel retention term has expired, there is no fail-safe method for accessing prior data. It's just for Snowflake's use to recover data that's been lost or destroyed as a result of extreme operational failures. Fail-safe data recovery might take somewhere from a few hours to many days.

Which Snowflake Edition provides Time Travel?

These functions are offered as standard for all accounts, requiring no additional license; however, standard Time Travel is limited to one day. Snowflake Enterprise Edition is required for extended time travel (up to 90 days). Furthermore, both Time Travel and Fail-safe necessitate additional data storage, which comes with a cost.

The UNDROP command can be used to restore a dropped table, schema, or database that has not yet been purged from the system (i.e. not yet put into Snowflake Fail-safe). UNDROP returns the item to its most recent state, which was before it was dropped.

Snowflake's time travel feature is activated by default in all editions, with a 1-day data retention period. For enterprise and higher versions, however, we can enable a longer data retention duration of up to 90 days. Individual databases, schemas, and tables can have time travel disabled by setting the data retention time in days to 0. Use the appropriate ALTER command to adjust an object's retention duration.

[ Related Article: Star schema and Snowflake schema in QlikView ]

Snowflake Time Travel Storage Cost

During both the Time Travel and Fail-safe phases, storage charges are incurred for retaining prior data.

Snowflake keeps only the information needed to restore the individual table rows that were updated or deleted, reducing the amount of storage required for historical data. As a result, storage utilization is expressed as a percentage of the altered table. Tables are only kept in full copies when they are dropped or shortened.

The charges for storage are assessed for each 24-hour period (i.e. one day) starting from the time the data was modified. The number of days historical data is kept is determined by the table type and the table's Time Travel retention period.

Visit here to learn Snowflake Training in Pune

Difference Between Time Travel and Fail-Safe in Snowflake

Time travel allows the user to query the data and see how it appeared previously, as well as query and restore the table's former state. Internally, snowflakes use fail-safe to restore data in the event of hardware failure.

Snowflake's Time Travel feature is a wonderful way to save data that was either deleted or lost accidentally in the past. Fail-safe offers free 7-day storage and begins working immediately after the time-travel period has ended. I hope you found some useful information from this Snowflake Time Travel blog.

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Madhuri is a Senior Content Creator at MindMajix. She has written about a range of different topics on various technologies, which include, Splunk, Tensorflow, Selenium, and CEH. She spends most of her time researching on technology, and startups. Connect with her via LinkedIn and Twitter .

Copyright © 2013 - 2024 MindMajix Technologies

• Today's news
• Reviews and deals
• Climate change
• 2024 election
• Fall allergies
• Health news
• Mental health
• Sexual health
• Family health
• So mini ways
• Unapologetically
• Buying guides

Entertainment

• How to Watch
• My Portfolio
• Latest news
• Stock market
• Premium news
• Biden economy
• EV Deep Dive
• Stocks: Most Actives
• Stocks: Gainers
• Stocks: Losers
• Trending Tickers
• World Indices
• US Treasury Bonds
• Top Mutual Funds
• Highest Open Interest
• Highest Implied Volatility
• Stock Comparison
• Advanced Charts
• Currency Converter
• Basic Materials
• Communication Services
• Consumer Cyclical
• Consumer Defensive
• Financial Services
• Industrials
• Real Estate
• Mutual Funds
• Credit cards
• Balance transfer cards
• Cash-back cards
• Rewards cards
• Travel cards
• Personal loans
• Student loans
• Car insurance
• Morning Brief
• Market Domination
• Market Domination Overtime
• Opening Bid
• Stocks in Translation
• Lead This Way
• Good Buy or Goodbye?
• Fantasy football
• Pro Pick 'Em
• College Pick 'Em
• Fantasy baseball
• Fantasy hockey
• Fantasy basketball
• Download the app
• Daily fantasy
• Scores and schedules
• GameChannel
• World Baseball Classic
• Premier League
• CONCACAF League
• Champions League
• Motorsports
• Horse racing
• Newsletters

New on Yahoo

• Privacy Dashboard

Yahoo Finance

Snowflake to announce financial results for the first quarter of fiscal 2025 on may 22, 2024.

No-Headquarters/BOZEMAN, Mont., April 29, 2024 --( BUSINESS WIRE )-- Snowflake (NYSE: SNOW), the Data Cloud company, today announced it will release its financial results for the first quarter of fiscal year 2025, which ends April 30, 2024, following the close of the U.S. markets on Wednesday, May 22, 2024. Snowflake will host a conference call to discuss the financial results.

Conference Call Details

The conference call will begin at 3 p.m. Mountain Time on May 22, 2024. Investors and participants may attend the call by dialing (833) 470-1428 (Access code: 837931), or if outside the United States, by dialing +1 (929) 526-1599 (Access code: 837931).

The call will also be webcast live on the Snowflake Investor Relations website .

An audio replay of the conference call and webcast will be available two hours after its completion and will be accessible for 30 days on the Snowflake Investor Relations website .

About Snowflake

Snowflake makes enterprise AI easy, efficient, and trusted. Thousands of companies around the globe, including hundreds of the world’s largest, use Snowflake’s Data Cloud to share data, build AI and machine learning applications, and power their business. The era of enterprise AI is here. Learn more at snowflake.com (NYSE: SNOW).

View source version on businesswire.com: https://www.businesswire.com/news/home/20240429040157/en/

Investor Contact Jimmy Sexton Head of Investor Relations [email protected]

Press Contact Eszter Szikora Head of Public Relations [email protected]

Understanding storage cost ¶

Storage cost represents the cost of:

Files staged for bulk data loading/unloading (stored compressed or uncompressed).

Database tables, including historical data for Time Travel .

Fail-safe for database tables.

Clones of database tables that reference data deleted in the table that owns the clones.

The monthly costs for storing data in Snowflake is based on a flat rate per terabyte (TB). The amount charged depends on your type of account (Capacity or On Demand) and region (US or EU).

For storage pricing, see the Snowflake Pricing Guide .

Staged file costs ¶

Files staged for bulk data loading/unloading incur storage costs based on the size of the files. For more information on loading data, see Load Data into Snowflake .

Database costs ¶

Database costs include data stored in database tables. Database costs also include historical data maintained for Time Travel. Snowflake automatically compresses all data stored in tables and uses the compressed file size to calculate the total storage used for an account.

See also Data Storage Considerations .

Time Travel and Fail-safe costs ¶

Time Travel and Fail-safe fees are calculated for each 24-hour period (i.e. 1 day) from the time the data changed. The number of days historical data is maintained is based on the table type and the Time Travel retention period for the table.

Snowflake minimizes the amount of storage required for historical data by maintaining only the information required to restore the individual table rows that were updated or deleted. As a result, storage usage is calculated as a percentage of the table that changed. Full copies of tables are only maintained when tables are dropped or truncated.

See also Storage Costs for Time Travel and Fail-safe .

Temporary and transient tables costs ¶

To help manage the storage costs associated with Time Travel and Fail-safe, Snowflake provides two table types, temporary and transient. Temporary and transient tables do not incur the same fees as permanent tables:

Transient and temporary tables contribute to the storage charges that Snowflake bills your account until explicitly dropped. Data stored in these table types contributes to the overall storage charges Snowflake bills your account while they exist.

Temporary tables are typically used for non-permanent session specific transitory data such as ETL or other session specific data. Temporary tables only exist for the lifetime or their associated session. On session end, temporary table data is purged and unrecoverable. Temporary tables are not accessible outside the specific session which created them.

Transient tables exist until explicitly dropped and are available to all users with appropriate privileges.

Transient and temporary tables can have a Time Travel retention period of either 0 or 1 day.

Transient and temporary tables have no Fail-safe period.

Transient and temporary tables can, at most, incur a one day’s worth of storage cost.

The following table illustrates the different scenarios, based on table type:

Using temporary and transient tables to manage storage costs ¶

When choosing whether to store data in permanent, temporary, or transient tables, consider the following:

Temporary tables are dropped when the session in which they were created ends. Data stored in temporary tables is not recoverable after the table is dropped.

Historical data in transient tables cannot be recovered by Snowflake after the Time Travel retention period ends. Use transient tables only for data you can replicate or reproduce independently from Snowflake.

Long-lived tables, such as fact tables, should always be defined as permanent to ensure they are fully protected by Fail-safe.

Short-lived tables (i.e. <1 day), such as ETL work tables, can be defined as transient to eliminate Fail-safe costs.

If downtime and the time required to reload lost data are factors, permanent tables, even with their added Fail-safe costs, may offer a better overall solution than transient tables.

The default type for tables is permanent. To define a table as temporary or transient, you must explicitly specify the type during table creation.

Hybrid table costs ¶

If you choose to use hybrid tables for your operational and analytical workloads, consider that cost for data storage in that table type is based on your consumption rates of the following:

Hybrid Table Storage (incurred primarily by the hybrid tables themselves and the indexes they contain)

Hybrid Table Requests

Generally, hybrid tables are more costly than standard Snowflake tables.

For more information, see Understand cost for hybrid tables .

Cloning tables, schemas, and databases costs ¶

Snowflake’s zero-copy cloning feature provides a convenient way to quickly take a “snapshot” of any table (excluding hybrid tables), schema, or database and create a derived copy of that object which initially shares the underlying storage. This can be extremely useful for creating instant backups that do not incur any additional costs (until changes are made to the cloned object).

However, cloning makes calculating total storage usage more complex because each clone has its own separate life-cycle. This means that changes can be made to the original object or the clone independently of each other and these changes are protected through CDP.

For example, when a clone is created of a table, the clone utilizes no storage because it shares all the existing micro-partitions of the original table at the time it was cloned; however, rows can then be added, deleted, or updated in the clone independently from the original table. Each change to the clone results in new micro-partitions that are owned exclusively by the clone and are protected through CDP.

In addition, clones can be cloned, with no limitations on the number or iterations of clones that can be created (e.g. you can create a clone of a clone of a clone, and so on), which results in an n-level hierarchy of cloned objects, each with their own portion of shared and independent storage.

Cross-Cloud Auto-Fulfillment costs ¶

Cross-Cloud Auto-Fulfillment lets you provide a data product to consumers in other cloud regions without manual data replication. When your data product is auto-fulfilled to another region, you incur storage and other costs. For details, see Managing Cross-Cloud Auto-Fulfillment Costs .

Exploring storage cost