Snowflake Data Clean Rooms: Provider Data Analysis

This topic describes the provider and consumer flows needed to programmatically create, share and run an analysis within a clean room. Provider-side data analysis allows consumers to view aggregated insights on the provider’s datasets without joining their data.

The flow described in this topic includes the following tasks:

  1. Provider:

    1. Creating a fresh clean room.

    2. Securely linking datasets to it.

    3. Adding policies governing which columns can be joined on, and used in the analysis.

    4. Enabling a predefined analysis template.

    5. Sharing it with a consumer.

  2. Consumer:

    1. Installing a clean room shared by the provider.

    2. Examining the template provided within the clean room.

    3. Running an analysis within the clean room using the template.

Prerequisites

You need two separate Snowflake accounts to complete this flow. Use the first account to execute the provider’s commands, then switch to the second account to execute the consumer’s commands.

Provider

Note

The following commands should be run in a Snowflake worksheet in the provider account.

Set up the environment

The following commands are required for running the developer edition of a Snowflake Data Clean Room. If you don’t have the following role, please contact your account administrator.

use role samooha_app_role;
use warehouse app_wh;
Copy

Create the clean room

Create a name for the clean room. Enter a new clean room name to avoid colliding with existing clean room names. Note that clean room names can only be alphanumeric. Clean room names cannot contain special characters other than spaces and underscores.

set cleanroom_name = 'Provider Data Analysis Demo Clean room';
Copy

You can create a new clean room with the clean room name set above. If the clean room name set above already exists as an existing clean room, this process fails.

This procedure takes approximately 45 seconds to run.

The second argument to provider.cleanroom_init is the distribution of the clean room. This can either be INTERNAL or EXTERNAL. For testing purposes, if you are sharing the clean room to an account in the same organization, you can use INTERNAL to bypass the automated security scan which must take place before an application package is released to collaborators. However, if you are sharing this clean room to an account in a different organization, you must use an EXTERNAL clean room distribution.

call samooha_by_snowflake_local_db.provider.cleanroom_init($cleanroom_name, 'INTERNAL');
Copy

In order to view the status of the security scan, use:

call samooha_by_snowflake_local_db.provider.view_cleanroom_scan_status($cleanroom_name);
Copy

Once you have created your clean room, you must set its release directive before it can be shared with any collaborator. However, if your distribution was set to EXTERNAL, you must first wait for the security scan to complete before setting the release directive. You can continue running the remainder of the steps and return here before the provider.create_cleanroom_listing step while the scan runs.

In order to set the release directive, call:

call samooha_by_snowflake_local_db.provider.set_default_release_directive($cleanroom_name, 'V1_0', '0');
Copy

Cross-region sharing

In order to share a clean room with a Snowflake customer whose account is in a different region than your account, you must enable Cross-Cloud Auto-Fulfillment. For information about the additional costs associated with collaborating with consumers in other regions, see Cross-Cloud Auto-Fulfillment costs.

In developer edition, enabling cross-region sharing is a two-step process:

  1. A Snowflake administrator with the ACCOUNTADMIN role enables Cross-Cloud Auto-Fulfillment for your Snowflake account. For instructions, see Collaborate with accounts in different regions.

  2. You execute the provider.enable_laf_for_cleanroom command to enable Cross-Cloud Auto-Fulfillment for the clean room. For example:

    call samooha_by_snowflake_local_db.provider.enable_laf_for_cleanroom($cleanroom_name);
    
    Copy

After you have enabled Cross-Cloud Auto-Fulfillment for the clean room, you can add consumers to your listing as usual using the provider.create_cleanroom_listing command. The listing is automatically replicated to remote clouds and regions as needed.

Add analysis templates to the clean room

Add a list of pre-specified templates using their name identifiers. In this flow, you add a predefined template that lets you carry out data analysis on the provider datasets in a secure and provider-approved manner on provider-approved columns.

call samooha_by_snowflake_local_db.provider.add_templates($cleanroom_name, ['prod_provider_data_analysis']);
Copy

If you want to view the templates currently active in the clean room, call the following procedure.

Note

Note that all system-defined preset templates are encrypted and aren’t viewable by default. Any custom templates that you add will be visible, however.

call samooha_by_snowflake_local_db.provider.view_added_templates($cleanroom_name);
Copy

