In this article, we are going to look into the steps required for data sharing in snowflakes. We will begin the concepts by understanding how data sharing works, what is a share, an overview of customers and data providers, sharing metrics usage with data providers. Before exploring further, get this online snowflake training course which assists you in learning the core essentials of snowflake and its advanced concepts, capabilities of data warehousing, cloning, snowflake data migration, snowflake objects, analytical and failsafe solutions, etc.
Working of Data Sharing
No real data is duplicated or exchanged across accounts while using Secure Data Sharing. Snowflake's metadata store and unique services layer is used for all sharing. This is a crucial idea since it implies that shared data doesn’t require any storage space in a consumer's account and so doesn’t contribute to the consumer's charges for data storage on a monthly basis. Consumers are only charged for computational resources that are employed for querying the shared data.
Furthermore, as no data was copied or transmitted, Data Sharing is simple to set up for providers, and consumers have immediate shared data access:
- In their account, the provider builds a database share and authorizes access to specified database objects. Data from several databases could be shared by the provider as long as they are all part of the same account. After then, one or many accounts, including your own, are joined to the sharing.
- A read-only database will be constructed from a share on the consumer side. The same standard access control based on roles that Snowflake offers for every object in the system can be used to control access to this database.
Snowflake supports the creation of a provider network that could share data with numerous consumers (including inside their own company) and consumers who could have the accessibility to shared data through multiple providers using this architecture:
What is meant by a Share?
Snowflake objects, often known as shares, enclose every information needed for sharing a database. The following things are included in each share:
- The permissions grant accessibility for the databases and schema holding the shared objects.
- The permissions allow you to access specific database objects.
- The user accounts that have access to the database and their objects.
All shared objects are accessible to users in the consumer account when a database is built once (in a consumer account) from the share:
The provider account controls 100% of the shares, which are controlled, configurable, and secure.
- All consumers get rapid access to new objects included in a share, allowing for real-time data access.
- At any moment, sharing accessibility (or a share's objects) could be revoked.
Overview of Consumers and Data Providers
Providers
Any Snowflake account which creates the shares and makes them open for consumption by other accounts of Snowflake is referred to as a data provider. You will share the database with one or many accounts of a Snowflake as a data provider. Snowflake allows employing grants to enable access control at the granular level to chosen database items for each database you share.
Snowflake doesn’t impose any hard constraints on the no. of shares or accounts that can be added to a single share.
See Getting Started with Secure Data Sharing for a fast approach to share the data as the provider. Working with Shares provides more extensive information.
Consumers
Any account which decides to establish a database through a share made open by a data provider. is referred to as a data consumer. When you include a shared database for your account as the data consumer, you could query and access the database's objects just like any other database.
Snowflake doesn’t impose any hard constraints on the amount of data source shares you could consume; nevertheless, each share can only be used to generate one database.
Data Consumers have more information.
Sharing Metrics Usage with Providers
Consumers that access their data through data providers of Snowflake will have the accessibility to the following metrics:
- Snowflake of a Customer account name
- Snowflake of a Customer organization name
- Data consumption statistics, including:
- Consumption date.
- On a provider's share, the no. of queries a customer account runs. The data will be offered daily in aggregation.
- Any information a customer supplies when requesting a tailored listing, like the customer's business email address and firm name.
How to Share Data in Snowflake: A Step-by-Step Guide
Secure Data Sharing is a feature that is both powerful and simple to use. In just a few simple steps, anyone can become the data provider.
The tasks required to share a database and its objects with one or more accounts are covered in this article. All of the SQL commands required to complete these tasks are illustrated in the example at the end of the topic.
Step 1: Create a Share
To create a share, use the CREATE SHARE command. The share is just a container at this step, waiting for accounts and objects to be added.
Step 2: Granting Privileges for adding Objects to the Share
Use the GRANT <privilege> … TO SHARE for granting the following object privileges to the share:
- The database you want to share should have the USAGE privilege.
- Each database schema containing the objects you want to share should have the USAGE privilege.
- SELECT capability in each shared schema for sharing certain objects:
- Secure views
- Secure materialized views
- Secure UDFs
Step 3: Adding One or Many Accounts to the Share
For adding one or many accounts to the share, use ALTER SHARE. You could use SHOW GRANTS to see which accounts have been added to the share.
That concludes our discussion. The share is now available for consumption by the designated accounts. See Working with Shares for further information on how to do these and other data provider tasks.
Example:
The above-mentioned provider process is illustrated in the following example.
Notice this example which assumes:
- There is a database named sales_db that has an aggregates_eula schema and a table named aggregate_1.
Two accounts named xy12345 and yz23456 will share the database, schema, and table.
Conclusion:
In this article, we have successfully learned the implementation steps used for data sharing in snowflake.
Thank you for giving your 8 minute :)