Snowflake Data Clean Rooms: Consumer API reference guide¶

consumer.grant_run_on_cleanrooms_to_role¶

Description: Grants the specified role permission to run a subset of procedures on the specified clean rooms. Clean rooms must be installed in this account, not created by this account. (That is, only clean rooms for which you are a consumer.)

To grant limited use to your clean rooms, grant users the specified role rather than SAMOOHA_APP_ROLE. For more information about about role access, see Grant limited API access.

The following procedures can be run using a role specified here:

• CONSUMER.VIEW_ADDED_TEMPLATES

• CONSUMER.VIEW_ADDED_TEMPLATE_CHAINS

• CONSUMER.GET_ARGUMENTS_FROM_TEMPLATE

• CONSUMER.VIEW_COLUMN_POLICY

• CONSUMER.VIEW_CONSUMER_DATASETS

• CONSUMER.VIEW_JOIN_POLICY

• CONSUMER.VIEW_PROVIDER_COLUMN_POLICY

• CONSUMER.VIEW_PROVIDER_DATASETS

• CONSUMER.VIEW_PROVIDER_JOIN_POLICY

• CONSUMER.VIEW_REMAINING_PRIVACY_BUDGET

• CONSUMER.RUN_ANALYSIS

• CONSUMER.VIEW_PROVIDER_ACTIVATION_POLICY

• CONSUMER.VIEW_ACTIVATION_POLICY

• CONSUMER.RUN_ACTIVATION

Arguments:

• cleanroom_names (String) - Names of all the clean rooms on which to grant limited access to the specified role.

• run_role_name (String) - Name of a role that has limited permissions on the specified clean rooms. You must create the role before calling this procedure.

Returns: (String) - Success message.

Example:

CREATE ROLE MARKETING_ANALYST_ROLE;
CALL samooha_by_snowflake_local_db.consumer.grant_run_on_cleanrooms_to_role(
  ['overlap_cleanroom', 'market_share_cleanroom'],
  'MARKETING_ANALYST_ROLE'
);

consumer.revoke_run_on_cleanrooms_from_role¶

Description: Revokes permissions from the specified roles on the specified clean rooms. If the user has access to a non-revoked role, or has the SAMOOHA_APP_ROLE, they can still run clean room procedures in the specified clean rooms.

Arguments:

• cleanroom_names (String) - Names of one or more clean rooms in this account.

• run_role_name (String) - Name of role that should no longer have limited permissions on the specified clean rooms in this account.

Returns: (String) - Success message.

Example:

CALL samooha_by_snowflake_local_db.consumer.revoke_run_on_cleanrooms_from_role(
  ['overlap_cleanroom', 'market_share_cleanroom'],
  'TEMP_USERS_ROLE'
);

consumer.install_cleanroom¶

Description: Installs (joins) the clean room created by the specified provider. Calling this multiple times clears out the existing clean room each time; if you interrupt a second installation before it’s complete, the clean room becomes corrupted, and you will need to complete this procedure to make the clean room usable.

Arguments:

• cleanroom_name (String) - Name of the clean room to install.

• provider_account_locator (String) - Account locator of the provider who created this clean room.

Returns: (String) Success message.

Example:

CALL samooha_by_snowflake_local_db.consumer.install_cleanroom($cleanroom_name, $provider_locator);

consumer.is_enabled¶

Description: There can be a short delay after clean room installation before it is ready to use. You might call this procedure to confirm whether or not the clean room is ready for use after installation.

Arguments:

• cleanroom_name (String) - Name of clean room to check the status of.

Returns: (Boolean) Whether or not the specified clean room is installed and ready to use.

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. The clean room can always be installed again by calling consumer.install_cleanroom.

Arguments:

• cleanroom_name (String) - Name of clean room to uninstall.

Returns: (String) Success message.

Example:

CALL samooha_by_snowflake_local_db.consumer.uninstall_cleanroom($cleanroom_name);

library.enable_laf_on_account¶

Description: Enables Cross-Cloud Auto-Fulfillment on the current account. Requires ACCOUNTADMIN role.

Arguments: None

Returns: (String) Success message.

Example:

USE ROLE ACCOUNTADMIN;
CALL samooha_by_snowflake_local_db.library.enable_laf_on_account();

consumer.is_laf_enabled_for_cleanroom¶

Description: Describes whether or not cross-cloud auto-fulfillment has been enabled for this clean room. Cross-cloud auto-fulfillment must be configured by an account administrator.

Arguments:

• cleanroom_name (String) - Name of the clean room.

Returns: Whether or not cross-cloud auto-fulfillment has been enabled for this clean room.