Any template added to the clean room can also be cleared away if needed. See the Provider API Reference Guide for more details.

Set the column policy on each table

Display the data linked to see the columns present inside the table. To view the top 10 rows, call the following procedure.

select * from SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS limit 10;
Copy

Set the columns the consumer can group, aggregate (for example, SUM or AVG) and generally use in an analysis for every table and template combination. This gives flexibility so the same table can allow different column selections depending on the underlying template. This should only be called after adding the template.

Note that the column policy is replace only, so if the function is called again, then the previously set column policy is completely replaced by the new one.

Column policy should not be used on identity columns like email, HEM, or RampID because you don’t want the consumer to be able to group by these columns. In the production environment, the system will intelligently infer PII columns and block this operation, but this feature is not available in the sandbox environment. It should only be used on columns that you want the consumer to be able to aggregate and group by, like Status, Age Band, Channel, or Days Active.

call samooha_by_snowflake_local_db.provider.set_column_policy($cleanroom_name, [
    'prod_provider_data_analysis:SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS:STATUS', 
    'prod_provider_data_analysis:SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS:AGE_BAND', 
    'prod_provider_data_analysis:SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS:DAYS_ACTIVE',
    'prod_provider_data_analysis:SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS:REGION_CODE']);
Copy

If you want to view the column policy that has been added to the clean room, call the following procedure.

call samooha_by_snowflake_local_db.provider.view_column_policy($cleanroom_name);
Copy

Share with a consumer

Finally, add a data consumer to the clean room by adding their Snowflake account locator and account names as shown below. The Snowflake account name must be of the form <ORGANIZATION>.<ACCOUNT_NAME>.

Note

In order to call the following procedures, make sure you have first set the release directive using provider.set_default_release_directive. You can see the latest available version and patches using:

show versions in application package samooha_cleanroom_Provider_Data_Analysis_Demo_clean_room;
Copy
call samooha_by_snowflake_local_db.provider.add_consumers($cleanroom_name, '<CONSUMER_ACCOUNT_LOCATOR>', '<CONSUMER_ACCOUNT_NAME>');
call samooha_By_snowflake_local_db.provider.create_cleanroom_listing($cleanroom_name, '<CONSUMER_ACCOUNT_NAME>');
Copy

Multiple consumer account locators can be passed into the provider.add_consumers function as a comma-separated string, or as separate calls to provider.add_consumers.

If you want to view the consumers who have been added to this clean room, call the following procedure.

call samooha_by_snowflake_local_db.provider.view_consumers($cleanroom_name);
Copy

View the clean rooms that have been recently created via the following procedure:

call samooha_by_snowflake_local_db.provider.view_cleanrooms();
Copy

View more insights on the clean room recently created via the following procedure.

call samooha_by_snowflake_local_db.provider.describe_cleanroom($cleanroom_name);
Copy

A clean room created can also be deleted. The following command drops the clean room entirely, so consumers who previously had access to the clean room will not longer be able to use it. If a clean room with the same name is desired in the future, it must be re-initialized using the above flow.

call samooha_by_snowflake_local_db.provider.drop_cleanroom($cleanroom_name);
Copy

Note

The provider flow is finished at this point. Switch to the consumer account to continue with consumer flow.

Consumer

Note

The following commands should be run in a Snowflake worksheet in the consumer account.

Set up the environment

The following commands are required for running the developer edition of a Snowflake Data Clean Room.

use role samooha_app_role;
use warehouse app_wh;
Copy

Install the clean room

Once a clean room share has been installed, the list of clean rooms available can be viewed using the below command.

call samooha_by_snowflake_local_db.consumer.view_cleanrooms();
Copy

Assign a name for the clean room that the provider has shared with you.

set cleanroom_name = 'Provider Data Analysis Demo Clean room';
Copy

The following command installs the clean room on the consumer account with the associated provider and selected clean room.

This procedure may take ~45 seconds to run.

call samooha_by_snowflake_local_db.consumer.install_cleanroom($cleanroom_name, '<PROVIDER_ACCOUNT_LOCATOR>');
Copy

Once the clean room has been installed, the provider has to finish setting up the clean room on their side before it is enabled for use. The below function allows you to check the status of the clean room. Once it has been enabled, you should be able to run the Run Analysis command below. It typically takes about 1 minute for the clean room to be enabled.

call samooha_by_snowflake_local_db.consumer.is_enabled($cleanroom_name);
Copy

