Snowflake Data Clean Rooms: Consumer API reference guide

The following content details all the supported APIs on consumer accounts provided by the developer edition of a Snowflake Data Clean Room. All functions reside inside the following schema:

samooha_by_snowflake_local_db.consumer
Copy

Set up 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

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

set cleanroom_name = 'Test Cleanroom 1';
Copy

Install clean room

Install the clean room that the provider has shared via the following commands:

consumer.install_cleanroom

Description: Installs the clean room on the consumer account with the associated provider and selected clean room.

Input: cleanroom_name (string), provider_account_locator (string)

Output: success message (string)

Example:

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

consumer.is_enabled

Description: Once the clean room is installed, it takes about 1 minute for the provider to finish setting it up and enable it on their side. This function allows the user to check the status of the clean room, and see if it is enabled or not. The flag will usually switch to True after about a minute after installing the clean room.

Input: cleanroom_name (string)

Output: is enabled (boolean)

Example:

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

consumer.uninstall_cleanroom

Description: Uninstalls the clean room on the consumer account. This removes all databases associated with the clean room, including the shared clean room database. Note the clean room can always be installed again with consumer.install_cleanroom.

Input: cleanroom_name (string)

Output: success message (string)

Example:

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

Set up Provider Run Analysis

library.is_provider_run_enabled

Description: Checks if this clean room has Provider Run Analysis enabled. Note: explicit approval still needs to be given by calling consumer.enable_templates_for_provider_run (see below).

Input: cleanroom_name (string)

Output: enabled message (string)

Example:

call samooha_by_snowflake_local_db.library.is_provider_run_enabled($cleanroom_name)
Copy

library.is_consumer_run_enabled

Description: Checks if this clean room has Consumer Run Analysis enabled. This flag determines if the clean room consumer (installer) can run analyses, or else act as a data-contributor to the collaboration.

Input: cleanroom_name (string)

Output: enabled message (string)

Example:

call samooha_by_snowflake_local_db.library.is_consumer_run_enabled($cleanroom_name)
Copy

consumer.enable_templates_for_provider_run

Description: If a clean room has Provider Run Analysis enabled (i.e. the provider of the clean room has marked the clean room to allow providers to run analysis), this procedure must be called by the consumer to enable them. This procedure is needed to give explicit approval on a template-by-template basis to providers who want to run analysis.

The final boolean parameter allows the consumer to enable differential privacy for the provider’s analyses, if set to TRUE.

Input: cleanroom_name (string), template_names (array), enable_differential_privacy(boolean)

Output: success message (string)

Example:

call samooha_by_snowflake_local_db.consumer.enable_templates_for_provider_run($cleanroom_name, ['prod_overlap_analysis'], FALSE);
Copy

Add datasets

Setting security policies for Provider Run Analysis

consumer.set_join_policy

Description: Specifies which columns the provider is allowed to perform a join on when running templates within the clean room, when using Provider Run Analysis. 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 current one.

Note that the checks are carried out by parsing the SQL query to be run against the data for any unauthorized columns. Queries with wildcards might not be caught using these checks, and discretion should still be used when designing the analysis template.

Input: cleanroom_name(string), table_and_col_names(array)

Output: success message (string)

Example:

call samooha_by_snowflake_local_db.consumer.set_join_policy($cleanroom_name, ['SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS:EMAIL', 'SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.EXPOSURES:EMAIL']);
Copy

consumer.set_column_policy

Description: Sets which columns in the data the provider can carry out operations on. This should only be called after adding the template. This is also a function of the template, so inputs need to be of the form template_name:full_table_name:column_name. Note that the column policy is replace only, so if the function is recalled, then the previously set column policy is completely replaced by the current one.

Column policy should not be called on identity columns like email. It should only be used on aggregate and the group by columns.

Note that the checks are carried out by parsing the SQL query to be run against the data for any unauthorized columns. Queries with wildcards might not be caught using these checks, and discretion should still be used when designing the analysis template.

Checks are carried out on SQL Jinja arguments called dimensions or measure_columns. Please make sure you use these tags to enable this check.

Input: cleanroom_name(string), analysis_and_table_and_columns(array)

Output: success message (string)

Example:

call samooha_by_snowflake_local_db.consumer.set_column_policy($cleanroom_name,
['prod_overlap_analysis:SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS:STATUS',
 'prod_overlap_analysis:SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS:AGE_BAND',
 'prod_overlap_analysis:SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS:DAYS_ACTIVE',
 'prod_overlap_analysis:SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.EXPOSURES:CAMPAIGN']);
Copy

Analysis provider templates

The following commands allow users to run the analysis templates available in the clean room.

consumer.view_template_definition

Description: The clean room template definitions are available to help determine which parameters need to be passed to the template.

Note

Note that all Samooha procedures are encrypted and aren’t viewable by default. However, any custom templates that you add will be visible to you.