Example:

CALL samooha_by_snowflake_local_db.consumer.is_laf_enabled_for_cleanroom($cleanroom_name);

consumer.setup_cleanroom_request_share_for_laf¶

Description: Enables cross-cloud request sharing with a specified provider for a specific clean room. This is required for cross-region clean rooms to have full functionality, including request logs, consumer template requests, and provider-run analyses.

Arguments:

• cleanroom_name (String) - Clean room name.

• provider_account_name (String) - Data sharing account identifier of the provider.

Returns: (String) Success message.

Example:

CALL samooha_by_snowflake_local_db.consumer.setup_cleanroom_request_share_for_laf(
      $cleanroom_name, $provider_account_name);

consumer.setup_activation_share_to_laf_consumer¶

Description: Enables provider activation between a provider and a consumer on different cloud regions.

Arguments:

• provider_account - One or more comma-delimited provider Data sharing account identifiers.

Returns: (String) Success message.

Example:

CALL samooha_by_snowflake_local_db.consumer.setup_activation_share_to_laf_consumer('org1.locator1,org2.locator2');

library.is_provider_run_enabled¶

Description: Checks if this clean room allows provider-run analyses. The consumer must still grant explicit permission by calling consumer.enable_templates_for_provider_run before providers can run an analysis in this clean room.

Arguments:

• cleanroom_name (String) - Clean room name.

Returns: (String) Description of whether or not the clean room supports provider-run analyses.

Example:

CALL samooha_by_snowflake_local_db.library.is_provider_run_enabled($cleanroom_name)

consumer.enable_templates_for_provider_run¶

Description: Grants permission to the provider to run an analysis in the requested clean room. This is called after the provider calls provider.enable_provider_run_analysis to enable provider-run analysis on a clean room. consumer.enable_templates_for_provider_run grants the provider permission to run analysis in the specified templates in the specified clean room as many times as they want.

Arguments:

• cleanroom_name (String) - The name of the clean room where the provider can run analyses.

• template_names (Array of string) - An array of names of one or more templates in the clean room that the provider can run.

• enable_differential_privacy (Boolean) - If TRUE, enable differential privacy for all 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. You can customize the privacy settings by calling consumer.set_privacy_settings. Learn more.

• template_configuration (Object, Optional) - An optional object to specify additional settings for each template. This object contains key-value pairs, where the key is the template name (from template_names) and the value is an object with the following properties:

  • warehouse_type (String) - A permitted warehouse type that the provider can use with this template. Allowed values are those defined for WAREHOUSE_TYPE.

  • warehouse_size (Array of string) - One or more permitted warehouse sizes that can be used with this warehouse type and template. Allowed values are those defined for WAREHOUSE_SIZE.

Returns: (String) Success message.

Examples:

-- Simple example
CALL samooha_by_snowflake_local_db.consumer.enable_templates_for_provider_run($cleanroom_name, ['prod_overlap_analysis'], FALSE);

-- Specify warehouse types that the provider can request for each template.
CALL samooha_by_snowflake_local_db.CONSUMER.enable_templates_for_provider_run(
  $cleanroom_name,
  [$template1, $template2],
  TRUE,
  {
    $template1: {'warehouse_type': 'STANDARD', 'warehouse_size': ['MEDIUM', 'LARGE']},
    $template2: {'warehouse_type': 'SNOWPARK-OPTIMIZED', 'warehouse_size': ['MEDIUM', 'XLARGE']},
    $template3: {'warehouse_type': 'STANDARD', 'warehouse_size': ['MEDIUM', 'XLARGE']}
  });

consumer.prepare_multiprovider_flow¶

Description: Sends a request to run a multi-provider query to the providers of the specified clean rooms. This procedure doesn’t actually run the request; it simply asks the provider for permission to run this request in the future by calling consumer.execute_multiprovider_flow.

If a provider grants approval, the consumer can then run the query by calling consumer.execute_multiprovider_flow as often as they want (depending on any configured differential privacy settings). To re-run a previously approved query after calling prepare_multiprovider_flow with new values, the consumer must call prepare_multiprovider_flow with the exact previous query, but they don’t need to wait for provider approval before running consumer.execute_multiprovider_flow.

The request is valid for four hours; after that, the request will be dropped from the queue.

Arguments:

• cleanroom_names (String) - Array of clean room names in the consumer’s account. These clean rooms must be installed.

• template_name (String) - Name of the template to run in each of the clean rooms listed in cleanroom_names. This can be a Snowflake-provided template that is added by the clean room provider, or a custom template that you previously submitted to the clean room by calling consumer.create_template_request. In either case, the template must already be present in a clean room for the request to be submitted to the clean room provider.

