Snowflake — Data Sharing

Thanks for reading my earlier blog. In this blog, we are going to learn one of the most distinguishing feature of Snowflake.

In a typical use case of data warehouse/data lake implementation we have to create datasets/database or data layer to share across teams. We typically have various stakeholders/teams accessing data from centralized data platform. We create a target layer/data layer/schema to create tables/datasets to be utilized/consumed by teams. With this kind of implementation, we have to build additional pipelines to maintain state of table to ensure data availability and consistency. Data consistency becomes of major challenge in such kind of implementation.

Snowflake feature — Data Sharing is feature which allows to create/share objects required to be accessed or utilized by other teams. Data sharing enables users to share snowflake data objects like tables, external tables, secure views, secure UDFs, materialized views etc. Data sharing is implemented between providers and consumers. Providers are Snowflake users while Consumers can be Snowflake as well as non-snowflake users. This feature enables users/consumers to access data shares.

How Data Sharing works?

With Secure Data Sharing, no actual data is copied or transferred between accounts. All Shares/data sharing is accomplished through Snowflake’s unique services layer and metadata store. This share does not take up any storage in Consumer account and hence does not contribute any storage cost to Consumer. Consumer accounts are charged only for Compute cost used to query shared data. No data copied or exchanged between accounts hence data sharing setup is quick and easy.

What provider does?

The provider creates a share of a database in their account and grants access to specific objects in the database. The provider can also share data from multiple databases, as long as these databases belong to the same account. One or more accounts are then added to the share, which can include your own accounts (if you have multiple Snowflake accounts).

What Consumer does?

On the consumer side, a read-only database is created from the share. Access to this database is configurable using the same, standard role-based access control that Snowflake provides for all objects in the system.

How does sharing architecture looks like?

Through this architecture, Snowflake enables creating a network of providers that can share data with multiple consumers (including within their own organization) and consumers that can access shared data from multiple providers:

What is Share?

Shares are snowflake objects that encapsulates required information required to be shared with consumers. Each share consist of –

· The privileges that grant access to the database(s) and the schema containing the objects to share.

· The privileges that grant access to the specific objects in the database.

· The consumer accounts with which the database and its objects are shared.

Once a database is created (in a consumer account) from a share, all the shared objects are accessible to users in the consumer account:

How objects are reflected and controlled in Shares?

Shares are secure, configurable, and controlled 100% by the provider account. New objects added to a share become immediately available to all consumers, providing real-time access to shared data. Access to a share (or any of the objects in a share) can be revoked at any time.

What are non-snowflake accounts? Reader account or third-party accounts?

Data sharing is enabled between snowflake accounts. When we need to share data between non-snowflake users we can create reader account or third party account. These reader accounts can be created by providers and share with consumers. Consumer gets snowflake link, user and details to access data shares.

Each reader account belongs to provider account. Query cost as well as storage cost is to be owned by provider account. Provider can create warehouse, set limit and share with consumer/reader account. Reader accounts can run only select query on top of data shares. These users can not run any of DML queries on these shares.

How to create reader accounts?

ACCOUNTADMIN or user with CREATE ACCOUNT privileges can create reader account. These can be created using Snowflake web UI -> Account

Go to Account -> Reader Accounts -> Create Reader Account as shown in snapshot -

Once you click on reader account, provide details to create reader account –

Once account created, we get congratulations message with account URL and details.

These details can be shared with reader account and consumers can connect to snowflake instance and access data shares.

What are limitations of data shares?

Shared databases have following limitations for consumers-

· Shared databases are read-only. Users in a consumer account can view/query data, but cannot insert or update data, or create any objects in the database.

· The following actions are not supported:

o Creating a clone of a shared database or any schemas/tables in the database.

o Time Travel for a shared database or any schemas/tables in the database.

o Editing the comments for a shared database.

· Shared databases and all the objects in the database cannot be forwarded (i.e. re-shared with other accounts).

How can we view Data shares available/created in account?

Once you logon to snowflake web UI, go to shares. There are 2 types of shares — Inbound and Outbound shares . Inbound shares are the shares shared with snowflake account user. Outbound shares are shares created by snowflake account to be shared with consumer accounts.

How can we create Data Shares?

Shares can be created using Web UI or SQL execution to create data share. Provide Share name , select database , views to be shared. Database objects need to be granted with specific access privileges to create data share

I believe, by now you know one of distinguishing feature of Snowflake — data sharing. We will learn more about data protection — time-travel in detail with upcoming blogs.

About Me :

I am DWBI and Cloud Architect! I am currently working as Senior Data Architect — GCP, Snowflake. I have been working with various Legacy data warehouses, Bigdata Implementations, Cloud platforms/Migrations. I am SnowPro Core certified Data Architect as well as Google certified Google Professional Cloud Architect. You can reach out to me LinkedIn you need any further help on certification, Data Solutions and Implementations!