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
Copy

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

Arguments: cleanroom_name (string), provider_account_locator (string)

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

Arguments: cleanroom_name (string)

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

Arguments: cleanroom_name (string)

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

Arguments: cleanroom_name (string)

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

Arguments: cleanroom_name (string)

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

Arguments:

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

  • template_names (array of strings) - An array of one or more templates in the clean room which should be enabled for provider analysis

  • enable_differential_privacy(boolean) - If TRUE, enable differential privacy for all the templates listed in template_names. Differential privacy can be enabled for these templates only if differential privacy is enabled for the clean room itself. You can check differential privacy status for a clean room by calling consumer.is_dp_enabled.

Returns: success message (string)

Example:

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

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.

Arguments: db_name (string)

Returns: success message (string)

Example:

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

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']);
Copy

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']);
Copy

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 consumer.link_datasets. You can register broader groups of objects by calling library.register_schema, library.register_managed_access_schema, or consumer.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']);
Copy

library.register_table_or_view – Deprecated

Attention

This command is now deprecated. Please use library.register_objects.

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)

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);
Copy

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);
Copy

library.register_table – Deprecated

Attention

This command is now deprecated. Please use library.register_objects.

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']);
Copy

library.register_managed_access_table – Deprecated

Attention

This command is now deprecated. Please use library.register_objects.

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']);
Copy

library.register_view – Deprecated

Attention

This command is now deprecated. Please use library.register_objects.

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']);
Copy

library.register_managed_access_view – Deprecated

Attention

This command is now deprecated. Please use library.register_objects.

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']);
Copy

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');
Copy

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']);
Copy

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']);
Copy

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']);
Copy

library.unregister_table_or_view – Deprecated

Attention

This command is now deprecated. Please use library.unregister_objects.

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);
Copy

library.unregister_table – Deprecated

Attention

This command is now deprecated. Please use library.unregister_objects

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']);
Copy

library.unregister_managed_access_table – Deprecated

Attention

This command is now deprecated. Please use library.unregister_objects

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']);
Copy

library.unregister_view – Deprecated

Attention

This command is now deprecated. Please use library.unregister_objects

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']);
Copy

library.unregister_managed_access_view – Deprecated

Attention

This command is now deprecated. Please use library.unregister_objects

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']);
Copy

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.

Arguments: cleanroom_name(string), table_and_col_names(array)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.consumer.set_join_policy($cleanroom_name, ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:EMAIL', 'SAMOOHA_SAMPLE_DATABASE.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.

Arguments: cleanroom_name(string), analysis_and_table_and_columns(array)

Returns: success message (string)

Example:

call samooha_by_snowflake_local_db.consumer.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']);
Copy

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.

Arguments: cleanroom_name (string), template_name (string)

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

Arguments: cleanroom_name (string), template_name (string)

Returns: Argument list and specification (table)

Example:

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

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.

Arguments: cleanroom_name (string)

Returns: The added template chains (table)

Example:

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

consumer.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.consumer.view_template_chain_definition($cleanroom_name, 'insights_chain');
Copy

consumer.get_arguments_from_template_chain

Description: Returns the expected arguments for all of the templates in the template chain.

Arguments: cleanroom_name (string), template__chain_name (string)

Returns: Argument list and specification (table)

Example:

call samooha_by_snowflake_local_db.consumer.get_arguments_from_template_chain($cleanroom_name, 'insights_chain');
Copy

Run analyses

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.

Arguments: cleanroom_name (string), {template_name | template_chain_name} (string), consumer_tables (array), provider_tables (array), analysis_arguments (object)

Returns: 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.MYDATA.CONVERSIONS'],  -- Consumer tables
  ['SAMOOHA_SAMPLE_DATABASE.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

Activation

For more information about activating results, see Using the developer APIs to send results to a Snowflake account for activation.

consumer.set_activation_policy

Description: Defines which columns can be used within activation templates. This ensures that only the columns that are approved by the consumer 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.consumer.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' ]);
Copy

consumer.run_activation

Description: Executes a template that pushes results back to a Snowflake account for activation.

Arguments: cleanroom_name (string), segment_name (string), template_name (string), consumer_tables (array), provider_tables (array), activation_arguments (object), consumer_direct_activation (boolean), application_id (string)

If the consumer is pushing results to their own Snowflake account for activation, set the consumer_direct_activation argument as TRUE.

Pass an empty string as the argument to application_id.

Returns: 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'
  ));
