Replicating Shares Across Regions and Cloud Platforms

When sharing data in the Snowflake Marketplace or Data Exchange, providers receive requests from remote regions. This topic provides instructions on how to use account replication to replicate data shares between regions and cloud platforms.

Account replication enables the 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.

Important

If you are replicating a database to a geographic region or country that is different from that in which your source Snowflake account is located, you should confirm that your organization does not have any legal or regulatory restrictions as to where your data can be transferred or hosted.

Replication Group

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. Replication groups provide point-in-time consistency for the objects (e.g., databases and shares) in the collection.

A provider creates a primary replication group in a source account and enables replication to target accounts for consumers. For standard shares, data providers only need to create one copy of the share per region; and not a copy per consumer.

Note

This topic is a subsection of the features available with Account Replication. Account replication enables database and share replication, in addition to account object replication and failover.

Replicated Objects

Database Replication

This feature supports replicating databases. A snapshot includes changes to the objects and data. Refreshing a secondary database synchronizes the privilege grants on the database and the objects in the database (schemas, tables, views, etc.). Privileges granted to roles on these objects are synchronized from the source account to a target account. Note that only privilege grants on objects supported by database replication are synchronized. For the list of objects, see Replicated Database Objects.

Future grants on both supported and unsupported database objects are synchronized at the database and schema levels. After a secondary database is promoted to become the primary database in a set, creating objects of a specified type automatically grants privileges to roles, as defined by the future grants. For more information about future grants, see Simplifying Grant Management Using Future Grants.

Share Replication

This feature supports replication of share objects as well as access privileges granted to shares on database objects.

User Who Refreshes Objects in a Target Account

A user who executes the ALTER REPLICATION GROUP … REFRESH command to refresh objects in a target account from the source account must use a role with the REPLICATE privilege on the replication group. Snowflake protects this user in the target account by failing in the following scenarios:

  • If the user does not exist in the source account, the refresh operation fails.

  • If the user exists in the source account, but a role with the REPLICATE privilege was not granted to the user, the refresh operation fails.

Replication Privileges

See Replication Privileges for the list of replication privileges that are available to be granted to roles to specify the operations users can perform on objects in the system.

Replication Schedule

As a best practice, Snowflake recommends scheduling automatic refreshes using the REPLICATION_SCHEDULE parameter. The schedule can be defined when creating a new replication group with CREATE REPLICATION GROUP or later (using ALTER REPLICATION GROUP).

When a secondary replication group is created, an initial refresh is automatically executed. The next refresh is scheduled based on when the prior refresh started and the scheduling interval, or the next valid time based on the cron expression. For example, if the refresh schedule interval is 10 minutes and the prior refresh operation (either a scheduled refresh or manually triggered refresh) starts at 12:01, the next refresh is scheduled for 12:11.

Snowflake ensures only one refresh is executed at any given time. If a refresh is still executing when the next refresh is scheduled, the next refresh is delayed to start when the currently executing refresh completes. For example, if a refresh is scheduled to execute 15 minutes after the hour, every hour, and the prior refresh completes at 12:16, the next refresh is scheduled to execute when the previously executing refresh is completed.

Replication Group Constraints

Databases and shares are constrained in replication group membership, and must be uniquely replicated to target accounts.

The following constraints apply to database and share objects:

  • An object can be in multiple replication groups as long as each group is replicated to a different target account.

  • Secondary (replica) objects cannot be added to a primary replication group.

Transitioning From Database Replication to Group-based Replication

If you have previously enabled database replication for a database following the instructions in Sharing Data Securely Across Regions and Cloud Platforms, you must disable database replication before adding the database to a replication group.

Note

Execute the SQL statements in this section using the ACCOUNTADMIN role.

Step 1. Disable Replication for a Replication Enabled Database

Use the SYSTEM$DISABLE_DATABASE_REPLICATION function to disable database replication for a primary database, along with any secondary databases linked to it, before you add it to a replication group.

