Snowflake Data Clean Rooms: Provider API reference guide

This guide describes the APIs provided by the developer edition of a Snowflake Data Clean Room that allow a provider to create, configure, and share a clean room. All functions reside inside the following schema:

samooha_by_snowflake_local_db.provider
Copy

Set up environment

The following commands are required for running the developer edition of a Snowflake Data Clean Room:

use role samooha_app_role;
use warehouse app_wh;
Copy

Creates a name for the clean room. The clean room name can only contain alphanumeric characters. The clean room name cannot contain any special characters, other than spaces and underscores.

set cleanroom_name = 'Test Cleanroom 1';  -- This must only have alphanumeric characters.
Copy

Create clean room

Use the following commands to create and delete a clean room:

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 applicaiton package samooha_cleanroom_<CLEANROOM_ID> SET DISTRIBUTION = EXTERNAL;
Copy

Note

The distribution can be either EXTERNAL or INTERNAL. If a clean room is marked for EXTERNAL distribution, Snowflake needs to carry out an automated security scan first before it can be shared with collaborators. See provider.set_default_release_directive for more details.

Input: cleanroom_name(string), distribution(string)

Output: success message (string)

Example:

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

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

Note

If the clean room distribution is set to EXTERNAL, this can only be called once the clean room security scan moves to an APPROVED state. To view the current scan status, call:

show versions in application package samooha_cleanroom_<CLEANROOM_ID>;
Copy

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

Output: success message (string)

Example:

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

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.

Input: cleanroom_name(string)

Output: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.drop_cleanroom($cleanroom_name);
Copy

Add datasets and create policies

Use the following commands to add data and set up policies for the clean room.

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.

Input: cleanroom_name(string), table_and_col_names(array)

Output: success message (string)

Example:

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

Templates

Use the following commands to add the templates/analyses that are supported in this clean room.

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

Input: cleanroom_name(string), template_names(array)

Output: success message (string)

Example:

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

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.

Input: cleanroom_name (string), template_name (string)

Output: The template definition (string)

Example:

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

provider.clear_template

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

Input: cleanroom_name(string), template_name(string)

Output: success message (string)

Example:

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

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.

Input: cleanroom_name(string)

Output: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.clear_all_templates($cleanroom_name);
Copy

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.

Input: cleanroom_name(string), analysis_and_table_and_columns(array)

Output: success message (string)

Example:

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

provider.add_custom_sql_template

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

Note that this procedure overwrites any previous template with the same name. If you want to edit an existing template, it can be done via this function, by specifying the same template name.

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:

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

Input: cleanroom_name(string), template_name(string), template(string), differential_privacy_sensitivity(float)

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

Configure who can run analyses

Use the following commands to configure who can run analyses in the clean room. You can specify the provider (clean room creator), consumer (clean room installer), or both.

provider.enable_provider_run_analysis

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

Note

Very Important: this needs to be called after provider.add_consumer, and before a consumer installs a clean room. If this is changed after a consumer has already installed their clean room, then they will need to reinstall the clean room to reflect the new configuration.

Input: cleanroom_name(string), consumer_account_locator(string)

Output: success message (string)

Example:

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

provider.disable_provider_run_analysis

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

Note

Very Important: this needs to be called after provider.add_consumer, and before a consumer installs a clean room. If this is changed after a consumer has already installed their clean room, then they will need to reinstall the clean room to reflect the new configuration.

Input: cleanroom_name(string), consumer_account_locator(string)

Output: success message (string)

Example:

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

provider.enable_consumer_run_analysis

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

Note

Very Important: this needs to be called after provider.add_consumer, and before a consumer installs a clean room. If this is changed after a consumer has already installed their clean room, then they will need to reinstall the clean room to reflect the new configuration.

Input: cleanroom_name(string), consumer_account_locator(string)

Output: success message (string)

Example:

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

provider.disable_consumer_run_analysis

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

Note

Very Important: this needs to be called after provider.add_consumer, and before a consumer installs a clean room. If this is changed after a consumer has already installed their clean room, then they will need to reinstall the clean room to reflect the new configuration.

Input: cleanroom_name(string), consumer_account_locator(string)

Output: success message (string)

Example:

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

library.is_provider_run_enabled

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

Input: cleanroom_name (string)

Output: enabled message (string)

Example:

call samooha_by_snowflake_local_db.library.is_provider_run_enabled($cleanroom_name)
Copy

library.is_consumer_run_enabled

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

Input: cleanroom_name (string)

Output: enabled message (string)

Example:

call samooha_by_snowflake_local_db.library.is_consumer_run_enabled($cleanroom_name)
Copy

Running analyses as a clean room creator

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.

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

Output: request ID (string)

Example:

