Analyzing PHI & PII with Snowflake’s Data Clean Rooms

Introduction – Data Clean Rooms:


Sharing data can be tough. Organizations struggle to effectively manage their data internally. This problem only increases in magnitude for data sharing across multiple organizations. To make matters worse, regulations on sensitive data (PII and PHI) further complicate the process and this type of analysis is decided to be too troublesome to engage upon.

However, that’s a huge loss of opportunity. Since the business intelligence insights gained from analyzing personal health and sales datasets can be transformative to your business direction and decisions. Luckily, there is now a way to gain access to and analyze this data while abiding by security laws.


Data Clean Room:


A data clean room is a place to perform joint analysis on sensitive data while abiding by regulations. The clean room can be set up by anyone but it will most likely be the provider. A clean room can have multiple data providers. Each provider can control:

  • Incoming Data
  • How their data can be joined with other data
  • Types of analytics that can be performed on their data
  • Outgoing data

This type of data hosting and analysis is made possible by several key Snowflake features: secure data shares, the data marketplace platform,  secure functions, and secure join capabilities.

A consumer in the data clean room can access the provider’s data through defined functions, joins, and queries the provider specifies. Data masking (hashing) can provide an extra layer of security so that no naked identifiable data is ever transferred between provider and consumer.



Case Analysis – Advertising to the NBA:


I am the lead marketer for Weight Loss Champions. We sell weight loss pills. We want to launch an advertising campaign targeting NBA All-Stars to use and endorse our product.

Company: Weight Loss Champions

Product: Weight Loss Pills

Marketing Hypothesis: Quarantine has forced a lot of people indoors. Gyms are closed. Many people are drinking and not exercising and have put on weight. We see this as a great opportunity for our business. We also realize that the NBA 2020 Season is set to restart in the Orlando bubble at the end of July.

Objective: Our goal is to get an NBA All-Star to use our weight loss pills. Then try to get them to endorse our product.

Strategy: We have identified several places that these athletes will frequent, such as shopping centers, and restaurants. After doing a little digging, we discovered walkways between these areas that offer ad placements. We must decide what height on these walls to place our ads.


Let’s take a look at how we can accomplish this. The first step is establishing our data clean room.

How to Build Your Own Data Clean Room

The approximate time to create is 8 hours. It took me 2 hours to submit everything to Snowflake and then 6 hours working with them to get the room up. With this guide, I imagine you can do it even faster.

Make a Snowflake Account

Each participant will need a Snowflake account. Existing Snowflake customers can provide a secure sub-account. You can sign up for a 30-day free trial with $400 worth of credit here.

Submit a Support Case 

To submit a support case you will need to create an account on the Snowflake community and link it to your Snowflake account. Once you create your account, follow these instructions to submit a case. Here’s what the case should look like.


Data Clean Rooms 2


After submitting your case, a Snowflake representative will contact you to go through the details and set up the exchange. This could take a couple of hours.


Create a Secure Share

A listing on your private data exchange comes from a secure share. When creating the secure share, you will specify what databases, schemas, and functions. A secure share can be made through the share tab, toggling to the outbound option, and then selecting create.


Data Clean Room 2

Read the full guide to Secure Shares here.


Create a New Listing

Navigate to the Data Marketplace and open your private exchange. On the left side menu, it will be under Data -> Manage. From there you can create a new Listing by clicking the button on the upper right and selecting the Secure Share from before.


Data Clean Room 2


The provider should describe their data set here and provide documentation on how the data can be analyzed. This can be an ongoing process between the provider and the consumer. Where the consumer requests a specific type of analysis and the provider then creates a secure function or share to fulfill that type of analysis.


Configure Roles and Access:


Navigate to the Data Marketplace and click on the Admin tab on the left-hand side. Click on your Private Data Exchange to configure the roles. Here you can add members and specify which accounts can be providers, consumers, and Administrators.


Data Clean Room 3

Analyze your Data:


From here, the consumer can log in to their account. They will be able to access the share and all the available schemas, tables, and functions provisioned by the provider. Here you can take a look at the share I created for a client and the functions made available. We’ll be using them later in the analysis.


Data Clean Room 4

To learn this and other features, feel free to check our blog for more information.