Streamline Collaboration with Snowflake Data Sharing

Secure data sharing is a prominent feature of Snowflake that allows you to collaborate with others in the same region. This feature enables the secure transfer of specific database objects between Snowflake accounts.

As a consumer, you can access the data without worrying about local storage; you pay only for the computing resources used. Since the shared data is available in read-only format, it also ensures data integrity and consistency. This blog highlights Snowflake data sharing, providing specific ways to share a database and the associated best practices.

What is Snowflake Data Sharing?

Snowflake data sharing is a feature that allows users to share data among different Snowflake or reader accounts. This feature lets you load data from one account to another without copying or transferring data. The process involves moving data from one Snowflake account to another in the same region to make it accessible to different teams.

You can share Snowflake objects, including databases, tables, secure views, and more. In Snowflake secure data sharing, the data providers create shares by granting consumers access to specific database objects that they can then import.

The shares are named objects that contain all the information a provider wants to share in a database. You can add one or more accounts to a share. To access this database, the consumers must have Snowflake role-based access control. It is important to note that all data shared between accounts is read-only and cannot be modified or deleted by the consumer.

What are the Options for Sharing Data in Snowflake?

There are three options for sharing data in Snowflake.

• Listing: This option allows you to share data with additional metadata, making it available for one or more consumers. The listing option is preferable when you choose to market data as a product to a range of users.

• Direct Share: Here, you directly share a Snowflake object with another account in your region. You can choose the direct share option to provide users access to specific objects in your database.

• Data Exchange: For this option, you manage a group of accounts and offer to share the data among them so the group members can access it. You can select the data exchange option to manage multiple accounts and share data with them.

How Does Snowflake Data Sharing Work?

Snowflake Data Sharing: Data Sharing Architecture

Snowflake data sharing enables you to share specific parts of your database with designated consumer accounts. The image above shows that the provider account makes certain database objects accessible and distributes access among consumer accounts through a ‘share’. Consumer accounts can then create their own databases to access and work with this data.

Snowflake data sharing works on the principle of the Snowflake service layer and metadata store. It allows instantaneous data transfer, allowing providers to create shares and consumers to access data through their linked accounts.

As the data is not moved physically from one account to another, there’s no consumption of storage at the consumer’s end. The charges that the consumer has to pay are only for the computational resources they use to query the data.

How to Share Database Objects in Snowflake?

This section highlights the steps for using data sharing in Snowflake. Before that, you must know that creating a share and associating user accounts to it requires an ACCOUNTADMIN role or a role with CREATE SHARE privileges. You can use data definition language (DDL) to create and manage database roles and view, grant, and revoke access to the database objects in the share.

Create a Share as a Provider

There are multiple ways to create a share, including using Snowsight, Classic Console, SQL, or DDL language. Here are two of the most widely used methods.

Using Snowsight

To create a direct share using Snowsight, you can follow the steps below:

• You must sign in to Snowsight.
• Click Data Products and select Private Sharing.
• Select Create a Direct Share by clicking Share. A Share Data dialog box will open up.
• In the dialog box, select + Select Data. Under this option, you must specify all the necessary details, including the source database, target objects to share, and more. Finally, click Create Share.

To learn more about the steps involved, follow creating a share using Snowsight.

Using DDL

This section highlights the use of DDL commands to create a share. Here are the commands that you can follow.

• To create an empty share with the name sales_s:

CREATE SHARE sales_s;

• To create a database user, grant privileges on the objects to the user, and grant the database user to share, follow the command below:

CREATE DATABASE ROLE sales_db.dr1;

GRANT USAGE ON DATABASE sales_db TO DATABASE ROLE sales_db.dr1;

GRANT USAGE ON SCHEMA sales_db.aggregates_eula TO DATABASE ROLE sales_db.dr1;

GRANT SELECT ON TABLE sales_db.aggregates_eula.aggregate_1 TO DATABASE ROLE sales_db.dr1;

GRANT USAGE ON DATABASE sales_db TO SHARE sales_s;

GRANT DATABASE ROLE sales_db.dr1 TO SHARE sales_s;

• You can confirm the content of the share by following the command given below:

