Snowflake Key Features

Snowflake is a major cloud-computing data warehousing platform that enables companies to establish and manage a storage system with enhanced scalability and reliability. 

Snowflake enables data storage and analysis that is quicker and easier to use. Additionally, it offers excellent governance, security, and data protection, as well as extended SQL support, data import and export capabilities, and more.

Let’s explore the key features of Snowflake in this article.

Table of Contents

• Snowflake Time Travel
• Snowflake Fail-safe mechanism 
• Snowflake Cloning
  
  • Snowflake Time Travel Cloning  
  • Snowflake Zero-Copy Cloning  
• Frequently Asked Questions

Snowflake Time Travel

You may sometimes like to execute a single piece of code and revert back to the snapshot before the last test SQL execution. That's the scenario where database Time Travel is beneficial.

Time Travel is a feature that allows you to access data from any point in the past. For example, if you have an employee table and accidentally delete it, you can utilise the Time Travel feature to revert to a previous version and retrieve the data.

Snowflake supports database Time Travel. Snowflake Time Travel allows you to go back in time and view past data.

With Snowflake’s Time Travel feature, you can:

• Restore data-related objects, such as schemas, tables, and databases, that may have been accidentally removed.
• Duplicate and backup data from previous periods.
• Analyze data manipulation and consumption over a specified period.
• Back up, compare, and examine past data usage over a period.

Want to enhance your skills to become a master in Snowflake Certification, Enroll in our Snowflake Training Certification Course

Real-time Scenario

You're working on a bug patch and are connected to the PROD database. That’s where you execute an update statement on a table with a large volume of records.

You also conducted additional delete and update logic on the same table. You later realized that when using update and delete statements in your SQL query, you forgot to use the necessary ‘where’ clause.

You may need to retrieve the most recent database backup. Alternatively, you can truncate the table and load new data from the source.

If you're working in a PROD environment, you won't have sufficient time to back up your data and load new data. When restoring a backup copy, data may be lost between the last backup and the current data.

If you want to load new data, it will take hours to days, depending on the amount of data you have.

Then, you can go back in time to the Snowflake warehouse. You can retrieve the data as it was loaded with your initial update or delete statement.

Time Travel SQL Extensions

You can use the Time Travel SQL Extensions, such as the AT/BEFORE clause, in SELECT queries and CREATE... CLONE commands in Snowflake Time Travel to retrieve or clone historical data.

SQL extensions use the following arguments to locate specific historical data that you want to access:

• TIMESTAMP
• OFFSET (Time difference from the current time in seconds)
• STATEMENT (query ID for statement)

Data Retention Period

The data retention period is a crucial component of Snowflake's Time Travel feature. The characteristic of Time Travel heavily relies on the duration of data retention.

When a user makes changes to a table, Snowflake saves the current state of the data before making any alterations. This state of data from the past will persist for a set amount of time, known as the Time Travel data retention period.

• All Snowflake accounts have a standard retention duration of one day by default.
• The retention time for schemas, databases, and tables in Snowflake Enterprise Edition or higher ranges from 0 to 90 days, except for transient tables.

Specifying the Data Retention Period for Time Travel

Here are the steps to specify the data retention duration.

• You can use the DATA_RETENTION_TIME_IN_DAYS object parameter, available with the ACCOUNT ADMIN role, to set the data retention time for your account.
• When building a schema, database or individual table, you can use the DATA_RETENTION_TIME_IN_DAYS object option to alter the default.
• At any moment, users can change the data retention term for a schema, database, or table.
• You can both increase or decrease the data retention duration.
• You can use the UNDROP command to restore a dropped schema, table, or database that has not yet been placed in the Snowflake Fail-safe system.

Snowflake Fail-safe mechanism

The Fail-safe mechanism provides a non-configurable seven-day timeframe during which Snowflake can retrieve prior data. This time begins as soon as the Time Travel retention period expires. 

• The fail-safe mechanism is a data recovery service that you should use when all other options have failed.
• Once the Time Travel retention term expires, you will no longer be able to use the Fail-safe method for accessing prior data.
• This method helps recover data that has been lost or destroyed due to extreme operational failures.
• The Fail-safe method may take several hours to several days for data recovery.

