Blogs / Snowflake / Build your own data exchange using Snowflake

Build your own data exchange using Snowflake

Jul 16, 2021

SHARE

Data catalog has become the buzzword of data governance. It helps in data democratization and data discovery. Plenty of open source and SaaS offerings provided data cataloging. However, you can create your own data hub out of the box with Snowflake and is available from the standard edition. The data exchange lets you share within your organization, your partners, or even monetize your data.

This blog will combine row-level security and data exchange to create a secure data hub for your enterprise. For details on how to set up data exchange, refer to Snowflake documentation.

Business Scenario 

You get sales data from different sources such as store sales from POS machines, B2B sales from SAP, online sales from e-commerce websites into Snowflake. You would like to share online sales data with your marketing team to create targeted offers and B2B sales with customers to have visibility on the order status. In addition to the sales data, your enterprise wants to set up a data hub with listings of all data that is available for use.  You want to ensure that the security use cases are met for the published data set.

Build your own data exchange using Snowflake
Fig 1 – Typical data exchange setup in Snowflake

Creating Secure Views

The first step would be to identify all necessary data and create secure views for sharing.

  1. You should create three views – V_POS_SALES, V_ECOMM_SALES, and V_B2B_SALES by joining all the required tables.
  2. Look at our blog on “Customizing Row-Level Security in Snowflake” to understand how secure views are built to enforce row-level security based on roles. The same can be extended for accounts.
  3. Create a table AUTH_ACCOUNT that contains the list of accounts and access restrictions to these data sets. As you could see below account, VISUALBI has access to all views. RX17834 – the marketing account of your organization has access only to e-comm sales. WA06555 – the account in the customer organization has access only to B2B sales, specifically for customer number 1199476.
Build your own data exchange using Snowflake
Fig 2 – Sample of entries maintained in AUTH_ACCOUNT table

4. Run the procedure to create three secure views – V_POS_SALES_AUTH_VIEW, V_ECOMM_SALES_AUTH_VIEW, and V_B2B_SALES_AUTH_VIEW. This ensures that accounts that have access setup in the table can only access the data in the view.

Publish a Listing

Now, let us step into the data provider’s shoes and understand how to publish a listing.

  1. Create a Data Exchange of your own.
  2. Then, set up a provider profile. You can do so by navigating to Shared Data -> Manage Exchanges and selecting your exchange. Switch to the Provider Profiles tab and click on Add Profile button. A sample provider profile is given below.
Build your own data exchange using Snowflake
Fig 3 – Creating provider profile in Snowflake data exchange

Build your own data exchange using Snowflake
Fig 4 – Sample provider profile in Snowflake data exchange

3. To create a listing, navigate to Shared Data and select the Share Data button. Choose the name of your exchange. Provide a name for the listing and choose Free and instant access

Build your own data exchange using Snowflake
Fig 5 – Creating a listing in Snowflake data exchange

4. Create three standard listings – for POS sales, ECOMM sales, and B2B sales, as discussed in the business scenario. A sample of details to be provided is given below.

Build your own data exchange using Snowflake
Fig 6 – Sample listing in Snowflake data exchange

Note: Data exchange is not enabled for all accounts. Hence raise a support case with Snowflake support to enable the same

Consuming a Listing

Let us now take a look at how different types of consumers can access these listings.

  1. To be able to view the listing, you should add the Snowflake account as a member. You can do so by navigating to Shared Data -> Manage Exchanges and selecting your exchange. Switch to the Members tab and click on Add Member button. Provide the consumer’s Snowflake account link and select the Consumer exchange role.
Build your own data exchange using Snowflake
Fig 7 – Adding a consumer to Snowflake data exchange

2. A consumer can browse all three listings and get access to the data by clicking on the Get Data button and creating a new database. However, access is restricted based on what is maintained in the AUTH_ACCOUNT table.

Build your own data exchange using Snowflake
Fig 8 – Consumer getting access to a listing in Snowflake data exchange

3. Consider the marketing team, where access is provided only for e-comm data. Even though they can view and create databases from other shares, they will only be able to access data from the e-comm view

Build your own data exchange using Snowflake
Fig 9 – Marketing team’s query result set in their Snowflake account for e-comm view

Build your own data exchange using Snowflake
Fig 10 – Marketing team’s query result set in their Snowflake account for POS sales view

4. The customer account can access only B2B sales data and, in particular, only for their orders.

Build your own data exchange using Snowflake
Fig 11 – Customer account’s result set in their Snowflake Account for B2B Sales View

5. Finally, all users of your account VSUALBI can look at the listings published and request access to the data from the ACCOUNTADMIN.

Thus, we have created a data hub that caters to multiple consumer groups and yet is secure. It also helps to break down the data silo problem and makes your data discoverable. All of these advantages without having to pay an extra penny. Isn’t that cool ?! In the upcoming blog, we shall look at the best practices to be followed when creating your data exchange.

To learn more about Snowflake, check out these blogs. Reach out to us here today if you are interested in evaluating if Snowflake is suitable for you.

Learn more about Visual BI’s Snowflake offerings here.


Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com


Copyright © Visual BI Solutions Inc.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

Share This!

Share this with your friends and colleagues!