Secure Data Sharing for the Snowflake SnowPro Core Certification

Fifteenth Chapter: Data Sharing

Chapter from the Snowflake SnowPro Course: Data Sharing in Snowflake

Let’s imagine we want to share a small part of the data we have in Snowflake with another account, such as sharing just a table with another company. But of course, we only want to share a table since the rest is, for example, confidential information. This will no longer be a problem using Snowflake Secure Data Sharing! Let’s take a look at it in detail.

1. Introduction to Secure Data Sharing
2. Shares
3. Producers & Consumers
4. Inbound & Outbound Shares
5. Typical Snowflake SnowPro Questions about Data Sharing

Remember that all the chapters from the course can be found in the following link:

Road to SnowFlake SnowPro Core Certification: Complete Course

INTRODUCTION TO SECURE DATA SHARING

Secure Data Sharing enables sharing selected objects in a database in your account with other Snowflake accounts. The account that receives the data cannot modify it, as shared data is always read-only. These are the Snowflake objects that we can share:

• Tables
• External tables
• Secure views
• Secure materialized views
• Secure UDFs

But what is a share? Let’s get to it!

SHARES

Shares are named Snowflake objects that encapsulate all information required to share a database. Each share consists of the following:

• The privileges that grant access to the database(s) and the schema containing the objects to share.
• The privileges grant access to the specific objects we want to share.
• The consumer accounts with which the database and its objects are shared.

Shares in Snowflake Accounts.

Real examples & useful commands

Imagine that we want to share a table “myTable” contained in the “myDb” database. We should create a share and give the needed privileges to the share to access this table:

CREATE SHARE myShare;

GRANT USAGE ON DATABASE myDb TO SHARE myShare;

GRANT USAGE ON SCHEMA myDb.public TO SHARE myShare;

GRANT SELECT ON TABLE myDb.public.myTable TO SHARE myShare;

We can also set the exact privileges we want the share to have. In the last command, we gave the share the privilege to only perform “SELECT” queries on the table “myTable”.

Imagine that we share a database with ten tables with a consumer account, and we want to stop sharing one of them; it will be as easy as removing the grant of the table from the share. Other important commands include:

• Show all the shares that we have in the system:

SHOW SHARES

• See all the privileges that a share has:

SHOW GRANTS TO SHARE myShare

• See the accounts (consumers) that are using the share:

SHOW GRANTS OF SHARE myShare

PRODUCERS & CONSUMERS

In Snowflake, the account that shares a Share will be the producer, while the account that receives it will be the consumer (already mentioned before). Let’s take a closer look.

Producers and consumers in Snowflake

Producers

Producers (or providers) are called to the Snowflake account that creates shares and makes them available to other Snowflake accounts to consume. For example, I would be the producer if I want to share the table “myTable” with another account. The producers will always pay for the storage of the data they share.

Consumers

Accounts that receive the share/data. Shared data is instantaneous for consumers as no actual data is copied or transferred between accounts. For this reason, shared data is always up-to-date. Also, consumers don’t pay for storage, as the producer account already pays for it. There are several features that a consumer cannot do with a Shared Object, for example, create a clone or Time-Travel in tables.

Once the consumers receive the share, they have to create a database from this share, and at this point, all the shared objects will be accessible to users in the consumer account.

The consumer and producer accounts must be in the same region to share data. To avoid this restriction, Snowflake uses database replication to allow data producers to securely share data with consumers across different regions and cloud platforms.

Snowflake Data Sharing between regions and cloud providers (via docs.snowflake.com)

We have two types of Consumers:

• Full account → When you share something with an existing Snowflake account. The consumer account pays for the queries they make, although the shared data storage is still paid for by the producer, as mentioned before.
• Reader Account → Imagine that you want to share data with someone that doesn’t have a Snowflake account. Thanks to Snowflake reader accounts, you can also do it, not needing them to become Snowflake customers! Each reader account belongs to the producer account that created it, and they can only consume data from this producer account. The producer account pays all the compute credits that their warehouses use.

You can see this behavior in the following diagram. As you can see, the Reader account will use the warehouse of Account1 to query Table_1. In the case of the FullAccount, it will use its warehouse to query the same table. The Provider will always pay for the storage, in this case, Account_1.

Types of Data Consumers that we can find in Snowflake.

INBOUND & OUTBOUND SHARES

We will have two types of shares:

1. Outbound shares → Shares that you (as a producer) have created to share with other accounts (consumer accounts).
2. Inbound shares → Shares that other accounts (as producers) have created and shared with you (as a consumer). These are the available shares for your account to consume.

Let’s classify the shares that we can see in the following example:

Outbound and inbound Snowflake shares.

• Account 1 → This first account has an inbound share from the “Account 3”, and one outbound share to the “Account 2”.
• Account 2 → It just has one inbound share from the “Account 1”.
• Account 3 → Apart from having three different databases, it has one outbound share of one of them to the “Account 1”.

TYPICAL EXAM QUESTIONS ABOUT DATA SHARING

What is the minimum Snowflake edition needed for the Data Sharing capability?

1. Standard
2. Enterprise
3. Business Critical

Solution: 1, 2, 3. All the data-sharing features are available for these three types of editions.

Which database objects can be shared using Snowflake Secure Data Sharing?

1. Tables
2. External tables
3. Secure views
4. Secure materialized views
5. Secure UDFs
6. Users

Solution: 1, 2, 3, 4, 5.

How can a producer share a table with a consumer located in a different region?

1. This is not a problem; producers and consumers can be in different regions
2. Replicate your account to another region and create a share from that region.
3. Create a script to replicate your data in the consumer account.

Solution: 2. Data sharing works within the same region; however, you can replicate your account to another region and then share data from that replicated account within that account’s region. This is also true across cloud platforms.

Which Snowflake role can set up a Snowflake Share?

1. SECURITYADMIN
2. SYSADMIN
3. DATASHARINGADMIN
4. ACCOUNTADMIN
5. PUBLIC

Solution: 4. Only AccountAdmins can create Shares.

What are the two types of data consumer accounts available in Snowflake?

1. Shared Account
2. Reader Account
3. Public Account
4. Full Account

Solution: 2, 4

Is shared data always immediately available to Consumer Accounts?

1. True
2. False

Solution: 1

Thanks for Reading!

If you like my work and want to support me…

1. The BEST way is to follow me on Medium here.
2. Feel free to clap if this post is helpful for you! :)
3. More Snowflake SnowPro Core practice exam questions? Find them at FullCertified.com!