Data sharing inside and outside the organization is one of the most technically challenging tasks facing modern companies today.
Snowflake provides special features for distributing and sharing corporate data.
In most cases, data providers must upload the data from a database, encrypt each of the data sets, and then upload statistical data sets via FTP1 for distribution.
Then consumers have to download the data and painstakingly restore it by copying it into their databases. There are other tools for sharing on a cloud or on-premise platform, but they require ETL.2 E-mail exchange is also possible, but it is slow and limited to a small file and also often leads to an overflow of your e-mail account. The Snowflake company rethought the data exchange process and proposed a new approach based on the cloud architecture as a modern tool for distributing data.
Your data may be stored in Snowflake for some time. If you have changed or even deleted some of it, you can always request a previous state from a certain point in time, which is extremely convenient when working with data.
How to securely share your data using Snowflake
How to work with versions of objects
Secure Data Sharing
No data movement, no data copying
Instant access to shared data
The ability to share and grant access to other companies to use your database
Updates reflected instantly
Limited access to the row-level data by using secure views
It is necessary to understand that in the process of sharing there is no real copying of data. Therefore, the data consumer pays only for the computing service but does not pay for the storage of this data, since physically the data remains stored with the data provider. Since the information is not actually transferred, consumers get an instant update when the provider changes the data. A single data provider may have multiple data consumers, both within the company and with external consumers. Similarly, data consumers may have access to multiple providers, thereby forming a network of providers and consumers.
Let’s see how it works. The data sharing feature provides the ability to share database objects between Snowflake’s accounts within a region by using a specific share object. Such objects can be tables, secure views, and secure UDFs.3 The data provider creates a share object, and the data consumer uses this object for access.
Permissions that provide access to the provider’s database and selected objects
Consumer database and objects that are shared
Often there is a situation where you have a base table, and you need to organize access to only part of the records of this table. The best practice is to use secure views.
The data sharing feature in Snowflake works only between Snowflake accounts. If you want to grant access to the outside world, you will need to use a reader account.4
Data Sharing Process
Step | Description |
---|---|
1 | The provider account creates a share object called Share_1 on the database Provider_DB_1 and grants access to selected objects in table_1_1. |
2 | The consumer account creates the read-only database from the Share_1 object. Then, all shared objects are available to consumers. In Figure 10-1, the accounts are called Customer Account #1 and Customer Account #2. |
4 | If consumers do not have an account in Snowflake, the provider can create a reader account for them. In Figure 10-1, this is implemented for the object Share_2. |
5 | Shared objects can be a table (like table_1_1), but the best practice is to use a secure view. A view can include multiple private tables from various databases. |
6 | In a secure view, as an option, we can use control data access by rows. For this, we have to create a table in which there will be a mapping of a group of records on users. |
7 | The consumer account grants permissions according to role-based access control. |
Secure Table Sharing
- 1.
Create a share object.
- 2.
Add a table name to the share and grant privileges.
- 3.
Add a consumer account to the share object.
- 4.
Log into a consumer account.
- 5.
Add the available share to the account and query the shared tables.
Let’s do an example.
Example with Sharing Table
Creating Sample Data
We created a new database called samples and a schema called samples.finance.
We created a sample table called samples.finance.stocks_data and filled it with values.
We created a share object and provided access to another account.
Creating a Share and Granting Permissions to a New Account
In Listing 10-2 we did the following:
- We created a shared object called stocks_share and a schema called samples.finance. You can see the metadata of the share object in Figure 10-3.
We granted privileges by using the statement GRANT <privilege> .... TO SHARE on the database samples, the schema finance, or the concrete table stocks_share to the consumer account locator <consumer _account>.
- We checked privileges using SHOW GRANTS TO SHARE <share_name>. See Figure 10-4.
We added a new account to a share using ALTER SHARE <share_name> ADD ACCOUNTS=<consumer _account>;.
- 3.
Log into your consumer account called <consumer_account>. Check access to the table via the consumer account.
- 4.
Switch to the Worksheets tab and execute SQL. See Figure 10-5.
Now see Listing 10-3.
use role accountadmin;show shares;desc share <consumer_account>.STOCKS_SHARE;Listing 10-3Showing the Available Share
- 5.
Let’s create a database based on the share. See Figure 10-6.
create database shared_db from share <provider_account>.STOCKS_SHARE; - 6.
Query the shared table. See Figure 10-7.
Data Sharing Using a Secure View
- 1.
Add a new column to a table to divide data into a few groups.
- 2.
Create a mapping table (mapping the name of the groups and the name of the Snowflake account).
- 3.
Create a secure view on a table.
- 4.
Create a share object.
- 5.
Add the secure view name to the share and grant privileges.
- 6.
Add the account to the share object.
Let’s do an example.
Sharing a Table Using Secure View
- 1.
Log into your Snowflake account.
- 2.
Switch to a worksheet and execute the code in Listing 10-4.
Modifying the Table and Adding Values for Grouping Data
In Listing 10-4, we did the following:
We changed the table from the previous example by adding a new column called access_id.
- We divided the stock data into two groups.
IT companies: GRP_1
Auto companies: GRP_2
Figure 10-8 shows some summary data of the table.
- 3.
To provide public access based on a secure view, execute the code in Listing 10-5.
Creating a Mapping Table
In Listing 10-5, we did the following:
We created a mapping table called access_map;.
- We filled the table with values:
Group #1 of stocks for our account
Group #2 of stocks for <consumer_account>
- 4.
To provide public access based on a secure view, execute the code in Listing 10-6.
Creating the Secure View on the Table
In Listing 10-6, we did the following:
We created a new public schema.
We created a secure view called samples.public.stocks; based on the table and the mapping table.
We used the function current_account() for dynamically identifying the user account.
We granted privileges to access the secure view.
- 5.
We tested the access to the table and the secure view.
Now see Listing 10-7.
Checking Access to Tables
Checking Access to the Table Using a Session Parameter
- 7.
Create a share object, add the secure view to the share, and grant privileges.
Now see Listing 10-9.
Adding the Secure View in the Share Object and Grant Privileges
In Listing 10-9, we did the following:
We turned back to the session of the producer account.
We created a new share object called share_sv.
We added the secure view to the share.
We granted privileges to access the secure view for the consumer account.
Consumer’s Script
We created a database from the share object called share_sv.
We granted imported privileges from the share object to the sysadmin user.
We got access to the secure view called stocks.
Summary
In this chapter, we covered the Snowflake data sharing feature that provides an easy, fast, and secure way to distribute data. Moreover, you learned about share objects and considered several basic options for using these features.
Finally, we walked through two examples: a simple way to share a table and an advanced way to share one by using a secure view.
In the next chapter, you will learn about how to design modern analytical solutions based on Snowflake services.