what is time travel in snowflake

No-code Data Pipeline for Snowflake

Seamlessly load data from 150+ sources to Snowflake in real-time with Hevo.

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

By: Harsh Varshney Published: January 13, 2022

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.

Key Features of Snowflake

What is snowflake time travel feature.

• Enable Snowflake Time Travel
• Disable Snowflake Time Travel
• What are Data Retention Periods?
• What are Snowflake Time Travel SQL Extensions?

How Many Days Does Snowflake Time Travel Work? 

How to specify a custom data retention period for snowflake time travel , how to modify data retention period for snowflake objects.

• How to Query Snowflake Time Travel Data? 

How to Clone Historical Data in Snowflake? 

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

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

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.

• 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 . 

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.

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. 

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:

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 . 

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.

• Snowflake Commands

Related Articles

Hevo - No Code Data Pipeline

Continue Reading

Sarad Mohanan

Amazon Redshift Vs Hadoop: How to Make the Right Choice?

Amazon Redshift Architecture: 7 Important Components

Vivek Sinha

Amazon Redshift vs PostgreSQL Comparison: Choosing the Right Data Warehouse

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.

Streamline your Data Vault: enhancing audit, automation and time to value with Y42 and AutomateDV

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 delve into 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.

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)

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 Use Cases of phData’s Advisor Tool

Where to Find Snowflake Training Resources

Beyond The Data: Ajay H N, Solutions Architect

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.

Leveraging Snowflake's Time Travel

Learn how to leverage Snowflake's Time Travel feature in conjunction with DbVisualizer to effortlessly explore historical data, restore tables to previous states, and track changes for auditing and compliance purposes.

Introduction

Welcome to our tutorial on leveraging Snowflake's powerful Time Travel feature in combination with DbVisualizer. Snowflake's Time Travel provides an exceptional capability for data versioning, allowing you to delve into the past and query historical data effortlessly. By using Time Travel, you gain the ability to access data as it appeared at any given point in time within a specified retention period, all without the complexities of traditional backup and restore processes. This feature not only simplifies auditing and compliance requirements but also empowers you with granular control over data versions.

Throughout this tutorial, we will explore the numerous benefits and use cases of leveraging Snowflake's Time Travel. You'll discover how it facilitates data recovery, making it a valuable tool to rectify data corruption or accidental changes. Moreover, Time Travel enables historical analysis by uncovering data trends and patterns, empowering data scientists and analysts to gain deeper insights.

By leveraging Time Travel in conjunction with DbVisualizer, a versatile database management and visualization tool, you'll be able to efficiently query and explore historical data. This tutorial will guide you through the process of setting up Snowflake and DbVisualizer, configuring the connection, and demonstrating effective usage of Time Travel to enhance your data management workflows.

Get ready to unlock the full potential of Snowflake's Time Travel feature, as we equip you with the knowledge and skills to harness its power, streamline your data versioning processes, and uncover invaluable insights from your historical data. Let's dive in!

Prerequisites

To follow this tutorial, you will need the following:

• Snowflake : Cloud data warehouse with scalable architecture and seamless data sharing.
• DbVisualizer : Versatile tool for managing, querying, and visualizing databases through a user-friendly interface.
• Knowledge of basic SQL syntax and queries.

Setting Up Snowflake and DbVisualizer

In this section, we'll walk you through the process of setting up Snowflake and connecting it to DbVisualizer, enabling you to seamlessly manage and analyze your data.

Creating a Snowflake Account and Setting Up a Virtual Warehouse

• Begin by creating a Snowflake account if you haven't already. Visit the Snowflake website and follow their registration process.
• Once you've successfully registered, set up a virtual warehouse in Snowflake. This virtual warehouse will serve as your computing resource for querying and processing data.
• Within your Snowflake account, navigate to the Account section or dashboard where you'll find your account's connection details. This includes the URL, username, password, and role you'll need to connect to Snowflake.

Configuring a New Database Connection in DbVisualizer

• Launch DbVisualizer and open the "Database" menu. Choose the option to "Create a New Connection."

• Select "Snowflake" as the database type. This will prompt you to input specific Snowflake connection details.

• Enter the connection details you obtained from your Snowflake account. This includes the URL, username, password, and role.
• Choose the virtual warehouse you set up earlier from the available options. This warehouse will determine the compute resources allocated to your DbVisualizer queries.