Copy

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.

Arguments: cleanroom_name (string), template_name (string), template_definition (string)

Returns: 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 %};
  $$);
Copy

consumer.generate_python_request_template

Description: Generates a consumer clean room template that includes custom Python code. The generated template includes your Python code and a placeholder for your JinjaSQL template. Pass your final template to consumer.list_template_requests.

Learn more about consumer-defined templates.

Arguments:

  • function_name (string) - The name of the Python function your SQL template should call to execute your function.

  • arguments (array of string) - List of arguments for your Python function, where each argument is a space-delimited string pair in the format “<argument_name> <argument_type>”. For example: ['data variant', 'scale integer'].

  • packages (array of string) - Array of package names required for your Python code. If none, specify an empty array. Example: ['pandas','numpy'].

  • imports (array of strings) - Any custom Python libraries required for the Python code. Should be an array of zero or more stage addresses. Example: ['@db.schema.stage/my_python_sproc.py']

  • rettype (string) - The SQL return type of your function. Examples: 'integer', 'varchar'.

  • handler (string) - The name of the main handler function in your Python code. Typically this is 'main'.

  • code (string) - Your Python code implementation. If you include an import and your designated handler is defined within an import, this can be an empty string.

Returns: Generated python template (string). Replace the placeholder with your SQL code.

Example:

Call the helper function with a trivial Python example.

call SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.CONSUMER.GENERATE_PYTHON_REQUEST_TEMPLATE(
  'my_func',                         // SQL should use this name to call your function
  ['data variant', 'index integer'], // Arguments and types for the function
  ['pandas', 'numpy'],               // Standard libraries used
  [],                                // No custom libraries needed.
  'integer',                         // Return type integer
  'main',                            // Standard main handler
// Python implementation as UDF  
  $$
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)
    $$
);
Copy

Here is the response to the previous call. Insert your JinjaSQL as indicated at the placeholder, and pass it into consumer.create_template_request.

BEGIN

-- First define the Python UDF
CREATE OR REPLACE FUNCTION CLEANROOM.my_func(data variant, index integer)
RETURNS integer
LANGUAGE PYTHON
RUNTIME_VERSION = 3.10
PACKAGES = ('pandas', 'numpy')

HANDLER = 'main'
AS '
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)
    ';
        

-- Then define and execute the SQL query
LET SQL_TEXT varchar := '<INSERT SQL TEMPLATE HERE>';

-- Execute the query and return the result
LET RES resultset := (EXECUTE IMMEDIATE :SQL_TEXT);
RETURN TABLE(RES);

END;
Copy

consumer.list_template_requests

Description: Lists the requests that the consumer has made to add a template to a clean room.

Arguments: cleanroom_name (string)

Returns: 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');
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.

Arguments: cleanroom_name(string)

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

Arguments: cleanroom_name(string)

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

Arguments: cleanroom_name (string)

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

Arguments: cleanroom_name (string)

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

Arguments: cleanroom_name (string)

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

Arguments: cleanroom_name (string)

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

Arguments: cleanroom_name (string)

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

Arguments: None

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

Example:

call samooha_by_snowflake_local_db.consumer.view_cleanrooms();
Copy

Differential privacy

These commands control differential privacy in the clean room. You can also specify differential privacy at the template level when calling consumer.enable_templates_for_provider_run.

Learn more about managing differential privacy.

consumer.is_dp_enabled

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

Arguments: cleanroom_name(string)

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

Arguments: cleanroom_name (string)

Returns: The remaining privacy budget (float)

Example:

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

General Helper Methods

Use the following methods to assist in general clean room functionality.

consumer.set_cleanroom_ui_accessibility

Description: Shows or hides clean rooms in the web app for consumers in the current account.

Arguments:

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

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

    • HIDDEN - Hides the specified clean room in the web app from all users in the current consumer 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.consumer.set_cleanroom_ui_accessibility($cleanroom_name, 'HIDDEN');
Copy

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();
Copy

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();
Copy