Execute the following SQL statement from the source account with the primary database:

SELECT SYSTEM$DISABLE_DATABASE_REPLICATION('mydb');
Copy

Step 2. Add Databases and Shares to a Primary Replication Group and Create a Secondary Replication Group

Once you have successfully disabled database replication, you can now add the primary databases and primary shares to a replication group in the source account.

Then create a secondary replication group in each target account. When the secondary replication group is refreshed in each target account, any previously secondary databases will automatically be added as members of the secondary replication group and refreshed with the changes from the primary object.

See the Workflow below to create primary and secondary replication groups, and to schedule automatic refresh of objects in target accounts.

Note

When you add a previously replicated database to a replication group, Snowflake does not re-replicate the data that has already been replicated for that database. Only changes since the last refresh are replicated when the group is refreshed.

Workflow

The following SQL statements demonstrate the workflow for enabling database and share replication and refreshing objects. Each step is discussed in detail below.

Example

Execute the following SQL statements in your preferred Snowflake client to enable database and share replication, and refresh objects on a schedule.

Executed on Source Account

  1. Create a role and grant it the CREATE REPLICATION GROUP privilege. This step is optional:

    -- Execute the following SQL statements using the ACCOUNTADMIN role:
USE ROLE ACCOUNTADMIN;

CREATE ROLE myrole;

GRANT CREATE REPLICATION GROUP ON ACCOUNT
  TO ROLE myrole;
    Copy

  2. Create a replication group in the source account and enable replication to specific target accounts:

    Note

    • If you have databases to add to a replication group that have been previously enabled for replication, follow the Transitioning From Database Replication to Group-based Replication instructions (in this topic) to convert the objects to be replication group compatible before adding them to a group.

    • To add a database to a replication group, the active role must have the MONITOR privilege on the database. For details on database privileges, see Database Privileges (in a separate topic).

    • To add a share to a replication group, the active role must have the OWNERSHIP privilege on the share.

    USE ROLE myrole;

-- Execute the following SQL statement using a role with the CREATE REPLICATION GROUP privilege:
CREATE REPLICATION GROUP myrg
    OBJECT_TYPES = DATABASES, SHARES
    ALLOWED_DATABASES = db1, db2
    ALLOWED_SHARES = s1
    ALLOWED_ACCOUNTS = myorg.myaccount2, myorg.myaccount3
    REPLICATION_SCHEDULE = '10 MINUTE';
    Copy

Executed on Target Account

  1. Create a role in the target account and grant it the CREATE REPLICATION GROUP privilege. This step is optional:

    -- Execute the following SQL statements using the ACCOUNTADMIN role:
USE ROLE ACCOUNTADMIN;

CREATE ROLE myrole;

GRANT CREATE REPLICATION GROUP ON ACCOUNT
    TO ROLE myrole;
    Copy

  2. Create a replication group in the target account as a replica of the replication group in the source account:

    USE ROLE myrole;

 -- Execute the following SQL statement using a role with the CREATE REPLICATION GROUP privilege:
 CREATE REPLICATION GROUP myrg
     AS REPLICA OF myorg.myaccount1.myrg;
    Copy

Refresh a Secondary Replication Group Manually

As a best practice, Snowflake recommends scheduling automatic refreshes using the REPLICATION_SCHEDULE parameter. For details, see Replication Schedule (in this topic). To manually refresh all the objects in a secondary replication group, execute the following SQL statements from the target account:

To manually refresh the objects in a target account, execute the ALTER REPLICATION GROUP … REFRESH command.

Note

If the user who calls the function in the target account was dropped in the source account, the refresh operation fails.

Grant the REPLICATE Privilege on Replication Group to Role — Optional

The REPLICATE privilege is currently not replicated and must be granted on a replication group in both the source and target accounts.

Executed from the source account:

-- Execute the following SQL statements using a role with the OWNERSHIP privilege on the group:
GRANT REPLICATE ON REPLICATION GROUP myrg TO ROLE my_replication_role;
Copy