• You might need to download the Snowflake JDBC driver before creating a connection if you haven’t already. To do that, navigate to the Driver manager:

Then click on “Start Download” to download the required JDBC driver for Snowflake.

• With all the necessary details input, click the "Test Connection" button within DbVisualizer. This will initiate a connection test to Snowflake using the provided credentials.

If the test is successful, you'll receive a confirmation message indicating that DbVisualizer can connect to Snowflake using the specified parameters.

Exploring Time Travel in Snowflake

Time Travel is a powerful feature offered by Snowflake, a cloud-based data warehousing platform, that allows you to query historical data at various points in time. This feature is particularly valuable for analytical and auditing purposes, as it enables you to track changes to your data over time without the need for complex versioning or snapshotting mechanisms.

In Snowflake, every table is associated with a period, referred to as a "time travel period." This period represents the duration for which historical data is retained in the table. Snowflake automatically maintains a history of changes made to data during this period, allowing you to retrieve data as it existed at different points in the past.

Enabling Time Travel for a Snowflake Database

Enabling Time Travel is straightforward in Snowflake and can be configured at the database level. When creating or altering a database, you can specify the time travel retention period. This period determines how far back in time you can query historical data. Time travel retention can be set in terms of hours, days, or even for an infinite retention.

For example, to create a database with a time travel retention period of 30 days, the SQL query would look like this:

This means that data changes made in the past 30 days can be queried using the Time Travel feature.

Querying Historical Data Using Time Travel Syntax in DbVisualizer

DbVisualizer is a popular database management and visualization tool that supports various database systems, including Snowflake. To query historical data using Time Travel in DbVisualizer, you can follow these steps:

• Write a Time Travel Query: Once connected, you can write SQL queries to access historical data using Time Travel syntax. The syntax involves using the AS OF clause in your query to specify the point in time you want to retrieve data from. For instance, a query like so:

Would fetch all rows from the specified table as they existed at the given timestamp.

• Execute the Query:

• After writing the query, execute it in DbVisualizer. The results will display the data as it was at the specified timestamp, allowing you to analyze historical changes.
• Explore Historical Data: You can further refine your Time Travel queries by adding additional conditions, joins, and aggregations, just like regular SQL queries. This enables you to perform in-depth analysis on historical data. Let's say you want to further refine this query by adding an additional condition to filter the results. For example, you might want to retrieve only the users who registered before a certain date:
• In this modified query, the "WHERE" clause filters the results to include only users who registered before August 1, 2023. This is an additional condition added to the Time Travel query to narrow down the historical data based on a specific attribute ("registration_date" in this case). You can continue to add more conditions, joins, and aggregations to perform more complex analysis on the historical data using the Time Travel feature.

Utilizing Time Travel for Data Recovery and Auditing

Time Travel in Snowflake goes beyond its role in historical data analysis; it also serves as a powerful data recovery and auditing mechanism. Whether you're exploring the past or rectifying accidental changes, Time Travel offers a smooth path to data restoration. This section delves into how you can employ Time Travel for data recovery and auditing purposes.

Restoring a Table to a Previous Point in Time using Time Travel

Time Travel in Snowflake not only facilitates historical data analysis but also serves as a data recovery mechanism. In scenarios where you need to restore a table to a previous state, Time Travel provides a seamless solution. Here's how you can achieve this:

• Identify the Desired Timestamp : Determine the specific point in time to which you want to restore the table. This could be a timestamp just before the undesired changes were made.
• Generate a New Table : Create a new table with the same schema as the original table. You can use the `CREATE TABLE` statement to do this.
• Insert Historical Data : Write an `INSERT INTO` query that utilizes Time Travel to retrieve the data from the original table as it existed at the chosen timestamp. For example:

This populates the new table with the data from the past.

• Verification and Validation : After restoring the table, verify the data to ensure that it matches the state it had at the chosen timestamp. You can run queries to compare the new table's data with the original table's data at that time.

Auditing Changes and Tracking Data Modifications with Time Travel

Time Travel is a valuable tool for auditing purposes, allowing you to track changes and modifications made to your data over time. By leveraging Time Travel, you can maintain an accurate record of every change without the need for complex versioning systems. This is particularly beneficial for compliance, regulatory, and internal auditing requirements.

