Understanding clean room table policies¶

Clean rooms provide some data policies to control how data can be used by collaborators. These are in addition to any Snowflake table policies set on the underlying tables linked into the clean room. Any changes to the policies in the underlying tables are propagated to the linked views in the clean room without requiring you to call create_or_update_cleanroom_listing or to re-link the source tables.

Each collaborator in a clean room sets their own policies on their own data.

Note

When you link tables into a clean room, Snowflake table policies on the source tables are enforced in the linked tables in the clean room, but these policies aren’t necessarily reported by the clean room API or UI. For instance, a Snowflake join policy continues to be enforced in the clean room, but that join policy is not visible by calling consumer.view_provider_join_policy or consumer.view_join_policy. Therefore, you should either remove policies from the underlying linked tables, create equivalent clean room policies (when they exist), or communicate the existence of these policies clearly to your collaborators so that their queries don’t fail or behave unexpectedly (“why can’t I join on this column?”).

Clean room policies can be set using either the clean room API or UI.

Snowflake privacy policies prevent creation of a view from a protected table, so you cannot link in tables that have privacy policies.

Clean rooms exposes the following policies:

Clean room policy	Description	Equivalent Snowflake policy
Join policies	Specify which of your columns can be joined on.	Join policies
Column policies	Specify which of your columns can be projected.	Projection policies
Activation policies	Specify which of your columns can be exported from the clean room.	No equivalent Snowflake policy.
Respects Snowflake aggregation policies	Require rows to be aggregated in queries, and minimum row count per group.	Aggregation policies

Join policies¶

A clean room join policy specifies which columns in your tables can be joined on by any template in the clean room. Join policies are set at the clean room level.

Join policy columns cannot be projected (a column cannot be in both a clean room join policy and a column policy). If you want a join column to be projectable, set the join policies outside of the clean room and the projection policies in the clean room (or the reverse).

Join policy columns can also be activation policy columns.

Clean room join policies are not the same as Snowflake join policies, which specify which columns must be joined on.

If you do not specify a join policy for your data, all columns can be joined on (and also projected).

Implementing a join policy¶

Clean room join policies are enforced against a column only if the template uses the join_policy or join_and_column_policy filter. You can inspect a template to see where join policies are enforced by viewing the template definition and confirming the presence of this filter in a syntax like this:

{{ my_column | join_policy }}

Copy

Snowflake join policies on the underlying tables are enforced whether or not the template has a join_policy filter on the column.

The following code shows how to allow two columns from two different tables to be joined on. This policy affects all users and all templates in this clean room. There are equivalent procedures for providers and consumers in a clean room; call the procedure that reflects your role in the clean room. You can set the join policy any time after your data has been linked into a clean room.