• arguments (Object) - An object with the following fields, used to populate the template:

  • source_table (String array) - An array of table names that are used to populate the source_table array available to the template. The syntax for each table name is cleanroom_name.db.schema.table. Each provider sees only their own clean room tables listed in the request.

  • my_table (String array) - An array of table names of your own data that are used to populate the my_table array available to the template. Use the same syntax passed to the consumer_tables argument of consumer.run_analysis.

  • Any other template variables - Pass in any required values for the template as key-value pairs.

Returns: (String) Request ID to pass in to consumer.execute_multiprovider_flow.

Example:

CALL samooha_by_snowflake_local_db.consumer.prepare_multiprovider_flow(
    [$cleanroom_name_1, $cleanroom_name_2],
    'prod_aggregate_data',
    object_construct(
        'source_table', [
            CONCAT($cleanroom_name_1, '.SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'),
            CONCAT($cleanroom_name_2, '.SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS')
        ],
        'my_table', ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']),
        'hem_col', ['p1.HASHED_EMAIL', 'p2.HASHED_EMAIL'],
        'dimensions', ['p1.STATUS', 'p2.STATUS'],
        'consumer_join_col', 'HASHED_EMAIL'
    )
);

consumer.execute_multiprovider_flow¶

Description: Runs the most recent query submitted by the consumer to consumer.prepare_multiprovider_flow. The procedure runs the query in each specified clean room where the query was approved by the provider and returns the union of all query results. Running this procedure is the only way to tell whether a provider has approved a query.

Arguments:

• cleanroom_names (String) - Array of clean room names where the prepared query should be run. This array must match the full list of clean rooms in the query request.

• request_id (String, optional) - Request ID returned by prepare_multiprovider_flow.

Returns: (Table) Union of results from all approved clean rooms. If a provider has never approved a query from this consumer, the query will fail. However, if a provider has approved a different query from this consumer but not the most recent query, the procedure will return an empty result set.

Example:

CALL samooha_by_snowflake_local_db.consumer.execute_multiprovider_flow([$cleanroom1, $cleanroom2], $request_id);

consumer.register_db¶

Description: Register a database in an account to be able to link any objects from that database into a clean room in that account. For more fine-grained control you can call register_schema, register_managed_access_schema, or register_object instead. Objects added to the database after it has been registered might not be linkable, in which case you should re-register the database (or register the object itself).

You must have MANAGE GRANTS privileges on the database to run this procedure.

Arguments:

• db_name (String) - Name of database to register in this account.

Returns: (String) Success message.

Example:

USE ROLE <ROLE_WITH_MANAGE GRANTS>;

CALL samooha_by_snowflake_local_db.consumer.register_db('SAMOOHA_SAMPLE_DATABASE');

library.register_schema¶

Description: Register a schema in an account to be able to link any objects from that schema into a clean room in that account. For more fine-grained control you can call register_object instead. Objects added to the schema after it has been registered might not be linkable, in which case you should re-register the schema (or register the object itself).

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_names (Array of string) - Array of fully qualified schemas to register.

Returns: (String) Success message.

Example:

USE ROLE <ROLE_WITH_MANAGE GRANTS>;

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

library.register_managed_access_schema¶

Description: Register a managed access schema in an account to be able to link any objects from that schema into a clean room in that account. For more fine-grained control you can call register_object instead. Objects added to the schema after it has been registered might not be linkable, in which case you should re-register the schema (or register the object itself).

Arguments:

• schema_names (Array of string) - Array of fully qualified managed schemas to register.

Returns: (String) Success message.

Example:

USE ROLE <ROLE_WITH_MANAGE GRANTS>;

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 consumer.link_datasets. You can register broader groups of objects by calling library.register_schema, library.register_managed_access_schema, or consumer.register_db. You must have MANAGE GRANTS privileges on the database to run this procedure.

Arguments:

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

Returns: (String) Success message.

Examples

To register a table and a view:

USE ROLE <ROLE_WITH_MANAGE GRANTS>;

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

library.enable_external_tables_on_account¶

Description: Enable Iceberg or external tables to be used in all clean rooms in this account. Must be called by an ACCOUNTADMIN in both the provider and consumer accounts to allow Iceberg or external tables to be linked by either account. To limit this ability to specific clean rooms in this account, call enable_external_tables_for_cleanroom instead.

Arguments: None

Returns: (String) Success message.

Example:

USE ROLE ACCOUNTADMIN;

