Introduction
In an enterprise organization with globally distributed data, the creation of multiple Snowflake accounts across regions is natural. In order to share the data residing in different regions and accounts, there will be a need to collate the data to build a Global Snowflake account (Cloud Data warehouse). This document will assist in implementing cross region data sharing using listings. Listings can be used for the below scenarios.
- Data sharing between different regional Snowflake accounts.
- Data sharing between the same regional Snowflake accounts.
- Publishing the data to Snowflake marketplace to monetize the app.
- Tracking usage of your data used by consumers.
Defining Listings and Their Types
A listing serves as an abstract layer built on top of both the Direct share & data replication features within Snowflake. It operates on a provider and consumer model defined in data sharing features.
With listings, users can avoid the complexities of cross-regional data sharing. Listings intelligently identify the provider and consumer regions, streamlining the process of data movement. Notably, Snowflake offers the listing features to all editions.
There are various types of listings, allowing users acting as providers to choose the most suitable option for sharing data with consumers. The available types include:
These options cater to different use cases, offering flexibility in sharing data and information based on individual needs.
Designing and Setting Up Listings
Given the One-to-One relationship between private share and listings with the database, users should follow these steps:
- Create individual listings for each of the databases intended for replication from each Snowflake Account.
- Create individual listings for each of the provider Snowflake accounts.
- Establish databases in the Global Snowflake Account for consumption from providers.
It's important to note that there isn't an option to consume provider tables in the same database. Therefore, a new database will be created for each listing in the consumer account.
Supported Snowflake Objects for Sharing
Only specific Snowflake Schema objects can be shared through listings. The supported objects include:
- Tables
- External tables
- Secure views
- Secure materialized views
- Secure UDFs
- Dynamic tables
Creating Provider Listings for Cross-Regional Data Movement in Snowflake
For the purposes of this blog, we established two Provider Snowflake accounts—one in the same region as the consumer and another in a different region. The document outlines the steps involved in moving data from these two Provider Snowflake accounts to a single consumer account.
- Accepting Terms of Service: To become a Snowflake provider of listings, review and accept the Provider Terms of Service.
- Enabling the Data sharing: For the Cross-region data movement or replication, the Orgadmin must grant the privileges below to the Account admin in all the Cross Region SF accounts.Cross-Region Snowflake accounts. Details can be found here.
- Required Privileges: To offer listings to consumers as a provider, use the ACCOUNTADMIN role or create a Custom role with specific privileges granted.
- Creation of Direct Share: Create a Share by navigating to Data Private Sharing Via SNOWSIGHT UI. Add the list of Tables or Securable views to be replicated to the Global Account. For Example, the Share is created in Provider Snowflake account.
- Creation of Private Listing: Create a listing by navigating to Data → Provider Studio Via SNOWSIGHT UI. Attach the Share created in Step #4 or add individual Snowflake objects to the listing.
- Data Replication Frequency: Specify the data refresh frequency for cross-region data replication to sync data between the consumer and provider.
- Consuming the Listing: Login to Consumer Snowflake account (AMER) and navigate to Data → Private Sharing, to accept the listings Created by the Providers.
- Creation of Databases: While accepting the listing, a Pop-up Window will appear to create a Database to accommodate the Snowflake objects shared by the Provider. A new database will be created automatically.
Summary:
With these steps, the Snowflake object named EMPLOYEE in the Provider Snowflake account (APAC) is successfully replicated to the Consumer Snowflake account (AMER). Refresh frequency is not required for the same Provider data sharing as it incurs no additional cost or delay in data movement.
Creation of listings in Snowflake using SQL
Note: Currently, listing SQL API is in private preview.
Limitations:
- No publishing to the Snowflake Marketplace.
- No support for paid or personalized listings.
- Native applications are not supported.
To create a listing, start by creating a listing manifest. Manifests are written in YAML (https://yaml.org/spec/), and include a prefix and some number of required and optional fields.
Same Region Listing Creation:
Below is the sample SQL script for creating a listing.
Cross-Region Listing Creation:
Similar to same-region listing creation, with an additional section for specifying the refresh schedule.
Creation of Share using SQL:
As the Snowflake listing SQL API can only accept the share to be attached, rather than individual objects, a share must be created via UI or SQL statements to add objects for sharing and replication. The following statements are useful for this purpose.
Note: For security reasons, future objects cannot be automatically attached to a share. After creating new tables or secured views, manual privilege granting is required to tag them to a share.
Cross Edition Replication:
To Replicate from Business-critical edition (Provider) to Enterprise Edition (Consumer), execute the command to ignore the Edition check. Details here.
Naming Standards:
- Listings: As each listing is created for different data providers, it's advisable to use the Provider Snowflake account name as a prefix.
Naming Standards: <PROVIDER>_to_<CONSUMER SF Name>_<DATABASE_NAME>_PRIVATE LISTING
- Direct Share: As a direct share is created to group all required Snowflake objects for replication, it is recommended to use the Provider Snowflake account as a prefix.
Naming Standards: <PROVIDER SF Name>_to_<CONSUMER SF Name>_<DATABASE_NAME>_SHARE
- Databases: Each private listing needs to be consumed in the consumer Snowflake account, requiring the creation of different databases named as the replication layer.
Naming Standards: <PROVIDER SF Name>_REPLICA_DB