call samooha_by_snowflake_local_db.provider.submit_analysis_request(
    $cleanroom_name, 
    '<CONSUMER_ACCOUNT>',
    'prod_overlap_analysis', 
    ['SAMOOHA_SAMPLE_DATABASE_NAV2.demo.customers'], 
    ['SAMOOHA_SAMPLE_DATABASE_NAV2.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';
Copy

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.

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

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

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.

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

Output: analysis results (table)

Example:

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

Share with consumer

Use the following commands to share a clean room with consumers.

provider.add_consumers

Description: Adds a consumer account to the clean room. Multiple consumer account locators and account names can be passed into the provider.add_consumers function as a comma separated string, or as separate 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.

Input: cleanroom_name(string), consumer_account_locator(string), consumer_account_name(string), enable_differential_privacy(boolean, optional, default: FALSE)

Output: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.add_consumers($cleanroom_name, <CONSUMER ACCOUNT LOCATOR>, <CONSUMER ACCOUNT NAME>);
Copy

provider.create_cleanroom_listing

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.

Note

In order to use this procedure, you need to have set the release directive. See provider.set_default_release_directive for more details.

Input: cleanroom_name(string), consumer_account_name(string)

Output: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.create_cleanroom_listing($cleanroom_name, <CONSUMER ACCOUNT NAME>);
Copy

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

Note

A Snowflake administrator with the ACCOUNTADMIN role must enable Cross-Cloud Auto-Fulfillment in your Snowflake account before you can execute this command. For instructions for enabling Cross-Cloud Auto-Fulfillment in the Snowflake account, see Collaborate with accounts in different regions.

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

Input: cleanroom_name(string)

Output: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.enable_laf_for_cleanroom($cleanroom_name);
Copy

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.

Input: cleanroom_name(string)

Output: scan status (string)

Example:

call samooha_by_snowflake_local_db.provider.view_cleanroom_scan_status($cleanroom_name);
Copy

Functions to load Python code into clean room

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.

Note

This procedure adds your Python code to the clean room as a patch on the existing version. This will re-trigger the security scan and you may have to wait for it to be APPROVED before sharing the latest version to collaborators. If you do, then call provider.set_default_release_directive before sharing the clean room to providers with the latest version/patch. See the Snowflake native app documentation for more details.

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

Output: 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)
    $$
);
Copy

Clean room UI registration methods

Use the following commands to register a clean room loaded with custom analyses into the web app of a Snowflake Data Clean Room under the Custom Analysis tab.

provider.add_ui_form_customizations

Description:: Adds customizations for the UI form for clean rooms you want registered in the web app for your consumers.

Allowed customizations are:

  • type: One of the following UI elements:

    • boolean: True/false selector

    • integer: Use arrows to change the number

    • multiselect: Select multiple items from a dropdown

    • dropdown: Select 1 item from a dropdown

    • any: Regular text entry field

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

  • default: Any default value

  • display_name: Display name of the UI element

  • description: Description appearing under the name

  • infoMessage: Information message that renders when you hover over an “i” icon next to the name

  • size: Choose the element size between XS, S, M, L, XL

  • required: Indicates whether the element is required

  • group: Put the same value for multiple elements to group them all together in the UI form

  • warehouse_hints: Optional customization that controls which kind of warehouse the consumer account will use to run analyses. For example, for most machine learning use cases, Snowflake recommends specifying a Snowpark-optimized warehouse. For valid values for warehouse_size, see CREATE WAREHOUSE.

Input: template name (string), template_description (dict), customizations (dict), output_config (dict)

Output: success message (string)

Example:

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,
        }
    },    
    {
        'dimensions': {
            'display_name': 'Dimensions',
            'choices': ['STATUS', 'AGE', 'REGION_CODE', 'DAYS_ACTIVE'],
            'type': 'multiselect',
            'description': 'What dimensions do you want to train on?',
            'infoMessage': 'We recommend selecting all features for maximal signal.',
            'size': 'M'
        }
    },
    {
        'measure_columns': ['col1', 'col2'],
        'default_output_type': 'PIE'
    }
)
Copy

provider.register_cleanroom_in_ui

Description: Registers a clean room for use in the web app by the consumer. The clean room is created and configured in developer edition by the provider. This method 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 edition. They work with the clean room entirely through the user interface of the web app.

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

Output: 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>)
Copy

provider.view_ui_registration_request_log

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.

Input:

Output: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.view_ui_registration_request_log();
Copy

Clean room metadata getter methods

Use the following commands to show relevant properties of the clean room.

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.

Input: cleanroom_name(string)

Output: Extensive description string of cleanroom (table)

Example:

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

provider.view_provider_datasets

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

Input: cleanroom_name(string)

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

Example:

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

provider.view_join_policy

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

Input: cleanroom_name (string)

Output: The join policy (table)

Example:

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

provider.view_added_templates

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

Input: cleanroom_name (string)

Output: The added templates (table)

Example:

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

provider.view_column_policy

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

Input: cleanroom_name (string)

Output: The column policy (table)

Example:

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

provider.view_consumers

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

Input: cleanroom_name (string)

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

Example:

call samooha_by_snowflake_local_db.provider.view_consumers($cleanroom_name);
Copy

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

provider.view_request_logs

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

Input: cleanroom_name (string)

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

General helper methods

Use the following methods to generally assist in leveraging clean room functionality and supported flows.

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

Input: db_name (string)

Output: success message (string)

Example:

call samooha_by_snowflake_local_db.provider.register_db('SAMOOHA_SAMPLE_DATABASE');
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.

Input: db_name (string)

Output: success message (string)

Example:

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

provider.grant_reference_usage

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

Note

This command is for use with the web app only.

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.

Input: database_names (array)

Output: success message (string)

Example:

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

provider.revoke_reference_usage

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

Note

This command is for use with the web app only.

Input: database_names (array)

Output: success message (string)

Example:

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

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.

Input: table_name (array)

Output: success message (string)

Example:

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