-- Set join policies on a clean room where you are a provider
CALL samooha_by_snowflake_local_db.provider.set_join_policy('my_provider_cleanroom',
  ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:HASHED_EMAIL', 'MYDB.MYSCH.EXPOSURES:HASHED_EMAIL']);

-- Set join policies on a clean room where you are a consumer
CALL samooha_by_snowflake_local_db.consumer.set_join_policy('my_consumer_cleanroom',
  ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:HASHED_EMAIL', 'MYDB.MYSCH.EXPOSURES:HASHED_EMAIL']);

Copy

set_join_policy replaces the previous join policy with the new join policy.

The following procedures are used to view or manage join policies in code:

consumer.set_join_policy
consumer.view_provider_join_policy
consumer.view_join_policy
provider.view_join_policy
provider.set_join_policy

Column policies¶

Column policies specify which of your columns can be projected in analysis results when accessed by a specific template. You can set a column policy any time after you have linked in data and added templates to the clean room.

A column cannot be in both a join and a column policy. A column can be in both an activation and a column policy.

If you do not specify a column policy for your data, all your columns can be projected.

Implementing a column policy¶

Column policies can be set in the clean rooms UI, if the template allows it.

Clean room column policies are enforced against a column only if the template uses the column_policy or join_and_column_policy filter. You can inspect a template to see where column policies are enforced by viewing the template definition and confirming the presence of this filter in a syntax like this:

{{ my_column | column_policy }}

Copy

Snowflake projection policies on the underlying tables are enforced whether or not the template has a column_policy filter on the column.

The following code demonstrates allowing three columns to be projected only by the prod_overlap_analysis template. The template name is provided as part of the column naming syntax when specifying a column policy. This policy affects all users in the clean room, but only that template. There are equivalent procedures for providers and consumers in a clean room; call the procedure that reflects your role in the clean room.

-- Set column policies on a clean room where you are a provider
call samooha_by_snowflake_local_db.provider.set_column_policy('my_provider_cleanroom',
  ['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']);

-- Set column policies on a clean room where you are a consumer
call samooha_by_snowflake_local_db.consumer.set_column_policy('my_consumer_cleanroom',
  ['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']);

Copy

Calling this procedure again deletes the previous column policy and replaces it with the new column policy.

The following procedures are used to view or manage column policies in code:

consumer.set_column_policy
consumer.view_column_policy
consumer.view_provider_column_policy
provider.set_column_policy
provider.view_column_policy

Activation policies¶

Activation policies specify which of your columns can be activated by an activation template. An activation template saves query results to a table in the Snowflake account of the provider or consumer, or to a third-party activation connector.

A column can be part of an activation policy as well as any other policy.

If you do not specify an activation policy for your data, no columns of your data can be activated.

Implementing an activation policy¶

Activation policies can be set in the clean rooms UI if the template allows activation.

Activation policies are enforced against a column only if the template uses the activation_policy filter. You can inspect a template to see where activation policies are enforced by viewing the template definition and confirming the presence of this filter in a syntax like this:

{{ my_column | activation_policy }}

Copy

The following code demonstrates allowing the HASHED_EMAIL and REGION_CODE columns to be activated in a clean room. This policy affects all users and all activation templates in the clean room. There are equivalent procedures for providers and consumers in a clean room. Call the procedure that reflects your role in the clean room.

-- Set column policies on a clean room where you are a provider
call samooha_by_snowflake_local_db.provider.set_activation_policy('my_cleanroom', [
    'prod_overlap_analysis:SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:HASHED_EMAIL',
    'prod_overlap_analysis:SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:REGION_CODE' ]);

-- Set column policies on a clean room where you are a consumer
call samooha_by_snowflake_local_db.consumer.set_activation_policy('my_cleanroom', [
    'prod_overlap_analysis:SAMOOHA_SAMPLE_DATABASE_NAME.DEMO.CUSTOMERS:HASHED_EMAIL',
    'prod_overlap_analysis:SAMOOHA_SAMPLE_DATABASE_NAME.DEMO.CUSTOMERS:REGION_CODE' ]);

Copy

Calling this procedure again deletes the previous activation policy and replaces it with the new activation policy.

The following procedures are used to manage activation policies in code:

consumer.set_activation_policy
provider.set_activation_policy

Aggregation policies¶

Aggregation policies require that all queries against a table contain aggregations (GROUP BY, COUNT, and other functions), and also specify a minimum number of rows per result group, or the group will be omitted from the results. Aggregation policies require that all queries against a table with an aggregation policy be aggregated (GROUP BY, COUNT, and other aggregation functions), and also specify a minimum number of rows per result group, or the group will be omitted from the results.

Clean rooms do not have their own implementation of aggregation policies; to apply aggregation constraints on your linked data, either apply an aggregation policy on the source table, or implement aggregation constraints in your template.

Some Snowflake-provided templates use the threshold and threshold_value parameters set for a user or template. These values can be modified in the clean rooms UI, or by calling provider.add_consumers or provider/consumer.set_privacy. If set for a consumer, you can access these values in your template.