Disaster Recovery, Backup and Data Replication · Topic 7 of 9

Disaster Recovery Plans, Failover Procedures and Backups

Design a Snowflake disaster recovery plan, pick the failover or failback procedure, and apply backup best practices with backup sets, policies and locks.

  • 6 min
In a nutshell
  • RPO is set by the refresh schedule; RTO by promotion, redirect and readiness.
  • Failover groups, Client Redirect, retention lock and legal hold need Business Critical or higher.
  • Database replication works on all editions but moves data and schema objects, not account objects.

What it is

A disaster recovery (DR) plan states what happens when a region, a cloud or an account is lost. A business continuity plan is wider: it covers the people, the applications and the data that must keep working. In Snowflake the building blocks are replication and failover groups for availability, Client Redirect for connections, and Time Travel, Fail-safe and backups for recovering data.

The key design point is that these tools solve different problems. Replication protects against losing a place. Backups and Time Travel protect against losing data inside a place, such as a bad delete or a corrupt load that replication would copy faithfully.

Define RPO and RTObusiness targetschooseReplicationfailover groups, scheduleaddClient Redirectconnection URLaddBackupspoint in time copiesrehearseTested failoverrunbook

How it works

Designing the plan

Start from two numbers. The recovery point objective (RPO) is how much data you can afford to lose. The recovery time objective (RTO) is how long you can be down.

RequirementDesign choice
Lower RPOShorter REPLICATION_SCHEDULE on the failover group (costs more transfer and serverless compute)
Lower RTOSecondary account ready, Client Redirect in place, runbook rehearsed, security integrations created in advance
Users, roles, warehouses must exist in DRFailover group with account objects (Business Critical or higher)
Only data must exist in DRDatabase replication (any edition)
Different cloud or regionSecondary account in that region; cross region transfer is billed
Protection from user error or corruptionTime Travel, Fail-safe and backups, not replication
Regulated retention, ransomware resilienceBackup sets with a backup policy and retention lock (Business Critical or higher for the lock)

Things a plan must name besides the data: replicated warehouses arrive suspended, resource monitors and network policies need to be in the group, pipelines that depend on external triggers have to be pointed at the new primary, and identity provider and OAuth settings must exist in the target account.

Choosing the failover or failback procedure

The procedure depends on what failed and what exists.

SituationProcedure
Region or account lost, failover group and connection already setPromote the secondary group with ALTER FAILOVER GROUP ... PRIMARY, then ALTER CONNECTION ... PRIMARY
Only a planned move, such as a DR testRefresh first so nothing is lost, then promote; fail back the same way afterward
Original region is healthy againFailback: refresh the original from the new primary, then promote it
Only a database was replicatedThe secondary database is read only and carries no users or roles. Use the failover group design for real continuity
Standard or Enterprise editionFailover groups are not available; data can be replicated, but promotion of account objects is not possible
Several secondary accountsPromote the one in the best region; refresh the other targets from it
Outage declaredcheck last refreshSuspend scheduleif a refresh is runningpromote groupPromote connectionClient RedirectverifyResume worktasks, pipelines, users
Exam tip

A scenario that says "no application change" points to Client Redirect. A scenario that says "must include users and warehouses" points to a failover group, not to database replication.

Backups in Snowflake

A backup is a point in time, zero copy snapshot of a table, schema or database. It is stored in a backup set, and a backup policy can set the schedule and the expiry.

CREATE BACKUP POLICY daily_90
  SCHEDULE = '1440 MINUTE'
  EXPIRE_AFTER_DAYS = 90;

CREATE BACKUP SET orders_bs FOR TABLE sales.public.orders
  WITH BACKUP POLICY daily_90;

ALTER BACKUP SET orders_bs ADD BACKUP;

CREATE TABLE sales.public.orders_restored
  FROM BACKUP SET orders_bs IDENTIFIER '<backup_id>';

Facts to hold:

  • The minimum schedule in a policy is 60 minutes. A policy can set a schedule, an expiry or both.
  • Restore creates a new object. It does not overwrite the original.
  • SHOW BACKUPS IN BACKUP SET lists the backup IDs.
  • Database and schema backups include permanent tables, views, sequences, procedures, functions, tasks, grants and database roles. Transient, temporary, external and hybrid tables, streams, pipes, stages and materialized views are excluded.
  • Restored tasks and dynamic tables start suspended.
ControlWhat it doesEdition
Backup set and policyScheduled or manual backups with expiryAll editions
Retention lockNo role, not even ACCOUNTADMIN, can delete the backup before it expires; irreversible; expiry can only be extendedBusiness Critical or higher
Legal holdBlocks changes or deletion of one backup even after it expiresBusiness Critical or higher

Retention lock needs the APPLY BACKUP RETENTION LOCK privilege and legal hold needs APPLY LEGAL HOLD. After placing a legal hold, refresh replicated backup sets so the hold survives a failover.

Watch out

A retention lock cannot be undone, and Snowflake Support cannot remove it. Apply it only after the retention period is agreed.

Backup best practices

  • Use more than one layer: Time Travel for the last days, Fail-safe as the last resort, backups for long retention and for compliance.
  • Match the backup schedule to the RPO of that data, and set the expiry to the retention requirement.
  • Put backup sets for critical databases under a role that few people hold, and apply a retention lock where cyber resilience is required.
  • Replicate backup sets with the failover group so backups survive the loss of a region.
  • Test restores on a schedule. A backup that was never restored is not proven.

Common mistakes

Treating replication as a backup. A faulty load replicates to the secondary. Only Time Travel, Fail-safe and backups give earlier states.

Setting the schedule from habit. The schedule must come from the RPO, because every refresh costs money.

Planning only data. Users, roles, security integrations and warehouses are part of recovery.

Assuming a restore overwrites. It creates a new table, schema or database.

What to remember for the exam

  • RPO is set by the refresh schedule; RTO by promotion, redirect and readiness.
  • Failover groups, Client Redirect, retention lock and legal hold need Business Critical or higher.
  • Database replication works on all editions but moves data and schema objects, not account objects.
  • Backups are zero copy snapshots in backup sets; restore always creates a new object; policy schedule minimum is 60 minutes.
  • Failback means refresh from the new primary, then promote the original.

Check yourself

  • A bad batch job deleted rows an hour ago and the secondary already refreshed. What helps?

    Time Travel or a backup in the primary, because replication copied the deleted state.

  • Which backup control stops even ACCOUNTADMIN from deleting a backup early?

    A retention lock, available on Business Critical or higher.

  • How does a restore from a backup set behave?

    It creates a new table, schema or database from the chosen backup and leaves the original untouched.

Topic 7 of 9 in Disaster Recovery, Backup and Data Replication

Test yourself on Disaster Recovery, Backup and Data Replication

The full Snowflake ADA-C02 question bank has 814 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.