Skip to main content

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, the

<ORGID> = ZCKUXIJ

<ACCOUNTID> = HL66577

<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 only update you need to make to this script below is updating the 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" ]
*/

Setup Replication to your Target Snowflake 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;