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);
Register and unregister data¶
Use the following command to register and unregister databases, schemas, and objects. Tables and views must be registered before they can be linked into the clean room. If you register a database or schema, all of the objects in that database or schema are registered.
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.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.
Input: schema_name (array)
Output: 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.
Input: schema_name (array)
Output: success message (string)
Example:
call samooha_by_snowflake_local_db.library.register_managed_access_schema(['SAMOOHA_SAMPLE_DATABASE.DEMO']);
library.register_table_or_view¶
Description: Registers tables and views of all types.
Input: object_names (array), is_view (boolean), is_iceberg (boolean), is_external (boolean), is_under_managed_access_schema (boolean)
Output 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¶
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.
Input: table_name (array)
Output: success message (string)
Example:
call samooha_by_snowflake_local_db.library.register_table(['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']);
library.register_managed_access_table¶
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.
Input: table_name (array)
Output: success message (string)
Example:
call samooha_by_snowflake_local_db.library.register_managed_access_table(['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']);
library.register_view¶
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.
Input: view_name (array)
Output: success message (string)
Example:
call samooha_by_snowflake_local_db.library.register_view(['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']);
library.register_managed_access_view¶
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.
Input: view_name (array)
Output: 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.
Input: db_name (string)
Output: 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.
Input: schema_name (array)
Output: 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.
Input: schema_name (array)
Output: success message (string)
Example:
call samooha_by_snowflake_local_db.library.unregister_managed_access_schema(['SAMOOHA_SAMPLE_DATABASE.DEMO']);
library.unregister_table_or_view¶
Description: Unregisters tables and views of all types.
Input: 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¶
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.
Input: table_name (array)
Output: success message (string)
Example:
call samooha_by_snowflake_local_db.library.unregister_table(['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']);
library.unregister_managed_access_table¶
Description: Similar to unregister_table, but unregisters tables in a managed access schema (that is, a schema created with the WITH MANAGED ACCESS parameter).
Input: table_name (array)
Output: success message (string)
Example:
call samooha_by_snowflake_local_db.library.unregister_managed_access_table(['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']);
library.unregister_view¶
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.
Input: view_name (array)
Output: success message (string)
Example:
call samooha_by_snowflake_local_db.library.unregister_view(['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']);
library.unregister_managed_access_view¶
Description: Similar to unregister_view, but unregisters views in a managed access schema (that is, a schema created with the WITH MANAGED ACCESS parameter).
Input: view_name (array)
Output: success message (string)
Example:
call samooha_by_snowflake_local_db.library.unregister_managed_access_view(['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']);
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']);
Templates¶
The following commands allow users to work with 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');
Template chains¶
The following commands allow users to work with template chains available in the clean room. For general information about using template chains, see Using the developer APIs to execute templates sequentially.
consumer.view_added_template_chains¶
Description: Views the template chains currently active in the clean room.
Input: cleanroom_name (string)
Output: The added template chains (table)
Example:
call samooha_by_snowflake_local_db.consumer.view_added_template_chains($cleanroom_name);
consumer.view_template_chain_definition¶
Description: Returns the attributes of a template chain.
Input: cleanroom_name (string), template_chain_name (string)
Output: The template chain definition (string)
Example:
call samooha_by_snowflake_local_db.consumer.view_template_chain_definition($cleanroom_name, 'insights_chain');
consumer.get_arguments_from_template_chain¶
Description: Returns the expected arguments for all of the templates in the template chain.
Input: cleanroom_name (string), template__chain_name (string)
Output: Argument list and specification (table)
Example:
call samooha_by_snowflake_local_db.consumer.get_arguments_from_template_chain($cleanroom_name, 'insights_chain');
Run analyses and activations¶
The following commands executes a specific analysis or activation based on the specified template.
consumer.run_analysis¶
Description: Executes an analysis using a template or template chain. The consumer and provider tables need to be specified separately and certain inputs can be empty if they are not required. The template or template chain 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 | template_chain_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'
)
);
consumer.run_activation¶
Description: Executes a template that pushes results back to the provider for activation.
For more information about activating results back to the provider, see Using the developer APIs to activate results to provider.
Input: cleanroom_name (string), segment_name (string), template_name (string), consumer_tables (array), provider_tables (array), activation_arguments (object)
Output: Success message
Example:
call samooha_by_snowflake_local_db.consumer.run_activation(
$cleanroom_name,
'my_activation_segment',
'activation_custom_template',
['consumer_source_table'],
['provider_source_table'],
object_construct( -- 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.
'where_clause', 'p.EMAIL=c.EMAIL'
));
Consumer-defined templates¶
The following APIs allow you to add consumer-defined templates to a clean room. For more information, see Using the developer API to add consumer-defined templates.
consumer.create_template_request¶
Description: Sends a request to the provider of a clean room asking them to approve a custom template so it can be added to the clean room.
Input: cleanroom_name (string), template_name (string), template_definition (string)
Output: success message (string)
Example:
CALL samooha_by_snowflake_local_db.consumer.create_template_request('dcr_cleanroom',
'my_analysis',
$$
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 %};
$$);
consumer.list_template_requests¶
Description: Lists the requests that the consumer has made to add a template to a clean room.
Input: cleanroom_name (string)
Output: request_id(string), provider_identifier(string), template_name(string), template_definition(string), request_status(string), reason(string)
Example:
CALL samooha_by_snowflake_local_db.consumer.list_template_requests('dcr_cleanroom');
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¶
Use the following methods to generally assist in leveraging clean room functionality.
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.
Input: None
Output: 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.
Input: None
Output: success message (string)
Example:
CALL samooha_by_snowflake_local_db.library.disable_local_db_auto_upgrades();