CALL samooha_by_snowflake_local_db.library.enable_external_tables_on_account();

consumer.enable_external_tables_for_cleanroom¶

Description: Enable Iceberg or external tables to be linked into in the specified clean room in this account by the consumer. To allow Iceberg and external tables for all clean rooms in this account, call enable_external_tables_on_account instead.

Arguments:

• cleanroom_name (String) - The name of the clean room into which the provider can link Iceberg tables or external tables.

Returns: (String) Success message. If successful, it triggers a security scan and also provides the number of the patch that is generated if the security scan succeeds.

Example:

CALL samooha_by_snowflake_local_db.provider.enable_external_tables_for_cleanroom(
    $cleanroom_name);

library.unregister_db¶

Description: Removes the database-level grants given to the SAMOOHA_APP_ROLE role and Snowflake Data Clean Room native application. Any data in this database that is linked into a clean room will no longer be accessible in this account. You must have MANAGE GRANTS privileges on the database to run this procedure.

Arguments:

• db_name (String) - Name of the database to unregister.

Returns: (String) Success message.

Example:

USE ROLE <ROLE_WITH_MANAGE GRANTS>;

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

library.unregister_schema¶

Description: Unregisters one or more schemas, which prevents users from linking their 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. You must have MANAGE GRANTS privileges on the database to run this procedure.

Arguments:

• schema_names (Array of string) - Fully qualified names of schemas to unregister.

Returns: (String) Success message.

Example:

USE ROLE <ROLE_WITH_MANAGE GRANTS>;

CALL samooha_by_snowflake_local_db.library.unregister_schema(['SAMOOHA_SAMPLE_DATABASE.PUBLIC', 'MY_DB.MY_SCH']);

library.unregister_managed_access_schema¶

Description: Unregisters one or more managed access schemas, which prevents users from linking their tables and views into the clean room.

Arguments:

• schema_names (Array of string) - Fully qualified names of schemas to unregister.

Returns: (String) Success message.

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 are no longer available to any users in any clean rooms managed by this account.

Arguments:

• object_names (array) - Array of fully qualified object names to revoke access to.

Returns: (String) Success message.

Examples

To unregister a table and a view:

USE ROLE <ROLE_WITH_MANAGE GRANTS>;

