Tutorial: Create and run a clean room using the clean rooms UI and two accounts¶

Introduction¶

Clean rooms enable users to share data with a collaborator while maintaining the privacy of the data by tightly controlling what can be done with it. This tutorial leads you through the basic flow of using the clean rooms UI to work with a Snowflake Data Clean Room with two test accounts, which enable the full functionality of clean rooms. If have access to only a single Snowflake account with clean rooms installed, you can try the single-account tutorial instead.

What you will learn¶

In this tutorial, you will learn how to use the clean rooms UI by doing the following tasks:

Add a collaborator to your clean rooms environment.
Create a clean room, including how to add data, specify join policies, define which type of analysis a collaborator can run on the data, and share the clean room with a collaborator.
Install a clean room, add data, and define how this data is joined with the collaborator’s data.
Run an analysis.
Activate the results of the analysis.

About clean room collaborators¶

Clean room collaborators are either providers or consumers:

A provider is the account that creates and configures the clean room. In a typical clean room, the provider adds all the SQL templates that the consumer can run in the clean room. The provider adds data, sets usage restrictions on it, and invites consumers, who can join the clean room to run the templates.
A consumer is the account invited to participate in the clean room. The consumer adds their own data and runs the templates on the clean room data, according to the limitations set by the provider.

Prerequisites¶

You must have access to two Snowflake environment with the Snowflake Data Clean Rooms UI installed: one to use as a provider, and the other to use as a consumer. You must either install the environments yourself, or ask an administrator to grant you access to the clean rooms UI in a Snowflake account.
This tutorial uses a sample table named DEMO_CUSTOMERS_2. Either search Snowsight for the table SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS_2, or run the following SQL command in your Snowflake account to confirm that you have this table installed:
```
SHOW TABLES LIKE 'CUSTOMERS_2' IN SCHEMA SAMOOHA_SAMPLE_DATABASE.DEMO;
```
Copy
If the response has no rows, then you, or someone with ACCOUNTADMIN role, must run the following command to install the sample table:
```
USE ROLE ACCOUNTADMIN;
EXECUTE IMMEDIATE FROM @SAMOOHA_BY_SNOWFLAKE.APP_SCHEMA.MOUNT_CODE_STAGE/dcr_loader.sql;
```
Copy

Provider: Sign in to the clean rooms UI¶

Provider: Add the consumer as a collaborator¶

In this section you will add the consumer account that you are using for this tutorial as a collaborator. Administrators must define someone as a collaborator before other users can share a clean room with that collaborator.

To add the consumer as a collaborator:

In the left navigation, select Collaborators.
Select the Snowflake Partners tab.
Select + Snowflake Partner.
In the Company Name field, enter Tutorial Consumer.
In the Email Address field, enter the email associated with your clean room user.
In the Account Locator field, enter the account locator of the Snowflake account that you are using as a consumer.
Select the cloud and region of the account that hosts your consumer account.
Select Add.

Provider: Create and share a clean room¶

In this section, you will do the following:

Create a clean room.
Add data to the clean room that is shared with collaborators.
Define a join policy, which controls which columns a collaborator can join with their own data.
Define which types of analysis a collaborator can run in the clean room.
Share the clean room with the consumer.

Start the creation process¶

To begin the process of creating a clean room:

In the left navigation, select Clean Rooms.
On the Clean Rooms page, select + Clean Room.
Name your clean room Tutorial.

You will allow collaborators to run an audience overlap analysis in the clean room.

Add data to your clean room¶

To add data to your clean room:

In the Datasource section, select Snowflake.
From the Tables drop-down list, select the SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS table.
Select Next.

Specify a join policy¶

A join policy specifies which columns of your data a collaborator can join on.

To specify a join policy:

From the Join Columns drop-down list, select the following columns:
- HASHED_EMAIL
- HASHED_FIRST_NAME
- HASHED_LAST_NAME
- HASHED_PHONE
A collaborator can join their data with these columns only.
Select Next.

Configure an analysis template¶

Analysis templates control how a collaborator can access the shared data in a clean room. Collaborators can only run analyses and queries that conform to the template.

Select and configure an analysis template for clean room collaborators:

Select the Audience Overlap & Segmentation template.