• Enabling Detailed Tracking: With Time Travel enabled, Snowflake automatically keeps track of all changes to your data, including inserts, updates, and deletes, within the specified time travel period.
• Querying Historical Changes: You can query historical data using Time Travel to investigate specific changes. For example, you can identify who made a particular change, what the change was, and when it occurred.
• To initiate this process, you first need to create the `audit_changes` table, which will serve as a record keeper for changes:
• Subsequently, you'll establish a stream that captures historical changes for the target table. In this example, we're utilizing the `USERS` table:
• Once the stream is in place, you'll construct a task to populate the `audit_changes` table with the changes extracted from the stream:
• Finally, ensure the task starts executing with the following command:

•   Ensure that the task status is marked as "started," as illustrated in the image above.

With this configuration in place, any modifications made to the `USERS` table will be automatically captured and logged into the `AUDIT_CHANGES` audit table. This mechanism allows you to effortlessly maintain a detailed record of alterations and their respective context.

In this tutorial, we've delved into Snowflake's Time Travel feature, showcasing its versatility in data recovery and auditing. Time Travel isn't just about historical analysis—it's a tool that simplifies data restoration and empowers robust auditing.

We began by grasping the essence of Time Travel—its ability to query data as it appeared at various times. This streamlines auditing, compliance, and versioning without complex backups.

We navigated setting up Snowflake and DbVisualizer, fusing data warehousing capabilities with an intuitive interface.

Our journey through Time Travel involved enabling it, executing queries in DbVisualizer, and refining queries for insights.

Beyond data recovery, we harnessed Time Travel for auditing. We explored restoring tables, tracking changes through an audit_changes table, and using Snowflake's features to populate it.

As we conclude, remember the fusion of DbVisualizer and Snowflake's Time Travel. This dynamic duo enhances data management, analysis, and visualization. Embrace DbVisualizer to unearth insights and streamline data tasks. Your data adventures await—happy exploring!

Frequently Asked Questions (FAQs)

What is snowflake's time travel feature, and how can i use it to query historical data.

Snowflake's Time Travel feature allows you to access historical data within a specified retention period. You can query data as it appeared at different points in time, without the need for complex backup and restore processes. To learn how to leverage this feature alongside DbVisualizer for efficient exploration and analysis of historical data, check out the tutorial on "Leveraging Snowflake's Time Travel with DbVisualizer."

How can I effectively use Snowflake's Time Travel in combination with DbVisualizer for auditing and compliance purposes?

Ar: If you're interested in tracking changes and maintaining an audit trail for your data, you can leverage Snowflake's Time Travel feature alongside DbVisualizer. By following the tutorial on "Leveraging Snowflake's Time Travel with DbVisualizer," you can learn how to set up Snowflake, configure the connection, and use Time Travel to effortlessly explore historical data, restore tables to previous states, and track changes for auditing and compliance purposes.

 What are some practical use cases for Snowflake's Time Travel feature, and how does it benefit data analysis?

A: Snowflake's Time Travel is not only valuable for data versioning and recovery but also for historical analysis. By using Time Travel, you can uncover data trends, patterns, and insights from the past. The tutorial on "Leveraging Snowflake's Time Travel with DbVisualizer" demonstrates how to harness this feature to enhance data analysis workflows, making it easier to gain valuable insights from historical data.

How can I connect Snowflake with DbVisualizer to streamline data management tasks and analysis?

A: Connecting Snowflake with DbVisualizer can enhance your data management and analysis capabilities. DbVisualizer is a versatile tool that allows you to manage, query, and visualize databases in a user-friendly interface. If you're interested in learning how to set up this connection and leverage Snowflake's Time Travel feature, check out the tutorial that provides a step-by-step guide on "Leveraging Snowflake's Time Travel with DbVisualizer."

Where can I find a comprehensive guide on using Snowflake's Time Travel feature with DbVisualizer for historical data exploration and analysis?

A: If you're looking for a detailed guide on using Snowflake's Time Travel feature alongside DbVisualizer, there's a tutorial available titled "Leveraging Snowflake's Time Travel with DbVisualizer." This guide walks you through the process of setting up Snowflake, establishing a connection with DbVisualizer, and effectively using Time Travel to explore historical data, restore tables, and track changes for various purposes.