The storage charges are assessed every 24 hours (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.

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. It reduces the amount of storage required for historical data.
• Storage utilization is expressed as a percentage of the altered table space. Tables are only kept in complete copies when they are dropped or shortened.

Snowflake Cloning

Snowflake cloning replicates data from a database, schema, or table without requiring additional storage. Many organizations replicate real-time data from production to development or staging environments to modify objects with exact results.

Snowflake Cloning is generally faster in replication, which takes only a few minutes, depending on the source objects. 

The changes that you make to the cloned object don't affect its source. 

• When you clone a database, all the schemas and tables in that database will also be cloned. 
• When you clone a schema, all the tables in that schema will also be copied. It
• The Snowflake cloning demonstrates how to copy tables, databases, and schemas using a script. 
• When a table is cloned, its data structure and other characteristics are copied. The original table's load history is not preserved in cloned tables.

Suppose a data file was successfully loaded into the original table. In that case, the file is included in the cloned table.

• The clone command helps create duplicates of a database or table without manually copying all associated metadata, primary keys, or schema.
• You can replicate the entire database with a single operation, including its metadata and structure. 
• It's helpful when you need to replicate a database or table during the development stage quickly.

Example of Cloning in Snowflake

You must run the following script entirely if you copy and paste it into the worksheet in the Snowflake online interface:

-- Cloning Tables

-- Create a sample table

CREATE OR REPLACE TABLE demo_db.public.employees
(emp_id number,

first_name varchar,

last_name varchar

);

-- Populate the table with some seed records.

Insert into demo_db.public.employees

values(100,'John','Smith'),

(200, 'Sam', 'White'),

(300, 'Bob','Jones' ),

(400,'Linda','Carter');

-- Show the content of the table employees in the demo_db database and the public schema

select * from demo_db.public.employees;

Snowflake Time Travel Cloning 

As you know, the Snowflake database can retrieve previously modified or deleted data from its history.

Suppose a table or part of its entries were deleted unexpectedly. In that case, you could get them back within the table's configured time-travel period. 

You can retrieve them using the AT or BEFORE clauses. It allows for reference timestamps, offsets from the current time, or statement IDs.

Below is the syntax to use Time Travel to clone a table:

CREATE [ OR REPLACE ] { DATABASE | SCHEMA | TABLE | STREAM } [ IF NOT EXISTS ] <object_name>
CLONE <source_object_name>
[ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ]

This feature allows you to clone a table as it existed at a specific time or a short while ago.

Here are a few examples of using Time Travel to duplicate tables in Snowflake:

CREATE TABLE EMPLOYEE_CLONE CLONE EMPLOYEE
AT(OFFSET => -60*5);
CREATE TABLE EMPLOYEE_CLONE CLONE EMPLOYEE
AT(TIMESTAMP => 'SUN, 06 MAR 2022 13:45:00 +0530' :: TIMESTAMP_TZ);

Things you need to consider while cloning with Time Travel in Snowflake

Here are the four things you must keep in mind:

1. If the source object didnt exist at the time or location indicated in the AT or BEFORE clause, then an error will be returned.
2. There will be no errors if an object’s children did not exist at the time/point indicated in the AT or BEFORE clause, and are not cloned. 
3. An error will be returned if any object that needs to be cloned does not have historical data. 
4. Time Travel data retention stores data and metadata, allowing a standard Time Travel query to retrieve the names of columns and other metadata from the past. 

A clone gets its data from its history, but it gets its metadata from the source object in its current state.    

Example 

Suppose a column in a table has been renamed. The column names will be retrieved from the current state, while the data in the table will be fetched based on the time specified by the AT or BEFORE clause.

The Top 40+ Best BigQuery Interview Questions & Answers 2025 article can help you understand key concepts and prepare for interviews.

Snowflake Zero Copy Cloning 

The cloning process can produce a database copy called ‘Zero-Copy Cloning.’ 

When an object is cloned, it receives a copy of all the information previously stored in the original object. As a result of the cloning process, the copied object can be updated independently of the original.

• If you make changes to either the original object or the clone, you won't see those changes reflected in the other.
• In Zero-Copy Cloning, you can create backup copies of your data without increasing your storage needs or requiring you to wait for extended periods.
• You can make multiple copies of objects without creating a new physical copy or requiring additional storage space.
• Since costs are reduced without restriction, this method offers more flexibility.

Cloned objects are usually separated from the original table. In this way, you can update them without affecting the original table. Snowflake Zero-Copy Cloning enables the copying of all metadata, enhancing storage management.

Example

You can use the command below to clone a database or table.

CREATE TABLE <table_name>
CLONE <source_table_name>

Privileges required for Snowflake Zero Copy Clone 

You must have the basic permissions to clone an object. Your current role must have the appropriate permissions on the source object to create a clone of it:

How does Snowflake's Zero Copy Cloning work?

A Snowflake table's data is automatically partitioned into micro-partitions. All micro-partitions have uncompressed data ranging from 50 MB to 500 MB.

• Snowflake's actual size is smaller because data is always saved and compressed. 
• All Snowflake tables undergo this automatic micro-partitioning procedure.
• A clone can be duplicated an infinite number of times in Snowflake. 
• Each copy will have access to both shared and dedicated storage. 
• Snowflake assigns a unique identifier (ID) to each table in the database.
• A similar CLONE GROUP ID that reflects cloning status is assigned to each table. The table is a clone if the IDs in both columns are unique. 
• The account admin role is required to access the TABLE STORAGE METRICS view. 

When creating duplicate micro-partitions, Snowflake generates new metadata that references the original object's micro-partitions. The user is not required to perform any of these actions because of the cloud services layer of Snowflake.

How to clone objects in Snowflake?

Cloning objects in Snowflake can be done with the help of the simple SQL statement displayed below.

CREATE <object_type> <object_name>
CLONE <source_object_name>

The following is an example of the full syntax required to clone a Snowflake object.

CREATE [ OR REPLACE ] { DATABASE | SCHEMA | TABLE | STREAM | STAGE | FILE FORMAT | SEQUENCE | TASK } [ IF NOT EXISTS ] <object_name>
CLONE <source_object_name>

Objects that can be cloned in Zero Copy Clone Snowflake

Before cloning an object, you must determine which objects can be cloned and their limitations. Below are some of the cloneable objects.

1. Data Storage Objects 
   • Schemas 
   • Streams
   • Tables
   • Databases
2. Data Configuration Objects
   • File Formats 
   • Stages 
   • Sequences

Reasons for Cloning an Object in Snowflake Zero-Copy Cloning

Objects in Snowflake are cloned for the following reasons.

1. To support development, testing, and backup environments.
2. To perform alterations/development without having to set up a new environment or risk damaging the source object.
3. To complete a one-time task that uses its source item.

Advantages of Snowflake Zero-Copy Cloning 

The Zero Copy Clone Snowflake has many benefits. Here are some of them.

• Quick cloning: The Zero-Copy cloning Snowflake method enables users to instantly create multiple copies of the data without incurring any additional costs. It helps users save time.
• Easy to use : Snowflake Zero-copy cloning enables the duplication of data structures, such as databases, tables, and schemas, without copying the source. Therefore, cloning is a straightforward procedure that requires no specialized knowledge or equipment.
• Saves development and testing time: You don’t need to wait for long periods to set up a test environment. Leveraging Snowflake's zero-copy cloning doesn’t incur additional charges for the test and development environment.
• Saves costs on storage: You can make copies of an item without duplicating its storage. Cloning a table doesn't consume any space. That’s because it uses the same micro-partitioning scheme as the original table.    

You can change rows in a clone independently of the original table. When a clone is updated, it creates a set of new, CDP-protected micro-partitions.

Summary

• Snowflake’s Time Travel helps to maintain the historical versions of your data.
• You can query the data during its retention period, even if it has been modified and deleted.
• The Snowflake Enterprise Edition offers a retention period of up to 90 days for tables and schemas.
• Snowflake offers a Fail-safe mechanism. Once the retention period for an object expires, it automatically enters a Fail-safe mode, where it will be retained for an additional seven days.
• It offers a Zero-Copy Cloning feature, in which Snowflake doesn't copy the underlying data; instead, it creates metadata pointers to data blocks.
• You can integrate Snowflake cloning with Time Travel.
• The cloned objects are usually independent and mutable.

Frequently Asked Questions

1. Is learning Snowflake tough?

Ans: Snowflake is easy to learn. If you have the foundational knowledge of data management and cloud computing, then you can learn Snowflake quickly.

2. How long will it take to learn Snowflake?

Ans: You can learn Snowflake within 25 hours. MindMajix offers advanced 25-hour Snowflake training in two modes: live online and on-demand training. You can choose the training mode based on your comfort and requirements.

3. How to prepare for Snowflake certification?

Ans: Snowflake offers multiple certifications for learners to evaluate their skills and stay competitive in the job market. To pass the certifications easily, you need to have:

• A thorough knowledge of Snowflake data warehousing concepts
• Deep understanding of Snowflake architecture and data management

On top of all that, attending professional Snowflake training with MindMajix will help you crack the certification exams effortlessly.

4. What would be the career prospects for Snowflake professionals?

Ans: Infosys, KPMG, HCLTech, Cognizant, UST, and many other top companies worldwide recruit Snowflake professionals. According to Glassdoor, Snowflake engineers can earn a salary from 5 to 11 LPA in India. They can earn a salary from $74,000 to $100,000 in the USA.

5. What are the differences between Databricks and Snowflake?

Ans: 

• Databricks is powered by Apache Spark, which helps accelerate query performance compared to Snowflake.
• Snowflake has a multi-cluster warehouse, which offers better scalability than Databricks.
• Both platforms provide enhanced security and integration capabilities.

Conclusion

In summary, Snowflake's Time Travel feature is an excellent way to save data that was either deleted or lost accidentally in the past. Snowflake simplifies the process of copying entire databases for testing, eliminating the need to duplicate storage. Snowflake cloning helps companies save valuable time and costs.

If you are interested in exploring Snowflake features, you can enrol in a Snowflake course with MindMajix. It will help you learn Snowflake from the basics to core concepts in one place, advancing your career.