SHOW GRANTS TO SHARE sales_s;

+——————————-+———–+————+————————————–+————+—————-+————–+————–+

| created_on                    | privilege | granted_on | name                                 | granted_to | grantee_name   | grant_option | granted_by   |

|——————————-+———–+————+————————————–+————+—————-+————–+————–|

| 2017-06-15 16:45:07.307 -0700 | USAGE     | DATABASE   | SALES_DB                             | SHARE      | PRVDR1.SALES_S | false        | ACCOUNTADMIN |

| 2017-06-15 16:45:10.310 -0700 | USAGE     | SCHEMA     | SALES_DB.AGGREGATES_EULA             | SHARE      | PRVDR1.SALES_S | false        | ACCOUNTADMIN |

| 2017-06-15 16:45:12.312 -0700 | SELECT    | TABLE      | SALES_DB.AGGREGATES_EULA.AGGREGATE_1 | SHARE      | PRVDR1.SALES_S | false        | ACCOUNTADMIN |

+——————————-+———–+————+————————————–+————+—————-+————–+————–+

• To add a Snowflake account to the share, follow these steps:

ALTER SHARE sales_s ADD ACCOUNTS=xy12345, yz23456;

• Finally, you can check for the shares using the SHOW SHARES command:

SHOW SHARES;

To get a deeper understanding of the steps involved, follow DDL commands to create a share.

Add Objects to Share

After setting up the share, you must add objects to it. To do that, you can follow the SQL command given below.

GRANT SELECT ON VIEW sales_db.aggregates_eula.agg_secure TO SHARE sales_s;

SHOW GRANTS TO SHARE sales_s;

This example adds a secure view named agg_secure in the aggregates_eula schema to the sales_s share. To learn more, refer to adding an object to the share.

Consume Data from Share

This section describes how consumer accounts can access the data from the share. But first, you must create a database to access the shared data. Use the code in Snowflake’s command-line interface, SnowSQL, by removing the specific placeholder values.

For creating a database:

CREATE DATABASE snow_sales FROM SHARE xy12345.sales_s;

To view the share:

DESC SHARE xy12345.sales_s;

+----------+------------------------------------------+---------------------------------+
| kind     | name                                     | shared_on                       |
|----------+------------------------------------------+---------------------------------|
| DATABASE | SNOW_SALES                               | Thu, 15 Jun 2017 17:03:16 -0700 |
| SCHEMA   | SNOW_SALES.AGGREGATES_EULA               | Thu, 15 Jun 2017 17:03:16 -0700 |
| TABLE    | SNOW_SALES.AGGREGATES_EULA.AGGREGATE_1   | Thu, 15 Jun 2017 17:03:16 -0700 |
| VIEW     | SNOW_SALES.AGGREGATES_EULA.AGGREGATE_1_v | Thu, 15 Jun 2017 17:03:16 -0700 |
+----------+------------------------------------------+---------------------------------+

To grant privileges on the database to other roles in your account, replace the <role_name> with the specific role:

grant imported privileges on database snow_sales to role <role_name>;

Finally, you can query the database using this command:

USE ROLE <role>;

USE DATABASE snow_sales;

SELECT * FROM aggregates_1;

View Accounts That Have Created Database from Share

You can use the SHOW GRANTS OF SHARE command to view the accounts that have created a database from the share that you created. Follow the command given below:

SHOW GRANTS OF SHARE sales_s;

To learn more about the steps involved, follow the view accounts section.

Why Share Data with Snowflake?

• As a data provider, you can choose who can access your data and maintain data security and integrity.
• Sharing data through Snowflake helps avoid the challenges of synchronizing data across different systems. This ensures that the shared data remains up-to-date in real-time.
• As a data consumer, joining shared datasets with your own can become convenient as the data stays in the Snowflake ecosystem.
• You can include metadata when sharing your data using listings. This includes using a title, description, and usage examples to help the consumer utilize your data efficiently.
• You can automatically replicate the data to other regions and even charge for access to your data or make it publicly available on the Snowflake Marketplace.

What Are the Best Practices for Data Sharing?

This section highlights the best practices you can follow to share your data using secure data sharing. There are multiple steps you can take to perform data transfer efficiently. Here are some of the most common ones.