Ochuko is a full-stack Python/React software developer and freelance Technical Writer. He spends his free time contributing to open source and tutoring students on programming in collaboration with Google DSC.

SQL CAST Function: Everything You Need to Know

Postgresql upsert: insert on conflict guide, unlocking the power of ctes in sql, insert into sql clause, enhancing business operations with visualization tools, postgres text vs varchar: comparing string data types, alter table add column in sql: a comprehensive guide, schemas in postgresql, database schema design: a comprehensive guide for beginners, sql comment: a comprehensive guide.

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind.  Read more here .

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗

Strategic Services

Digital Engineering Services

Managed Services

Harness the power of Generative AI

Amplify innovation, creativity, and efficiency through disciplined application of generative AI tools and methods.

Focus Industries

Healthcare & Life Sciences 

Retail & CPG

Energy & Utilities

Banking, Financial Services & Insurance

Travel, Hospitality & Logistics

Telecom & Media

Explore Client Success Stories

We create competitive advantage through accelerated technology innovation. We provide the tools, talent, and processes needed to accelerate your path to market leadership.

Global Delivery with Encora

Nearshore in the Americas

Nearshore in Europe

Nearshore in Asia & Oceania

Expertise at Scale in India

Hybrid Global Teams

Experience the power of Global Digital Engineering with Encora.

Refine your global engineering location strategy with the speed of collaboration in Nearshore and the scale of expertise in India.

15+ other partnerships

Accelerating Innovation Cycles and Business Outcomes

Through strategic partnerships, Encora helps clients tap into the potential of the world’s leading technologies to drive innovation and business impact. 

Featured Insights

Narrowing Down a Use Case for Shared Loyalty in Travel

Exploring The Potential of Web3 for Shared Loyalty in Travel

E-commerce 4.0: The Emerging World of Immersive Commerce

Maximizing Engineering Performance: A Deep Dive into Platform Teams Dynamics

Latest News

Press Releases

Encora Ranks in India's Top 50 Workplaces in Health and Well...

Encora Attains AWS Cloud Operations Competency

Encora Secures Top Rankings Across Ten ER&D Segments in Zinn...

Encora and Wind River Team to Demonstrate Advanced AI Automa...

Open positions by country

Philippines

North Macedonia

Make a Lasting Impact on the World through Technology

Come Grow with Us

< Go Back

Time Travel in Snowflake

Talati adit anil.

June 01, 2023

Consider a scenario where you accidentally dropped the actual table or instead of deleting a set of records, you updated all the records present in the table. What will you do? How will you restore your data that has already been deleted/altered? You must be hoping of going back in time and correcting incorrectly executed statements. Snowflake provides this feature wherein you can get back the data that is present at a particular time. This feature of Snowflake is called Time Travel .

Introduction

Snowflake Time Travel is a very important tool that allows users to access Historical Data (i.e. data that has been updated or removed) at any point in time in the past. It is a powerful Continuous Data Protection (CDP) feature that ensures the maintenance and availability of historical data. 

Key Features

• Query Optimization: As a user, we should not be concerned about optimizing queries because Snowflake on its own optimizes queries by using Clustering and Partitioning.
• Secure Data Sharing: Using Snowflake Database, Tables, Views, and UDFs, data can be shared securely from one account to another.
• Support for File Formats: Supports almost all file formats: JSON, Avro, ORC, Parquet, and XML are all Semi-Structured data formats that Snowflake can import. Column type — Variant lets the user store Semi-Structured data.
• Caching: Caching strategy of Snowflake returns results quickly for repeated queries as it stores query results in a cache within a given session.
• Fault Resistant: In case of event failure, Snowflake provides exceptional fault-tolerant capabilities to recover tables, views, databases, schema, and so on.
• To query past data.
• To make clones of complete Tables, Schemas, and Databases at or before certain dates.
• To restore deleted Tables, Schemas, and Databases.
• To restore original data that was updated accidentally.
• To check consumption over a period of time.
• Cloning and Backing up data from previous times.

How to Enable & Disable Time Travel in Snowflake?

Enable time travel.

No additional configurations are required to enable Time Travel, it is enabled by default, with a one-day retention period. Although to configure longer data retention periods, we need to upgrade to Snowflake Enterprise Edition. The retention period can be set to a maximum of 90 days. Based on the retention period, charges will increase. The below query builds a table with a retention period of 90 days:

