Snowflake Data Clean Rooms: Consumer API reference guide¶
The following content details all the developer APIs provided by Snowflake Data Clean Rooms for consumers. All functions reside inside the following schema:
samooha_by_snowflake_local_db.consumer
Set up environment¶
Execute the following commands to set up the Snowflake environment before using developer APIs to work with a Snowflake Data Clean Room. It you don’t have the SAMOOHA_APP_ROLE role, please contact your account administrator.
use role samooha_app_role;
use warehouse app_wh;
Assign a name for the clean room that the provider has shared with you:
set cleanroom_name = 'Test Cleanroom 1';
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>');
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);
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);
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)
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)
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);
Add datasets¶
consumer.link_datasets¶
Description: A user can link a Snowflake table into the clean room and share it by creating a secure view of the table from within the clean room.
Input: cleanroom_name (string), tables_list (array)
Output: success message (string)
Example:
call samooha_by_snowflake_local_db.consumer.link_datasets($cleanroom_name, ['SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS']);
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']);
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']);
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');
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');
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'
)
);
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);
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);
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);
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);
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);
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);
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);
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();
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);
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);
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');
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');
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']);