Executed from the target account:

-- Execute the following SQL statements using a role with the OWNERSHIP privilege on the group:
GRANT REPLICATE ON REPLICATION GROUP myrg TO ROLE my_replication_role;
Copy

Manually Refresh a Secondary Replication Group

For example, to refresh the objects in the replication group myrg, execute the following statement from the target account:

USE ROLE my_replication_role;

-- Execute the following SQL statements using a role with the REPLICATE privilege:
ALTER REPLICATION GROUP myrg REFRESH;
Copy

Replicating Databases and Shares

Prerequisites

Enable Replication on Your Accounts

A user with the ORGADMIN role must enable replication for two or more accounts in your organization. See Prerequisite: Enable Replication for Accounts in the Organization for instructions.

Note

Before configuring data replication, you must have an existing account or create an account in the region where you wish to share data and link it to your local account. For more information about creating accounts, see Creating an Account.

Create a Share in Your Source Account

If you have previously created a share in your source (local) account, you can skip this step.

To create a new share:

  1. Log in to the Snowflake Classic Console as a user with the ACCOUNTADMIN role.

  2. Create a share and add objects to the share. For instructions, see Getting Started with Secure Data Sharing.

Step 1: Create a Role with the CREATE REPLICATION GROUP Privilege in the Source Account — Optional

Create a role and grant it the CREATE REPLICATION GROUP privilege. This step is optional. If you have already created this role, skip to Step 2: Create a Primary Replication Group in a Source Account.

-- Execute the following SQL statements using the ACCOUNTADMIN role:
USE ROLE ACCOUNTADMIN;

CREATE ROLE myrole;

GRANT CREATE REPLICATION GROUP ON ACCOUNT
    TO ROLE myrole;
Copy

Step 2: Create a Primary Replication Group in a Source Account

Create a primary replication group and enable the replication of specified databases and shares from the current (source) account to one or more target accounts in the same organization.

View All Accounts Enabled for Replication

To retrieve the list of accounts in your organization that are enabled for replication, use SHOW REPLICATION ACCOUNTS.

SHOW REPLICATION ACCOUNTS;

+------------------------+-------------------------+--------------+-----------------+-----------------+-------------------+
| snowflake_region       | created_on              | account_name | account_locator |  comment        | organization_name |
+------------------------+-------------------------+--------------+-----------------+-----------------+-------------------+
| AWS_US_WEST_2          | 2020-07-15 21:59:25.455 | myaccount1   | myacctlocator1  |                 | myorg             |
+------------------------+-------------------------+--------------+-----------------+-----------------+-------------------+
| AWS_US_EAST_1          | 2020-07-23 14:12:23.573 | myaccount2   | myacctlocator2  |                 | myorg             |
+------------------------+-------------------------+--------------+-----------------+-----------------+-------------------+
| AWS_US_EAST_2          | 2020-07-25 19:25:04.412 | myaccount3   | myacctlocator3  |                 | myorg             |
+------------------------+-------------------------+--------------+-----------------+-----------------+-------------------+
Copy

View Replication Group Membership

Database and share objects have Replication Group Constraints on membership. Before creating new groups or adding objects to existing groups, you can review the list of existing replication groups and the objects in each group.

Note

Only an account administrator (user with the ACCOUNTADMIN role) or the group owner (role with the OWNERSHIP privilege on the group) can execute the SQL statements in this section.

View all replication groups linked to the current account, and the object types in each group:

SHOW REPLICATION GROUPS;
Copy

View all the databases in replication group myrg:

SHOW DATABASES IN REPLICATION GROUP myrg;
Copy

View all the shares in replication group myrg:

SHOW SHARES IN REPLICATION GROUP myrg;
Copy

Enable Database and Share Replication from a Source Account to Target Account

Create a replication group of specified databases and shares in the source account and enable replication to a list of target accounts. See CREATE REPLICATION GROUP for syntax.

