Snowflake : Sharing Data with External Organizations using Snowflake Data Share

Snowflake

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.

  1. 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.

  1. 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.
  1. 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>';
  1. 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;
  1. 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.

Example:

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);
  1. 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>';

Snowflake Data Share provides an efficient and secure way to share data with external organizations in real-time without the need for data movement or replication. 

Snowflake important urls to refer

Author: user

Leave a Reply