Protecting Your Snowflake Data with Backups

Beyond the well-known Time Travel feature, Snowflake Backups provide an additional layer of protection against data loss due to unforeseen circumstances.

Snowflake Backups enable you to create snapshots of Snowflake objects such as tables, schemas, and databases. Backups can be created manually or automatically and are configured with a backup policy that defines their schedule and retention period.

Backups are available for all Snowflake editions. Some advanced features, like retention lock and backups with legal holds, are available for Business Critical Edition or higher.

Let’s explore how backups work using a simple example. We’ll assume we already have a data warehouse with a populated gold layer containing the business-ready data consumed by reports and applications. We want to create a backup of the entire gold schema every day and retain each backup for 30 days.

Setting up the example

We don’t want to perform backup operations using the ACCOUNTADMIN or another highly privileged role. Instead, we will create a custom BACKUP_ADMIN role and grant it only the privileges it needs:

USE ROLE USERADMIN;
-- Create the custom role
CREATE ROLE BACKUP_ADMIN;
-- Preserve the role hierarchy
GRANT ROLE BACKUP_ADMIN TO ROLE SYSADMIN;

USE ROLE SYSADMIN;
-- Create a custom warehouse
CREATE WAREHOUSE BACKUP_ADMIN_WH WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60;
GRANT USAGE ON WAREHOUSE BACKUP_ADMIN_WH TO ROLE BACKUP_ADMIN;

For this example, we will create a GOLD schema and populate it with a few sample tables from the Snowflake sample data database:

CREATE DATABASE DWH_DB;
-- Create the gold schema and populate with some sample data
CREATE SCHEMA GOLD;
CREATE TABLE GOLD.CUSTOMER AS
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;
CREATE TABLE GOLD.ORDERS AS
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS;
CREATE TABLE GOLD.NATION AS
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION;
-- Create the admin schema to store the backup-related objects
CREATE SCHEMA ADMIN;

Next, we will grant the BACKUP_ADMIN role access to the objects in the GOLD schema:

USE ROLE SYSADMIN;
GRANT USAGE ON DATABASE DWH_DB TO ROLE BACKUP_ADMIN;
GRANT USAGE ON SCHEMA DWH_DB.GOLD TO ROLE BACKUP_ADMIN;

Creating a backup policy

Before we can create a backup, we need a backup policy which defines:

• how frequently a backup is created
• how long each backup is retained

We will store the policy in the ADMIN schema.

First, the custom role needs privileges to create backup policies in this schema:

USE ROLE SECURITYADMIN;
GRANT USAGE ON SCHEMA DWH_DB.ADMIN TO ROLE BACKUP_ADMIN;
GRANT CREATE BACKUP POLICY ON SCHEMA DWH_DB.ADMIN TO ROLE BACKUP_ADMIN;

Now we can switch to the custom role and create a backup policy named DAILY_GOLD_BACKUP_POLICY that creates a backup every 24 hours and retains it for 30 days:

USE ROLE BACKUP_ADMIN;
USE WAREHOUSE BACKUP_ADMIN_WH;

CREATE BACKUP POLICY DWH_DB.ADMIN.DAILY_GOLD_BACKUP_POLICY
SCHEDULE = '24 HOUR'
EXPIRE_AFTER_DAYS = 30;

After creating the backup policy, nothing happens yet.

Creating the backup set

We also need a backup set that associates the objects we want to protect with the backup policy. The backup set will be stored in the ADMIN schema, so the custom role needs the privilege to do so:

USE ROLE SECURITYADMIN;
GRANT CREATE BACKUP SET ON SCHEMA DWH_DB.ADMIN TO ROLE BACKUP_ADMIN;

But this is not enough to perform backups. There is one additional privilege we need because the privilege to create a backup policy doesn’t automatically allow the role to apply that policy to a backup set. To do that, the custom role also needs the privilege to apply the policy, which we issue using the ACCOUNTADMIN role:

USE ROLE ACCOUNTADMIN;
GRANT APPLY ON BACKUP POLICY DWH_DB.ADMIN.DAILY_GOLD_BACKUP_POLICY
TO ROLE BACKUP_ADMIN;

While we should avoid using the ACCOUNTADMIN role for regular tasks where possible, this is one of the few places where it is required.

Going back to the custom role, we can now create the backup set for schema GOLD using the backup policy we created earlier:

USE ROLE BACKUP_ADMIN;
CREATE BACKUP SET DWH_DB.ADMIN.GOLD_BACKUP_SET
FOR SCHEMA DWH_DB.GOLD
WITH BACKUP POLICY DWH_DB.ADMIN.DAILY_GOLD_BACKUP_POLICY;

We now have a backup set called GOLD_BACKUP_SET which protects the GOLD schema using the policy called DAILY_GOLD_BACKUP_POLICY.

Since our backup policy is configured to run every 24 hours, we can come back tomorrow to check if it was executed. But we don’t want to wait that long and we should have a way to test our backup configuration.

