Creating custom clean room templates¶

Data protection¶

Templates can access only datasets linked into the clean room by the provider and consumer.

Both the provider and consumer have the ability to set join, column, and activation policies on their data to protect which columns can be joined on, projected, or projected in activated results.

A quick example¶

Here is a simple SQL example that joins a provider and a consumer table by email and shows the overlap count per city:

SELECT COUNT(*), city FROM consumer_table
  INNER JOIN provider_table
  ON consumer_table.hashed_email = provider_table.hashed_email
  GROUP BY city;

Here is how that query would look as a template that allows the caller to choose the select/group and join columns and the tables:

SELECT COUNT(*), IDENTIFIER({{ group_by_col | column_policy }})
  FROM IDENTIFIER({{ my_table[0] }}) AS C
  INNER JOIN IDENTIFIER({{ source_table[0] }}) AS P
  ON IDENTIFIER({{ consumer_join_col | join_policy }}) = IDENTIFIER({{ provider_join_col | join_policy }})
  GROUP BY IDENTIFIER({{ group_by_col | column_policy }});

Notes on the template:

• Values within {{ double bracket pairs }} are custom variables. group_by_col, my_table, source_table, consumer_join_col, provider_join_col, and group_by_col are all custom variables populated by the caller.

• source_table and my_table are Snowflake-defined string array variables populated by the caller. Array members are fully-qualified names of provider and consumer tables linked into the clean room. The caller specifies which tables should be included in each array.

• Provider tables must be aliased as P and consumer tables as C in a template. If you have multiple tables, you can index them as P1, P2, C1, C2, and so on.

• IDENTIFIER is needed for all column and table names, because variables in {{ double brackets }} evaluate to string literals, which aren’t valid identifiers.

• JinjaSQL filters can be applied to variables. Snowflake implements the custom filters join_policy and column_policy, which verify whether a column complies with join or column policies in the clean room respectively, and fail the query if it does not. A filter is applied to a column name as {{ column_name | filter_name }}.

All these points will be discussed in detail later.

Here is how a consumer might run this template in code. Note how column names are qualified by the table aliases declared in the template.

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.CONSUMER.RUN_ANALYSIS(
  $cleanroom_name,
  $template_name,
  ['my_db.my_sch.consumer_table],       -- Populates the my_table variable
  ['my_db.my_sch.provider_table'],      -- Populates the source_table variable
  OBJECT_CONSTRUCT(                     -- Populates custom named variables
    'consumer_join_col','c.age_band',
    'provider_join_col','p.age_band',
    'group_by_col','p.device_type'
  )
);

In order to use this template in the web application, the provider must create a custom UI form for the template. The UI form has named form elements that correspond to template variable names, and the values provided in the form are passed into the template.

Template variables¶

Template callers can pass in values to template variables. JinjaSQL syntax enables variable binding for any variable name within {{ double_brackets }}, but Snowflake reserves a few variable names that you should not override, as described below.

SELECT income FROM my_db.my_sch.customers WHERE income < {{ max_income }};

Required table aliases¶

At the top level of your query, all tables or subqueries must be aliased as either P (for provider-tables) or C (for consumer tables) in order for Snowflake to validate join and column policies correctly in the query. Any column that must be verified against join or column policies belong to a table that is aliased as either P or C. (Specifying P or C tells the back end whether to validate a column against the provider or the consumer policy respectively.)

If you use multiple provider or consumer tables in your query, add a numeric, sequential 1-based suffix to each table alias after the first. So: P, P1, P2, and so on for the first, second, and third provider tables, and C, C1, C2, and so on for the first, second, and third consumer tables. The P or C index should be sequential without gaps (that is, create the aliases P, P1, and P2, not P, P2, and P4).

Example

SELECT col1 FROM IDENTIFIER({{ source_table[0] }}) AS P;

Template filters¶

Snowflake supports most of the standard JinjaSQL filters, along with a few custom filters:

• join_policy: Verifies whether the column is allowed by the table’s join policy, and fails if it is not.

• column_policy: Verifies whether the column is allowed by the template’s column policy (is allowed to be projected).

• activation_policy: Verifies whether the filtered column is allowed by the clean room’s activation policies (provider.set_activation_policy or consumer.set_activation_policy).

• join_and_column_policy: Verifies whether the column is permitted by the join, activation, or column policies. Used to provide more flexibility in the clean room, to allow the collaborators to update join and column policies without changing the template.

• sql_safe: Standard JinjaSQL filter. Use to convert string literals into SQL statements or identifiers as needed.

• bind: Standard JinjaSQL filter.

• inclause: Standard JinjaSQL filter.

• The `identifier` JinjaSQL filter is not supported by Snowflake templates.

JinjaSQL filters modify the content that precedes it:

• {{ my_col | column_policy }} Correct

• {{ column_policy | my_col }} Incorrect

Running custom Python code¶

Templates can run Python code uploaded to the clean room. The template can call a Python function that accepts values from a row of data and returns values to use or project in the query.

• When a provider uploads custom Python code into a clean room, the template calls Python functions with the syntax cleanroom.function_name. More details here.

• When a consumer uploads custom Python code into a clean room, the template calls the function with the bare function_name value passed to consumer.generate_python_request_template (not scoped to cleanroom as provider code is). More details here.

Provider code example:

-- Provider uploads a Python function that takes two numbers and returns the sum.
call samooha_by_snowflake_local_db.provider.load_python_into_cleanroom(
  $cleanroom_name,
  'simple_addition',                        -- Function name to use in the template
  ['someval integer', 'added_val integer'], -- Arguments
  [],                                       -- No packages needed
  'integer',                                -- Return type
  'main',                                   -- Handler for function name
  $$

def main(input, added_val):
  return input + int(added_val)
    $$
);

-- Template passes value from each row to the function, along with a
-- caller-supplied argument named 'increment'
call samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name,
    'simple_python_example',
$$
    SELECT val, cleanroom.simple_addition(val, {{ increment | sqlsafe }})
    FROM VALUES (5),(8),(12),(39) AS P(val);
$$
);