Using the developer APIs to send results to provider for activation¶

Sometimes collaborators want the consumer to run an analysis, then push the results back to the provider for activation. This topic describes how to use the developer APIs to send results from the consumer to the provider.

The basic flow for provider activation is:

Provider:

Creates a template that the consumer can execute to send results to the provider for activation.
Links the samooha_by_snowflake_local_db.library.temp_public_key table into the clean room.

Consumer:

Executes the provider’s template to send results back to the provider’s Snowflake account.

Provider:

Views results in a table in their Snowflake account.

Create a template for provider activation¶

A template used to activate results is similar to a template used to run an analysis. For example, it is added to the clean room with the provider.add_custom_sql_template command like an analysis template. However, an activation template has the following important differences:

Name of the activation template must begin with the string activation. For example, an activation template named activation_my_template meets the requirement.
Activation template must create a table with a name that conforms to specific conventions and explicitly returns the table name.

Defining the table in an activation template¶

To hold the activation results in the clean room, every activation template must create a table. There are two requirements for this table:

Name of the table must begin with the string cleanroom.activation_data_. For example, a table named cleanroom.activation_data_analysis_results meets the requirement.
The portion of the template definition that creates the table must return the string that was appended to cleanroom.activation_data_ to form the name of the table. For example, if the table name is cleanroom.activation_data_analysis_results, then the string analysis_results must be returned.

The following is an example of what must be included in the template definition to define a table and export it:

BEGIN
  CREATE OR REPLACE TABLE cleanroom.activation_data_analysis_results AS
    SELECT * FROM identifier({{ my_table[0] }})
  RETURN 'analysis_results';
END;

Copy

For more information about the calling the provider.add_custom_sql_template command to add an activation template to a clean room, see Snowflake Data Clean Rooms: Provider API reference guide.

Link required table¶

To enable provider activation, the provider must link the samooha_by_snowflake_local_db.library.temp_public_key table into the clean room before sharing it with the consumer. For example, to link the required table into a clean room my_activation_cleanroom, execute:

CALL samooha_by_snowflake_local_db.provider.link_datasets(
    'my_activation_cleanroom',
    ['samooha_by_snowflake_local_db.library.temp_public_key']);

Copy

You could also use a single call to link the required table when you link your other tables and views.

Send results back to provider¶

The consumer sends results back to the provider by calling the consumer.run_activation command to execute the activation template, which is similar to executing the consumer.run_analysis command. The difference is the second argument, which specifies a string that helps the provider identify the contents of the activation.

For example, the consumer can call:

CALL samooha_by_snowflake_local_db.consumer.run_activation(
  'activation_clean_room',
  'my_activation_segment',
  'activation_custom_template',
  ['consumer_source_table'],
  ['provider_source_table'],
  object_construct(
    'dimensions', ['p.CAMPAIGN'],
    'where_clause', 'p.EMAIL=c.EMAIL'
));

Copy

For more information about the syntax of the consumer.run_activation command, see Snowflake Data Clean Rooms: Consumer API reference guide.

View activated results as a provider¶

After the consumer activates the results to the provider, the provider can view these results in their Snowflake account (not the clean room environment). Note that the provider must sign in to their clean room environment before signing in to their Snowflake account to view the results.

For more information, see View activated results.