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.

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

Note

When you link tables into a clean room, all 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.

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.

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