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.

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.

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.
   

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

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.

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.
   

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.

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

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

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.