Snowflake Replication Groups and Failover Groups: Editions, Objects, and Access Control

1.Primary and secondary objects, replication and failover groups

Replication copies objects from a source account to one or more target accounts in the same organization. It works across regions and across cloud platforms. The source holds the primary objects. Each target holds secondary objects, which are replicas of those primaries. A primary is where your workloads write. A secondary is a read-only copy you keep for reporting in another region, or as a standby you can switch to.

You don't replicate objects one at a time. You put them in a group, which Snowflake replicates as a unit. Snowflake has two kinds of group:

- A replication group gives read-only access to the replicated objects, and its secondary can never become writable. - A failover group adds promotion: any account in the group's allowed-accounts list can be promoted to hold the primary failover group.

Both kinds keep the replicated objects consistent with each other as of a single point in time in the target account. A replication group suits read-only uses, such as serving data closer to consumers in another region. A failover group suits business continuity, because it lets the target take over writes.

What a primary database brings to its secondary includes the objects inside it. Two of those objects matter for this exam: Automatic Clustering of clustered tables and materialized views. The replicated-objects list marks both as replicated. So when a database is in a replication or failover group, clustered tables with Automatic Clustering and materialized views are part of what the secondary database receives on each refresh. Neither one is on the list of skipped types, unlike external tables, hybrid tables and event tables, which are not replicated. The sources here say only that these two are replicated. They don't describe how clustering or materialized view maintenance runs in the target account.

Sources1

2.What each edition allows, and replicating to a lower edition

This leads to a common scenario question. A Standard or Enterprise account can replicate databases and shares in a replication group, but it can't replicate users, roles, warehouses or other account objects, and it can't fail over. If an account is upgraded to Business Critical, Snowflake says failover capabilities can take up to 12 hours to become available.

Replicating to a lower edition is allowed, with a guard. Suppose a primary replication group in a Business Critical (or higher) account contains only databases and shares, and one or more of its approved target accounts are on a lower edition. Snowflake shows an error message. The same happens when a Business Critical account has a HIPAA/HITRUST business associate agreement for PHI data and a target account doesn't, even if that target is also Business Critical. The purpose is to stop administrators from sending sensitive data to accounts that weren't meant to hold it. ACCOUNTADMIN, or a role with CREATE REPLICATION GROUP, CREATE FAILOVER GROUP or OWNERSHIP on the group, can override the check. The sources cut off before saying how the override is done.

Sources21

3.Which objects replicate, and group membership rules

Account-level objects need Business Critical: users, roles (account and database roles plus their grant hierarchies), warehouses, resource monitors, network policies, account parameters, and security, API, notification, storage and external access integrations. Some integrations need extra work in the target. A replicated storage integration needs a new trust relationship with your cloud storage, and replicated external functions need to be granted access to the remote service again.

When you replicate a database, the objects inside it go too, but not every type is supported. Snowflake skips unsupported objects during replication, so they won't exist in the target after a failover. The table lists the types this exam guide names.

Iceberg tables depend on external volumes, which are account-level objects. You have to configure replication for the external volume before an Iceberg table can replicate.

Group membership has its own rules for databases and shares:

- An object can be in only one failover group. - An object can be in several replication groups, as long as each group replicates to a different target account. - An object can't be in a failover group and a replication group at the same time. - An account can have only one replication or failover group that contains objects other than databases and shares. - Only outbound shares replicate. Inbound shares from providers don't.

Sources13

4.How replication affects access control

If you replicate roles, each database refresh also brings across the grants on the secondary database and the objects in it. The target ends up with the source's privilege model, not just its data. Roles include the privileges granted to them and roles granted to other roles. If users are also replicated, the grants of roles to users replicate too. The REPLICATE and FAILOVER privileges are the exception: they never replicate, so you have to grant them separately in each target.

Every secondary object is read-only, and that includes replicated account objects. If USERS replicate, you can't create or change users in the target. If ROLES replicate, you can't create or change roles there, so you can't grant privileges on a secondary object locally. You can still create local databases and shares in the target, and you can grant privileges on those local objects to a secondary role.

Objects you created in the target outside replication (for example, with scripts) have no global identifier by default. A refresh drops any account object of a replicated type that has no global identifier. That is why the first refresh of USERS or ROLES can fail on purpose.

Sources13

5.Setting up account replication with a schedule

Setup starts at the organization level. An organization administrator turns on replication for every source and target account by setting ENABLE_ACCOUNT_DATABASE_REPLICATION. Use the organization_name.account_name identifier. The legacy account locator can give unexpected results when several accounts share the same locator.

-- Enable replication by executing this statement for each source and target account in your organization
SELECT SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER('<organization_name>.<account_name>', 'ENABLE_ACCOUNT_DATABASE_REPLICATION', 'true');

Next, create the primary group in the source account. In the statement you list the object types, the allowed databases, the allowed target accounts, and optionally a replication schedule. Adding a database to a group requires the MONITOR privilege on that database. If a database was previously enabled for replication with ALTER DATABASE, first call SYSTEM$DISABLE_DATABASE_REPLICATION on it. Snowflake doesn't copy that database's data again: the group refresh sends only the changes since the last refresh.

USE ROLE myrole; CREATE FAILOVER GROUP myfg OBJECT_TYPES = USERS, ROLES, WAREHOUSES, RESOURCE MONITORS, DATABASES ALLOWED_DATABASES = db1, db2 ALLOWED_ACCOUNTS = myorg.myaccount2, myorg.myaccount3  ?  = '10 MINUTE';

Then, in each target account, create the secondary as a replica of the primary. If the primary has a schedule, Snowflake runs the first refresh automatically when the secondary is created. Otherwise, a role with the REPLICATE privilege on the group runs ALTER FAILOVER GROUP myfg REFRESH by hand.

USE ROLE myrole; CREATE FAILOVER GROUP myfg AS REPLICA OF myorg.myaccount1.myfg;

Sources2

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Any edition can create a failover group, because replication groups work everywhere.Why is that wrong?

   Replication groups, database replication and share replication work on every edition. Failover groups and account-object replication need Business Critical or higher.

   Covered in What each edition allows, and replicating to a lower edition

2. 2.After ROLES replicate, you can still create extra roles in the target account for local users.Why is that wrong?

   Replicated object types are read-only in the target. With ROLES replicated, you can't create or change roles there.

   Covered in How replication affects access control

3. 3.Users created by script in the target survive the first refresh that replicates USERS.Why is that wrong?

   A refresh drops account objects of replicated types that have no global identifier. Script-created objects don't have one by default.

   Covered in How replication affects access control