Snowflake’s Data Sharing feature is a powerful and secure way to share data across different Snowflake accounts without copying or moving the data. This functionality allows organizations to collaborate efficiently by providing direct, read-only access to specific tables, schemas, or databases. In this article, we will explore the concept of Data Sharing in Snowflake, discuss its advantages, and walk through a detailed example using the ‘freshers_in’ prefix for our table.
Snowflake Data Sharing
Data Sharing in Snowflake is achieved through the use of Shares, which are containers for the objects (tables, schemas, or databases) you want to share. A Share can be created by a data provider (the account that owns the data) and can be consumed by one or more data consumers (the accounts that access the shared data). The shared data remains in the provider’s account, and consumers can query it directly without incurring any data storage costs. It’s important to note that Data Sharing only provides read-only access to the shared data, so the data consumers cannot modify it.
Advantages of Snowflake Data Sharing
- Real-Time Data Access: Data Sharing allows data consumers to access the latest data in real-time, without delays introduced by data copying or transferring.
- Cost Efficiency: Since there is no data duplication or movement, Data Sharing reduces storage and data transfer costs for both data providers and consumers.
- Simplified Collaboration: Data Sharing streamlines collaboration between organizations by enabling them to securely access the same dataset without managing data transfers or synchronization.
- Security and Compliance: Snowflake provides robust security features, such as role-based access control, encryption, and data masking, to ensure that shared data is protected and accessed only by authorized users.
Let’s say we have a table named ‘freshers_in_students’ containing student records in a Snowflake account (data provider). We want to share this table with another Snowflake account (data consumer) for collaborative analysis. Here’s how we can achieve this in Snowflake:
Step 1: Create a Share in the data provider’s account
-- Create a Share named 'freshers_in_students_share' CREATE SHARE freshers_in_students_share; -- Add the 'freshers_in_students' table to the Share ALTER SHARE freshers_in_students_share ADD TABLE freshers_in_students;
Step 2: Grant access to the data consumer’s account
-- Replace 'DATA_CONSUMER_ACCOUNT' with the Snowflake account name of the data consumer GRANT USAGE ON SHARE freshers_in_students_share TO ACCOUNT DATA_CONSUMER_ACCOUNT;
Step 3: Create a Database in the data consumer’s account
In the data consumer’s account, execute the following command:
-- Replace 'DATA_PROVIDER_ACCOUNT' with the Snowflake account name of the data provider CREATE DATABASE freshers_in_students_db FROM SHARE DATA_PROVIDER_ACCOUNT.public.freshers_in_students_share;
Now, the data consumer has read-only access to the ‘freshers_in_students’ table through the ‘freshers_in_students_db’ database. They can run SELECT queries on the shared table without affecting the original data.
Snowflake’s Data Sharing feature offers an efficient and secure way to collaborate across different Snowflake accounts by providing real-time, read-only access to shared data. By understanding how Data Sharing works and leveraging it in your data management strategy, you can improve collaboration, reduce costs, and streamline data access for your organization.
Snowflake important urls to refer