Collaborators are limited to running only the analyses that you select.
From the Tables drop-down list, select DEMO.CUSTOMERS.

Collaborators can only analyze data in the DEMO.CUSTOMERS table.
From the Segmentation & Activation Columns drop-down list, select the following columns:
- AGE_BAND
- DEVICE_TYPE
- EDUCATION_LEVEL
- STATUS
A consumer can filter and create segments using these columns.
Enable Allow categorical value previews during filtering.
Select Next.

Consumer: Sign in to the clean rooms UI¶

In this section, you switch from being the provider who created and shared the clean room to acting as the consumer to install and run the clean room.

Consumer: Install and configure the clean room¶

In this section you will do the following actions:

Install the clean room that was shared with you from the provider account.
Add data to the clean room so it can be joined with the provider’s data.
Add a join policy to define how the consumer data and the provider data are related.
Define the columns that analysts can use to create segments, filter results, and enrich activation data.

Start the installation process¶

To start installing a clean room that has been shared by the provider account:

In the left navigation, select Clean Rooms.
Select the Invited tab.
Find the Tutorial tile, and select Join.

Add consumer data to the clean room¶

To add data to the clean room:

In the Datasource section, select Snowflake.
From the Tables drop-down list, select and then save SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS_2.
- If this table is not in your list, see the Prerequisites section in the tutorial introduction to learn how to install it.
Select Next.

Define a join policy¶

Next, specify which consumer columns are joinable in an analysis or query in this clean room:

In the Specify Join Policies pane, in the Join Policies section, choose columns from your data (labeled My Columns) and equivalent columns from the provider’s table (labeled Collaborator Columns).
Ensure that the columns from the consumer’s table (My Columns) and the columns from the provider’s table (Collaborator Columns) match in content (the column names don’t need to match).

For example, the consumer’s HASHED_EMAIL column should be joined with the provider’s HASHED_EMAIL column. All joined columns must match in the analysis that you select.
Select Next to navigate to the Configure Analysis & Query pane.

Define the segmentation and activation columns¶

When you select segmentation and activation columns during the clean room installation process, you define which columns are available to users running an analysis in the clean room. Analysts can create segments based on only these columns. When you send activation data back to the provider, analysts cannot enrich the results of the analysis with data unless it comes from one of these columns.

To define the segmentation and activation columns:

Select and then save the DEMO.CUSTOMERS_2 table from the Tables drop-down list.
From the Segmentation & Activation Columns drop-down list, select and then save the following columns:
- INCOME_BRACKET
- REGION_CODE
- STATUS
Select Finish to install the clean room.

Installation takes a few minutes to complete.
Select Refresh every few minutes to check for changes.

When the tile label changes from Processing to a Run button, the clean room is installed and you can run an analysis.

Consumer: Run an analysis¶

In this step, you run an audience overlap and segmentation analysis in the clean room. You must first select the data to use in the analysis.

To configure and run an analysis:

In the Joined tab, find the clean room tile and then select Run.
Select Audience Overlap & Segmentation » Proceed.
In My Tables, select CUSTOMERS.
In Collaborator’s Tables, select CUSTOMERS.
In Required Parameters » My Join Columns, define the following joins:
1. From the drop-down list, select and then save HASHED_EMAIL.
2. Select + Join Column, then select HASHED_FIRST_NAME and HASHED_LAST_NAME.
3. Select + Join Column, then select HASHED_PHONE.
When you run an analysis in the clean room, results include records where any of the following items are true:
- The consumer’s HASHED_EMAIL matches the provider’s HASHED_EMAIL.
- The consumer’s HASHED_FIRST_NAME matches the provider’s HASHED_FIRST_NAME and the consumer’s HASHED_LAST_NAME matches the provider’s HASHED_LAST_NAME.
- The consumer’s HASHED_PHONE matches the provider’s HASHED_PHONE.
In the User Segmentation section, perform the following steps:
1. From the My Columns drop-down list, select INCOME_BRACKET.
2. From the Collaborator Columns drop-down list, select AGE_BAND.
The results of the analysis are grouped into these segments.
In the Filters section, use the drop-down lists to specify CUSTOMERS.STATUS = GOLD.

