Snowflake Snowpro Core Preparation Part 16 — Time Travel, Fail-Safe, Cloning, Replication,Encryption & Data Sharing

12 min readJun 5, 2024

In this blog, we are going to discuss about Sowflake’s features — Time Travel, Fail-Safe, Cloning, Replication,Encryption & Data Sharing.

Time Travel:

● Snowflake Time Travel enables accessing historical data (i.e., data that has been changed or deleted) at any point within a defined period.

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

● Using Time Travel, you can perform the following actions within a defined period of time:

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

Time Travel SQL:

● AT | BEFORE clause which can be specified in SELECT statements and CREATE … CLONE commands (immediately after the object name).

> TIMESTAMP
> OFFSET (time difference in seconds from the present time)
> STATEMENT (identifier for statement, e.g., query ID)

● UNDROP command for tables, schemas, and databases.

Data Retention Period:

● For Standard Edition — 1 day(Default) — Can be set to zero as well.

● For Enterprise Edition and above –

For Transient and Temporary objects — Default 1 day — Can be set to zero as well.
For Permanent objects — 0 to 90 days.

● The DATA_RETENTION_TIME_IN_DAYS object parameter can be used by users with the ACCOUNTADMIN role to set the default retention period for your account.

● Retention period is inherited(Database — Schema — Table).

● The MIN_DATA_RETENTION_TIME_IN_DAYS account parameter can be set by users with the ACCOUNTADMIN role to set a minimum retention period for the account.

Examples:

Changing Data retention in days parameter

Querying historical data

Time Travel with Clone

Fail Safe:

● Separate and distinct from Time Travel, Fail-safe ensures historical data is protected in the event of a system failure or other event (e.g., a security breach).

● Fail-safe provides a non-configurable 7-day period during which historical data may be recoverable by Snowflake.

● This period starts immediately after the Time Travel retention period ends.

● Fail-safe is not provided as a means for accessing historical data after the Time Travel retention period has ended.

● It is for use only by Snowflake to recover data that may have been lost or damaged due to extreme operational failures.

● Data recovery through Fail-safe may take from several hours to several days to complete.

Cloning:

● Snowflake’s Zero Copy Cloning is a feature which provides a quick and easy way to create a copy of any table, schema, or an entire database without incurring any additional costs as the derived copy shares the underlying storage with the original object.

● Zero Copy Cloning — Creates copies of database objects without copying the data.

● Cloned and original objects are independent from each other.

● Until changes, the cloned object shares the same storage as original.

● Any changes done on cloned snapshot creates additional storage components which results in additional costs.

● Clone inherits all granted privileges on the clones of all child objects contained in the source object.

● Internal named stages are not cloned.

● Temporary and transient tables can’t be cloned as permanent tables.

Examples:

Replication:

● Replication of objects from a source account to one or more target accounts in the same organization.

● Replicated objects in each target account are referred to as secondary objects and are replicas of the primary objects in the source account.

● Replication is supported across regions and across cloud platforms.

● A replication group is a defined collection of objects in a source account that are replicated as a unit to one or more target accounts.

● Replication groups provide read-only access for the replicated objects.

● A failover group is a replication group that can also fail over.

● A secondary failover group in a target account provides read-only access for the replicated objects.

● When a secondary failover group is promoted to primary failover group, read-write access is possible.

● Any target account specified in the list of allowed accounts in a failover group can be promoted to serve as the primary failover group.

● Non replicated database objects as of May 2024 –

> Tables — Temporary, External, Hybrid and Iceberg.

> Temporary stages — Stage created is temporary and will be dropped at the end of the session.

External -> Only stage drop. Files exist.
Internal ->Both stage and files are dropped.

Replication Limitations:

● Databases created from shares cannot be replicated.

● Refresh operations fail if the primary database includes a stream with an unsupported source object. The operation also fails if the source object for any stream has been dropped.

● Append-only streams are not supported on replicated source objects.

Replication Steps:

Step 1: Disable replication for a replication enabled database

Step 2: Add the database to a primary failover group on Source Account

Step 3: Create a secondary failover group on Target Account

Step 4: Manually refresh secondary failover group(Optional)

Step 5: Create a role with the FAILOVER privilege on the failover group (Optional)

Monitoring: Account replication history of failover group myfg in the last 7 days:

Data Encryption:

● End-to-end encryption (E2EE) is a method to secure data that prevents third parties from reading data while at-rest or in transit to and from Snowflake and to minimize the attack surface.

A user uploads one or more data files to a stage.

2. The user loads the data from the stage into a table.

3. The user can unload query results into an external or internal stage.

4. The user downloads data files from the stage and decrypts the data on the client side.

Key Management:

● Snowflake manages data encryption keys to protect customer data.

● Snowflake uses strong AES 256-bit encryption with a hierarchical key model rooted in a hardware security module.

● Keys are automatically rotated on a regular basis by the Snowflake service, and data can be automatically re-encrypted (“rekeyed”) on a regular basis.

● Hierarchical key model — Several layers of keys in which each higher layer of keys (parent keys) encrypts the layer below (child keys) -> Wrapping

Encryption Key Rotation:

● All Snowflake-managed keys are automatically rotated by Snowflake when they are more than 30 days old.

● Active keys are retired, and new keys are created.

● When active, a key is used to encrypt data and is available for usage by the customer.