The retention period can also be changed using the ‘alter’ query as below:

Disable Time Travel

Time Travel cannot be turned off for accounts, but it can be turned off for individual databases, schemas, and tables by setting data_retention_time_in_days field to 0 using the below query:

Query Time Travel Data

Whenever any Data Manipulation Language (DML) query is executed on a table, Snowflake saves prior versions of the Table data for a given period of time depending on the retention period. The previous version of data can be queried using the AT | BEFORE Clause. Using AT, the user can get data at a given period of time whereas using BEFORE all the data from that point till the end of the retention period can be fetched. The following SQL extensions have been added to facilitate Snowflake Time Travel:

• CLONE: To create a logical duplicate of the object at a specific point in its history.
• TIMESTAMP: From a given time (Data & Time) provided.
• OFFSET: Time difference from current time till offset provided in seconds.
• STATEMENT: Using a Statement ID from the point where the last DML query was fired.
• UNDROP: If a table is dropped accidentally, it can be restored using the UNDROP command.

The below query generates a Clone of a Table from the given Date and Time as indicated by the Timestamp:

The below query creates a Clone of a Schema and all its Objects as they were an hour ago:

The below query pulls Historical Data from a Table from a given Timestamp:

The below query pulls Historical Data from a Table that was updated 5 minutes ago:

The below query collects Historical Data from a Table up to the given statement’s Modifications (Statement ID):

The below query is used to restore Database EMP:

The following graphic from the Snowflake documentation summarizes all the above points visually:

Data Retention

Snowflake preserves the previous state of the data when DML operations are performed. By default, all Snowflake accounts have a standard retention duration of one day which is automatically enabled.

• For Snowflake Standard Edition, the Retention Period can be adjusted to 0 from default 1 day for all objects (Temporary & Permanent).
• For Snowflake Enterprise Edition (or higher) it gives more flexibility for setting retention period, that is The Retention Time for permanent Databases, Schemas, and Tables can be configured to any number between 0 and 90 days whereas for temporary objects it can be set to 0 from the default 1 day.

The below query sets a retention period of 90 days while creating the table: 

Snowflake provides another exciting feature called Fail-safe where historical data can be protected in case of any failure. Fail-safe allows a maximum period of 7 days which begins after the Time Travel retention period ends wherein Historical data can be recovered. Recovering data through Fail-safe can take hours to days and it involves cost.

The number of days historical data is maintained is based on the table type and the Fail-safe period for the table. Transient and temporary tables have no Fail-safe period.