This limits analysis results to results where STATUS = GOLD.
Select Run.

You can optionally choose a different warehouse size to run the analysis by changing the Warehouse drop-down selection.
In the Analyses & Queries page, when the status of your analysis is Completed:
1. Select the analysis to see your results.
2. Scroll to the Results section of the page. You can toggle the results to see either overlap or non-overlap rates.
3. To see the segmentation groups of your analysis, select Download, and then open the comma-delimited file.
Continue to the next step to activate (send) enriched results to the consumer’s Snowflake account.

Consumer: Activate the results¶

In this step you will activate the results of your analysis to the provider’s Snowflake account. These results are enriched with data from the consumer and provider tables.

To activate the results of the analysis:

In the Results section, select Activate.
Select the name of the provider account you used to share the clean room.
In the Segment Name field, specify My test segment, or another unique name for this result set.
From the ID Columns drop-down list, select HASHED_EMAIL.
From the Attribute Columns drop-down list, select Select All.

When the provider looks at the results of the analysis, the matched records will be enriched with the additional data found in these columns.

The available columns are the same as the segmentation and activation columns that you selected as the consumer when you installed the clean room.
Select Push Data.

Congratulations! You have now installed and configured a clean room in a consumer account, run an analysis, and pushed the results back to the provider account for activation.

Provider: View the activated data¶

In the previous step you activated to the provider’s Snowsight account. Provider activation data is stored in the SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.PUBLIC.PROVIDER_ACTIVATION_SUMMARY table of the provider’s Snowflake account.

Prerequisite

The first time a consumer activates data to a provider’s account, the provider must sign in to the clean rooms UI for about 30 minutes after the consumer has activated data. This is needed only once per clean room per consumer account. Later activations by the same consumer in the same clean room do not need this step. To activate data to the provider’s account you must create a pipeline between the consumer account and the provider account.

After this prerequisite step, you can view the activated data in your provider account using either Snowsight or SQL:

Sign in to Snowsight for the provider account. (Use the Snowflake UI, not the clean room UI.) environment.
In the navigation menu, select Data » Databases.
Navigate to SAMOOHA_BY_SNOWFLAKE_LOCAL_DB » PUBLIC » Tables » PROVIDER_ACTIVATION_SUMMARY.
Select Data Preview to view the activation data.

Sign in to Snowsight for the provider account. You are signing in to the Snowflake account, not the clean room environment.
Open Projects » Worksheets.
Select + » SQL Worksheet.
In the new worksheet, paste and run the following statement to list the activation data that was pushed from the consumer’s clean room environment. Substitute the segment name that you entered when you ran the activation in the clean rooms UI.
```
SELECT *
   FROM SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.PUBLIC.PROVIDER_ACTIVATION_SUMMARY
   WHERE segment = '<your segment name>';
```
Copy

Clean up¶

You can delete the clean room and activation data that you created for this tutorial to clean up your production environment.

Delete the activation data¶

To delete the activation data from the provider’s Snowflake account:

Sign in to Snowsight for the provider account. You are signing in to the Snowflake account, not the clean room environment.
Open Projects » Worksheets.
Select + » SQL Worksheet.
In the new worksheet, paste and run the following statement to delete the activation data created for this tutorial. Substitute your custom segment name in the location indicated:
```
DELETE FROM SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.PUBLIC.PROVIDER_ACTIVATION_SUMMARY
   WHERE segment = '<your segment name>';
```
Copy

Delete the clean room¶

Deleting a clean room in the provider account removes it from both the provider account and the consumer account.

To delete a clean room:

Sign in to the clean rooms UI.
In the left navigation, select Clean Rooms.
On the Created tab, find the Tutorial tile and select the More icon ().
Select Delete.
Select Proceed.

Learn more¶

Congratulations! You have now used the clean rooms UI to create and share a clean room as a provider. You have also acted as the consumer who is using the clean room to analyze data within a privacy-preserving environment.

You can use the following resources to learn more:

For general information, see About Snowflake Data Clean Rooms.
For more information about the clean rooms UI, see Clean rooms UI overview.
For information about using the developer APIs to work with a Snowflake Data Clean Room programmatically, see Snowflake Data Clean Rooms developer’s guide.