For example, enable replication of databases db1 and db2, and share s1 from the source account to the myaccount2 account in the same organization.

Executed on the source account:

USE ROLE myrole;

-- Execute the following SQL statement using a role with the CREATE REPLICATION GROUP privilege:
CREATE REPLICATION GROUP myrg
    OBJECT_TYPES = databases, shares
    ALLOWED_DATABASES = db1, db2
    ALLOWED_SHARES = s1
    ALLOWED_ACCOUNTS = myorg.myaccount2;
Copy

Step 3: Create a Role with the CREATE REPLICATION GROUP Privilege in the Target Account — Optional

Create a role in the target account and grant it the CREATE REPLICATION GROUP privilege. This step is optional. If you have already created this role, skip to Step 4: Create a Secondary Replication Group in the Target Account.

-- Execute the following SQL statements using the ACCOUNTADMIN role:
USE ROLE ACCOUNTADMIN;

CREATE ROLE myrole;

GRANT CREATE REPLICATION GROUP ON ACCOUNT
    TO ROLE myrole;
Copy

Step 4: Create a Secondary Replication Group in the Target Account

Create a secondary replication group in the target account as a replica of the primary replication group in the source account.

Execute a CREATE REPLICATION GROUP … AS REPLICA OF statement in each target account for which you enabled replication in Step 2: Create a Primary Replication Group in a Source Account (in this topic).

Executed from each target account:

USE ROLE myrole;

-- Execute the following SQL statement using a role with the CREATE REPLICATION GROUP privilege:
CREATE REPLICATION GROUP myrg
  AS REPLICA OF myorg.myaccount1.myrg;
Copy

Refresh a Secondary Replication Group Manually

Manually refresh a secondary replication group in the target account. This refreshes all the objects in the replication group.

Note

As a best practice, Snowflake recommends scheduling automatic refreshes using the REPLICATION_SCHEDULE parameter. See Replication Schedule (in this topic).

Refresh a secondary replication group.

For example, to refresh the objects in the replication group myrg, execute the following statement from the target account:

-- Only a user with the ACCOUNTADMIN role can execute the following SQL statement:
ALTER REPLICATION GROUP myrg REFRESH;
Copy

View Replication Groups

To view replication groups, use SHOW REPLICATION GROUPS:

To show all primary and secondary replication groups associated with the current account, execute the following SQL statement:

SHOW REPLICATION GROUPS;
Copy

Command Output:

+------------------+-------------------------------+--------------+------+-------------+---------+------------+------------------+--------------------------------------+------------------------------------------+--------------------+-----------------+----------------------+-----------------+------------+
| snowflake_region | created_on                    | account_name | name | type        | comment | is_primary | primary          | object_types                         | allowed_accounts                         | organization_name  | account_locator | replication_schedule | secondary_state | owner      |
+------------------+-------------------------------+--------------+------+-------------+---------+------------+------------------+--------------------------------------+------------------------------------------+--------------------+-----------------+----------------------+-----------------+------------+
| AWS_US_EAST_1    | 2022-01-14 16:59:01.462 -0800 | MYACCOUNT2   | MYRG | REPLICATION | NULL    | false      | MYORG.MYACCOUNT1 |                                      |                                          | MYORG              | MYACCTLOCATOR2  | 15 MINUTE            | SUSPENDED       |  NULL      |
| AWS_US_WEST_2    | 2022-01-14 16:56:10.497 -0800 | MYACCOUNT1   | MYRG | REPLICATION | NULL    | true       | MYORG.MYACCOUNT1 | DATABASES, SHARES                    | MYORG.MYACCOUNT2                         | MYORG              | MYACCTLOCATOR1  | 15 MINUTE            | NULL            |  MYROLE    |
+------------------+-------------------------------+--------------+------+-------------+---------+------------+------------------+--------------------------------------+------------------------------------------+--------------------+-----------------+----------------------+-----------------+------------+
Copy
Language: English