Activating query results¶

Overview of activation¶

A collaborator can send template results outside the clean room in a process called activation. The template must support activation, and each data provider must approve activation at the column level in their data offering specification.

Activation is implemented using a dedicated activation template. An activation template doesn’t return results to the query runner, but instead writes them to a results table in the target user’s account.

Implementing activation¶

Here are the steps to implement activation:

1. You must use a role that has the REGISTER DATA OFFERING privilege to join any collaboration where you are an analysis runner and the collaboration specification includes an activation_destinations field.

2. Ensure that all specifications are properly configured:

   Data offering specCollaboration specAnalysis spec

   The data offering specification for the table with the activated column must set activation_allowed: TRUE for that column:

   CopyExpand

    api_version: 2.0.0
    spec_type: data_offering
    name: 2025_orders
    version: 2025_01_01_v1
    description: Activating Cleveland sales results for 2025
   
    datasets:
     - alias: customers
       data_object_fqn: db1.schema1.orders
       allowed_analyses: template_only
       object_class: custom
       schema_and_template_policies:
         email:
           category: join_standard
           column_type: hashed_email_sha256
           activation_allowed: TRUE
         purchase_amount:
           category: passthrough
           activation_allowed: TRUE
   

   Show lessSee more

   Scroll to top

3. You must use an activation template. This template saves results to an internal table. All projected columns from this template are activated.

   Any column in the template with the activation_policy filter applied must have activation_allowed: TRUE in the data offering specification.

   Note

   If a template doesn’t apply the activation_policy filter to a column, the column can be activated whether or not activation_allowed: TRUE is set for that column in the data offering spec.

   The following example shows a template with the activation policy applied to two columns supplied by the analysis runner:

   CopyExpand

   BEGIN
     CREATE OR REPLACE TABLE cleanroom.activation_data_analysis_results AS
       SELECT count(*) AS ITEM_COUNT, c.status, c.age_band
       FROM IDENTIFIER({{ my_table[0] }}) AS c
       JOIN IDENTIFIER({{ source_table[0] }}) AS p
       ON {{ c_join_col | sqlsafe | activation_policy }} = {{ p_join_col | sqlsafe | activation_policy }}
       GROUP BY c.status, c.age_band
       ORDER BY c.age_band;
     RETURN 'analysis_results';
   END;
   

   Show lessSee more

   Scroll to top

4. The analysis runner calls RUN to run the analysis and activate the results.

   • If activating to yourself, results are available immediately in the caller’s account.

   • If activating to another collaborator:

     1. The collaborator calls VIEW_ACTIVATIONS until it returns a status of SHARED.

        Activating to another account can take considerable time for large result sets, as the data must be shared to the collaborator’s account. Cross-cloud collaborators will also experience additional delays due to replication frequency settings.

     2. When the status of the activation is SHARED, the collaborator calls PROCESS_ACTIVATION to send the results to their account.

        The response to PROCESS_ACTIVATION includes the table and segment names. This sets the activation status to PROCESSED.

5. The analysis runner can read results as described in the next section.

Reading the activation results¶

When activation is complete, as described in the previous section, results are stored in the collaboration_name.activation.segment_records table in your account.

The table has the following schema:

To retrieve the activation results, run the following SQL command, optionally filtering by segment name:

SELECT *
  FROM <collaboration_name>.activation.segment_records
    [WHERE segment_name = '<segment_name>'];