Remove Objects from Share

This section discusses how to remove an object from a share, a useful step that can help make it easier to manage the shared data and reduce clutter. To remove objects from a share, you can restrict access to the data. Follow the command given below.

REVOKE SELECT ON VIEW sales_db.aggregates_eula.agg_secure FROM SHARE sales_s;

The above command removes the secure view named agg_secure in the aggregates_eula schema from the sales_s share. To learn more about the command, follow the remove object from share.

Audit Access to Data Shares

Auditing data shares is often considered good if the information is accessible to multiple consumers. Removing accounts from the share that do not consume any data ensures that only authorized accounts have access to the data. If some accounts do not use your data, you can limit access by removing them from the share.

The command given below will highlight if the consumer accounts are using your shares, mentioning the accounts that have created a database:

show grants of share sales_s;

If there are accounts not utilizing your data, you can remove their access using the syntax below.

ALTER SHARE [ IF EXISTS ] <name> { ADD | REMOVE } ACCOUNTS = <consumer_account> [ , <consumer_account> , ... ]
                                        [ SHARE_RESTRICTIONS = { TRUE | FALSE } ]

ALTER SHARE [ IF EXISTS ] <name>  ADD ACCOUNTS = <consumer_account> [ , <consumer_account> , ... ]
                                        [ SHARE_RESTRICTIONS = { TRUE | FALSE } ]

ALTER SHARE [ IF EXISTS ] <name> SET { [ ACCOUNTS = <consumer_account> [ , <consumer_account> ... ] ]
                                       [ COMMENT = '<string_literal>' ] }

ALTER SHARE [ IF EXISTS ] <name> SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]

ALTER SHARE <name> UNSET TAG <tag_name> [ , <tag_name> ... ]

ALTER SHARE [ IF EXISTS ] <name> UNSET COMMENT

Validate Data Shares

It is important to validate the data shares by ensuring that the access to your data aligns with your expectations. Snowflake provides a session parameter that can help simulate accessing data as a consumer. Follow the code syntax below, replacing the sharedb.shares with the schema you created in the database.

— Test the table and secure view by first querying the data using the provider account.

select count(*) from sharedb.shares.sharedview;
select top 1 * from sharedb.shares.sharedview;

— Set session parameter to use the shared customer account

alter session set simulated_data_sharing_consumer=ABC123;

— Validate the simulated data sharing consumer can access the appropriate data

select count(*) from sharedb.shares.sharedview;
select top 10 * from sharedb.shares.sharedview;

Drop a Share

If the share you work with is not useful, you can drop it. Before dropping the share, you must know there is no way to restore the dropped share. After dropping it, data retrieval is impossible, and the consumer accounts have their access revoked. Follow the code syntax given below to drop the syntax.

DROP SHARE sales_s;

+-------------------------------+
| status                        |
|-------------------------------|
| SALES_S successfully dropped. |
+-------------------------------+

Practical Use Cases of Data Sharing

Here are some practical cases of using secure data sharing in Snowflake.

• Secure data sharing enables you to share data using direct access to live data without performing ETL or data integration tasks. This feature allows you to engage with multiple teams to perform analytical tasks on your data.
• By sharing data, researchers can collaborate with different institutions on projects. This ensures that the data used for R&D purposes is identical to the original data extracted from different domains.
• Data sharing Snowflake allows you to scale your business while adding consumers and specifying granular secure views. All these features perform together while maintaining data consistency.
• Datasets shared within the Snowflake environment are live in real-time. Consumers can see the changes made to the data. Data is consistent without extra effort in moving data from one account to another.

Conclusion

This article highlights the Snowflake data sharing feature and how it can help create a data-driven environment. It enables you to host your data in other Snowflake accounts in your region, leading to a more collaborative environment. There are two ways to make your data accessible, including open-sourcing it or pricing it with the help of the Snowflake Marketplace.

Interested in integrating data from some other source to Snowflake? Here are a few articles that can enable you to do so:

MySQL to Snowflake Data Integration

Google Sheets to Snowflake

Aurora to Snowflake

Frequently Asked Questions (FAQs)