Sharing Data Securely Across Regions and Cloud Platforms

This topic provides instructions on using database replication to allow data providers to securely share data with data consumers across different regions and cloud platforms. Database replication is now a part of Account Replication. For instructions on share replication using account replication, see Replicating Shares Across Regions and Cloud Platforms.

Cross-region data sharing is supported by Snowflake accounts hosted on any of the following cloud platforms:

  • Amazon Web Services (AWS)

  • Google Cloud Platform

  • Microsoft Azure

Important

  • Currently, database roles are not included in the replication of a primary database. As a result, cross-region data sharing is not supported when objects are granted to a share via database roles.

  • 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 replcation 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 Manage Organizations and Accounts.

Setting up data replication involves the following tasks:

  1. Create an ORGADMIN role in one of your accounts and link the accounts for replication. For more information, see Getting Started with Organizations.

  2. Promote an existing database in your local account as primary.

  3. Replicate your existing database to the other region.

For detailed instructions, see Replicating Databases Across Multiple Accounts.

Step 2. Share Data with Data Consumers

Sharing data with data consumer in the same region involves the following tasks:

  1. Create a share.

  2. Add objects to the share.

  3. Add one or more consumer accounts to the share.

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

Example 1

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

Execute the following SQL commands to enable replication of a primary database.

use role accountadmin;

-- Promote an existing database in your local account as primary
alter database PrimaryDB enable replication to accounts AZURE_EASTUS2.AcmeProviderAccount2;
Copy

Execute from Target Account

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

use role accountadmin;

-- Replicate the existing database to a secondary database in the other region
create database SecondaryDB
as replica of AWS_US_WEST_2.AcmeProviderAccount1.PrimaryDB;

-- Create a database for stored procedures
create database SecondaryDB_SP;
use database SecondaryDB_SP;

-- Schedule refresh of the secondary database

create task refresh_SecondaryDB_task
  warehouse = mywh
  schedule = '10 minute'
as
  alter database SecondaryDB refresh;

alter task refresh_SecondaryDB_task resume;

-- Refresh the secondary database now (as an alternative to the scheduled refresh)

alter database SecondaryDB refresh;

-- Create a share
create share share1;

-- Add objects to the share
grant usage on database SecondaryDB to share share1;
grant usage on schema SecondaryDB.sch to share share1;

grant select on view SecondaryDB.sch.view1 to share share1;

-- Add one or more consumer accounts to the share
alter share share1 add accounts=ConsumerAccount;
Copy

Example 2

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 leverage streams and tasks to copy the desired rows from the main database to a new database and replicate the new database. In this scenario the new database is designated as a primary database 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.

use role accountadmin;

-- In your local account, create a database with a subset of data
create database PrimaryDB;
create schema PrimaryDB.sch;
create table PrimaryDB.sch.tableB as select CUSTOMERID, USER_ORDER_COUNT, TOTAL_SPENT, TAGS from SourceDB.sch.tableA where REGION='azure_eastus2';
create secure view PrimaryDB.sch.view1 as select CUSTOMERID, USER_ORDER_COUNT, TOTAL_SPENT, TAGS from PrimaryDB.sch.tableB;

-- Set up a stream to record changes made to the source table
create stream mystream on table SourceDB.sch.tableA append_only = true;

-- Set up a task to lift the changes from the source database and insert them to the PrimaryDB database
CREATE TASK mytask1
  WAREHOUSE = mywh
  SCHEDULE = '5 minute'
WHEN
  SYSTEM$STREAM_HAS_DATA('mystream')
AS
  INSERT INTO tableB(CUSTOMERID, USER_ORDER_COUNT, TOTAL_SPENT, TAGS) select CUSTOMERID, USER_ORDER_COUNT, TOTAL_SPENT, TAGS FROM mystream WHERE REGION='azure_eastus2' AND METADATA$ACTION = 'INSERT';

-- Promote the new database as primary
alter database PrimaryDB enable replication to accounts AZURE_EASTUS2.AcmeProviderAccount2;
Copy

Execute from Target Account

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

use role accountadmin;

-- Replicate the existing database to a secondary database in the other region
create database SecondaryDB
  as replica of AWS_US_WEST_2.AcmeProviderAccount1.PrimaryDB;

-- Schedule refresh of the secondary database

create task refresh_SecondaryDB_task
  warehouse = mywh
  schedule = '10 minute'
as
  alter database SecondaryDB refresh;

alter task refresh_SecondaryDB_task resume;

-- Create a share
create share share1;

-- Add objects to the share:
grant usage on database SecondaryDB to share share1;
grant usage on schema SecondaryDB.sch to share share1;

grant select on view SecondaryDB.sch.view1 to share share1;

-- Add one or more consumer accounts to the share
alter share share1 add accounts=ConsumerAccount;
Copy