Snowflake Data Share is a powerful feature that allows you to securely share your data with external organizations without the need for data movement, replication, or ETL processes. This article will provide an in-depth understanding of Snowflake Data Share and how it can be used to share data with external organizations.
Overview of Snowflake Data Share
Snowflake Data Share enables you to share read-only access to your data with external accounts. The shared data is called a “share” and can include one or more database objects like schemas, tables, or views. The recipients, known as “consumer accounts,” can create a database from the share and query the shared data as if it were part of their own Snowflake account.
Benefits of Snowflake Data Share
- Real-time data sharing: As data sharing does not involve any physical data movement or copying, the shared data is always up-to-date and changes made by the provider are immediately reflected in the consumer’s view of the data.
- Secure: Snowflake Data Share uses the same security and encryption measures for shared data as it does for regular data storage.
- Cost-effective: Since data is not copied, there is no additional storage cost for the consumer account, and both the provider and consumer only pay for the compute resources they use.
Creating a Share
To share data with an external organization, follow these steps:
Step 1: Create a share
CREATE SHARE freshers_in_share;
Step 2: Add objects to the share (schemas, tables, or views)
ALTER SHARE freshers_in_share ADD SCHEMAS freshers_in.sales, freshers_in.inventory;
Step 3: Grant access to the share for the external account
GRANT USAGE ON SHARE freshers_in_share TO ACCOUNT '<consumer_account_id>';
Consuming a Share
To access the shared data, the consumer account must follow these steps:
Step 1: Create a database from the share
CREATE DATABASE freshers_in_shared_data FROM SHARE <provider_account_id>.freshers_in_share;
Step 2: Query the shared data as needed
SELECT * FROM freshers_in_shared_data.sales;
Managing Data Access and Security
Snowflake provides granular access control options for managing access to shared data:
- Granting object-level privileges: You can control access to specific objects (schemas, tables, or views) within the share.
- Using secure views: You can use secure views to mask sensitive data, filter rows, or limit columns before sharing the data.
- Row access policies: You can apply row access policies to filter data based on consumer account context.
Let’s assume you have a table called freshers_in.sales and want to share it with an external organization while masking sensitive information.
Step 1: Create a secure view that masks the sensitive data
CREATE SECURE VIEW freshers_in.secure_sales_view AS SELECT order_id, customer_id, product_id, order_date, quantity FROM freshers_in.sales;
Step 2: Update the share to include the secure view
ALTER SHARE freshers_in_share ADD OBJECTS (VIEW freshers_in.secure_sales_view);
Revoking Access to a Share
To revoke access to a share, you can use the following command:
REVOKE USAGE ON SHARE freshers_in_share FROM ACCOUNT '<consumer_account_id>';