Storage fees are incurred for maintaining historical data during both the Time Travel and Fail-safe periods. The fees are calculated for each 24 hours (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 retention period set 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. In most cases, Snowflake does not keep a full copy of data. Only when tables are dropped or truncated, full copies of tables are maintained.

Temporary and Transient Tables

To manage the storage costs Snowflake provides two table types: TEMPORARY & TRANSIENT, which do not incur the same fees as standard (i.e. permanent) tables:

• Transient tables can have a Time Travel retention period of either 0 or 1 day.
• Temporary tables can also have a Time Travel retention period of 0 or 1 day; however, this retention period ends as soon as the table is dropped or the session in which the table was created ends.
• Transient and temporary tables have no Fail-safe period.
• The maximum additional fees incurred for Time Travel and Fail-safe by these types of tables are limited to 1 day. 

The above table illustrates the different scenarios, based on table type.

hbspt.cta._relativeUrls=true;hbspt.cta.load(7958737, '1308a939-5241-47c3-bf0f-864090d8516d', {"useNewLoader":"true","region":"na1"});

Snowflake Time Travel is a powerful feature that enables users to examine data usage and manipulations over a specific time. Syntax to query with time travel is fairly the same as in SQL Server which is easy to understand and execute. Users can restore deleted objects, make duplicates, make a Snowflake backup, and recover historical data. 

About Encora

Fast-growing tech companies partner with Encora to outsource product development and drive growth. Contact us to learn more about our software engineering capabilities.

Encora accelerates enterprise modernization and innovation through award-winning digital engineering across cloud, data, AI, and other strategic technologies. With robust nearshore and India-based capabilities, we help industry leaders and digital natives capture value through technology, human-centric design, and agile delivery.

Share this post

Table of Contents

Related insights.

5 Axioms to Improve Your Team Communication and Collaboration

Good communication within a team is key to keeping everyone on the right track. But it can be ...

JavaScript: setTimeout() and Promise under the Hood

In this blog, we will delve deeper into how setTimeout works under the hood.

Exponential Smoothing Methods for Time Series Forecasting

Recently, we covered basic concepts of time series data and decomposition analysis. We started ...

Innovation Acceleration

Headquarters - Scottsdale, AZ 85260 ©Encora Digital LLC

Global Delivery

Partnerships

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

Storage Costs for Time Travel and Fail-safe ¶

Storage fees are incurred for maintaining historical data during both the Time Travel and Fail-safe periods.

Storage Usage and Fees ¶

The 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.

Also, 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.

Temporary and Transient Tables ¶

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

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

Temporary tables can also have a Time Travel retention period of 0 or 1 day; however, this retention period ends as soon as the table is dropped or the session in which the table was created ends.

Transient and temporary tables have no Fail-safe period.

As a result, the maximum additional fees incurred for Time Travel and Fail-safe by these types of tables is limited to 1 day. The following table illustrates the different scenarios, based on table type:

Considerations for 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:

CREATE [ OR REPLACE ] [ TEMPORARY | TRANSIENT ] TABLE <name> ...

For more information, see CREATE TABLE .

Migrating Data from Permanent Tables to Transient Tables ¶

Migrating data from permanent tables to transient tables involves performing the following tasks:

Use CREATE TABLE … AS SELECT to create and populate the transient tables with the data from the original, permanent tables.

Apply all access control privileges granted on the original tables to the new tables. For more information about access control, see Overview of Access Control .

Use DROP TABLE to delete the original tables.

Optionally, use ALTER TABLE to rename the new tables to match the original tables.

Snowflake Stock Is Heating Up After The Bell: Here's Why

What Happened: In a new regulatory filing released Wednesday after the bell, newly appointed Snowflake CEO Sridhar Ramaswamy  disclosed the purchase of 31,542 shares of the company’s stock. Ramaswamy bought the shares on Monday at an average price of $158.52. (Check out Benzinga’s Insider Trades page for more information.)

Last month, Snowflake announced former CEO  Frank Slootman decided to  retire from his role as CEO , but would remain with the company as chairman of the board. Ramaswamy was named CEO, effective immediately.

See Also: Small Caps Rally, Magnificent 7 Ease, Gold Eyes $2,200, Bitcoin Falls Below $69,000: What’s Driving Markets Wednesday?

SNOW Stock Prediction 2024

Equity research analysts on and off Wall Street typically use earnings growth and fundamental research as a form of valuation and forecasting. But many in trading turn to technical analysis as a way to form predictive models for share price trajectory.

Some investors look to trends to help forecast where they believe a stock could trade at a certain point in the future. Looking at Snowflake, an investor could make an assessment about a stock's long-term prospects using a moving average and trend line. If they believe a stock will remain above the moving average, which many believe is a bullish signal, they can extrapolate that trend into the future using a trend line.

For Snowflake, the 200-day moving average sits at $175.35, according to Benzinga Pro, which was above the current price of $160.04. For more on charts and trend lines, see a description here.

Traders believe that when a stock is above its moving average, it is a generally bullish signal, and when it crosses below, it is a more negative signal. Investors could use trend lines to make an educated guess about where a stock could trade at a later date if conditions remain stable.

SNOW Price Action: Snowflake shares were up 4.04% after hours at $166.50 at the time of publication Wednesday, according to Benzinga Pro.

Photo: Shutterstock

© 2024 Benzinga.com. Benzinga does not provide investment advice. All rights reserved.

Snowflake Time Travel in a Nutshell

Yeah, the title is a bit clickbaity, so if you are too sensitive, please stop reading, because the article won’t explain to you all the details of the mentioned feature of Snowflake. But despite that, it will show some interesting things that are not mentioned in the documentation and it will help to answer at least one question in the certification exam. So, I think it’s worth giving it a few minutes of your time.

Snowflake is an advanced data platform provided as Software-as-a-Service (SaaS). It enables data storage, processing, and analytic solutions that are faster, easier to use, and far more flexible than traditional offerings. Snowflake isn’t a service built on top of Hadoop or Spark or any other “big data” technology, it is a completely new SQL query engine designed for the cloud and cloud-only. To the user, Snowflake provides all of the functionality of an enterprise analytic database, along with many additional special features and unique capabilities.

In this article, we won’t go into explaining what Snowflake is but will be more specific about one of the cool features of this data platform, which is Time Travel. Let me know in the comments if you want a brief overview of this product or maybe some explanation of its other features.

Time travel

Snowflake Time Travel enables to query data as it was saved at a particular point in time and roll back to the corresponding version. It means that the intentional or unintentional changes to the underlying data can be reverted. Time Travel is a very powerful feature that allows:

• 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.
• Restore tables, schemas, and databases that have been dropped.

To support Time Travel, the following SQL extensions have been implemented:

• OFFSET (time difference in seconds from the present time)
• STATEMENT (identifier for statement, e.g. query ID)
• UNDROP command for tables, schemas, and databases.

A key component of Snowflake Time Travel is the data retention period. When data in a table is modified, including deletion of data or dropping an object containing data, 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 and, therefore, Time Travel operations (SELECT, CREATE … CLONE, UNDROP) can be performed on the data.

By default, the data retention period is set to 1 day and Snowflake recommends keeping this setting as is to be able to prevent unintentional data modifications. This period also can be extended up to 90 days, but keep in mind that Time Travel incurs additional storage costs. Setting the data retention period to 0 will disable Time Travel. This feature can be enabled/disabled on account, database, schema or table level.

After the retention period expired the data is moved into Snowflake Fail-Safe and cannot be restored by the regular user. Only Snowflake Support can restore the data in Fail-Safe.

Querying historical data

When any DML operations are performed on a table, Snowflake retains previous versions of the table data for a defined period of time. This enables querying earlier versions of the data using the AT | BEFORE clause. Now let’s see the examples. For the sake of this article, we will create a separate DB in Snowflake, a table and fill it with some seed values. Here we go:

And we have our seed records. Now let’s add some duplicates.

Having duplicates in our table isn’t a good idea, so we can use Time Travel to see how the data was looking before dups appeared. As was mentioned before there are few different methods to do that, let’s check them all.

As you can see we are back to the first version of the data! Next queries will return the same result.

Also, it is possible to restore the data before the change happened by finding the Query ID that introduced mentioned change. We can find it on the “Query History” tab in “Activity”.

Cloning historical data

There is another feature in Snowflake that is worth investigating – Zero-Copy Cloning. Basically, it creates a copy of a database, schema or table. A snapshot of data present in the source object is taken when the clone is created and is made available to the cloned object. The cloned object is writable and is independent of the clone source. That is, changes made to either the source object or the clone object are not part of the other. Cloning in Snowflake is zero-copy cloning, meaning that at the time of clone creation the data is not being copied and the newly created cloned table references the existing data partitions of the mother table. But it is worth another article, so at the moment brief intro and later we will dig deeper.

Cloning with Time Travel works using the same parameters:

The results of SELECTs are the same:

Dropping and Undropping

When a table, schema, or database is dropped, it is not immediately overwritten or removed from the system. Instead, it is retained for the data retention period for the object, during which time the object can be restored. 

To drop a table, schema, or database, the following commands are used:

• DROP SCHEMA
• DROP DATABASE

To undrop a table, schema, or database:

• UNDROP TABLE
• UNDROP SCHEMA
• UNDROP DATABASE

This is actually where the fun comes. Let’s start with a simple example and then go into the woods.

Simple example:

The dropped tables can be seen by using the command:

In the results of the SHOW TABLES HISTORY in the column “ dropped_on ” the last time the table was dropped will be shown. If the value for the corresponding table in this column is NULL it means that the table is up and running. Dropping and undropping the same table multiple times will not create additional records in this view, only the timestamp in “ dropped_on ” will be updated.

But if a table is dropped and then a new table is created with the same name, the UNDROP command will fail, stating that the table exists.

As you can see in the screenshot above there are two tables restored_table_2, but one of them has a timestamp value in the column “dropped_on”. This is because the first time I ran the query to create this table I used the wrong schema, so I ran CREATE OR REPLACE TABLE … command, which actually dropped a wrong table and created a new one with the same name. So if now I try to UNDROP the old table with wrong records, the query will fail as stated earlier.

But it doesn’t mean that the data from the previous table is lost. As we saw earlier, we still can see 2 tables in SHOW TABLES HISTORY. In order to restore the original (or in this case wrong) table, the newly created table has to be renamed:

And now the UNDROP will work.

And we can see the wrong records I added to this table:

Now, I hope you won’t do such a thing, but here we saw what happens if you drop a table and then create a totally new one with the same name and still we can recover an old table. So what happens if you drop this new table and then create again a new one again with the same name? Will you be able to recover the data from the most ancient table? The answer is yes. It will take some effort, but it is possible. This is how our SHOW TABLES HISTORY looks like right now, we have 5 tables, and all are up and running:

Let’s drop restored_table and create it again using the same command and add a few new records to it:

And now we go further and drop again restored_table and create a new one.

To restore the first table that was dropped we will need to go through a set of renamings. Our original table had 4 rows. Let’s go. Restore the second table.

Restore the original table and rename it to v1

And here we are, with all the versions of our data restored. As you can see in Snowflake it becomes obsolete the creation of different versions of the tables as you can always go back in time, but be careful with the data retention period – if it’s expired you cannot get your data back that easily. 

In the end, Time Travel is a very powerful tool for:

• Restoring data-related objects (tables, schemas, and databases) that might have been accidentally or intentionally deleted.
• Duplicating or backing up data from key points in the past.
• Analyzing data usage/manipulation over specified periods of time.

Hope you find it useful 😉 see ya in the next one 😉

Photo by Maddy Baker on Unsplash

Leave a Reply Cancel reply

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

Sergi's Blog Cookies Policy

This Website uses cookies to improve your experience. Please visit the Cookies Policy page for more information about cookies and how we use them.

Snowflake Stock Is Heating Up After The Bell: Here's Why

Snowflake Inc (NYSE:SNOW) shares are heating up after the market close on Wednesday following insider buying from the company’s new CEO .

What Happened: In a new regulatory filing released Wednesday after the bell, newly appointed Snowflake CEO Sridhar Ramaswamy  disclosed the purchase of 31,542 shares of the company’s stock. Ramaswamy bought the shares on Monday at an average price of $158.52. (Check out Benzinga’s Insider Trades page for more information.)

Last month, Snowflake announced former CEO  Frank Slootman decided to  retire from his role as CEO , but would remain with the company as chairman of the board. Ramaswamy was named CEO, effective immediately.

Trending: Elon Musk Reacts As Tucker Carlson Says 'No Clue At All How Nancy Pelosi…Stock Picks Are, Like, Way Bette

Must Read: GOP Mitt Romney Pens Letter To DEA Bashing Cannabis Rescheduling, Dems' Letter To DOJ Bashes Broken Promises

Ramaswamy previously led all of Alphabet Inc’s (NASDAQ:GOOG)(NASDAQ:GOOGL) Google advertising products, including search, display and video advertising, analytics, shopping, payments and travel. During his 15 years at Google, AdWords and Google's advertising business grew from $1.5 billion to over $100 billion. 

See Also: Small Caps Rally, Magnificent 7 Ease, Gold Eyes $2,200, Bitcoin Falls Below $69,000: What’s Driving Markets Wednesday?

SNOW Stock Prediction 2024

Equity research analysts on and off Wall Street typically use earnings growth and fundamental research as a form of valuation and forecasting. But many in trading turn to technical analysis as a way to form predictive models for share price trajectory.

Some investors look to trends to help forecast where they believe a stock could trade at a certain point in the future. Looking at Snowflake, an investor could make an assessment about a stock's long-term prospects using a moving average and trend line. If they believe a stock will remain above the moving average, which many believe is a bullish signal, they can extrapolate that trend into the future using a trend line.

For Snowflake, the 200-day moving average sits at $175.35, according to Benzinga Pro , which was above the current price of $160.04. For more on charts and trend lines, see a description here.

Traders believe that when a stock is above its moving average, it is a generally bullish signal, and when it crosses below, it is a more negative signal. Investors could use trend lines to make an educated guess about where a stock could trade at a later date if conditions remain stable.

SNOW Price Action: Snowflake shares were up 4.04% after hours at $166.50 at the time of publication Wednesday, according to Benzinga Pro .

Photo: Shutterstock

© 2024 Benzinga.com. Benzinga does not provide investment advice. All rights reserved.

This article Snowflake Stock Is Heating Up After The Bell: Here's Why originally appeared on Benzinga.com .