Running free-form SQL queries on clean room tables¶

Policies and differential privacy support¶

When you expose clean room data for free-form queries, all Snowflake policies are respected. Clean room policies (join policies, column policies) are not enforced in free-form queries.

Differential privacy is not enforced on data exposed to free-form queries. This includes both Snowflake differential privacy and clean room differential privacy.

Enabling free-form queries¶

USE ROLE SAMOOHA_APP_ROLE;
CALL samooha_by_snowflake_local_db.provider.patch_cleanroom($cleanroom_name,TRUE);

Provider

The provider takes the following steps to make datasets in a clean room available to clean room collaborators using free-form queries:

1. Create the clean room in the standard way.

2. Register and link the datasets into the clean room in the standard way. Note that currently your data must be registered using the API; you cannot register views in the clean room UI and use them for free-form queries. You should apply any Snowflake aggregation, join, or other policies before sharing your data outside the clean room.

3. Call provider.enable_workflows_for_consumers to allow specific users free-form access to the tables that you will specify in the next step.

4. Call provider.enable_datasets_for_workflow to allow the users in the previous step free-form access to the datasets specified here.

5. Add your collaborators in the standard way by calling provider.add_consumers.

6. Publish your clean room.

7. If you want to revoke permission to query these tables, you can do this at the user level by calling provider.disable_consumer_run_analysis or provider.remove_consumers, the view level by calling library.unregister_objects or library.unregister_db, or by deleting the clean room.

If a clean room already exists and data is registered, you can simply call provider.enable_workflows_for_consumers and provider.enable_datasets_for_workflow to expose the specified tables to the specified users.

The following code creates three sample tables, creates a new clean room, links in the tables, and grants free-form query access to those tables for clean room collaborators via the clean room.

----------------- Create sample data -----------------
USE ROLE MYROLE;
CREATE DATABASE freeform_db;

-- Create a table with an aggregation constraint.
CREATE OR REPLACE TABLE freeform_db.public.agg_constrained_table
  AS SELECT * FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS;

CREATE AGGREGATION POLICY freeform_db.public.agg_policy AS ()
  RETURNS AGGREGATION_CONSTRAINT ->
  AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 5);

ALTER TABLE freeform_db.public.agg_constrained_table
  SET AGGREGATION POLICY freeform_db.public.agg_policy;

-- Create a table with a projection constraint.
CREATE OR REPLACE TABLE freeform_db.public.proj_constrained_table
  AS SELECT * FROM SAMOOHA_SAMPLE_DATABASE_FREEFORM.DEMO.CUSTOMERS;

CREATE OR REPLACE PROJECTION POLICY freeform_db.public.proj_policy AS ()
  RETURNS PROJECTION_CONSTRAINT ->
  PROJECTION_CONSTRAINT(ALLOW => false);

ALTER TABLE freeform_db.public.proj_constrained_table MODIFY COLUMN hashed_email
  SET PROJECTION POLICY freeform_db.public.proj_policy;

-- Create a table with a masking policy.
CREATE OR REPLACE TABLE freeform_db.public.masked_table
  AS SELECT * FROM SAMOOHA_SAMPLE_DATABASE_FREEFORM.DEMO.CUSTOMERS;

CREATE OR REPLACE MASKING POLICY freeform_db.public.masking_policy
  AS (val string) RETURNS STRING ->
  CASE
    WHEN current_account() IN ('DCR_PROVIDER_PP6') THEN VAL
    ELSE '*********'
  END;

ALTER TABLE freeform_db.public.masked_table MODIFY COLUMN hashed_email
  SET MASKING POLICY freeform_db.public.masking_policy;

----------------- Create and publish a clean room that supports -----------------
----------------- free-form queries against this data.          -----------------

-- Create the clean room. Nothing new here.
USE ROLE SAMOOHA_APP_ROLE;
SET cleanroom_name = 'freeform queries';
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.cleanroom_init($cleanroom_name, 'INTERNAL');

