Sharing data securely across regions and cloud platforms

This topic provides instructions on using replication to allow data providers to securely share data with data consumers across different regions and cloud platforms.

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:

  • Amazon Web Services (AWS)

  • Google Cloud Platform (GCP)

  • Microsoft Azure (Azure)

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.

Data sharing considerations

Diagram of data replication and sharing between regions and clouds

Sharing data with data consumers in a different region and cloud platform

Snowflake data providers can share data with data consumers in a different region in a few simple steps.

Step 1: Set up data replication

Note

Before configuring data replication, you must create an account in a region where you wish to share data and link it to your local account. For more information, see Working with Organizations and Accounts.

Setting up data replication involves the following tasks:

  1. Enable replication for your accounts.

    A user with the ORGADMIN role must enable replication for the source account that contains the data to share and the target accounts in regions where you want to share data with consumers. For instructions on enabling replication, see Prerequisite: Enable replication for accounts in the organization. For more information about the ORGADMIN role, see Getting Started with Organizations.

  2. Create a replication group and add databases and shares.

  3. Replicate the group with the databases and shares to the regions where you want to share data with consumers.

Step 2. Share data with data consumers

Sharing data with data consumers in the same region involves adding one or more consumer accounts to the secondary shares that you replicated from the source account.

For detailed instructions, see Getting Started with Secure Data Sharing.

Example 1: Share data

A data provider, Acme, wants to share data with data consumers in a different region.

Diagram of a basic example on how to share data between regions

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.

Note

If you have previously enabled replication for an individual database, you must disable database replication for the database before you add it to a replication group. For details, see Transitioning from database replication to group-based replication.

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;
Copy

Execute from target account

From the target account in the other region, execute the following SQL statements.

  1. Create a secondary replication group in account_2:

    USE ROLE ACCOUNTADMIN;
    
    CREATE REPLICATION GROUP my_rg
      AS REPLICA OF acme.account1.my_rg;
    
    Copy
  2. Manually refresh the replication group to replicate the databases and shares to account_2:

    ALTER REPLICATION GROUP my_rg REFRESH;
    
    Copy
  3. Add one or more consumer accounts to share1:

    ALTER SHARE share1 ADD ACCOUNTS = consumer_org.consumer_account_name;
    
    Copy

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.

Diagram of an advanced example on how to share data between regions

Execute from source account

Use the following SQL commands to create a new database in the source account and enable replication.

Note

If you have previously enabled replication for an individual database, you must disable database replication for the database before you add it to a replication group. For details, see Transitioning from database replication to group-based replication.

  1. 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';
    
    Copy
  2. 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;
    
    Copy
  3. Create a stream to record changes made to the source table:

    CREATE STREAM mystream ON TABLE source_db.sch.table_a APPEND_ONLY = TRUE;
    
    Copy
  4. 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';
    
    Copy
  5. Start the task to update data:

    ALTER TASK mytask1 RESUME;
    
    Copy
  6. 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;
    
    Copy
  7. 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;
    
    Copy

Execute from target account

Execute the following SQL commands from the target account in the other region.

  1. 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;
    
    Copy
  2. Manually refresh the group to replicate objects to the current account:

    ALTER REPLICATION GROUP my_rg REFRESH;
    
    Copy
  3. Add one or more consumer accounts to the share:

    ALTER SHARE share1 ADD ACCOUNTS = consumer_org.consumer_account_name;
    
    Copy

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;
Copy

Execute from target account

Execute the following SQL commands from the target account in the other region.

  1. 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;
    
    Copy
  2. Manually refresh the group to replicate objects to the current account:

    ALTER REPLICATION GROUP my_rg REFRESH;
    
    Copy
  3. Add one or more consumer accounts to the share:

    ALTER SHARE share1 ADD ACCOUNTS = consumer_org.consumer_account_name;
    
    Copy

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.