Snowflake Replication and Sharing Data Across Regions and Clouds

Snowflake Replication is a powerful solution to seamlessly and securely replicate and share data across clouds (Azure, AWS, Google).

There are many practical scenarios and use cases where replication makes sense.

Here is step by step instructions to setup and manually test replication. In this instance we assume you are going to replicate to an instance you own and manage (you should not replicate to Snowflake instances that you do not manage).

Create a New Account

Go to Admin>Account>Add an Account

Understanding Account vs Org Identifiers

To setup replication, it requires careful understanding and planning of your organization structure. For this article and simplicity of documentation we will refer to “ACCOUNTID” and “ORGID”. Reading through documentation and keeping the identifiers straight is your best bet to get replication right.

For our purposes I have mocked up fake identifiers to help us understand and map the right identifiers.

<ORGID> = ZJFEJKM

<ACCOUNTID> = HL62579

<TARGETACCOUNTID> = HL21576

Hopefully this reference will help you are you plug in your own data.

Setup your Source Org to Replicate

Source System Create Replication Role and Replication Group

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

CREATE ROLE REPLICATION;

GRANT CREATE REPLICATION GROUP ON ACCOUNT
  TO ROLE REPLICATION;

Setup Replication to your Target Snowflake Account

The following step is completed in the source account. To execute this script requires ORGADMIN permissions for the user which may require assistance from your organization/system administrator for Snowflake.

ORGID and the TARGETACCOUNTID.

-- SETUP ACCOUNT REPLCIATION TO TARGET ACCOUNT
USE ROLE ORGADMIN;
SELECT SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER('<ORGID>.<TARGETACCOUNTID>', 'ENABLE_ACCOUNT_DATABASE_REPLICATION', 'true');
/*. ALSO, RESULT SHOULD BE:
[ "SUCCESS" ]
*/

More information and background on ORGADMIN

To create a replication group and share data between your accounts, your Snowflake account needs to be enabled as an ORGADMIN account under your organization. This is not enabled by default when you add accounts to your orgnaization. If you need to enable this, the following code needs to be executed by your org admin:

USE ROLE ORGADMIN;
ALTER ACCOUNT <source account ID> SET IS_ORG_ADMIN = TRUE;

Setup Replication From Source to Target Snowflake Account

The following step is completed in the source account that will push data to the target account.

Your will update ORGID and the TARGETACCOUNTID. This is where you also define the database that you would like to replicate. The CREATE REPLICATION GROUP function documentation covers additional OBJECT_TYPES and parameters.

The CREATE REPLCIATION GROUP name can be anything you like. For simplicity and ease of maintenance, I used the <ORGID> as the replication group name. You may decide to append other information in the replication group.

-- CREATE REPLICATION GROUP. NAME THE GROUP BASED ON THE SOURCE INSTANCE
USE ROLE ACCOUNTADMIN;
CREATE REPLICATION GROUP <ORGID>
    OBJECT_TYPES = DATABASES
    ALLOWED_DATABASES = <DATABASE>
    ALLOWED_ACCOUNTS = <ORGID>.<TARGETACCOUNTID>
    REPLICATION_SCHEDULE = '1000 MINUTE';

Grant Replicate permission to the REPLICATION group

The replication group name is set as the <ORGID>.

-- Execute the following SQL statements using a role with the OWNERSHIP privilege on the group:
USE ROLE ACCOUNTADMIN;
GRANT REPLICATE ON REPLICATION GROUP <ORGID> TO ROLE REPLICATION;

Setup your Destination Account

Destination System Create Replication Role and Replication Group

USE ROLE ACCOUNTADMIN;

CREATE ROLE REPLICATION;

GRANT CREATE REPLICATION GROUP ON ACCOUNT
    TO ROLE REPLICATION;

Setup Replication Group

USE ROLE REPLICATION;

 -- Execute the following SQL statement using a role with the CREATE REPLICATION GROUP privilege:
 CREATE REPLICATION GROUP <ORGID>
     AS REPLICA OF <ORGID>.<ACCOUNTID>.<ORGID>;

Manually Run Replica

USE ROLE REPLICATION;

-- Execute the following SQL statements using a role with the REPLICATE privilege:
ALTER REPLICATION GROUP <ORGID> REFRESH;