Snowflake Data Clean Rooms and Synthetic Data for Privacy-Preserving Collaboration

1.Clean rooms: multi-party analysis without exposing raw data

A Snowflake Data Clean Room is a secure, multi-party environment. Collaborators combine and analyze each other's data without exposing the underlying raw data. The principle is analysis without disclosure. Collaborators never query the raw data in the clean room. Analyses run only through approved templates, and results come back as aggregated insights or are activated (saved) to a collaborator's account, as all collaborators decide. Collaborators can review and approve or reject new templates or data sources that others add. The exam guide calls this secure multi-party computation. The Snowflake documentation describes it in terms of templates, policies and approvals, and does not describe a cryptographic protocol, so this lesson does not either.

The collaboration specification gives each collaborator one or more roles. A collaboration has exactly one Owner. The Owner creates the clean room, assigns roles, decides who can share data with whom, and tears the clean room down. Owning a collaboration does not make you an Analysis Runner or a Data Provider. Edition requirements follow the roles. Data Providers need Enterprise Edition, because sharing data with policy enforcement is not possible on Standard Edition. Activating results to another account also needs Enterprise Edition. Trial accounts cannot use clean rooms at all.

A collaboration holds three kinds of resource. A template is a JinjaSQL query that analysis runners execute. A data offering packages one or more tables and is a live view of the source data, not a snapshot. Its specification controls which columns are exposed and which policies apply. A code spec adds custom Python functions or procedures that templates can call, for example ML models. The specification below shows alice acting as owner, analysis runner and data provider at the same time.

api_version: 2.0.0
spec_type: collaboration
name: basic_collaboration
owner: alice                # alice is the collaboration owner.
collaborator_identifier_aliases:
  alice: corp1.acct123
  bob: corp2.acctxyz
analysis_runners:
  alice:                    # alice is also an analysis runner.
    data_providers:
      alice:                # alice provides data to herself.
        data_offerings:     # alice provides these data offerings.
        - id: alice_data_1
        - id: alice_data_2
      bob:                  # bob provides data to alice.
        data_offerings:     # bob provides this data to alice.
        - id: bob_data_1
    templates:              # alice can use this template with any data she can access.
    - id: template1

Sources12

2.Registering, linking and joining a collaboration

A resource takes two steps to enter a collaboration. Registration is an account-level action. It copies the resource into the clean rooms environment and returns an ID. Resources live in a registry, either the account default (readable by anyone with READ REGISTRY) or a custom registry whose creator controls access. Linking then shares a registered resource with specific collaborators in one collaboration. Resources are versioned and new versions never overwrite old ones, so moving to a new version means linking it and, if you choose, removing the old one. Collaborations are not versioned, and changes to them are not tracked.

Joining a collaboration is a consent gate. Every collaborator must join, including the owner. Everyone except the owner must first review the collaboration, which shows them the specification. Resources you registered cannot be used until you have joined. Collaborators in other regions must enable Cross-Cloud Auto-Fulfillment before they can review and join. After creation, the owner can use EDIT (a preview feature) to add or remove collaborators or change their roles, but the affected collaborators must approve. Many of these actions are asynchronous, so check the state afterwards, for example with GET_STATUS.

Sources2

3.Synthetic data: sharing the statistics, not the rows

Sometimes even a template-controlled analysis is too much exposure, and the other party only needs data that behaves like yours. SNOWFLAKE.DATA_PRIVACY.GENERATE_SYNTHETIC_DATA learns the statistical distribution of a source table or view and writes an output table with the same column names and types. The output keeps approximate distributions and correlations but has no direct reference or link to any source row. That makes it suitable for sharing or testing data that is too sensitive to hand over. Any user can call the procedure, because the SNOWFLAKE.CORE_VIEWER database role is granted to PUBLIC. The account must accept the Anaconda terms first.

