Snowflake Data Sharing Patterns — Part 1

Snowflake Data Sharing

Introduction

Data Sharing has come a long way from using SFTP server to share the data, to Secure Data sharing in the Cloud. Snowflake has taken this secure data sharing one step forward with a very easy and faster to implement data sharing strategies. Snowflake secure data sharing opens up unique ways of collaborating with other Snowflake Data Providers or subscribe to data sets from the marketplace. This article will give an overview of the Snowflake Secure Data Sharing and mainly diving into Data Sharing patterns in Snowflake.

Overview

• Snowflake Data Sharing: Snowflake provides Secure Data sharing with options to share selective objects or subscribe to selective objects with other Snowflake Accounts
• Snowflake Data Sharing Concepts:

Share: Named snowflake object that encapsulates all the information related to snowflake objects that can be shared in a database.

Provider: A data provider is any Snowflake account that creates shares and makes them available to other Snowflake accounts to consume. As a data provider, you share a database with one or more Snowflake accounts.

Consumer: A data consumer is any account that chooses to create a database from a share made available by a data provider. As a data consumer, once you add a shared database to your account, you can access and query the objects in the database just as you would with any other database in your account.

• Snowflake Data Sharing Methods:

Direct Share: Account to Account to share between two Snowflake Accounts

Data Marketplace: Provides capability to discover and access third party data sources. Through Snowflake Marketplace, you can monetize the data if there is a demand for the data.

Data Exchange: Provides ability to create a data hub for securely collaborating around data between a selected group of members that you invite. It enables providers to publish data that can then be discovered by consumers.

Supported Cloud Platforms and Regions

Data Sharing Usage:

Data Sharing in Snowflake is useful to support following strategies:

• Intra company data sharing strategy
• Cross company data sharing strategy
• Data Sourcing strategy
• Data Distribution Strategy through Data Marketplace

Data Sharing Patterns:

• Pattern 1: Data Sharing in Same Cloud Platform within same region with another Snowflake Account

Usage:

1. Pattern applies to data sharing in same region (for example: US-WEST-2, Oregon)

2. Provider pays for Storage

3. Consumer pays for Compute

• Pattern 2: Data Sharing in Different Cloud Platform or Same Cloud Platform , but different Region with another Snowflake Account

Usage:

1. Patterns applies for data sharing between different Cloud Platform or Different Regions within same Cloud platform
2. Provider pays for Storage
3. Provider pays for Data Replication Costs
4. Consumer pays for Compute

• Pattern 3: Data Sharing in Same Cloud, Same Region with Reader Account for Non-Snowflake Customers

Usage:

1. Patterns applies for data sharing in Same region with Non Snowflake Customers
2. Provider pays for Storage
3. Provider also pays for Compute and Manages a Reader Account for Consumer

• Pattern 4: Data Sharing Inbound from Data Providers in Snowflake

Usage:

1. Pattern applies for incoming data sharing from another Snowflake account
2. Provider pays for Storage
3. Consumer pays for compute

• Pattern 5: Data Sharing from Snowflake Data Marketplace

Usage:

1. Pattern applies for Data Sharing from Snowflake marketplace
2. Consumer pays for Subscription cost if it’s not free
3. Consumer pays for Compute to consume data

In this Article, Pattern 1 — Data Sharing within Same Region will be covered in detail. Pattern 2 and Pattern 3 will covered in Part 2 of this series, Pattern 4 and Pattern 5 will be covered in Part 3 of this series.

Pattern 1: Data Sharing within Same Region

Data Pattern Statement:

The Pattern will be used for Data Sharing between same region (for example US-WEST-2 in AWS) with one Snowflake Account or Multiple Snowflake Accounts.

Usage of the Data Pattern:

The Pattern will be used for data sharing in same region (US-WEST-2, Oregon) for

1. Intra Company Data Sharing strategy
2. Cross company data sharing strategy
3. Data Distribution strategy

Architecture

Patter A — Data Sharing within Same Snowflake Region

Solution

The Solution applies to pattern where it involves data sharing of a Subset of data or Entire Data Set through Snowflake only in one Cloud platform and same region for example AWS US-WEST-2. This solution requires implementation of the following steps:

1. Task 1: Create a centralized database where all outbound data objects to consumers will reside in each schema created for the consumers. Any common data objects like tables or views can reside in common_data schema as shown in the architecture diagram.
2. Task 2: Share Set-up using “Create Share” Command
3. Task 3: Adding Objects to share

Object Sharing can be done at following levels:

Entire database can be added to share (or)

Entire schema can be added to share (or)

Specific objects: Tables, Views, UDF’s, Materialized Views, External Tables

3. Task 4: Granting access to consume account in “SHARE”.

Data Encryption, Monitoring, Alerting and Archiving

1. Data Encryption: All data in Snowflake is encrypted. Date sharing with Snowflake consumers is done through Secure objects and by default is encrypted.
2. Monitoring: Snowflake currently out of the box doesn’t provide any monitoring capabilities on how frequently a data share is being accessed by Consumer accounts
3. Data Quality: All Data quality checks on the data that is being shared have to be performed by provider account.

Trade-Off’s

1. This pattern applies only to use cases within same region (US-WEST-2)
2. This pattern applies to consumers in same cloud platform (US-WEST-2)

Guidelines and Naming Conventions

1. Database: <ACCOUNT>_CONSUMER_OB_OUTBOUND
2. Schema:

COMMON_DATA that holds common data that needs to be shared with multiple consumers to avoid duplication

<CONSUMER>_OB: A outbound schema for each consumer with whom data is being shared

3. Share: <ACCOUNT>_<CONSUMER>_OB

4. Data Objects:

<CONSUMER>_<TABLE_NAME> for a Table Name

<CONSUMER>_<VIEW_NAME> for a View Name