We can manually add a backup to the backup set:

USE ROLE BACKUP_ADMIN;
ALTER BACKUP SET DWH_DB.ADMIN.GOLD_BACKUP_SET ADD BACKUP;

We can then see the available backups in the backup set with the SHOW BACKUPS command:

SHOW BACKUPS IN BACKUP SET DWH_DB.ADMIN.GOLD_BACKUP_SET;

Each backup has its own BACKUP_ID, creation time, expiration time, and other properties. The backup represents the entire GOLD schema at that point in time.

What happens when Snowflake creates the backup?

Creating a backup doesn’t mean that Snowflake creates a physical copy of the data. It uses the zero-copy mechanism which we are familiar with because it is also used when cloning.

As with a clone of a data object, a backup also references the immutable storage objects representing the data at the time the backup was taken. As long as an unexpired backup still references them, Snowflake does not remove them, just like with a cloned object.

This makes creating daily data recovery points significantly more storage-efficient than physically copying the entire schema every night.

Restoring data from a backup

Now let’s imagine something unexpected happened, such as an accidental update, delete, or data corruption, and we want to restore the data from a backup.

Supposing a table was unintentionally truncated by an ETL process:

TRUNCATE TABLE DWH_DB.GOLD.ORDERS;

Fortunately, we have a backup from which we can restore it. Let’s find the available backups:

SHOW BACKUPS IN BACKUP SET DWH_DB.ADMIN.GOLD_BACKUP_SET;

From the output of this command we take the BACKUP_ID of the backup we want to use (currently we only have one backup that we created manually earlier, but in future, we would typically have many backups, one for each day).

We can then restore the backup into a new schema (first giving the role that performs the restoring the privilege to create a new schema):

USE ROLE SYSADMIN;
GRANT CREATE SCHEMA ON DATABASE DWH_DB TO ROLE BACKUP_ADMIN;

USE ROLE BACKUP_ADMIN;
CREATE SCHEMA DWH_DB.GOLD_RESTORED
FROM BACKUP SET DWH_DB.ADMIN.GOLD_BACKUP_SET
IDENTIFIER 'b25e6c31-a760-4317-b75a-2835a694763a';

We can then check if the schema was created and if it contains all tables, including the ORDERS table that was accidentally truncated which should have data since it was restored from the backup.

We can then do with the restored data as we wish, for example insert the data from the backed-up ORDERS table into the GOLD schema.

Comparing backups with time travel

At this point, the obvious question is: couldn’t we have recovered the data in the table using Time Travel? And the answer is of course, yes, we could. For an accidental TRUNCATE that was discovered quickly, time travel would likely be the first recovery measure.

But backups aren’t intended simply to replace time travel. They give us explicit recovery points for a set of tables with their own schedule and retention lifecycle. The functionalities complement each other.

Protecting backups with retention lock

Our backups currently expire after 30 days, but a sufficiently privileged administrator can still remove them before then. For stronger protection, you need Snowflake Business Critical Edition, which supports retention lock.

For example, when creating a backup policy, we could specify WITH RETENTION LOCK:

CREATE BACKUP POLICY DWH_DB.ADMIN.DAILY_GOLD_LOCKED_POLICY
    WITH RETENTION LOCK
    SCHEDULE = '24 HOUR'
    EXPIRE_AFTER_DAYS = 30;

A backup set protected by a retention-locked policy cannot have its backups prematurely deleted, not even by the ACCOUNTADMIN role.

Summary

Snowflake Backups provide another layer of data protection alongside features such as Time Travel, Fail-safe, and replication. They allow us to create explicit recovery points for a table, schema, or database and manage their lifecycle through backup policies.

As we saw in this example, implementing a backup strategy requires only a few components: a backup policy defining the schedule and retention period, a backup set identifying what should be protected, and the appropriate privileges for the role responsible for managing the backups. Once configured, Snowflake takes care of creating and expiring the recovery points according to the policy.

There are, however, some important considerations that we haven’t covered in this simple example:

• Not every object contained in a schema or database is included in its backup. For example, temporary tables, external tables, Iceberg tables, hybrid tables, materialized views, stages, pipes, streams, and some other object types aren’t backed up. Account-level objects such as users and roles also aren’t included. If your objects reference resources outside the database or schema being backed up, those dependencies also need to be considered when planning a restore.
• Backups remain in the same cloud region as the source. Therefore, they shouldn’t be considered a replacement for cross-region disaster recovery. For scenarios where protection against a regional or cloud-provider outage is required, backups can be combined with Snowflake replication.

For more information, refer to the Snowflake Backups documentation.

I’m Maja Ferle, Snowflake Data Superhero and a senior consultant at In516ht. You can get in touch with me on LinkedIn or read my latest book, Snowflake Data Engineering.

Protecting Your Snowflake Data with Backups was originally published in Snowflake Builders Blog: Data Engineers, App Developers, AI, & Data Science on Medium, where people are continuing the conversation by highlighting and responding to this story.