What you will be able to do
- Predict how streams, tasks, pipes, stages, policies and Iceberg tables behave after a failover
- Lay out a disaster recovery plan built on failover groups and connections
- Run a failover or failback safely while a refresh may be in progress
- Redirect client connections to a newly promoted account
- Say what the sources do and don't establish about backups, schemas, clones and usage history in replication
1.How replicated objects behave after a failover
A secondary account is only useful in a disaster if its objects work once it is promoted. Several object types depend on other objects, and Snowflake replication considers that dependency broken (a dangling reference) when the other object sits in a different group or isn't replicated at all.
Streams. A stream and its base object have to replicate together, either in the same database or with both databases in the same group. Otherwise the refresh fails with a dangling-reference error. Staleness also carries over: if the primary stream goes stale, the secondary stream is stale too. The fix is to recreate the stream in the primary with CREATE OR REPLACE STREAM, after which the next refresh makes the replica readable again.
Tasks. A task can check a stream in its WHEN clause. If the task and the stream live in different databases, put both databases in the same failover group. Otherwise the task's database can fail over without the stream's database, and the resumed task records an error because it can't find the stream.
Security policies and tags. Masking, row access and tag-based masking policies and tags all replicate. If a policy is in a different group from the objects it protects, the policy's group has to replicate first.
Stages and pipes. These replicate only through replication and failover groups, not through database replication. After promotion, a pipe goes to FAILING_OVER and then to RUNNING. It then loads the data that has arrived since the former primary's last refresh. A stored procedure or UDF that imports code from a stage works only if that stage and its files are replicated too.
Iceberg tables. Only Snowflake-managed Iceberg tables replicate, and their external volume has to replicate as well. Unsupported types, such as external tables, are skipped and won't exist after a failover.
Checkpoint 1 of 8· Check yourself
Task T in database OPS checks stream S in database RAW in its WHEN clause. Only OPS is in failover group FG1. What happens after FG1 fails over and T is resumed?
If the task's database fails over without the stream's database, the task can't find the stream. Snowflake recommends putting both databases in the same failover group.
“If streams are stored in a different database from the tasks that reference them, include both databases in the same failover group.”Source: docs.snowflake.com
2.Designing the disaster recovery plan
A business continuity plan uses the same pieces in a fixed order. First enable replication on a set of accounts in the same organization, across regions or cloud providers. Next, create primary failover groups that set what replicates and where it goes. You can split objects across several failover groups, for example to replicate some databases more often than others. Then create a secondary (replica) of each primary in one or more secondary accounts. Finally, run an initial refresh and set a schedule so each secondary keeps receiving changes.
The target account isn't a full copy of the source's security setup. Tri-Secret Secure and private connectivity such as AWS PrivateLink are off by default in target accounts. If compliance needs them, you have to configure them in each target before you depend on it.
The plan also needs to decide what gets promoted together. Snowflake recommends a bulk failover: promote all the relevant failover groups and connections at the same time.
Checkpoint 2 of 8· Put it in order
Put these disaster recovery setup steps in order
- 1.Create a primary failover group defining object types and target accounts
- 2.Enable replication for the source and target accounts in the organization
- 3.Run an initial refresh and set a schedule to keep secondaries current
- 4.Create a secondary failover group (replica) in each target account
Replication must be enabled before groups can be created. A secondary needs an existing primary, and refreshes need an existing secondary.
“Create at least one secondary failover group (replica) of each primary failover group in one or more secondary accounts.”Source: docs.snowflake.com
3.Running failover and failback
To fail over with SQL, sign in to the target account with a role that has the FAILOVER privilege, and run ALTER FAILOVER GROUP myfg PRIMARY for each group you want to promote. The objects in that group become writable there and read-only in the former primary. Failback uses the same mechanism in the other direction. The original source is still in the group's allowed accounts, so it can be promoted again once it recovers. The account it takes over from becomes a secondary again.
The main risk is an in-progress refresh. To protect data integrity, Snowflake blocks failover while a refresh is running, and a partial outage may leave the replication service still refreshing. You have two options:
- SUSPEND stops future refreshes and waits for the current one to finish. - SUSPEND IMMEDIATE also cancels a scheduled refresh that is already running.
Cancelling during SECONDARY_DOWNLOADING_METADATA or SECONDARY_DOWNLOADING_DATA can leave the target inconsistent. Check the phase before you cancel.
SELECT phase_name, start_time, end_time
FROM TABLE(
INFORMATION_SCHEMA.REPLICATION_GROUP_REFRESH_PROGRESS('myfg')
);Checkpoint 3 of 8· Put it in order
Put this urgent SQL failover procedure in order
- 1.ALTER FAILOVER GROUP myfg RESUME in each target account with a secondary
- 2.Query REPLICATION_GROUP_REFRESH_HISTORY to confirm no refresh is in progress
- 3.ALTER FAILOVER GROUP myfg PRIMARY
- 4.ALTER FAILOVER GROUP myfg SUSPEND IMMEDIATE
Promotion fails while a refresh runs, so you suspend and verify first. Promoting suspends scheduled refreshes, so you resume them in each target afterwards.
“Now you can promote the secondary failover group myfg to primary failover group”Source: docs.snowflake.com
Checkpoint 4 of 8· Fill the gap
Failover suspended the scheduled refreshes. Which keyword restarts them in a target account?
ALTER FAILOVER GROUP myfg ? ;Failover suspends scheduled refreshes on every secondary. Run RESUME in each target account to restart them.
Source: docs.snowflake.comSchemas. The sources here establish two schema-level facts. With failover groups you can choose which schemas in a database replicate; without one, every schema replicates. A CREATE OR REPLACE on the source isn't atomic on the target during a refresh, so the object can briefly disappear there. The sources don't say what happens to a schema created on the old primary after its last refresh once you fail back. Treat that point as outside these sources.
Checkpoint 5 of 8· Exam question
A company on Business Critical edition wants to replicate databases, roles, users and warehouses to a second account, and be able to promote that account if the primary region fails. Which object should the administrator create?
Correct answer: D — A failover group that lists DATABASES, ROLES, USERS and WAREHOUSES in OBJECT_TYPES and allows the second account in ALLOWED_ACCOUNTS
- A. Incorrect: a replication group keeps the target read-only and can only be refreshed. It cannot be promoted, so there is no such PRIMARY command for replication groups.
- B. Incorrect: database replication carries only the database contents. Roles, users and warehouses are account-level objects that need a group, so scripts would be fragile and incomplete.
- C. Incorrect: a connection handles client redirect through a URL. It does not replicate databases or account objects.
- D. Correct: a failover group replicates account objects together with databases and, unlike a replication group, its target can be promoted to primary. It needs Business Critical or higher.
4.Redirecting client connections
Promoting data doesn't move your users. Client Redirect does that with a connection object, and only ACCOUNTADMIN can run its SQL. You create a primary connection whose name is unique across all connections and account names in the organization. That name becomes part of the connection URL that clients use. Then you run ALTER CONNECTION … ENABLE FAILOVER TO ACCOUNTS to list the accounts that can hold a secondary connection.
CREATE CONNECTION myconnection;During a failover you promote the connection along with the failover groups. In Snowsight's Initiate failover dialog, you pick the connections to promote after the groups. Clients using the connection URL then reach the newly promoted account without changing their configuration.
Checkpoint 6 of 8· Check yourself
After failover groups are promoted to account B, BI tools still reach account A. What was missed?
Clients follow the connection URL. Only a promoted connection points them at the new primary account.
“After the failover, those connections connect to the account”Source: docs.snowflake.com
Sources5
5.Backups in a replicated account, and what the sources don't cover
Replication protects against losing a region, not against a bad write. A faulty ETL job's changes replicate to the secondary at the next refresh. For backups, the sources only cover how backups relate to replication: backup policies and backup sets are both database objects that replicate. Backups are available on every edition. Backups with retention lock and backups with legal holds need Business Critical or higher. So a regional recovery copy comes from failover groups, and point-in-time recovery comes from Snowflake's separate data-protection features. Those features belong to a different lesson.
The exam guide also asks about historical usage data and cloned objects under replication. None of the sources available here describe how either one behaves, so this lesson makes no claims about them.
Checkpoint 7 of 8· Check yourself
An Enterprise Edition account wants backups with retention lock replicated to a second region. Which statement is supported?
Plain backups work on every edition. Retention lock and legal holds need Business Critical or higher.
“Backups are available for all Snowflake editions. Backups with retention lock and backups with legal holds are available for Business Critical Edition (or higher).”Source: docs.snowflake.com
Checkpoint 8 of 8· Exam question
A team replicates databases with a replication group and wants automatic refreshes. Select TWO correct ways to schedule refreshes in this setup.(Select 2)
Correct answers: A, D — For a plain replicated database without a group, create a task in the target account that runs ALTER DATABASE ... REFRESH; Run ALTER REPLICATION GROUP ... SET REPLICATION_SCHEDULE in the target account with an interval or a USING CRON expression
- A. Correct: a database replicated on its own has no built-in schedule, so a task in the target that runs the refresh is the usual approach.
- B. Incorrect: refreshes are pulled by the secondary group, so the schedule is not set on the source's primary group.
- C. Incorrect: no AUTO_REFRESH property exists on secondary databases.
- D. Correct: the schedule belongs to the secondary group and is set from the target account, as an interval or a CRON expression.
- E. Incorrect: databases have no REPLICATION_SCHEDULE property, and the primary does not drive refreshes.
Sources2
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.A replicated auto-ingest pipe keeps the secondary current by loading files before failover.Why is that wrong?
Secondary pipes stay READ_ONLY. They move to FAILING_OVER only when their database is promoted, and then to RUNNING.
2.In an outage you can promote a secondary failover group at any time, even mid-refresh.Why is that wrong?
Snowflake blocks promotion while a refresh is in progress. Suspend the group, or suspend and cancel the refresh, before running PRIMARY.
Covered in Running failover and failback
3.Scheduled refreshes keep running in every target account after a failover.Why is that wrong?
Failover suspends scheduled refreshes on all secondary failover groups. You have to resume them in each target.
Covered in Running failover and failback
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“the replicated stream in a secondary database is also stale”
↩︎ How replicated objects behave after a failover“the replication (or failover) group with the security policy must be replicated before any replication group that contains objects that reference the policy”
↩︎ How replicated objects behave after a failover“If streams are stored in a different database from the tasks that reference them, include both databases in the same failover group.”
↩︎ Checkpoint - 2.
“Supported for replication and failover groups only. Not supported for database replication.”
↩︎ How replicated objects behave after a failover“If you use failover groups, you can choose which schemas within a database are replicated.”
↩︎ Running failover and failback“Backups are available for all Snowflake editions. Backups with retention lock and backups with legal holds are available for Business Critical Edition (or higher).”
↩︎ Backups in a replicated account, and what the sources don't cover - 3.
“You can optionally divide the replicated objects across multiple failover groups, for example if some databases should be replicated more frequently than others.”
↩︎ Designing the disaster recovery plan“For the most consistent and reliable failover experience, select all the applicable failover groups and connections and promote them all at the same time.”
↩︎ Designing the disaster recovery plan“Those objects become read-only on the account that formerly was the primary and is now a secondary account.”
↩︎ Running failover and failback“canceling a refresh operation in the SECONDARY_DOWNLOADING_METADATA or SECONDARY_DOWNLOADING_DATA phase might result in an inconsistent state on the target account.”
↩︎ Running failover and failback“Snowflake prevents failover if a refresh operation is in progress.”
↩︎ Exam trap 2“On failover, scheduled refreshes on all secondary failover groups are suspended.”
↩︎ Exam trap 3“Create at least one secondary failover group (replica) of each primary failover group in one or more secondary accounts.”
↩︎ Checkpoint“Now you can promote the secondary failover group myfg to primary failover group”
↩︎ Checkpoint“After the failover, those connections connect to the account”
↩︎ Checkpoint - 4.
“Target accounts do not have Tri-Secret Secure or private connectivity to the Snowflake service, such as AWS PrivateLink, enabled by default.”
↩︎ Designing the disaster recovery plan - 5.
“The connection name is included as part of the connection URL used to connect to Snowflake accounts.”
↩︎ Redirecting client connections“Modify this primary connection using an ALTER CONNECTION … ENABLE FAILOVER TO ACCOUNTS statement.”
↩︎ Redirecting client connections
Also cited
“After you promote a secondary database, the pipes will transition to a FAILING_OVER execution state.”
↩︎ Exam trap 1“Pipes in a secondary database are in a READ_ONLY execution state and receive notifications but do not load data”
↩︎ Prediction