CALL samooha_by_snowflake_local_db.library.unregister_objects(
    ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS','MY_DB.MY_SCH.MY_VIEW']);

consumer.link_datasets¶

Description: Link a table or view into the clean room, giving templates within that clean room access to the table, according to any join and column policies that you specify.

Arguments:

• cleanroom_name (String) - Name of the clean room to link data into.

• full_tables (Array of string) - List of fully qualified table or view names to expose to the clean room. These objects must first be registered (made available to the clean room environment) with the appropriate registration method.

Returns: (String) Success message.

Example:

CALL samooha_by_snowflake_local_db.consumer.link_datasets(
  $cleanroom_name,
  ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS', 'MY_DB.MY_SCH.EXPOSURES']);

consumer.unlink_datasets¶

Description: Removes access to the specified tables or views in the specified clean room for all users. This works only for data that you have linked into the clean room.

Arguments:

• cleanroom_name (String) - Name of the clean room for which access should be removed.

• tables_list (Array of string) - List of fully qualified table or view names for which access should be blocked.

Returns: (String) Success message.

Example:

CALL samooha_by_snowflake_local_db.consumer.unlink_datasets($cleanroom_name,
  ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS', 'MYDB.MYSCH.EXPOSURES']);

consumer.view_consumer_datasets¶

Description: View all tables and views linked into the specified clean room by any consumer.

Arguments:

• cleanroom_name (String) - Name of the clean room.

Returns: Table of objects linked into the specified clean room, along with the clean room’s internal view name for each object.

Example:

CALL samooha_by_snowflake_local_db.consumer.view_consumer_datasets($cleanroom_name);

consumer.set_join_policy¶

Description: Specifies which columns the provider is allowed to join on when they run templates within the clean room and when they use provider-run analysis. 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. If you don’t specify a join policy for your data, all columns can be joined on.

Queries with wildcards might circumvent a join policy, so use discretion when you design your analysis template.

Arguments:

• cleanroom_name (String) - Name of the clean room where the join policy is applied.

• table_col_names (String array) - Fully qualified names of columns that can be joined, in the format database name.schema name:column name

Returns: (String) Success message.

Example:

CALL samooha_by_snowflake_local_db.consumer.set_join_policy($cleanroom_name,
  ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:HASHED_EMAIL', 'MYDB.MYSCH.EXPOSURES:HASHED_EMAIL']);

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

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

consumer.set_column_policy¶

Description: Defines which columns the consumer data can be projected. The column policy is applied to a template in a clean room. 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. If you do not specify a column policy, all columns can be projected.

Don’t set a column policy on identity columns or sensitive columns like email because you generally don’t want this sort of data to be projected.

Queries with wildcards might not be caught by using these checks, so use discretion when you design the analysis template.

Arguments:

• cleanroom_name (String) - Name of the clean room where the column policy is applied.

• analysis_table_cols(String array) - Fully qualified names of columns that can be projected, in the format database name.schema name:column name

Returns: (String) Success message.

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

consumer.view_column_policy¶

Description: Show all consumer column policies in the specified clean room. To see any column policies set by the provider, call consumer.view_provider_column_policy.

Arguments:

• cleanroom_name (String) - Name of the clean room to describe.

Returns: (Table) Information about all consumer column policies in the clean room.

Example:

CALL samooha_by_snowflake_local_db.consumer.view_column_policy($cleanroom_name);

consumer.view_provider_column_policy¶

Description: Views all column policies that were 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);

consumer.view_template_definition¶

Description: View the raw JinjaSQL of the specified template. If a template was obscured by applying the is_obfuscated argument, you can’t see the template source code.

Arguments:

• cleanroom_name (String) - Name of the clean room that holds the template.

• template_name (String) - Name of the template to view.

Returns: (String) The template definition.

Example:

CALL samooha_by_snowflake_local_db.consumer.view_template_definition(
  $cleanroom_name,
  'prod_overlap_analysis');

consumer.get_arguments_from_template¶

Description: Get a list of arguments used by the template. You can pass values for these argument into the template when you call consumer.run_analysis.

Arguments:

• cleanroom_name (String) - Name of the clean room that has the template.

• template_name (String) - Name of the template to return arguments for.

Returns: (Table) Argument list and specification.

Example:

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

consumer.view_added_template_chains¶

Description: List all template chains defined in a given clean room.

Arguments:

• cleanroom_name (String) - Name of the clean room to list template chains for.

Returns: (Table) Information about any template chains in the specified clean room.

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 specified template chain.

Arguments:

• cleanroom_name (String) - Name of the clean room with the template chain to describe.

• template_chain_name (String) - Name of the template chain to describe.

Returns: (String) The definition of the specified template chain.

Example:

CALL samooha_by_snowflake_local_db.consumer.view_template_chain_definition(
  $cleanroom_name,
  'insights_chain');

consumer.run_analysis¶

Description: Runs an analysis by using a template or template chain and returns the results table.

Arguments:

• cleanroom_name (String) - Name of the clean room in which to run the analysis.

• template_name (String) - Name of the template or template chain to run in the clean room. This template must have been added to the clean room by the provider or consumer.

• consumer_tables (Array of string) - Array of fully qualified consumer table names. These are assigned to the my_table template variable. These tables must already be linked into the clean room. See available tables by calling consumer.view_consumer_datasets.

• provider_tables (Array of string) - Array of fully qualified provider table names. These are assigned to the source_table template variable. These tables must have been linked into the clean room. See available tables by calling consumer.view_provider_datasets.

• analysis_arguments (Object) - An object with key-value pairs passed to the template. The template can access the variable by key name. If you pass in {'age': 20}, the template accesses the value as {{age}}. Pass in an empty object if no values are required. To see which values are required, examine the template in question by calling consumer.view_template_definition. Examine the template to determine whether you need to fully qualify any column names used. If the table is aliased as p or c in the template, use lowercase p and c table aliases for column names.

  This object has one optional reserved value:

  • epsilon (Float, optional) - Specifies the epsilon value for differential privacy (link removed), if differential privacy is enabled for this clean room. Default is 0.1.

• use_cache (Boolean, optional) - Whether or not to use cached results for the same query. Default is FALSE.

Returns: (Table) Query results.

Example:

CALL samooha_by_snowflake_local_db.consumer.run_analysis(
  $cleanroom_name,
  'prod_overlap_analysis',
  ['DB1.MYDATA.CONVERSIONS'],  -- Consumer tables
  ['MYDB.MYSCH.EXPOSURES'],    -- Provider tables
  object_construct(
    'max_age', 30
  )
);

library.view_external_activation_history¶

Description: View the history of activation requests in the current account.

Arguments: None

Returns: A table with the details and status of activation requests.

Example:

call SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.LIBRARY.view_external_activation_history();

consumer.set_activation_policy¶

Description: Defines which columns can be used in an activation template. This ensures that only the columns that are approved by the consumer can be used with the activation template. If you don’t define an activation policy, then no columns of your data can be activated. Setting an activation policy overwrites any existing activation policy for your account.

Arguments:

• cleanroom_name (String) - Name of the cleanroom in which to set the activation policy.

• columns (array) - Name of columns of your own data that can be activated, in the format template name:database name.schema name.table name:column_name.

Returns: (String) Success message.

Example:

CALL samooha_by_snowflake_local_db.consumer.set_activation_policy(
  $cleanroom_name,
  [
    'prod_overlap_analysis:SAMOOHA_SAMPLE_DATABASE_NAME.DEMO.CUSTOMERS:HASHED_EMAIL',
    'prod_overlap_analysis:SAMOOHA_SAMPLE_DATABASE_NAME.DEMO.CUSTOMERS:REGION_CODE' ]);

consumer.approve_provider_activation_consent¶

Description: Approves a provider’s request to allow provider activation, which is the ability to push results to the provider’s Snowflake account.

Arguments:

• cleanroom_name (String) - Name of the clean room where the provider is requesting to run a template.

• activation_template_name (String) - Name of the activation template that the provider wants to run.

Returns: (String) Success message. This procedure fails if the provider has not called provider.request_provider_activation_consent in this clean room with the specified template.

Example:

CALL samooha_by_snowflake_local_db.consumer.approve_provider_activation_consent(
  $cleanroom_name,
  'activation_my_template');

consumer.run_activation¶

Description: Runs a template that pushes results back to the consumer’s or provider’s Snowflake account. The consumer_direct_activation argument determines whether this is a consumer or provider activation.

Arguments:

• cleanroom_name (String) - Name of the clean room in which to run the activation.

• segment_name (String) - Arbitrary string used to label rows that are generated by this activation run. Each activation run adds new rows to an existing results table. Provide a unique string in this field each time you call this procedure to be able to filter results to a specific run.

• template_name (String) - Name of the activation template to call.

• consumer_tables (Array of string) - Array of fully qualified consumer table names to pass to the template.

• provider_tables (Array of string) - Array of fully qualified provider table names to pass to the template.

• activation_arguments (Object) - Key-value set of arguments to pass to the template.

• consumer_direct_activation (Boolean, optional) - TRUE to push results back to the consumer account, FALSE to send results to the provider. Default is FALSE.

Returns: (String) Success message.

Example:

-- Run a consumer activation, as specified by the final TRUE argument.
SET segment_name = 'my_activation_segment';
CALL samooha_by_snowflake_local_db.consumer.run_activation(
  $cleanroom_name,
  $segment_name,
  $template_name,
  ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'],
  ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'],
  object_construct(
    'c_join_col', 'c.hashed_email',
    'p_join_col', 'p.hashed_email'
  ),
  TRUE);

consumer.create_template_request¶

Description: Sends a request to a clean room provider, asking them to approve a custom template to add to the clean room. See Using the developer API to add consumer-defined templates.

Arguments:

• cleanroom_name (String) - Name of the clean room where the template is added.

• template_name (String) - Name of the template to add. Must be all lowercase letters, numbers, spaces, or underscores. Activation template names must start with “activation”.

• template_definition (String) - The JinjaSQL template. Learn the template syntax.

Returns: (String) Success message.

Example:

CALL samooha_by_snowflake_local_db.consumer.create_template_request(
  $cleanroom_name,
  $template_name,
  $$
  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.get_sql_jinja¶

Description: Evaluates a JinjaSQL template to a SQL statement. This procedure is used when developing custom templates, to see how the template is rendered after processing with a given set of parameters.

This procedure can process only standard JinjaSQL (https://github.com/sripathikrishnan/jinjasql) statements; it can’t process clean room extensions to JinjaSQL such as join_policy or column_policy.

Arguments:

• template_string (String) - The JinjaSQL code to process. Only standard JinjaSQL is supported.

• arguments (Object) - An object where field names correspond to variables that are used in the template.

Returns: (String) The SQL statement generated by the submitted template with the provided variable values.

Example:

CALL samooha_by_snowflake_local_db.consumer.get_sql_jinja(
$$
SELECT COUNT(*), IDENTIFIER({{ group_by_col }})
  FROM IDENTIFIER({{ my_table | sqlsafe }})
  INNER JOIN IDENTIFIER({{ source_table | sqlsafe }})
  ON IDENTIFIER({{ consumer_join_col }}) = IDENTIFIER({{ provider_join_col }})
  GROUP BY IDENTIFIER({{ group_by_col }});
$$,
object_construct(
'group_by_col', 'city',
'consumer_join_col', 'hashed_email',
'provider_join_col', 'hashed_email',
'my_table', 'mydb.mysch.t1',
'source_table', 'mydb.mysch.t2'));

Response:

SELECT COUNT(*), IDENTIFIER('city')
  FROM IDENTIFIER(mydb.mysch.t1)
  INNER JOIN IDENTIFIER(mydb.mysch.t2)
  ON IDENTIFIER('hashed_email') = IDENTIFIER('hashed_email')
  GROUP BY IDENTIFIER('city');

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

For more information about uploading custom code into a clean room, see Upload and run custom functions in a clean room.

Arguments:

• function_name (String) - The function name that is used by a template to call your function.

• arguments (Array of string pairs) - An array of arguments required by function function_name. Each element is a space-delimited pair that gives the argument name and its Snowflake SQL data type. For example: ['size INT', 'start_date DATE'].

• packages (Array of string) - Array of package names required for your Python code. If none, specify an empty array. See the full list of supported packages. (https://repo.anaconda.com/pkgs/snowflake/). Example: ['pandas','numpy'].

• imports - [Not supported: Do not use]

• rettype (String) - The Snowflake 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 in an import, this can be an empty string.

Returns: (String) Returns your Python UDF with a placeholder for your JinjaSQL template. You must escape any nested $$ or single-quote marks (’) correctly before passing your template string into consumer.create_template_request. Read Consumer-submitted 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.
  [],                                -- Reserved.
  'INTEGER',                         -- SQL return type.
  'main',                            -- Standard 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)
    $$
);

The following example shows the generated code. Replace <INSERT SQL TEMPLATE HERE> with your template JinjaSQL code.

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 run the SQL query
LET SQL_TEXT varchar := $$<INSERT SQL TEMPLATE HERE>$$;

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

END;  

consumer.list_template_requests¶

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

Arguments:

• cleanroom_name (String) - The clean room to list template requests for.

Returns: A table with the following columns:

• request_id - ID of the request, generated by the clean rooms system.

• provider_identifier - Provider’s account locator.

• template_name - Template name that the consumer provided in the request.

• template_definition - Source code of the template that the consumer asked to add to the clean room.

• request_status - Status of the request: PENDING, APPROVED, or REJECTED.

• reason - If the request status is REJECTED, the provider should give a reason for the rejection here.

Example:

CALL samooha_by_snowflake_local_db.consumer.list_template_requests($cleanroom_name);

consumer.describe_cleanroom¶

Description: Provides a summary of key information about the specified clean room, including templates, datasets, and policies. If a template was obscured by applying the is_obfuscated argument, you must use Snowflake Enterprise Edition or higher to be able to see the template name.

Arguments:

• cleanroom_name (String) - Name of the clean room to describe.

Returns: (String) Description of the clean room.

Example:

CALL samooha_by_snowflake_local_db.consumer.describe_cleanroom($cleanroom_name);

consumer.view_provider_datasets¶

Description: Lists all datasets that the provider added to the clean room.

Arguments:

• cleanroom_name (String) - Name of the clean room.

Returns: (Table) A table of datasets added by the provider. Use the table name returned here in your queries.

Example:

CALL samooha_by_snowflake_local_db.consumer.view_provider_datasets($cleanroom_name);

consumer.view_added_templates¶

Description: Lists all templates in the clean room. If a template was obscured by applying the is_obfuscated argument, you must use Snowflake Enterprise Edition or higher to be able to view the template.

Arguments:

• cleanroom_name (String) - Name of the clean room.

Returns: A list of templates in this clean room, and the source code for each (unless the template was obscured by the provider).

Example:

CALL samooha_by_snowflake_local_db.consumer.view_added_templates($cleanroom_name);

library.is_consumer_run_enabled¶

Description: Checks whether consumer-run analysis is enabled for the specified clean room. This is enabled by default, but a clean room provider can disable it.

Arguments:

• cleanroom_name (String) - Name of the clean room.

Returns: (String) Whether or not the clean room allows consumer-run analyses.

Example:

CALL samooha_by_snowflake_local_db.library.is_consumer_run_enabled($cleanroom_name);

consumer.view_cleanrooms¶

Description: Lists all clean rooms that are joined (installed) or that are joinable by this account. To see only installed clean rooms, run consumer.view_installed_cleanrooms. To see clean rooms created by this account, call provider.view_cleanrooms.

Arguments: None

Returns: (Table) All installed or invited clean rooms for this account.

Example:

CALL samooha_by_snowflake_local_db.consumer.view_cleanrooms();

consumer.view_installed_cleanrooms¶

Description: Lists all clean rooms that are installed (joined) in this account. To see both joined and unjoined clean rooms, call consumer.view_cleanrooms. To see all clean rooms created by this account, call provider.view_cleanrooms.

Arguments: None

Returns: (Table) The clean rooms installed in this account.

Example:

CALL samooha_by_snowflake_local_db.consumer.view_installed_cleanrooms();

consumer.is_dp_enabled¶

Description: Checks whether differential privacy is enabled in the clean room. The clean room must be installed to check this value.

Arguments:

• cleanroom_name (String)

Returns: (Boolean) Whether or not the clean room has differential privacy enabled.

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. After the budget is exhausted, further calls to run_analysis aren’t allowed until the budget is reset. The budget resets daily.

Arguments:

• cleanroom_name (String) Name of the clean room. The clean room must be installed for this procedure to succeed.

Returns: (Float) The remaining privacy budget.

Example:

CALL samooha_by_snowflake_local_db.consumer.view_remaining_privacy_budget($cleanroom_name);

consumer.set_privacy_settings¶

Description: Sets privacy settings for provider-run analyses (including activation) that use custom templates. Overwrites all previously set values. Each time you call this method it erases all previous configuration settings.

Arguments:

• cleanroom_name (String) - Name of the clean room where these settings should be applied.

• privacy_settings (String) - A string JSON object that specifies privacy settings when custom templates are run by a provider. Here is the syntax of the object:

  '{
    "null" : <template_config>
  }'
  

  Copy

  template_config is an object with differential privacy and aggregation settings. See Available privacy settings to see what fields you can provide in this object.

Example:

-- Apply differential privacy for provider-run analysis using all custom templates.
CALL samooha_by_snowflake_local_db.consumer.set_privacy_settings(
  $cleanroom_name,
  PARSE_JSON('{
    "null":{ "differential": 1, "epsilon": 0.1, "privacy_budget": 3 }
    }')
  );

Returns: (String) Success message.

consumer.start_or_update_service¶

Description: Creates and starts the latest version of Snowpark Container Services that is defined by the provider in this clean room. Any time the provider calls provider.load_service_into_cleanroom to create or update a container, the consumer must call consumer.start_or_update_service to update the service.

The consumer must define and start the pool before calling this procedure.

Arguments:

• cleanroom_name (String) - Name of the clean room where the container should be loaded.

• compute_pool_name (String) - The name of a compute pool that is defined by the consumer in this clean room. The pool must already be created, and the clean room must have privileges to access to the pool.

• service_options (Object, optional) - An object specifying parameters for this service. The following properties are supported:

  • query_warehouse - (String, optional) Name of the warehouse to use for this service. Doesn’t need to be the same warehouse as the one running the clean room.

  • min_instances - (Integer, optional) Minimum number of instances to use for this service.

  • max_instances - (Integer, optional) Minimum number of instances to use for this service.

Returns: (Table) Results of the load, if successful. Throws an error if not successful.

Example:

CALL samooha_by_snowflake_local_db.consumer.start_or_update_service(
    $cleanroom_name,
    'dcr_lal_pool',
    object_construct(
        'query_warehouse', 'app_wh',
        'min_instances', '1',
        'max_instances', '1'
));

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: (String) Success message.

Example:

CALL samooha_by_snowflake_local_db.consumer.set_cleanroom_ui_accessibility(
  $cleanroom_name,
  'HIDDEN');

library.enable_local_db_auto_upgrades¶

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

Arguments: None

Returns: (String) Success message.

Example:

CALL samooha_by_snowflake_local_db.library.enable_local_db_auto_upgrades();

library.disable_local_db_auto_upgrades¶

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

Arguments: None

Returns: (String) Success message.

Example:

CALL samooha_by_snowflake_local_db.library.disable_local_db_auto_upgrades();

consumer.manage_datastats_task_on_account¶

Description: Enables or disables the background task that computes clean room statistics. The task is running by default, but you can disable it to reduce your costs.

Arguments:

• enable (Boolean) - TRUE to enable the task, FALSE to disable the task.

Returns: (String) Success message.

Example:

-- Disable the task in this account.
CALL samooha_by_snowflake_local_db.consumer.manage_datastats_task_on_account(FALSE);

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: (String) Success message.

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: (String) Success message.

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: (String) Success message.

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: (String) Success message.

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: (String) Success message.

Example:

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

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 (String) Success message.

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 can’t 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: (String) Success message.

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: (String) Success message.

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 can’t 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: (String) Success message.

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: (String) Success message.

Example:

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