Snowflake Data Clean Rooms: Provider API reference guide¶

provider.cleanroom_init¶

Description: Creates a clean room in your account. You can give the clean room a name that contains alphanumeric characters and spaces only. You also need to pass in the distribution of the clean room to specify whether this is a test clean room you intend to only share within your organization (INTERNAL) or a production clean room that you intend to share externally with other organizations (EXTERNAL).

If a distribution is not specified, then it defaults to INTERNAL.

You can change an INTERNAL clean room to an EXTERNAL clean room by using:

alter application package samooha_cleanroom_<CLEANROOM_ID> SET DISTRIBUTION = EXTERNAL;

Arguments: cleanroom_name(string), distribution(string)

Returns: success message (string)

Example:

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

provider.set_default_release_directive¶

Description: Sets a release directive for the clean room, that is, the version and patch that collaborators will receive.

By default, all clean rooms are created with the following version and patch numbers:

• version: V1_0

• patch: 0

show versions in application package samooha_cleanroom_<CLEANROOM_ID>;

Arguments: cleanroom_name(string), version(string), patch(string)

Returns: success message (string)

Example:

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

provider.drop_cleanroom¶

Description: An existing clean room can be dropped by the creator. This deletes the cleanroom entirely, which means consumers who have the clean room installed can no longer access or use it.

Arguments: cleanroom_name(string)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.drop_cleanroom($cleanroom_name);

provider.register_db¶

Description: Executes with callers rights and allows account administrators to register databases to be visible to the Snowflake Data Clean Room application. The ability to SELECT is granted on all schemas and tables in the database, and to the Snowflake Data Clean Room native application (SAMOOHA_BY_SNOWFLAKE).

Arguments: db_name (string)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.register_db('SAMOOHA_SAMPLE_DATABASE');

library.register_schema¶

Description: Similar to register_db, but operates at a schema level. An array or string representing the fully qualified schema name can be passed in, and grant selects to the SAMOOHA_APP_ROLE role are made, enabling the user to link the objects in the schema into the clean room.

If you want to register a managed access schema (that is, a schema created with the WITH MANAGED ACCESS parameter), use library.register_managed_access_schema instead.

Arguments: schema_name (array)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.library.register_schema(['SAMOOHA_SAMPLE_DATABASE.DEMO']);

library.register_managed_access_schema¶

Description: Similar to register_schema, but registers a schema that was created with the WITH MANAGED ACCESS parameter. An array or string representing the fully qualified schema name can be passed in, and grant selects to the SAMOOHA_APP_ROLE role are made, enabling the user to link the objects in the schema into the clean room.

Arguments: schema_name (array)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.library.register_managed_access_schema(['SAMOOHA_SAMPLE_DATABASE.DEMO']);

library.register_objects¶

Description: Grants the clean room access to tables and views of all types, making them available to be linked into the clean room by calling provider.link_datasets. You can register broader groups of objects by calling library.register_schema, library.register_managed_access_schema, or provider.register_db.

Arguments:

• object_names (array) - Array of fully-qualified object names. These objects can then be linked into the clean room.

Returns: success message (string)

Examples

To register a table and a view:

