Note
If you share data with other accounts using listings offered to specific consumers, or on the Snowflake Marketplace,
you can use Cross-Cloud Auto-fulfillment
to automatically fulfill your data product to other regions. Auto-fulfillment is not supported for listings shared with a data exchange.
Cross-region data sharing is supported by Snowflake accounts hosted on any of the following cloud platforms:
Important
If you replicate a primary database to accounts in 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.
Example 1: Share data
A data provider, Acme, wants to share data with data consumers in a different region.
Execute from source account
To create a replication group that contains the databases and shares to replicate to another region, execute the following
SQL statement.
Create a replication group my_rg
that includes database db1
and share share1
to replicate to the account account_2
in the acme
org.
USE ROLE ACCOUNTADMIN;
CREATE REPLICATION GROUP my_rg
OBJECT_TYPES = databases, shares
ALLOWED_DATABASES = db1
ALLOWED_SHARES = share1
ALLOWED_ACCOUNTS = acme.account_2;
Execute from target account
From the target account in the other region, execute the following SQL statements.
Create a secondary replication group in account_2
:
USE ROLE ACCOUNTADMIN;
CREATE REPLICATION GROUP my_rg
AS REPLICA OF acme.account1.my_rg;
Manually refresh the replication group to replicate the databases and shares to account_2
:
ALTER REPLICATION GROUP my_rg REFRESH;
Add one or more consumer accounts to share1
:
ALTER SHARE share1 ADD ACCOUNTS = consumer_org.consumer_account_name;
You can automate refresh operations by setting the REPLICATION_SCHEDULE parameter for the primary replication group using the
ALTER REPLICATION GROUP command in the source account. For more information,see
Replication schedule.
Example 2: Share a subset of data from a database
A data provider, Acme, wants to share a subset of data with data consumers in a different region. To reduce replication costs, they
would like to only replicate the relevant rows from their master table. Since replication is done at the database level, this example
describes how Acme can use streams and tasks to copy the desired rows from the main database to a new database, create a share and
grant privileges on the view, and replicate both in a replication group to an account in a different region for consumer access.
In this scenario the new database and share are designated as primary objects for data replication.
Execute from source account
Use the following SQL commands to create a new database in the source account and enable replication.
In your local account, create a database db1
with a subset of data from the database with the source data:
USE ROLE ACCOUNTADMIN;
CREATE DATABASE db1;
CREATE SCHEMA db1.sch;
CREATE TABLE db1.sch.table_b AS
SELECT customerid, user_order_count, total_spent
FROM source_db.sch.table_a
WHERE REGION='azure_eastus2';
Create a secure view with the data to share:
CREATE SECURE VIEW db1.sch.view1 AS
SELECT customerid, user_order_count, total_spent
FROM db1.sch.table_b;
Create a stream to record changes made to the source table:
CREATE STREAM mystream ON TABLE source_db.sch.table_a APPEND_ONLY = TRUE;
Create a task to insert data into the table in db1
with changes from the source data:
CREATE TASK mytask1
WAREHOUSE = mywh
SCHEDULE = '5 minute'
WHEN
SYSTEM$STREAM_HAS_DATA('mystream')
AS
INSERT INTO table_b(CUSTOMERID, USER_ORDER_COUNT, TOTAL_SPENT)
SELECT customerid, user_order_count, total_spent
FROM mystream
WHERE region='azure_eastus2'
AND METADATA$ACTION = 'INSERT';
Start the task to update data:
ALTER TASK mytask1 RESUME;
Create a share and grant privileges to the share:
CREATE SHARE share1;
GRANT USAGE ON DATABASE db1 TO SHARE share1;
GRANT USAGE ON SCHEMA db1.sch TO SHARE share1;
GRANT SELECT ON VIEW db1.sch.view1 TO SHARE share1;
Create a primary replication group with the database and share:
CREATE REPLICATION GROUP my_rg
OBJECT_TYPES = DATABASES, SHARES
ALLOWED_DATABASES = db1
ALLOWED_SHARES = share1
ALLOWED_ACCOUNTS = acme_org.account_2;
Execute from target account
Execute the following SQL commands from the target account in the other region.
Create a secondary replication group to replicate the databases and shares from the source account:
USE ROLE ACCOUNTADMIN;
CREATE REPLICATION GROUP my_rg
AS REPLICA OF acme_org.account_1.my_rg;
Manually refresh the group to replicate objects to the current account:
ALTER REPLICATION GROUP my_rg REFRESH;
Add one or more consumer accounts to the share:
ALTER SHARE share1 ADD ACCOUNTS = consumer_org.consumer_account_name;
You can automate refresh operations by setting the REPLICATION_SCHEDULE parameter for the primary replication group using the
ALTER REPLICATION GROUP command in the source account. For more information,see
Replication schedule.
Example 3: Share data from multiple databases
A data provider, Acme, wants to share data from multiple databases with data consumers in a different region. They
create a secure view and share (for instructions, see Sharing data from multiple databases), then
replicate all the databases and share in a replication group to replicate data to accounts in other regions.
Execute from source account
Create a replication group my_rg
that includes the databases and share from Example 1: Create and share a secure view in an existing database to replicate
to account_2
in the acme
org:
CREATE REPLICATION GROUP my_rg
OBJECT_TYPES = databases, shares
ALLOWED_DATABASES = database1, database2, database3
ALLOWED_SHARES = share1
ALLOWED_ACCOUNTS = acme.account_2;
Execute from target account
Execute the following SQL commands from the target account in the other region.
Create a secondary replication group to replicate the databases and shares from the source account:
USE ROLE ACCOUNTADMIN;
CREATE REPLICATION GROUP my_rg
AS REPLICA OF acme_org.account_1.my_rg;
Manually refresh the group to replicate objects to the current account:
ALTER REPLICATION GROUP my_rg REFRESH;
Add one or more consumer accounts to the share:
ALTER SHARE share1 ADD ACCOUNTS = consumer_org.consumer_account_name;
You can automate refresh operations by setting the REPLICATION_SCHEDULE parameter for the primary replication group using the
ALTER REPLICATION GROUP command in the source account. For more information,see
Replication schedule.