For joins, mark every column you will join on as a join key. Within a single run, each source value gets the same artificial value in every table. To keep join keys consistent across separate runs, pass a SYMMETRIC_KEY secret as consistency_secret. This works only for string columns and needs READ or OWNERSHIP on the secret. Setting 'similarity_filter': True drops output rows that are too similar to source rows, based on NNDR and DCR, so the output can have fewer rows. With the filter on, a NULL in any non-string column makes the procedure fail. Limits per call: up to five inputs, each with at least 20 distinct rows, at most 100 columns and at most 14M rows. External, Iceberg and hybrid tables and streams are not supported as inputs.

CALL SNOWFLAKE.DATA_PRIVACY.GENERATE_SYNTHETIC_DATA({
  'datasets':[
      {
        'input_table': 'CLINICAL_DB.PUBLIC.PATIENTS1',
        'output_table': 'MY_DB.PUBLIC.PATIENTS1',
        'columns': { 'patient_id': {'join_key': TRUE}, 'age':{'join_key': TRUE}}
      },
      {
        'input_table': 'CLINICAL_DB.PUBLIC.PATIENTS2',
        'output_table': 'MY_DB.PUBLIC.PATIENTS2',
        'columns': { 'patient_id': {'join_key': TRUE}, 'age':{'join_key': TRUE}}
      }
    ],
    'replace_output_tables': TRUE
});

CALL SNOWFLAKE.DATA_PRIVACY.GENERATE_SYNTHETIC_DATA({
  'datasets':[
      {
        'input_table': 'CLINICAL_DB.PUBLIC.BASE_TABLE',
        'output_table': 'MY_DB.PUBLIC.PATIENTS1',
        'columns': { 'patient_id': {'join_key': TRUE}}
      }
    ],
    ' ? ': SYSTEM$REFERENCE('SECRET', 'MY_CONSISTENCY_SECRET', 'SESSION', 'READ')::STRING,
    'replace_output_tables': TRUE
});

Sources3

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.The collaboration owner can automatically run analyses and provide data because they created the clean room.Why is that wrong?

   The owner role carries no elevated run privileges. Running analyses or providing data requires those roles to be assigned in the specification.

   Covered in Clean rooms: multi-party analysis without exposing raw data

2. 2.A Standard Edition account can be a data provider in a clean room as long as it applies policies.Why is that wrong?

   Data providers need Enterprise Edition. On Standard Edition you cannot share data through a clean room with policy enforcement.

   Covered in Clean rooms: multi-party analysis without exposing raw data

3. 3.Synthetic join keys stay consistent across any number of runs without extra configuration.Why is that wrong?

   Without a secret, join keys are consistent only within a single run. Consistency across runs needs a consistency_secret and works only for string columns.

   Covered in Synthetic data: sharing the statistics, not the rows

Practise it for real

Generate two synthetic tables whose patient_id values join consistently across separate procedure runs

1. 1.Create a symmetric key secret with CREATE OR REPLACE SECRET my_db.public.my_consistency_secret TYPE=SYMMETRIC_KEY ALGORITHM=GENERIC.

   Why: A consistency secret is what keeps join key values consistent across multiple runs.

   You should see: The secret exists, and your role holds OWNERSHIP of it, which satisfies the READ or OWNERSHIP requirement.

2. 2.Make sure your role has USAGE on the warehouse, SELECT on the input tables, USAGE on the input and output databases and schemas, and CREATE TABLE on the output schema.

   Why: These are the grants the procedure requires. Also check for a FUTURE GRANT of OWNERSHIP on the output schema, which would silently take over ownership of the output tables.

   You should see: No privilege errors when you call the procedure.

3. 3.Call GENERATE_SYNTHETIC_DATA on the first source table with patient_id marked as join_key and consistency_secret set to SYSTEM$REFERENCE('SECRET', 'MY_CONSISTENCY_SECRET', 'SESSION', 'READ')::STRING.

   Why: Join keys must be designated explicitly, and the secret ties this run's key mapping to later runs.

   You should see: An output table with the same column names and types and artificial patient_id values.

4. 4.In a separate call, run the procedure on the second source table with identical join_key settings and the same secret, then join the two output tables on patient_id.

   Why: Identical arguments plus the shared secret produce the same artificial value for the same source value.

   You should see: The join returns results similar to the same join on the source tables.