● When retired, the key is used solely to decrypt data and is only available for accessing the data.

● When wrapping child keys in the key hierarchy, or when inserting data into a table, only the current, active key is used to encrypt data.

Tri-Secret Secure:

● Customers can use the key management service in the cloud platform that hosts their Snowflake account to maintain their own additional encryption key.

● The combination of a Snowflake-maintained key and a customer-managed key creates a composite master key to protect the Snowflake data -> Tri-Secret Secure

● The composite master key acts as an account master key and wraps all the keys in the hierarchy; however, the composite master key never encrypts raw data.

● If the customer-managed key in the composite master key hierarchy is revoked, your data can no longer be decrypted by Snowflake.

● Dual key encryption+built-in user authentication = 3 levels of protection.

All the details about Snowflake’s — Time Travel, Fail-Safe, Cloning, Replication & Encryption are available in this YouTube Video:

Data Sharing:

● Snowflake provides many ways to share data from your Snowflake account with users in other Snowflake accounts, including collaborating with other parties in a secure environment.

● Data Provider — Provides the data. Can manage who has access to your data and avoid challenges in keeping data synchronized across different people and groups.

● Data Consumer — Consumes the data. Reduce the data transformations to perform because the data stays in Snowflake, making it easy to join datasets shared with you with your own data.

● Options –

> Listings — Offer a listing privately to specific accounts, or publicly on the Snowflake Marketplace.
> Direct Share — Use a direct share to share data with one or more accounts in the same Snowflake region. You don’t need to copy or move data shared with a direct share.
> Data Exchange — If creating listings that you offer privately to specific accounts isn’t an option, you can use a data exchange to share data with a selected group of accounts that you invite.

Secure Data Sharing:

● Secure Data Sharing lets you share selected objects in a database in your account with other Snowflake accounts.

● Snowflake enables the sharing of databases through shares, which are created by data providers and imported by data consumers.

● All database objects shared between accounts are read-only (i.e., the objects cannot be modified or deleted, including adding or modifying table data).

● No actual data is copied or transferred between accounts.

● All sharing uses Snowflake’s services layer and metadata store only.

● No storage cost; Only compute resources (i.e., virtual warehouses) used to query the shared data.

● The provider creates a share of a database in their account and grants access to specific objects in the database.

● The provider can also share data from multiple databases, as long as these databases belong to the same account.

● On the consumer side, a read-only database is created from the share.

● Access to the shared database is configurable using the same, standard role-based access control that Snowflake provides for all objects in the system.

● Any full Snowflake account can both provide and consume shared data.

● Snowflake also supports third-party accounts, a special type of account that consumes shared data from a single provider account.

● Shares are named Snowflake objects that encapsulate all the information required to share a database.

● Data providers add Snowflake objects (databases, schemas, tables, secure views, etc.) to a share using either or both of the following options:

> Option 1: Grant privileges on objects to a share via a database role.
> Option 2: Grant privileges on objects directly to a share.

● New objects added to a share become immediately available to all consumers, providing real-time access to shared data.

● Updates to existing objects in a share become immediately available to all consumers.

● Access to a share (or any of the objects in a share) can be revoked at any time.

● Create as many shares as you want and add as many accounts to a share as you want.

● You can consume as many shares as you want from data providers, but you can only create one database per share.

Grant privileges on objects to a share via a database role:

Grant privileges on objects directly to a share :

Reader Account:

● Share data with a consumer who does not already have a Snowflake account or is not ready to become a licensed Snowflake customer.

● Reader accounts (formerly known as “read-only accounts”) provide a quick, easy, and cost-effective way to share data without requiring the consumer to become a Snowflake customer.

● Each reader account belongs to the provider account that created it.

● As a provider, use shares to share databases with reader accounts; however, a reader account can only consume data from the provider account that created it.

● Users in a reader account can query data that has been shared with the reader account but cannot perform any of the DML tasks that are allowed in a full account, such as data loading, insert, update, and similar data manipulation operations.

Snowflake Marketplace:

● The Snowflake Marketplace is where you can explore, access, and provide listings to consumers.

● Use the Snowflake Marketplace to discover and access third-party data and services, as well as market your own data products across the Snowflake Data Cloud.

● As a data provider, you can use listings for –

> Publish listings for free-to-use datasets to generate interest and new opportunities.
> Publish listings with samples of datasets.
> Share live datasets securely and in real-time without creating copies of the data.
> Eliminate the costs of building and maintaining APIs and data pipelines to deliver data to customers.
> Use paid listings to charge for data products.

● As a consumer, you can use listings for –

> Discover and test third-party data sources.
> Combine new datasets with your existing data in Snowflake to derive new business insights.
> Eliminate the costs of building and maintaining various APIs and data pipelines to load and update data.
> Use the business intelligence (BI) tools of your choice.

Data Exchange:

● Data Exchange provides a data hub for securely collaborating around data with a selected group of members.

● As a provider, publish data which can then be discovered by the consumers participating in your exchange.

● With a Data Exchange, easily provide data to a specific group of consistent business partners taking part in the Data Exchange, such as internal departments in your company or vendors, suppliers, and partners external to your company.

● Want to share data with a variety of consumers inside and outside your organization, you can also use listings offered to specific consumers or publicly on the Snowflake Marketplace.

● You can manage membership, access to data, and audit data usage, as well as apply security controls to the data shared in the Data Exchange.