call samooha_by_snowflake_local_db.library.register_objects(
    ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS','SAMOOHA_SAMPLE_DATABASE.INFORMATION_SCHEMA.FIELDS']);

library.register_table_or_view – Deprecated¶

Description: Registers tables and views of all types.

Arguments: object_names (array), is_view (boolean), is_iceberg (boolean), is_external (boolean), is_under_managed_access_schema (boolean)

Returns: success message (string)

Examples

To register a table:

call samooha_by_snowflake_local_db.library.register_table_or_view(
    ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'],
    false,
    false,
    false,
    false);

To register an Iceberg table:

call samooha_by_snowflake_local_db.library.register_table_or_view(
        ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'], 
        false, 
        true,
        false,
        false);

library.register_table – Deprecated¶

Description: 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 the SAMOOHA_APP_ROLE role are made, enabling the user to link the table into the clean room.

If you want to register tables in a managed access schema (that is, a schema created with the WITH MANAGED ACCESS parameter), use library.register_managed_access_table instead.

Arguments: table_name (array)

Returns: success message (string)

Example:

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

library.register_managed_access_table – Deprecated¶

Description: Similar to register_table, but registers tables in a schema that was created with the WITH MANAGED ACCESS parameter. An array or string representing the fully qualified table name can be passed in, and grant selects to the SAMOOHA_APP_ROLE role are made, enabling the user to link the table into the clean room.

Arguments: table_name (array)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.library.register_managed_access_table(['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']);

library.register_view – Deprecated¶

Description: Similar to register_db, but operates at a view level. An array or string representing the fully qualified view name can be passed in, and grant selects to the SAMOOHA_APP_ROLE role are made, enabling the user to link the view into the clean room.

If you want to register views in a managed access schema (that is, a schema created with the WITH MANAGED ACCESS parameter), use library.register_managed_access_view instead.

Arguments: view_name (array)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.library.register_view(['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']);

library.register_managed_access_view – Deprecated¶

Description: Similar to register_view, but registers views in a schema that was created with the WITH MANAGED ACCESS parameter. An array or string representing the fully qualified view name can be passed in, and grant selects to the SAMOOHA_APP_ROLE role are made, enabling the user to link the view into the clean room.

Arguments: view_name (array)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.library.register_managed_access_view(['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']);

library.unregister_db¶

Description: Reverses the register_db procedure and remove the database-level grants given to the SAMOOHA_APP_ROLE role and Snowflake Data Clean Room native application. This also removes any database from the UI dropdown element.

Arguments: db_name (string)

Returns: success message (string)

Example:

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

library.unregister_schema¶

Description: Unregisters a schema, which prevents users from linking its tables and views into the clean room.

If you want to unregister a managed access schema (that is, a schema created with the WITH MANAGED ACCESS parameter), use library.unregister_managed_access_schema instead.

Arguments: schema_name (array)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.library.unregister_schema(['SAMOOHA_SAMPLE_DATABASE.DEMO']);

library.unregister_managed_access_schema¶

Description: Similar to unregister_schema, but unregisters a schema that was created with the WITH MANAGED ACCESS parameter.

Arguments: schema_name (array)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.library.unregister_managed_access_schema(['SAMOOHA_SAMPLE_DATABASE.DEMO']);

library.unregister_objects¶

Description: Revokes clean room access to tables and views of all types. Objects will no longer be available to any users in any clean rooms managed by this account.

Arguments:

• object_names (array) - Array of fully-qualified object names for which access should be revoked.

Returns: success message (string)

Examples

To unregister a table and a view:

call samooha_by_snowflake_local_db.library.unregister_objects(
    ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS','SAMOOHA_SAMPLE_DATABASE.INFORMATION_SCHEMA.FIELDS']);

library.unregister_table_or_view – Deprecated¶

Description: Unregisters tables and views of all types.

Arguments: object_names (array), is_view (boolean), is_iceberg (boolean), is_external (boolean), is_under_managed_access_schema (boolean)

Output success message (string)

Examples

To unregister a table:

call samooha_by_snowflake_local_db.library.unregister_table_or_view(
    ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'],
    false,
    false,
    false,
    false);

library.unregister_table – Deprecated¶

Description: Similar to unregister_db, but operates at a table level. An array or string representing the fully qualified table name can be passed in to unregister the tables. Users cannot link unregistered tables into a clean room.

If you want to unregister tables in a managed access schema (that is, a schema created with the WITH MANAGED ACCESS parameter), use library.unregister_managed_access_table instead.

Arguments: table_name (array)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.library.unregister_table(['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']);

library.unregister_managed_access_table – Deprecated¶

Description: Similar to unregister_table, but unregisters tables in a managed access schema (that is, a schema created with the WITH MANAGED ACCESS parameter).

Arguments: table_name (array)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.library.unregister_managed_access_table(['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']);

library.unregister_view – Deprecated¶

Description: Similar to unregister_db, but operates at a view level. An array or string representing the fully qualified view name can be passed in to unregister the views. Users cannot link unregistered views into a clean room.

If you want to unregister views in a managed access schema (that is, a schema created with the WITH MANAGED ACCESS parameter), use library.unregister_managed_access_view instead.

Arguments: view_name (array)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.library.unregister_view(['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']);

library.unregister_managed_access_view – Deprecated¶

Description: Similar to unregister_view, but unregisters views in a managed access schema (that is, a schema created with the WITH MANAGED ACCESS parameter).

Arguments: view_name (array)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.library.unregister_managed_access_view(['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']);

provider.link_datasets¶

Description: Links a Snowflake table into the clean room. The procedure automatically makes the table accessible to the clean room by creating a secure view of the table from within the clean room, thereby avoiding any need to make a copy of your table.

The third argument is optional. If you do not use it to specify a list of consumers, all consumers of the clean room can access the specified tables. If you specify consumers, only those consumers can access the specified tables.

Arguments: cleanroom_name (string), tables_list (array), consumer_list (array)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.link_datasets($cleanroom_name, ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS', 'SAMOOHA_SAMPLE_DATABASE.DEMO.EXPOSURES']);

grant reference_usage on database <DB NAME> to share in application package samooha_cleanroom_<cleanroom_name>;

provider.unlink_datasets¶

Description: Removes access to the specified tables in the specified clean room for all users. Specified tables must have been linked by the provider.

Input: cleanroom_name (string), tables_list (array)

Output: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.unlink_datasets($cleanroom_name, ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS', 'SAMOOHA_SAMPLE_DATABASE.DEMO.EXPOSURES']);

provider.restrict_table_options_to_consumers¶

Description: Controls whether a particular consumer can access a table in the clean room.

The second argument is a JSON object, where each name/value pair is the name of a table and an array of consumers who can access it. Each consumer is specified using its account locator.

Input: cleanroom_name(string), access_details(variant)

Output: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.restrict_table_options_to_consumers(
    $cleanroom_name,
    {
        'DB.SCHEMA.TABLE1': ['CONSUMER_1_LOCATOR'],
        'DB.SCHEMA.TABLE2': ['CONSUMER_1_LOCATOR', 'CONSUMER_2_LOCATOR']
    }
);

provider.set_join_policy¶

Description: Specifies which columns the consumer is allowed to perform a join on when running templates within the clean room. 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 examining either the where_clause argument to a SQL Jinja template, or any arguments to which the join_policy filter, has been added. This check looks 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.

By default, checks are carried out on SQL Jinja arguments called where_clause. Make sure you use this tag to enable this check.

Arguments: cleanroom_name(string), table_and_col_names(array)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.set_join_policy($cleanroom_name, ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:EMAIL', 'SAMOOHA_SAMPLE_DATABASE.DEMO.EXPOSURES:EMAIL']);

provider.add_templates¶

Description: Adds a list of pre-defined templates using their name identifiers. Examples include “prod_overlap_analysis”, and “prod_provider_data_analysis”.

Arguments: cleanroom_name(string), template_names(array)

Returns: success message (string)

Example:

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

provider.view_template_definition¶

Description: Given the template name, retrieves the template definition from the clean room. This helps the user visually inspect it and determine which parameters they need to pass when running the template.

Arguments: cleanroom_name (string), template_name (string)

Returns: The template definition (string)

Example:

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

provider.clear_template¶

Description: Removes a specified template (referred to by its name) from the clean room.

Arguments: cleanroom_name(string), template_name(string)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.clear_template($cleanroom_name, 'prod_custom_template');

provider.clear_all_templates¶

Description: Removes all the templates that have been added to the clean room. Once this is called, templates will need to be added again.

Arguments: cleanroom_name(string)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.clear_all_templates($cleanroom_name);

provider.set_column_policy¶

Description: Sets which columns in the data the consumer 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.

The column policy API 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.

By default, checks are carried out on SQL Jinja arguments called dimensions or measure_columns. Make sure you use these tags to enable this check.

Alternatively, you may use the join_policy and column_policy tags in the SQL Jinja template to enforce security policies against custom SQL Jinja arguments.

Arguments: cleanroom_name(string), analysis_and_table_and_columns(array)

Returns: success message (string)

Example:

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

provider.add_custom_sql_template¶

Description: Adds a custom SQL Jinja template into the clean room. This makes the template callable by the consumer.

You can call this API more than once to add multiple custom templates to the clean room. The procedure overwrites any previous template with the same name. If you want to edit an existing template, pass the existing template name as an argument to the API.

If the consumer is using the template to activate results back to the provider, the command must meet the following requirements:

• Name of the custom template must begin with the string activation. For example, activation_custom_template.

• Definition of the template must create a table that begins with cleanroom.activation_data_. For example, CREATE TABLE cleanroom.activation_data_analysis_results AS ... .

• Definition of the template must return the unique part of the table name that was created in the definition, which is the string appended to cleanroom.activation_data_. For example, return 'data_analysis_results'.

In SQL Jinja templates, there are two special arguments:

• source_table: an array of tables from the provider side

• my_table: an array of tables from the consumer side

All provider/consumer tables must be referenced using these arguments since the name of the secure view actually linked to the clean room will be different to the table name. Critically, provider table aliases MUST be p (or p1), p2, p3, p4, etc. and consumer table aliases must be c (or c1), c2, c3, etc. This is required in order to enforce security policies in the clean room.

Furthermore, for the “column_policy” and “join_policy” to carry out checks on the consumer analysis requests, all column names must be referred to as dimensions or measure_columns in the SQL Jinja template. Make sure you use these tags to refer to columns you want to be checked in custom SQL Jinja templates by default.

Alternatively, any argument in a custom SQL Jinja template can be checked for compliance with the join and column policies using the following filters (Note: these filters cannot currently be used with the standard p or c aliases described previously):

• join_policy: Checks if a string value or filter clause is compliant with the join policy

• column_policy: Checks if a string value or filter clause is compliant with the column policy

• join_and_column_policy: Checks if columns used for a join in a filter clause are compliant with the join policy, and that columns used as a filter are compliant with the column policy

For example, in the clause {{ where_clause | sqlsafe | join_and_column_policy }}, an input of p.HEM = c.HEM and p.STATUS = 1 will be parsed to check if p.HEM is in the join policy and p.STATUS is in the column policy.

Note: Only use the sqlsafe filter with caution, it allows collaborators to put pure SQL into the template.

If you do not specify a list of consumers, all consumers can use the custom template. If you use the parameter to specify a list of consumers, only those consumers can use the template in the clean room.

Arguments: cleanroom_name(string), template_name(string), template(string), differential_privacy_sensitivity(float, OPTIONAL), consumer_list(array, OPTIONAL)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name, 'prod_custom_template', 
    $$
select 
    identifier({{ dimensions[0] | column_policy }}) 
from 
    identifier({{ my_table[0] }}) c 
    inner join 
    identifier({{ source_table[0] }}) p 
    on 
        c.identifier({{ consumer_id  }}) = identifier({{ provider_id | join_policy }}) 
    {% if where_clause %} where {{ where_clause | sqlsafe | join_and_column_policy }} {% endif %};
    $$);

provider.restrict_template_options_to_consumers¶

Description: Controls whether a particular consumer can use a template in the clean room.

The second argument is a JSON object, where each name/value pair is the name of a template and an array of consumers who can use it. Each consumer is specified using its account locator.

Input: cleanroom_name(string), access_details(variant)

Output: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.restrict_template_options_to_consumers(
    $cleanroom_name,
    {
        'prod_template_1': ['CONSUMER_1_LOCATOR', 'CONSUMER_2_LOCATOR']
    }
);

provider.list_template_requests¶

Description: Lists all requests from consumers who want to add a consumer-defined template to a clean room. This includes pending, approved, and rejected requests.

Arguments: cleanroom_name (string)

Returns: request_id(string), consumer_identifier(string), template_name(string), template_definition(string), status(string)

Example:

CALL samooha_by_snowflake_local_db.provider.list_template_requests('dcr_cleanroom');

provider.approve_template_request¶

Description: Approves a request to add a template to the clean room. To obtain the <request_id> argument, execute the provider.list_template_requests command to retrieve the UUID of the request.

Arguments: cleanroom_name (string), request_id (string)

Returns: success message (string)

Example:

CALL samooha_by_snowflake_local_db.provider.approve_template_request('dcr_cleanroom', 
    '01b4d41d-0001-b572');

provider.reject_template_request¶

Description: Rejects a request to add a template to the clean room. To obtain the <request_id> argument, execute the provider.list_template_requests command to retrieve the UUID of the request.

Arguments: cleanroom_name (string), request_id (string), reason_for_rejection(string)

Returns: success message (string)

Example:

CALL samooha_by_snowflake_local_db.provider.reject_template_request('dcr_cleanroom',
  '01b4d41d-0001-b572',
  'Failed security assessment');

provider.enable_provider_run_analysis¶

Description: Enables the provider (clean room creator) to run analyses in the clean room (this is disabled by default).

Arguments: cleanroom_name(string), consumer_account_locator(string)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.enable_provider_run_analysis($cleanroom_name, ['<CONSUMER_ACCOUNT_LOCATOR>']);

provider.disable_provider_run_analysis¶

Description: Disables the provider (clean room creator) to run analyses in the clean room (this is disabled by default).

Arguments: cleanroom_name(string), consumer_account_locator(string)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.disable_provider_run_analysis($cleanroom_name, ['<CONSUMER_ACCOUNT_LOCATOR>']);

provider.enable_consumer_run_analysis¶

Description: Enables the consumer (clean room installer) to run analyses in the clean room (this is enabled by default).

Arguments: cleanroom_name(string), consumer_account_locator(string)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.enable_consumer_run_analysis($cleanroom_name, ['<CONSUMER_ACCOUNT_LOCATOR>']); 

provider.disable_consumer_run_analysis¶

Description: Disables the consumer (clean room installer) to run analyses in the clean room (this is enabled by default).

Arguments: cleanroom_name(string), consumer_account_locator(string)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.disable_consumer_run_analysis($cleanroom_name, ['<CONSUMER_ACCOUNT_LOCATOR>']); 

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).

Arguments: cleanroom_name (string)

Returns: 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.

Arguments: cleanroom_name (string)

Returns: enabled message (string)

Example:

call samooha_by_snowflake_local_db.library.is_consumer_run_enabled($cleanroom_name)

provider.set_activation_policy¶

Description: Defines which columns can be used within an activation template. Ensures that only the columns that are approved by the provider can be used with the activation template.

Input: cleanroom_name (string), columns (array)

The columns argument is passed in the format <template_name>:<fully_qualified_table_name>:<column_name>.

Output: Success message

Example:

call samooha_by_snowflake_local_db.provider.set_activation_policy('my_cleanroom', [ 
    'prod_overlap_analysis:SAMOOHA_SAMPLE_DATABASE_NAME.DEMO.CUSTOMERS:HASHED_EMAIL',  
    'prod_overlap_analysis:SAMOOHA_SAMPLE_DATABASE_NAME.DEMO.CUSTOMERS:REGION_CODE' ]);

provider.request_provider_activation_consent¶

Description: Requests that the consumer approve the ability to push analysis results to the provider’s Snowflake account for activation.

Input: cleanroom_name (string), activation_template_name (string)

Output: Success message

Example:

call samooha_by_snowflake_local_db.provider.request_provider_activation_consent(
    'my_cleanroom', 'activation_my_template');

provider.add_template_chain¶

Description: Creates a new template chain. Templates must exist before being added to the template chain.

Arguments: cleanroom_name(string), template_chain_name(string), templates(array of objects)

The JSON object that represents a template can contain the following fields:

• template_name (string) - Specifies the template being added to the template chain. The template must already exist.

• cache_results (boolean) - Determines whether the results of the template are temporarily saved so other templates in the template chain can access them. To cache results, specify TRUE.

• output_table_name (string) - When cache_results = TRUE, specifies the name of the Snowflake table where template results are stored.

• jinja_output_table_param (string) - When cache_results = TRUE, specifies the name of the Jinja parameter that other templates must include to accept the results that are stored in output_table_name.

• cache_expiration_hours (integer) - When cache_results = TRUE, specifies the number of hours before the results in the cache are dropped. When the cache expires, then next time the template chain is executed the cache is refreshed with the results of the template.

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.add_template_chain(
  'collab_clean_room',
  'my_chain',
  [
    {
      'template_name': 'crosswalk',
      'cache_results': True,
      'output_table_name': 'crosswalk',
      'jinja_output_table_param': 'crosswalk_table_name',
      'cache_expiration_hours': 2190
    },
    {
      'template_name': 'transaction_insights',
      'cache_results': False
    }
  ]
);

provider.view_added_template_chains¶

Description: Views the template chains currently active in the clean room.

Arguments: cleanroom_name (string)

Returns: The added template chains (table)

Example:

call samooha_by_snowflake_local_db.provider.view_added_template_chains($cleanroom_name);

provider.view_template_chain_definition¶

Description: Returns the attributes of a template chain.

Arguments: cleanroom_name (string), template_chain_name (string)

Returns: The template chain definition (string)

Example:

call samooha_by_snowflake_local_db.provider.view_template_chain_definition($cleanroom_name, 'insights_chain');

provider.clear_template_chain¶

Description: Removes a specified template chain (referred to by its name) from the clean room.

Arguments: cleanroom_name(string), template_chain_name(string)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.clear_template_chain($cleanroom_name, 'insights_chain');

provider.clear_all_template_chains¶

Description: Removes all the template chains that have been added to the clean room.

Arguments: cleanroom_name(string)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.clear_all_template_chains($cleanroom_name);

provider.submit_analysis_request¶

Description: Running analysis as a clean room provider happens by submitting an analysis request. This analysis request goes through to the clean room, and gets checked against the consumer’s security policies. Once the security checks, and differential privacy layers allow the analysis to go through, the analysis executes within the clean room and the results are stored securely inside the clean room.

When enabling the provider to run analyses, a cryptographic key is added to the clean room which only the provider knows. This key is used to encrypt the results of the analysis, before it transits back to the provider tenant, where it is then decrypted using the secure key. This ensures that no one but the provider can ever see the results of an analysis the provider has requested.

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

Returns: request ID (string)

Example:

call samooha_by_snowflake_local_db.provider.submit_analysis_request(
    $cleanroom_name, 
    '<CONSUMER_ACCOUNT>',
    'prod_overlap_analysis', 
    ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'], 
    ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'], 
    object_construct(       
      'dimensions', ['c.REGION_CODE'],        
      'measure_type', ['AVG'],           
      'measure_column', ['c.DAYS_ACTIVE']                                         
    ));

-- This API returns a request ID that we save into a local variable.
set request_id = '<REQUEST_ID';

provider.check_analysis_status¶

Description: Once an analysis request has been submitted, use this API to check the status of the request. The request can take up to 1 minute after submission to appear. Once done, the status appears as COMPLETED.

Arguments: cleanroom_name (string), request_id (string), consumer_account_locator (string)

Returns: status (string)

Example:

-- It can take up to 2 minutes for this to pick up the request ID after the initial request
call samooha_by_snowflake_local_db.provider.check_analysis_status(
    $cleanroom_name, 
    $request_id, 
    '<CONSUMER_ACCOUNT>'
);

provider.get_analysis_result¶

Description: Once the analysis status appears as COMPLETED for a given request ID, the results for a request_id can be obtained using this API. This takes the results, decrypts then with the secret key created in your account during analysis enablement, and outputs the analysis results.

Arguments: cleanroom_name (string), request_id (string), consumer_account_locator (string)

Returns: analysis results (table)

Example:

call samooha_by_snowflake_local_db.provider.get_analysis_result(
    $cleanroom_name, 
    $request_id, 
    '<CONSUMER_ACCOUNT>'
);

provider.add_consumers¶

Description: Adds consumer accounts to the clean room. Single or multiple consumer account locators and account names are added through string parameters containing comma separated values. You can also add accounts with multiple calls to provider.add_consumers.

Optionally, a boolean flag can be passed in to indicate if differential privacy should be enabled for this clean room. By default, differential privacy is not enabled for any clean room.

Arguments: cleanroom_name(string), consumer_account_locators(string), consumer_account_names(string), enable_differential_privacy(boolean, optional, default: FALSE)

Returns: success message (string)

Example 1:

call samooha_by_snowflake_local_db.provider.add_consumers($cleanroom_name, 'IMA38718', 'PKBYKUM.SAMOOHA_MANAGED_ACCOUNT_DEMO_642778');

In example 1, the account is locator=IMA38718, accountname=PKBYKUM.SAMOOHA_MANAGED_ACCOUNT_DEMO_642778.

Example 2:

call samooha_by_snowflake_local_db.provider.add_consumers($cleanroom_name, 'IMA38718,LEB88915', 'PKBYKUM.SAMOOHA_MANAGED_ACCOUNT_DEMO_642778,PM.CLEANROOMAWSUSWEST21');

In example 2, the first account is locator=IMA38718, accountname=PKBYKUM.SAMOOHA_MANAGED_ACCOUNT_DEMO_642778. The second account is locator=LEB88915, accountname=PM.CLEANROOMAWSUSWEST21.

provider.create_cleanroom_listing – Deprecated¶

Description: Once a clean room has been configured, creates a private listing with it on the Snowflake marketplace and share it with the specified collaborators.

You identify the collaborator using the orgname.account_name format of their account URL. The consumer can find this string following the instructions in Finding the organization and account name for an account.

Arguments: cleanroom_name(string), consumer_account_name(string)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.create_cleanroom_listing($cleanroom_name, <consumerorg.consumeracct>);

provider.create_or_update_cleanroom_listing¶

Description: Creates a private listing for the clean room on the Snowflake Marketplace and shares it with the specified collaborators. Call this on new clean rooms, or whenever you make updates to an existing clean room’s policies, datasets, UI, or other user-facing changes.

Arguments: cleanroom_name(string)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.create_or_update_cleanroom_listing($cleanroom_name);

provider.enable_laf_for_cleanroom¶

Description: Enables Cross-Cloud Auto-Fulfillment, which allows you to share the clean room with collaborators whose Snowflake account is in a different region than your own account. Cross-Cloud Auto-Fulfillment is also known as Listing Auto-Fulfillment (LAF).

There are additional costs associated with collaborating with consumers in other regions. For more information about these costs, see Cross-Cloud Auto-Fulfillment costs.

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_or_update_cleanroom_listing command. The listing is automatically replicated to remote clouds and regions as needed.

Arguments: cleanroom_name(string)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.enable_laf_for_cleanroom($cleanroom_name);

provider.view_cleanrooom_scan_status¶

Description: Views the scan status for a clean room with DISTRIBUTION set to EXTERNAL. The scan needs to be marked as “APPROVED” before the default release directive can be set and the clean room shared to collaborators.

Arguments: cleanroom_name(string)

Returns: scan status (string)

Example:

call samooha_by_snowflake_local_db.provider.view_cleanroom_scan_status($cleanroom_name);

provider.load_python_into_cleanroom¶

Description: Confidentially loads any Python function into the clean room. Any code loaded into the clean room using this API will not be visible to the consumer. The resulting function can be called inside any SQL Jinja template as clean room.

Arguments: cleanroom_name(string), function_name(string), arguments(array), packages(array), ret_type(string), handler(string), code(string)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.load_python_into_cleanroom(
    $cleanroom_name, 
    'assign_group',                      // Name of the UDF
    ['data variant', 'index integer'],   // Arguments of the UDF, along with their type
    ['pandas', 'numpy'],                 // Packages UDF will use
    'integer',                           // Return type of UDF
    'main',                              // Handler
    $$
import pandas as pd
import numpy as np

def main(data, index):
    df = pd.DataFrame(data)  # you can do something with df but this is just an example
    return np.random.randint(1, 100)
    $$
);

provider.add_ui_form_customizations¶

Description: Defines the UI for the clean room in the web app for consumers. At a minimum, you must specify values for display_name, description, and methodology in the template description parameter.

Arguments:

• template name (string): Name of the template to which this UI applies. This is not the user-visible title, which is specified using the template_description.display_name field.

• template description (dict): Information displayed to the user in the UI. Contains the following fields:

  • display_name (Required): Display name of the template in the web app.

  • description (Required): Description of the template.

  • methodology(Required): Description of how the consumer should use the form to execute an analysis.

  • warehouse_hints (Optional): Recommends to the user what type of warehouse to use to run the analysis. This is an object with the following fields:

    • warehouse_size: See warehouse_size in CREATE WAREHOUSE for valid values.

    • snowpark_optimized (Boolean): Whether to use a Snowpark-optimized warehouse to process the query. For most machine learning use cases, Snowflake recommends TRUE.

  • render_table_dropdowns: Whether to show the default dropdowns that let the user select which provider and/or consumer tables to use in the query. This is an object with the following fields:

    • render_consumer_table_dropdown: (Default = TRUE) If TRUE, show the default consumer table selector. If FALSE, hides the consumer tables selector. The template can access the chosen values as a list using the my_table variable.

    • render_provider_table_dropdown: (Default = TRUE) If TRUE, show the default provider table selector. If FALSE, hides the provider tables selector. The template can access the chosen values as a list using the source_table variable.

• customizations (dict): Defines input fields that the user can configure. This is a dictionary of key/object pairs, each pair representing one user-configurable UI element. The key is an arbitrary string name that the template can use to access the value specified by the user. The object defines the UI element. This object has the following fields:

  • display_name (Required): Display name of the UI element

  • description (Required): Description appearing under the name

  • methodology (Required): Description of how the consumer should use the form to execute an analysis.

  • type: The type of UI element. If references is specified for this element, then omit this value (the type is determined for you). Supported values:

    • any (default): Regular text entry field

    • boolean: True/false selector

    • integer: Use arrows to change the number

    • multiselect: Select multiple items from a dropdown

    • dropdown: Select one item from a dropdown

    • date: Date selector

  • default: Default value of this element.

  • choices: List of choices available for dropdown and multiselect types

  • infoMessage: Informational hover text that shows when you hover over an “i” icon next to the name

  • size: Element size. Supported values: XS, S, M, L, XL

  • required: Indicates whether the element is required. Specify TRUE or FALSE.

  • group: A group name, used to group items in the UI. Use the same group name for items that should be grouped together in the UI. If you hide the default drop-down lists, you can use the {{ source_table }} and {{ my_table}} special arguments in the custom template, then define your own drop-down list that contains the desired tables. For more details about using these special variables when defining the custom template, see provider.add_custom_sql_template.

  • references: Creates a drop-down list containing tables or columns that are available in the clean room without having to know them in advance or list them individually. If used, don’t specify the type value. The following string values are supported:

    • PROVIDER_TABLES: Create a drop-down list of all the provider’s tables in the clean room accessible by the user.

    • PROVIDER_JOIN_POLICY: Create a crop-down list of all columns that can be joined on from the provider table specified by provider_parent_table_field.

    • PROVIDER_COLUMN_POLICY: Create a drop-down list of all columns with a column policy in the provider table specified by provider_parent_table_field.

    • CONSUMER_TABLES: Create a drop-down list of all the consumer’s tables in the clean room accessible by the user.

    • CONSUMER_COLUMNS: Create a drop-down list of all columns in the consumer table specified by consumer_parent_table_field that can be accessed by the user. You should not use consumer column references in provider-run templates, as the consumer might apply join & column policies, which may lead to a query failing when the policy for the column is not being respected.

    • CONSUMER_JOIN_POLICY: Create a drop-down list of all columns that can be joined on from the consumer table specified by consumer_parent_table_field.

    • CONSUMER_COLUMN_POLICY: Create a drop-down list of all columns with a column policy in the consumer table specified by consumer_parent_table_field.

  • provider_parent_table_field: Specify the name of the UI element where the user selects a provider table (don’t provide the table name itself here). Use only when references is set to PROVIDER_COLUMN_POLICY or PROVIDER_JOIN_POLICY.

  • consumer_parent_table_field: Specify the name of the UI element where the user selects a consumer table (don’t provide the table name itself here). Use only when references is set to CONSUMER_COLUMNS, CONSUMER_JOIN_POLICY, or CONSUMER_COLUMN_POLICY.

• output_config (dict) Defines optional output specifications. Allowed fields:

  • measure_columns: Used to specify measure columns for graphical output. Quantitative data points that are displayed in the output. These columns include metrics and dimensions that allow for detailed data analysis.

  • default_output_type: The default format to display the results. The user will typically be able to change the display format in the UI if the data is in the proper format. Supported types:

    • TABLE: (Default) Displays data in a tabular format.

    • BAR: Displays data in a bar chart, which is good for comparing different categories.

    • LINE: Displays data in a line chart, which is good for showing trends over time or continuous data.

    • PIE: Displays data in a pie chart, which is suitable for showing proportions or percentages.

Returns: success message (string)

Example:

-- Specify the display name, description, and warehouse, and hide the default table dropdowns. 
-- Define the following two fields in the UI:
--   A provider table selector that shows all provider tables. Chosen tables can be accessed by the template with the variable 'a_provider_table'
--     (This dropdown is equivalent to setting render_table_dropdowns.render_provider_table_dropdown:true)
--   A column selector for the tables chosen in 'a_provider_table'. Chosen columns can be accessed by the template with the variable 'a_provider_col'

call samooha_by_snowflake_local_db.provider.add_ui_form_customizations(
    $cleanroom_name,
    'prod_custom_template',
    {
        'display_name': 'Custom Analysis Template',
        'description': 'Use custom template to run a customized analysis.',
        'methodology': 'This custom template dynamically renders a form for you to fill out, which are then used to generate a customized analysis fitting your request.',
        'warehouse_hints': {
            'warehouse_size': 'xsmall',
            'snowpark_optimized': FALSE
        },
        'render_table_dropdowns': {
            'render_consumer_table_dropdown': false,
            'render_provider_table_dropdown': false
        },
        'activation_template_name': 'activation_my_template',
        'enabled_activations': ['consumer', 'provider']  
    },    
    {
        'a_provider_table': {
            'display_name': 'Provider table',
            'order': 3,
            'description': 'Provider table selection',
            'size': 'S',
            'group': 'Seed Audience Selection',
            'references': ['PROVIDER_TABLES'],
            'type': 'dropdown'
        },
        'a_provider_col': {
            'display_name': 'Provider column',
            'order': 4,
            'description': 'Which col do you want to count on',
            'size': 'S',
            'group': 'Seed Audience Selection',
            'references': ['PROVIDER_COLUMN_POLICY'],
            'provider_parent_table_field': 'a_provider_table',
            'type': 'dropdown'
        }
    },
    {
        'measure_columns': ['col1', 'col2'],
        'default_output_type': 'PIE'
    }
);

provider.set_cleanroom_ui_accessibility¶

Description: Shows or hides the clean room in the web app to all users logged in with a provider account.

Arguments:

• cleanroom_name(string) - The name of the clean room.

• visibility_status(string) - One of the following case-sensitive values:

  • HIDDEN - Hides the clean room in the web app from all users in the current provider account. The clean room will still be accessible via API calls.

  • EDITABLE - Makes the clean room visible in the web app.

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.set_cleanroom_ui_accessibility($cleanroom_name, 'HIDDEN');

provider.register_cleanroom_in_ui – DEPRECATED¶

Description: Registers a clean room for use in the web app by the consumer. The clean room is created and configured by the provider using developer APIs. This command then registers it into the web app for consumers to install, add their table, and run any custom analyses you’ve added without needing to use developer APIs. They work with the clean room entirely through the user interface of the web app.

You can call this API more than once to include multiple custom templates in the web app.

Arguments: cleanroom_name(string), template name(string), consumer_account_locator(string), user_email(string)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.register_cleanroom_in_ui($cleanroom_name, 'prod_custom_template', <CONSUMER ACCOUNT LOCATOR>, <USER_EMAIL>)

provider.view_ui_registration_request_log – DEPRECATED¶

Description: Views the list of requests raised from the account to register clean rooms into the web app. Each request has an associated ID which can be used in conjunction with the view_ui_registration_log procedure to view the status of the requests. The requests are shared to the backend where they are processed and the clean room added into the clean room.

Arguments:

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.view_ui_registration_request_log();

provider.describe_cleanroom¶

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

Arguments: cleanroom_name(string)

Returns: Extensive description string of cleanroom (table)

Example:

call samooha_by_snowflake_local_db.provider.describe_cleanroom($cleanroom_name);

provider.view_provider_datasets¶

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

Arguments: cleanroom_name(string)

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

Example:

call samooha_by_snowflake_local_db.provider.view_provider_datasets($cleanroom_name);

provider.view_join_policy¶

Description: Views the join policies currently active in the clean room.

Arguments: cleanroom_name (string)

Returns: The join policy (table)

Example:

call samooha_by_snowflake_local_db.provider.view_join_policy($cleanroom_name);

provider.view_added_templates¶

Description: Views the templates currently active in the clean room.

Arguments: cleanroom_name (string)

Returns: The added templates (table)

Example:

call samooha_by_snowflake_local_db.provider.view_added_templates($cleanroom_name);

provider.view_column_policy¶

Description: Views the column policies currently active in the clean room.

Arguments: cleanroom_name (string)

Returns: The column policy (table)

Example:

call samooha_by_snowflake_local_db.provider.view_column_policy($cleanroom_name);

provider.view_consumers¶

Description: Views the consumers the clean room has been shared with.

Arguments: cleanroom_name (string)

Returns: Consumer accounts that have access to the cleanroom (table)

Example:

call samooha_by_snowflake_local_db.provider.view_consumers($cleanroom_name);

provider.view_cleanrooms¶

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

Arguments:

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

Example:

call samooha_by_snowflake_local_db.provider.view_cleanrooms();

provider.view_request_logs¶

Description: Views the request logs being sent from consumers of this clean room.

Arguments: cleanroom_name (string)

Returns: A set of logs recorded of the queries being run against the cleanroom (table)

Example:

call samooha_by_snowflake_local_db.provider.view_request_logs($cleanroom_name);

provider.grant_reference_usage¶

Description: Allows upstream databases that contain data to be granted reference_usage to the clean room.

This is a much more limited grant than those in register_db. Using this procedure is necessary when adding views or UDTFs which reference data from upstream databases, but it won’t allow the SAMOOHA_APP_ROLE role to see the actual tables or show these databases in the dropdown in the web app.

This command should be called on databases that contain data used in linked views and UDTFs.

Arguments: database_names (array)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.grant_reference_usage(['<DATABASE_NAME>']);

provider.revoke_reference_usage¶

Description: Removes databases from the list of referenced databases created by provider.grant_reference_usage.

Arguments: database_names (array)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.revoke_reference_usage(['<DATABASE_NAME>']);

provider.mount_request_logs_for_all_consumers¶

Description: Gives providers access to information coming back to the provider from the consumers of a clean room.

Arguments: cleanroom_name (string)

Returns: success message (string)

Example:

CALL samooha_by_snowflake_local_db.provider.mount_request_logs_for_all_consumers($cleanroom_name);

library.enable_local_db_auto_upgrades¶

Description: Enables the task, samooha_by_snowflake_local_db.admin.expected_version_task, that automatically upgrades the Snowflake Native App for Snowflake Data Clean Rooms as new versions are released.

Arguments: None

Returns: success message (string)

Example:

CALL samooha_by_snowflake_local_db.library.enable_local_db_auto_upgrades();

library.disable_local_db_auto_upgrades¶

Description: Disables the task, samooha_by_snowflake_local_db.admin.expected_version_task, that automatically upgrades the Snowflake Native App for Snowflake Data Clean Rooms as new versions are released.

Arguments: None

Returns: success message (string)

Example:

CALL samooha_by_snowflake_local_db.library.disable_local_db_auto_upgrades();