Snowflake Grants : Share

A Snowflake Share is another interesting feature that allows you to securely share data from your source (producer) Snowflake account with other/same Snowflake accounts, be it internal or external consumers, without having to move or copy the data. A Snowflake account can both provide and consume shared data. This feature is particularly very useful for collaborating across different departments, business units, or even external organizations.

Key Features of Snowflake Share:

• Real-Time Access: Data shared via a Snowflake Share is accessed in real-time. This means any updates to the data in the provider account are immediately available to the consumer without the need to move or copy the data.

• No Data Movement: Snowflake Share enables data sharing without moving, copying, or exporting data. This ensures that the data remains secure and reduces storage and maintenance costs.

• Secure and Governed: Data is shared securely, and the data provider has full control over which objects (databases, schemas, tables, views) are shared. The provider can also revoke access at any time.

• Cross-Account Sharing:You can share data with other Snowflake accounts, whether they are within your organization or with external partners, customers, or vendors.

• Data Marketplace Integration: Snowflake Shares can also be listed on the Snowflake Data Marketplace, allowing organizations to offer their data to a broader audience.

• No Extra Costs: The data provider incurs no additional costs for sharing data, as the data remains in the provider's account. Consumers are responsible for any compute costs incurred while querying the shared data.

A Step-by-Step Guide to Sharing Data Securely

Let's review a use case, suppose your organization has a Snowflake account where you store salesdata. You want to share this sales data with an external marketing partner for analysis. Instead of exporting the data and sending it via traditional methods, you can create a Snowflake Share

• Create a Share: In Snowflake, create a share that will include the datasets you want to provide to your consumers.

USE ROLE accountadmin;

SET sales_share= '{{SHARE_NAME}}';

CREATE SHARE IF NOT EXISTS 
identifier($sales_share) COMMENT = 'XYZ Sales Share';

• Grant Access: You grant USAGE on the relevant database, schemaand SELECT on the sales tablesto this share.

USE ROLE accountadmin;

SET db_name = '{{DATABASE_NAME}}';
SET schema_name = '{{SCHEMA_NAME}}';

-- grant usage on database
GRANT USAGE ON DATABASE identifier($db_name)  
TO SHARE identifier($sales_share);

-- grant usage on schema
GRANT USAGE ON SCHEMA identifier($schema_name)  
TO SHARE identifier($sales_share);

-- grant usage on table sales_table_1
GRANT SELECT ON TABLE "sales_table_1" TO SHARE 
identifier($sales_share);

• Add the Partner's Snowflake Account: You add the partner's Snowflake account to the share.

-- display the privileges that have been granted to a share
SHOW GRANTS TO SHARE sales_share;

ALTER SHARE sales_share ADD 
ACCOUNTS=marketing_account, partner_account;

• Consumer Access: Once access is granted, consumers can access the shared data by creating a database in their own Snowflake account that points to the share.

CREATE DATABASE sales_db 
FROM SHARE provider_account.sales_share;

• Access by the Partner: The partner can now access the shared sales data through their Snowflake account in real-time without needing to move or copy the data. This allows the partner to perform analysis directly on your data, while you retain control and can revoke access whenever needed.

SELECT * FROM sales_table_1

Summary

• All database objects shared between accounts are read-only.
• Shared data does not take up any storage in a consumer account and therefore does not contribute to the consumer’s monthly data storage charges. The only charges to consumers are for the compute resources (i.e. virtual warehouses) used to query the shared data.
• Access to this database is configurable using the same, standard role-based access control that Snowflake provides for all objects in the system.

Final Thoughts

If you have alternates worth mentioned here, please share them in the comment below. Consider liking and sharing if you find this helpful.
Thank you. Have a good day!

References

A huge thanks to the Snowflake documentation, community and all the resources available that made this write-up possible.

1. Snowflake Share
2. Data as a Service
3. Secure Data sharing
4. Info-graphic

Disclaimer: This article is AI-assisted. The article structure and idea list are 100% manually curated and researched. I proofread all AI-generated texts to ensure information accuracy and to add some contexts