Sharing Data Securely Across Regions and Cloud Platforms

Data Sharing Considerations¶

Since cross-region data sharing utilizes Snowflake data replication functionality, understand how database replication works in Snowflake as part of your planning process. For more information, see:
Data providers only need to create one copy of the dataset per region; and not a copy per consumer.
When sharing a view that references objects in multiple databases (for instructions, see Sharing Data from Multiple Databases), each of these other databases must be replicated.
Data providers can share data with consumers on any public multi-tenant deployment. But sharing to or from Virtual Private Snowflake (VPS) is currently not supported.

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:

Create an ORGADMIN role in one of your accounts and link the accounts for replication. For more information, see Getting Started with Organizations.
Promote an existing database in your local account as primary.
Replicate your existing database to the other region.

For detailed instructions, see Replicating Databases Across Multiple Accounts.

Example 1¶

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

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.

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

Sharing Data Securely Across Regions and Cloud Platforms¶

Data Sharing Considerations¶

Sharing Data With Data Consumers in a Different Region and Cloud Platform¶

Step 1: Set up Data Replication¶

Step 2. Share Data with Data Consumers¶

Example 1¶

Execute from Source Account¶

Execute from Target Account¶

Example 2¶

Execute from Source Account¶

Execute from Target Account¶