Using the developer API to add consumer-defined templates¶

In the traditional flow of Snowflake Data Clean Rooms, a provider uses the developer API to create custom templates, then adds them to the clean room that the provider shares with a consumer. The consumer then uses that template to run analyses in the clean room.

In some cases, the consumer wants to be able to create their own custom template to control the type of analysis that can be executed in the clean room. In this scenario, the provider still creates and shares the clean room, but the consumer defines a template within that clean room. Because it is their clean room, the provider must explicitly allow this consumer-defined template to be added to the clean room.

Consumer-contributed clean room templates include JinjaSQL code, and can also include custom Python code.

The ability to add a consumer-defined template to a provider’s clean room is often required in a multi-provider clean room where the consumer is running analyses against data from more than one provider at the same time. In this environment, the consumer might be in the best position to know how to extract insights from data coming from multiple parties, and can use the developer API to create a custom template to do that. With consumer-defined templates in a multi-provider environment, each provider must approve the template before it can be used. For an extended example showing how to use a multi-provider clean room, see Snowflake Data Clean Rooms: Multi-Provider Clean Rooms.

Basic workflow for consumer-defined templates¶

The process of using the developer API to add consumer-defined templates to a provider’s clean room involves coordinated actions by both the consumer and the provider. This basic workflow is:

Consumer:

Sends a request to the provider of the clean room that a custom template be added. This request includes the definition of the template, which is written in the Jinja templating language.

Provider:

Checks for new requests from consumers who want to add templates to the clean room.
After reviewing the template definition, approves or rejects the request.

Consumer:

Checks to see whether their request has been approved. The consumer can check the status of the request at any time.

Request adding a template¶

A consumer executes the consumer.create_template_request command to send a request to the provider of a clean room. This request includes the name of the template along with the Jinja code that defines the template.

For example, suppose the provider shared a clean room dcr_cleanroom with a consumer. The consumer wants to add a template named my_overlap_analysis to the clean room. To send a request to the provider of the clean room, the consumer executes the following command:

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

The third argument is the Jinja code that defines the template.

For reference documentation for the consumer.create_template_request command, see the Consumer-defined Templates section in Snowflake Data Clean Rooms: Consumer API reference guide.

Including Python code in your template¶

Your JinjaSQL template can call Python functions if you include Python in your template.

To include Python code in your template:

Write your Python code.
Call consumer.generate_python_request_template, passing in your code, to produce a template with a placeholder for your JinjaSQL code. consumer.generate_python_request_template can create template code for only a single function. If you need to support multiple Python functions in your SQL template, call generate_python_request_template for each custom Python function and combine the results into a single template.

Check for new requests from consumers¶

To check for new requests from consumers, a provider executes the provider.list_template_requests command, then parses the results to identify requests with a status of PENDING. A PENDING status indicates that the consumer has sent a new request that the provider has not taken action upon. The response to provider.list_template_requests includes the Jinja code of the template, which lets the provider decide whether they want to approve or reject the request.

For example, suppose the name of the provider’s clean room is dcr_cleanroom. To check whether the consumer has sent a new request to add a template, the provider executes the following commands:

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

SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) WHERE status = 'PENDING';

Copy

The response to the provider.list_template_requests command includes the following for every template request:

UUID that uniquely identifies the request.
Consumer account that sent the request, in the account locator format.
Template name, which was specified by the consumer when they sent the request.
Jinja code that defines the template.
Status of the request, which can be one of PENDING, APPROVED, or REJECTED.

For reference documentation for the provider.list_template_requests command, see the Consumer-defined Templates section in Snowflake Data Clean Rooms: Provider API reference guide.

Approve or reject a request¶

After receiving a request to add a consumer-defined template, the provider decides whether to approve or reject the request.

Approve a request

Providers execute the provider.approve_template_request command to approve a request to add a template. The provider specifies which template to approve by passing the request identifier as an argument.

For example, suppose the name of the provider’s clean room is dcr_cleanroom and the consumer’s request to add a template was assigned the identifier 01b4d41d-0001-b572. To approve the request and add the template to the clean room, the provider executes the following command:

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

Copy

Reject a request

Providers execute the provider.reject_template_request command to reject a request to add a template. The provider specifies which template to reject by passing the request identifier as an argument.

For example, suppose the name of the provider’s clean room is dcr_cleanroom and the consumer’s request to add a template was assigned the identifier 01b4d41d-0001-b572. After examining the Jinja template, the provider determines that it introduces a security risk into the clean room, so they want to reject the request with a descriptive reason. To reject the request to add the template, the provider executes the following command:

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

Copy

For reference documentation for the provider.approve_template_request and provider.reject_template_request commands, see the Consumer-defined Templates section in Snowflake Data Clean Rooms: Provider API reference guide.

Check status of request as a consumer¶

At any time, the consumer can execute the consumer.list_template_requests command to check the status of their request to add a template. The command returns the following information:

UUID that uniquely identifies the request.
Provider account to which the request was sent, in the account locator format.
Template name, which was specified by the consumer when they sent the request.
Jinja code that defines the template.
Status of the request, which can be one of PENDING, APPROVED, or REJECTED.
If the request was rejected, the reason for the provider’s action.

For example, suppose the consumer sent a request to add a template to the dcr_cleanroom clean room. To check the status of the request to determine if it was approved, the consumer executes the following command:

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

Copy

For reference documentation for the consumer.list_template_requests command, see the Consumer-defined Templates section in Snowflake Data Clean Rooms: Consumer API reference guide.

List all requests from consumers¶

A provider executes the provider.list_template_requests command to view all requests, including requests that have been approved or rejected.

For example, suppose the name of the provider’s clean room is dcr_cleanroom. To list all requests that have been made regardless of the outcome, the provider executes the following command:

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

Copy

For reference documentation for the provider.list_template_requests command, see the Consumer-defined Templates section in Snowflake Data Clean Rooms: Provider API reference guide.