-- Link in the policy-protected tables from above. Nothing new here.
USE ROLE MYROLE;
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.register_db('freeform_db');
USE ROLE SAMOOHA_APP_ROLE;
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.link_datasets($cleanroom_name,
  ['freeform_db.public.agg_constrained_table',
  'freeform_db.public.proj_constrained_table',
  'freeform_db.public.masked_table']);

-- Grant the following consumer access to the tables specified next.
SET flow_name = freeform_sql;
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.enable_workflows_for_consumers($cleanroom_name,
  [$flow_name],
  ['<CONSUMER_LOCATOR>']);

-- Grant the consumer specified above access to the specified tables.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.enable_datasets_for_workflow($cleanroom_name,
  $flow_name,
  ['freeform_db.public.agg_constrained_table',
   'freeform_db.public.proj_constrained_table',
    'freeform_db.public.masked_table']);

-- Add collaborators and publish, in the standard way.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.add_consumers(
  $cleanroom_name, '<CONSUMER_LOCATOR>', '<ORG_NAME>.<CONSUMER_LOCATOR>');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.set_default_release_directive(
  $cleanroom_name, 'V1_0', '0');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.create_or_update_cleanroom_listing(
  $cleanroom_name);

Consumer

After the provider has published a clean room with free-form SQL work flows, consumers with access to that clean room can run queries against the exposed views.

1. Install the clean room in the standard way. No need to link in data, as the consumer can access their tables directly.

2. Call consumer.get_provider_freeform_sql_views to list the free-form SQL views available to the current account and role.

3. Run standard SQL queries against the data. The producer’s policies will be enforced against the data.

-- Install the clean room.
USE ROLE SAMOOHA_APP_ROLE;
SET cleanroom_name = 'freeform queries';

CALL samooha_by_snowflake_local_db.consumer.install_cleanroom($cleanroom_name, '<PROVIDER_LOCATOR>');

-- List free form views available in the clean room.
CALL samooha_by_snowflake_local_db.consumer.GET_PROVIDER_FREEFORM_SQL_VIEWS($cleanroom_name);

-- Run queries on the views
SELECT * FROM <PROJECTION_POLICY_VIEW_NAME>;
SELECT * FROM <MASKING_POLICY_VIEW_NAME>;
SELECT COUNT(hashed_email), age_band
  FROM <AGGREGATION_POLICY_VIEW_NAME> group by age_band;

Provider: Create a clean room and set policies¶

1. Create a clean room or edit an existing clean room, and specify tables or views for your table.

2. Join policies specified during the clean room creation process are ignored when using the SQL Query template, but respected for any other templates.

3. In Configure Analysis & Query select Horizontal » SQL Query.

4. In the SQL Query settings section, set the following properties:

   1. Under Tables, select tables that should be available to clean room collaborators in free-form queries. By default, all columns in the selected tables can be projected, and aggregation policies do not need to be applied. To control which columns can be projected, and which must be aggregated, you must set column policies in the next section.

   2. In the Column Policies section set the following values to control if or how your columns can be used in a query:

      1. Aggregation policy columns: Specify which columns must be aggregated in order to appear in query results. If you apply an aggregation policy to a column and one column is used in a query, then the results must be aggregated. Any columns listed here will be added to the Privacy settings section.

      2. Projection policy columns: Columns with a projection policy cannot be projected (that is, included in a SELECT statement). However, consumers can filter or join on a column with a projection policy.

      3. Fully permitted columns: The consumer can SELECT, filter, or join on these columns without restriction (aggregation or otherwise).

   3. The Privacy settings section lists all columns with an aggregation policy applied. The Threshold value indicates how many entities must exist for that value to appear in the results. For example, if you set a threshold of 5 on a FIRST_NAME column, and the name “Erasmus” appears only 4 times in the table, all rows with “Erasmus” will be filtered out before any processing has occurred (so, for example, a COUNT(*) on such a table will omit those 4 rows with the below-threshold group size).

Consumer: Run a free-form query¶

1. Join or edit the clean room in the clean rooms UI.

2. In the Configure Analysis & Query section, choose your tables that you will use for free-form queries.

3. Select Finish to save your changes.

4. To run a query, select Run in the clean room with the SQL Query template and select the SQL Query template.