Snowflake Data Sharing

by | Blog, Hands On

Introduction

Snowflake provides easy solution to securely data sharing, enables account-to-account sharing of data through Snowflake database tables, secure views, and secure UDFs.

Snowflake uses data sharing to share the following data with all Snowflake accounts:

Secure Data Sharing means no actual data is copied or transferred between accounts. All sharing is accomplished through Snowflake’s unique services layer and metadata store hence it means that shared data does not take up any storage in a consumer account and, therefore, does not contribute to the consumer’s monthly data storage charges.

When we create the shares and provide them with user friendly name. First-class Snowflake objects that encapsulate all of the information require sharing a database.

Each share consists of:

  • The privileges that grants access to the database and the schema containing the objects to share.
  • The privileges that grant access to the specific objects (tables, secure views, and secure UDFs).
  • The consumer accounts with which the database and its objects are shared.

Shares are secure, configurable, and controlled 100% by the provider account (account sharing the dataset):

  • New objects added to a share become immediately available to all consumers, providing real-time access to shared data.
  • Access to a share (or any of the objects in a share) can be revoked at any time.

Sharing data with non-snowflake users:

Data sharing is only supported between Snowflake accounts. As a data provider, you may wish to share data with a consumer who does not already have a Snowflake account and/or is not ready to become a licensed Snowflake customer. To facilitate sharing data with these consumers, Snowflake supports providers creating reader accounts. Reader accounts (formerly known as “read-only accounts”) provide a quick, easy, and cost-effective way to share data without requiring the consumer to become a Snowflake customer. Users in a reader account can query data that has been shared with it, but cannot perform any of the DML tasks that are allowed in a full account (data loading, insert, update, etc.).

Steps to perform data sharing using web UI:

Step 1: Create share account

We need to create the person account with whom we need to share the account. We can create user account or readers account as per the need in snowflake:

E.g.:In this example we will see how to create Readers account.

snowflake screen shot

Fill the required details in the pop up dialog box.

snowflake create reader account

Now the readers account is created, in same way you can create the snowflake user.

Step 2: Create a Share

If you have the ACCOUNTADMIN role (or have a role that has been granted the CREATE SHARES privilege), you can use the Shares button on the menu list in the Snowflake web interface to perform most tasks related to creating and managing shares.

snowflake shares buttonIn UI you can create share by clicking Create Share and fill required details.

snowflake screenshot

Once the share has been created in UI you can add the consumer (People with whom we need to share the data).

snowflake screenshot

Pop up appears to select the accounts with whom you want to share the data.

snowflake add consumers

Steps to perform data sharing using SQL commands:

Same Steps can be done in worksheets using the SQL commands.

Step 1: Create a Share

Once the accounts for the clients with whom we need to share the data are created you can follow these steps in worksheets.

Use CREATE SHARE to create a share. At this step, the share is simply a container waiting for objects and accounts to be added.

Step 2: Add Objects to the Share by Granting Privileges

Use GRANT <privilege> … TO SHARE to grant the following object privileges to the share:

  • USAGE privilege on the database you wish to share.
  • USAGE privilege on each database schema containing the objects you wish to share.
  • SELECT privilege for sharing specific objects (tables, secure views, and secure UDFs) in each shared schema.

Optionally use SHOW GRANTS to view the object grants for the share.

Step 3: Add One or More Accounts to the Share

Use ALTER SHARE to add one or more accounts to the share. To review the accounts added to the share, you can use SHOW GRANTS.

It is important that person sharing the DB has the DB owner rights or else it gives issue insufficient permission.

That’s it! The share is now ready to be consumed by the specified accounts.

Example

The following example illustrates the entire provider process as described above.

Note that this example assumes:

  • A database named sales_db exists with a schema named aggregates_eula and a table named aggregate_1.
  • The database, schema, and table will be shared with two accounts named xy12345 and yz23456.
USE ROLE accountadmin;

CREATE SHARE sales_s;

GRANT USAGE ON DATABASE sales_db TO SHARE sales_s;
GRANT USAGE ON SCHEMA sales_db.aggregates_eula TO SHARE sales_s;
GRANT SELECT ON TABLE sales_db.aggregates_eula.aggregate_1 TO SHARE sales_s;

SHOW GRANTS TO SHARE sales_s;

ALTER SHARE sales_s ADD ACCOUNTS=xy12345, yz23456;

SHOW GRANTS OF SHARE sales_s;

General Limitations for Shared Databases

Shared databases have the following limitations for consumers:

  • Shared databases are read-only. Users in a consumer account can view/query data, but cannot insert or update data, or create any objects in the database.
  • The following actions are not supported:
    • Time Travel for a shared database or any schemas/tables in the database.
    • Editing the comments for a shared database.
  • Shared databases and all the objects in the database cannot be forwarded (i.e. re-shared with other accounts).

Demo videos available online:

https://www.youtube.com/watch?v=vDH7bphq0Bs

https://www.youtube.com/watch?v=CPeI8NuT_A4

Author: Mayur Shahasane.