Input: cleanroom_name (string), template_name (string)

Output: The template definition (string)

Example:

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

consumer.get_arguments_from_template

Description: Defines how the data should be organized and what data is required for each template to ensure that the output is easily digestible.

Input: cleanroom_name (string), template_name (string)

Output: Argument list and specification (table)

Example:

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

Run analysis

The following command executes a specific analysis:

consumer.run_analysis

Description: A consumer can run provider analysis templates on selected tables and sets of inputs from the consumer database. The consumer and provider tables need to be specified separately and certain inputs can be empty if they are not required. The template executes logic that has been configured for the specific analysis and returns the joined data as a table. The only optional input is “epsilon”, i.e. how much of the different privacy budget the query needs to consume. This is defaulted to 0.1, if not specified.

Input: cleanroom_name (string), template_name (string), consumer_tables (array), provider_tables (array), analysis_arguments (object)

Output: Template calculations output (table)

Example:

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

  ['SAMOOHA_SAMPLE_DATABASE_NAV2.MYDATA.CONVERSIONS'],  -- Consumer tables
  ['SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.EXPOSURES'],     -- Provider tables

  object_construct(                 -- Rest of the custom arguments needed for the template
    'dimensions', ['p.CAMPAIGN'],   -- always 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.
    'measure_column', ['p.EXP_COST'],
    'measure_type', ['COUNT'],
    'where_clause', 'p.EMAIL=c.EMAIL'
  )
);
Copy

Clean room metadata getter methods

The following methods show relevant properties of the clean room:

consumer.describe_cleanroom

Description: Creates a text summary containing all information about what has been added to the clean room, including templates, datasets, policies, etc.

Input: cleanroom_name(string)

Output: Extensive description string of cleanroom (table)

Example:

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

consumer.view_provider_datasets

Description: Views all datasets that have been added to the clean room by the provider.

Input: cleanroom_name(string)

Output: All the provider dataset names in cleanroom (table)

Example:

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

consumer.view_join_policy

Description: Outlines which columns users can securely join inside a clean room, set by the consumer on consumer datasets.

Input: cleanroom_name (string)

Output: The join policy (table)

Example:

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

consumer.view_provider_join_policy

Description: Outlines which columns users can securely join inside a clean room, set by the provider on provider datasets.

Input: cleanroom_name (string)

Output: The join policy (table)

Example:

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

consumer.view_added_templates

Description: Views all active templates in the clean room.

Input: cleanroom_name (string)

Output: The added templates (table)

Example:

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

consumer.view_column_policy

Description: Views all column policies that have been applied to the clean room by the consumer.

Input: cleanroom_name (string)

Output: The column policy (table)

Example:

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

consumer.view_provider_column_policy

Description: Views all column policies that have been applied to the clean room by the provider.

Input: cleanroom_name (string)

Output: The column policy (table)

Example:

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

consumer.view_cleanrooms

Description: Views all recently created clean rooms sorted by the date they were created on.

Input:

Output: All existing cleanrooms ordered by create date (table)

Example:

call samooha_by_snowflake_local_db.consumer.view_cleanrooms();
Copy

consumer.is_dp_enabled

Description: Checks if differential privacy has been enabled in the clean room.

Input: cleanroom_name(string)

Output: Whether the cleanroom has DP enabled (boolean)

Example:

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

consumer.view_remaining_privacy_budget

Description: Views the privacy budget remaining that can be used to make queries from the clean room. Once exhausted, further calls to run_analysis will not be allowed until the budget is reset. The budget resets daily.

When differential privacy is not enabled, the privacy budget is set to an arbitrarily high level (e.g. 10000) and not depleted.

Input: cleanroom_name (string)

Output: The remaining privacy budget (float)

Example:

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

General helper methods

Here there are a series of helper methods that generally assist in leveraging clean room functionality and supported flows.

consumer.register_db

Description: By adding a database into the clean room, it enables you to link any dataset from the database. If this isn’t called then grants will have to be made to samooha_app_role individually.

Input: db_name (string)

Output: success message (string)

Example:

call samooha_by_snowflake_local_db.consumer.register_db('SAMOOHA_SAMPLE_DATABASE');
Copy

library.unregister_db

Description: Allows you to remove grants granted by register_db to samooha_app_role to a database. This will also remove any database from the UI dropdown.

Input: db_name (string)

Output: success message (string)

Example:

call samooha_by_snowflake_local_db.library.unregister_db('SAMOOHA_SAMPLE_DATABASE');
Copy

library.register_table

Description: This command is similar to register_db, but operates at a table level. An array or string representing the fully qualified table name can be passed in, and grant selects to samooha_app_role are made, enabling the user to link the table into the clean room.

Input: table_name (array)

Output: success message (string)

Example:

call samooha_by_snowflake_local_db.library.register_table(['SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS']);
Copy