Run the analysis

Now that the clean room is installed, you can run the analysis template given to the clean room by the provider using a “run_analysis” command. You can see how each field is determined in the sections below.

Note

Before running the analysis, you can alter the warehouse size, or use a new, bigger, warehouse size if your tables are large.

-- Example run analysis procedure with single provider dataset

call samooha_by_snowflake_local_db.consumer.run_analysis(
  $cleanroom_name,                    -- cleanroom
  'prod_provider_data_analysis',      -- template name

  [],                                 -- consumer tables - this is empty since this template operates only on provider data
  
  ['SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS'],    -- the provider table we want to carry out analysis on

  object_construct(                        -- The keyword arguments needed for the SQL Jinja template
      'dimensions', ['p.STATUS'],          -- Group by column

      'measure_type', ['COUNT'],           -- Aggregate function you want to perform like COUNT, AVG, etc.

      'measure_column', ['p.DAYS_ACTIVE'], -- The column that you want to perform aggregate function on

      'where_clause', 'p.REGION_CODE=$$REGION_10$$'  -- Acts as a filter to consider only certain records
                                                      -- $$ is used to pass string literals
    )
);
Copy

For each of the columns you need to refer to in either the dataset filtering “where_clause”, or the dimensions or measure_columns, you can use p. to refer to fields in provider tables, and c. to refer to fields in consumer tables. Use p2, p3, etc. for more than one provider table and c2, c3, etc. for more than one consumer table.

Note: In this flow, you can see that the clean room has provider data analysis enabled which means that you can run secure and privatized data analysis on the provider datasets in the clean room. You don’t need to link your dataset. Check the End-to-End: Overlap Analysis flow for an example where both parties can link datasets for joint analysis.

How to determine the inputs to run_analysis

To run the analysis, you need to pass in several parameters to the run_analysis function. This section will show you how to determine what parameters to pass in.

Template names

Firstly, you can see the supported analysis templates by calling the following procedure.

call samooha_by_snowflake_local_db.consumer.view_added_templates($cleanroom_name);
Copy

To run an analysis with a template, you do not know what arguments to specify at this point and what types are expected. So, you can visually look at the template with this, if the template were a custom template.

Note

Note that all system-defined preset templates are encrypted and aren’t viewable by default. Any custom templates that you add will be visible, however.

call samooha_by_snowflake_local_db.consumer.view_template_definition($cleanroom_name, 'prod_provider_data_analysis');
Copy

This can often also contain a large number of different SQL Jinja parameters. The following functionality parses the SQL Jinja template and extracts the arguments that need to be specified in run_analysis into a convenient list

Note

Note that all system-defined preset templates are encrypted, and so this function will not get the arguments for these templates. You will be able to retrieve the parameters for your custom templates, however.

call samooha_by_snowflake_local_db.consumer.get_arguments_from_template($cleanroom_name, 'prod_provider_data_analysis');
Copy

Dataset names

If you want to view the dataset names that have been added to the clean room by the provider, call the following procedure. Note that you cannot view the data present in the datasets that have been added to the clean room by the provider due to the security properties of the clean room.

call samooha_by_snowflake_local_db.consumer.view_provider_datasets($cleanroom_name);
Copy

Dimension and measure columns

While running the analysis, you might want to filter, group by and aggregate on certain columns. If you want to view the column policy that has been added to the clean room by the provider, call the following procedure.

call samooha_by_snowflake_local_db.consumer.view_provider_column_policy($cleanroom_name);
Copy

Common errors

If you are getting the Not approved: unauthorized columns used error as a result of run analysis, you might want to view the join policy and column policy set by the provider again.

call samooha_by_snowflake_local_db.consumer.view_provider_join_policy($cleanroom_name);
call samooha_by_snowflake_local_db.consumer.view_provider_column_policy($cleanroom_name);
Copy

It is also possible that you have exhausted your privacy budget, and so cannot execute any more queries. Your remaining privacy budget can be viewed using the below command. It resets daily, or the clean room provider can reset it.

call samooha_by_snowflake_local_db.consumer.view_remaining_privacy_budget($cleanroom_name);
Copy

You can check if differential privacy has been enabled for your clean room using the following API:

call samooha_by_snowflake_local_db.consumer.is_dp_enabled($cleanroom_name);
Copy