Basic multi-party collaboration¶

-- Basic Snowflake Collaboration Data Clean Rooms example.
-- This file represents user "alice" in a two-collaborator clean room example.

-- Run this worksheet in a Snowflake account with access to the latest version of
-- Snowflake Data Clean Rooms.

-- This file demonstrates the following actions:
-- * How to register a template and a dataset
-- * How to create a collaboration with pre-registered resources.
-- * How to add a template to a collaboration that has already been created, and the
--   template approval flow.
-- * How to run an analysis.

-- This scenario involves two collaborators: bob and alice
-- bob and alice each submits one data source
-- bob and alice are data providers for themselves and each other
-- bob submits one template that only alice can use
-- alice submits one template that they can both use, and one template that only alice can use

-- For more information, read docs.snowflake.com/user-guide/cleanrooms/overview

USE WAREHOUSE APP_WH;
USE ROLE SAMOOHA_APP_ROLE;

-- Secondary roles must be disabled to call link_data_offerings.
USE SECONDARY ROLES NONE;

CREATE DATABASE IF NOT EXISTS ALICE_DB;
CREATE SCHEMA IF NOT EXISTS ALICE_DB.ALICE_SCH;
CREATE OR REPLACE TABLE ALICE_DB.ALICE_SCH.ALICE_DATA AS SELECT * FROM samooha_sample_database.demo.customers LIMIT 100;

-- Register a data offering to use in the initial collaboration definition.
CALL samooha_by_snowflake_local_db.registry.register_data_offering(
    $$
    api_version: 2.0.0
    spec_type: data_offering
    version: v1
    name: <alice data offering name>
    datasets:
     - alias: customer_list
       data_object_fqn: ALICE_DB.ALICE_SCH.ALICE_DATA
       object_class: custom
       allowed_analyses: template_only
       schema_and_template_policies:
         hashed_email:
           category: join_standard
           column_type: hashed_email_b64_encoded
         status:
           category: passthrough
    $$
    );
    
-- Save the ID of the registered data offering. 
SET alice_data_offering_id = '<data_offering_id>';

CALL samooha_by_snowflake_local_db.registry.view_registered_data_offerings();

-- Register a template to use in the initial collaboration definition.
CALL samooha_by_snowflake_local_db.registry.register_template(
$$
api_version: 2.0.0
spec_type: template
name: alice_only_template
version: <version_number>
type: sql_analysis
description: A test template
template:
  SELECT t1.status, COUNT(*)
    FROM IDENTIFIER( {{ source_table[0] }} ) AS t1
    JOIN IDENTIFIER( {{ source_table[1] }} ) AS t2
    ON t1.hashed_email_b64_encoded = t2.hashed_email_b64_encoded
    GROUP BY t1.status;
$$);

-- Save the ID of the registered template.
SET my_template_id = '<alice_only_template_id>';
CALL samooha_by_snowflake_local_db.registry.view_registered_templates();


-- Create a collaboration with the previously registered template and data offering.
-- The collaboration supports two collaborators, with aliases alice (this account) and bob.
-- Owner: alice
-- Analysis runners:
--   * alice, using her own data, and the template you created and registered earlier.
--   * bob, with no listed templates or data.
-- Data providers:
--   * alice and bob, for alice
--   * alice and bob, for bob
-- Resources added: The template and data offering alice registered earlier.
-- You will add more templates and data offerings to these users later. Only these
-- users are invited to the collaboration, and no additional users can be added later.
-- Replace the <...> placeholders with the appropriate values.
-- Account data sharing IDs are -- SELECT CURRENT_ORGANIZATION_NAME() || '.' || CURRENT_ACCOUNT_NAME();
CALL samooha_by_snowflake_local_db.collaboration.initialize(
$$
api_version: 2.0.0
spec_type: collaboration
name: my_first_collaboration_1_0
owner: alice
collaborator_identifier_aliases:
  alice: <my account data sharing ID>
  bob: <bob account data sharing ID>
analysis_runners:
  bob:
    data_providers:
      alice:
        data_offerings:
        - id: <alice data offering ID>
      bob:
        data_offerings: []
  alice:
    data_providers:
      alice:
        data_offerings:
        - id: <alice data offering ID>
      bob:
        data_offerings: []
    templates:
    - id: <alice only template ID>
$$,
'APP_WH'
);
SET collaboration_name = '<collaboration_name>';

-- INITIALIZE automatically joins the owner. Check status until JOINED.
CALL samooha_by_snowflake_local_db.collaboration.get_status($collaboration_name);

-- Collaboration is visible here when the owner has joined.
CALL samooha_by_snowflake_local_db.collaboration.view_collaborations();

-- Auto-approve any template requests from other collaborators that affect you.
CALL samooha_by_snowflake_local_db.collaboration.enable_template_auto_approval(
  $collaboration_name
);

-- SWITCH TO collaborator to join the collaboration and add a template
-- The template will be auto-approved.

-- Create a new template.
CALL samooha_by_snowflake_local_db.registry.register_template(
    $$
    api_version: 2.0.0
    spec_type: template
    name: both_use_template
    version: 2026_01_12_V1
    type: sql_analysis
    description: test_description
    template:
      select * from identifier({{ source_table[0] }}) limit 5;
    
    $$
);
SET both_use_template = '<template ID>';

-- Ask to add the template to the collaboration. You must ask bob, because you're
-- including bob in the sharing list. When you share a template with yourself,
-- you auto-approve it.
CALL samooha_by_snowflake_local_db.collaboration.add_template_request(
  $collaboration_name,
  $both_use_template,
  ['alice', 'bob']   -- List of collaborators who can use this template.
  );

-- SWITCH TO bob to approve the request. Request wasn't approved automatically
-- because bob didn't enable auto-approve.

-- See if bob approved the request.
CALL samooha_by_snowflake_local_db.collaboration.view_update_requests($collaboration_name);

-- See what the collaboration spec looks like now, after all the resource updates.
-- Collaboration updates are asynchronous, so if all changes that you made aren't present,
-- wait a minute or two, and then try again.
CALL samooha_by_snowflake_local_db.collaboration.view_collaborations() ->>
  SELECT "COLLABORATION_SPEC" FROM $1 WHERE "SOURCE_NAME" = $collaboration_name;

-- SWITCH TO bob to add a data offering.

-- Run an analysis.
-- Tables are scoped as <data_offering_id>.<alias>.
CALL samooha_by_snowflake_local_db.collaboration.view_data_offerings(
  $collaboration_name
);
SET $bob_data_offering = '<bob data offering ID>';

CALL samooha_by_snowflake_local_db.collaboration.view_templates(
  $collaboration_name
);

-- Run bob's template.
-- Replace the placeholders with your variables.
CALL samooha_by_snowflake_local_db.collaboration.run(
  $collaboration_name,
    $$
    api_version: 2.0.0
    spec_type: analysis
    description: <optional description of the analysis>
    template: '<alice_only_template>'
    template_configuration:
      view_mappings:
        source_tables:
          - '<alice_data_offering_view_name>'
          - '<bob_data_offering_view_name>'
    $$
  );

-- Multi-step cleanup process to delete the collaborations.
-- Doesn't delete registered resources.
CALL samooha_by_snowflake_local_db.collaboration.teardown($collaboration_name);
CALL samooha_by_snowflake_local_db.collaboration.get_status($collaboration_name);

-- When get_status reports LOCAL_DROP_PENDING, call teardown again.
CALL samooha_by_snowflake_local_db.collaboration.teardown($collaboration_name);

DROP DATABASE ALICE_DB;

-- Basic Snowflake Collaboration Data Clean Rooms example.
-- This file represents user "bob" in a two-collaborator clean room example.

-- Run this worksheet in a Snowflake account with access to the latest version of
-- Snowflake Data Clean Rooms.

-- This file  demonstrates the following actions:
-- * Joining a collaboration
-- * Registering and adding a template and a data offering to an existing collaboration.
-- * Running an analysis.

-- For more information, read docs.snowflake.com/user-guide/cleanrooms/overview

USE WAREHOUSE APP_WH;
USE ROLE SAMOOHA_APP_ROLE;

-- Secondary roles can't be active when calling join or link_data_offering.
USE SECONDARY ROLES NONE;

-- Create sample data.
CREATE DATABASE IF NOT EXISTS BOB_DB;
CREATE SCHEMA IF NOT EXISTS BOB_DB.BOB_SCH;
CREATE OR REPLACE TABLE BOB_DB.BOB_SCH.BOB_DATA AS SELECT * FROM samooha_sample_database.demo.customers_2 LIMIT 100;

-- See which collaborations you are invited to, or have joined.
CALL samooha_by_snowflake_local_db.collaboration.view_collaborations();

-- Use SOURCE_NAME column value from the response to view_collaborations().
SET collaboration_name = '<collaboration name>';

-- Use OWNER_ACCOUNT column value from the response to view_collaborations().
SET collaborator_data_sharing_id = '<collaborator_id>';

-- Review and join the collaboration.
-- Joining is asynchronous, so you must call get_status until the status is JOINED before
-- you can perform actions on the collaboration.
CALL samooha_by_snowflake_local_db.collaboration.review($collaboration_name, $collaborator_data_sharing_id);
CALL samooha_by_snowflake_local_db.collaboration.join($collaboration_name);
CALL samooha_by_snowflake_local_db.collaboration.get_status($collaboration_name);

-- Demonstrate the auto-approve flow.
-- Alice enabled auto-approve on her account, so this request will
-- be auto-approved, and the template will be added immediately.

-- Create a template.
CALL samooha_by_snowflake_local_db.registry.register_template(
    $$
    api_version: 2.0.0
    spec_type: template
    name: auto_approve_template
    version: V1
    type: sql_analysis
    description: test_description
    template:
      SELECT * FROM IDENTIFIER({{ SOURCE_TABLE[0] }}) LIMIT 10;
    $$
);
SET auto_approve_template = '<template_id>';

CALL samooha_by_snowflake_local_db.collaboration.add_template_request($collaboration_name, $auto_approve_template, ['alice', 'bob']);
CALL samooha_by_snowflake_local_db.collaboration.view_update_requests($collaboration_name);

-- SWITCH TO other account and request adding a template, and then come back to approve the request.

-- You haven't enabled template auto-approve, so you must approve the request before the template is added.
CALL samooha_by_snowflake_local_db.collaboration.view_update_requests($collaboration_name);
CALL samooha_by_snowflake_local_db.collaboration.approve_update_request(
  $collaboration_name,
  '<request_ID>'
);

-- SWITCH TO bob to see the request status.

-- Register your own data offering.
CALL samooha_by_snowflake_local_db.registry.register_data_offering(
    $$
    api_version: 2.0.0
    spec_type: data_offering
    version: v3
    name: bob_data
    datasets:
     - alias: my_customer_list
       data_object_fqn: BOB_DB.BOB_SCH.BOB_DATA
       object_class: custom
       allowed_analyses: template_only
       schema_and_template_policies:
         hashed_email:
           category: join_standard
           column_type: hashed_email_b64_encoded
         status:
           category: passthrough
    $$
);

SET my_data_id = '<data offering id>';

-- Share the data offering with yourself and alice.
CALL samooha_by_snowflake_local_db.collaboration.link_data_offering(
  $collaboration_name,
  $my_data_id,
  ['alice', 'bob']
);

CALL samooha_by_snowflake_local_db.collaboration.view_data_offerings(
  $collaboration_name
);

-- View templates that you can use in this collaboration. You can run only templates that list you in the
-- SHARED_WITH column.
CALL samooha_by_snowflake_local_db.collaboration.view_templates($collaboration_name);

-- Run an analysis with your template.
CALL samooha_by_snowflake_local_db.collaboration.run(
    $collaboration_name,
    $$
    api_version: 2.0.0
    spec_type: analysis
    description: <optional description of the analysis>
    template:  '<both_use_template>'
    template_configuration:
      view_mappings:
        source_tables:
          -  '<my_data_offering_view_name>'
          -  '<bob_data_offering_view_name>'
    $$
);
  
-- SWITCH TO other account to run an analysis.

-- Try running an analysis using alice-only template.
-- This will fail, because you aren't listed as an analysis
-- runner for this template.
CALL samooha_by_snowflake_local_db.collaboration.run(
  $collaboration_name,
  $$
  api_version: 2.0.0
  spec_type: analysis
  description: <optional description of the analysis>
  template: '<alice_only_template>'
  template_configuration:
    view_mappings:
      source_tables:
        - '<my_data_offering_view_name>'
        - '<bob_data_offering_view_name>'
  $$
);

-- Clean up resources.
DROP DATABASE BOB_DB;

-- ============================================================================
-- Single-user Collaboration Clean Rooms demo
-- ============================================================================
-- This example demonstrates a basic Snowflake Data Clean Rooms collaboration
-- using a single Snowflake account and a single role: SAMOOHA_APP_ROLE.
-- One user acts as the owner, data provider, and analysis runner.
--
-- The user creates two sample datasets, registers two data offerings and two
-- templates, then creates a collaboration with one data offering and one template each.
--  After the collaboration is created, the user links the remaining data offering and
-- template, then runs an analysis with each template. Finally, the code
-- cleans up all resources used.
--
-- For more information, see:
--   docs.snowflake.com/user-guide/cleanrooms/overview
--   docs.snowflake.com/user-guide/cleanrooms/spec-reference
-- ============================================================================


-- ============================================================================
-- SETUP: Create sample databases and data.
-- ============================================================================

USE ROLE SAMOOHA_APP_ROLE;
USE WAREHOUSE APP_WH;

-- You can't use secondary roles with most collaboration procedures.
USE SECONDARY ROLES NONE;

CREATE DATABASE IF NOT EXISTS DEMO_DB;
CREATE SCHEMA IF NOT EXISTS DEMO_DB.DATA_SCH;

-- Dataset 1: 300 rows from CUSTOMERS.
CREATE OR REPLACE TABLE DEMO_DB.DATA_SCH.CUSTOMERS_1 AS
  SELECT HASHED_EMAIL, STATUS, AGE_BAND
  FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS
  LIMIT 300;

-- Dataset 2: 300 rows from CUSTOMERS_2.
CREATE OR REPLACE TABLE DEMO_DB.DATA_SCH.CUSTOMERS_2 AS
  SELECT HASHED_EMAIL, STATUS, AGE_BAND
  FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS_2
  LIMIT 300;


-- ============================================================================
-- Register data offerings and templates.
-- ============================================================================

-- Register the first data offering.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_DATA_OFFERING(
  $$
  api_version: 2.0.0
  spec_type: data_offering
  version: V1
  name: customers_1
  datasets:
    - alias: customers_1
      data_object_fqn: DEMO_DB.DATA_SCH.CUSTOMERS_1
      object_class: custom
      allowed_analyses: template_only
      schema_and_template_policies:
        hashed_email:
          category: join_standard
          column_type: hashed_email_b64_encoded
        status:
          category: passthrough
        age_band:
          category: passthrough
  $$
);

SET data_offering_1_id = '<data_offering_1_id>';

-- Register the second data offering.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_DATA_OFFERING(
  $$
  api_version: 2.0.0
  spec_type: data_offering
  version: V1
  name: customers_2
  datasets:
    - alias: customers_2
      data_object_fqn: DEMO_DB.DATA_SCH.CUSTOMERS_2
      object_class: custom
      allowed_analyses: template_only
      schema_and_template_policies:
        hashed_email:
          category: join_standard
          column_type: hashed_email_b64_encoded
        status:
          category: passthrough
        age_band:
          category: passthrough
  $$
);

SET data_offering_2_id = '<data_offering_2_id>';

-- Register a template that joins two tables on hashed_email and returns
-- a count of rows grouped by age_band.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_TEMPLATE(
$$
api_version: 2.0.0
spec_type: template
name: age_band_count
version: V1
type: sql_analysis
description: Joins two tables on hashed_email and returns age_band with row counts.
template:
  SELECT t1.age_band, COUNT(t1.age_band) AS age_band_count
    FROM IDENTIFIER({{ source_table[0] }}) AS t1
      JOIN IDENTIFIER({{ source_table[1] }}) AS t2
      ON t1.hashed_email_b64_encoded = t2.hashed_email_b64_encoded
    GROUP BY t1.age_band;
$$
);

SET age_band_template_id = '<age_band_template_id>';

-- Register a template that joins two tables on hashed_email and returns
-- a count of rows grouped by status.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_TEMPLATE(
$$
api_version: 2.0.0
spec_type: template
name: status_count
version: V1
type: sql_analysis
description: Joins two tables on hashed_email and returns status with row counts.
template:
  SELECT t1.status, COUNT(t1.status) AS status_count
    FROM IDENTIFIER({{ source_table[0] }}) AS t1
      JOIN IDENTIFIER({{ source_table[1] }}) AS t2
      ON t1.hashed_email_b64_encoded = t2.hashed_email_b64_encoded
    GROUP BY t1.status;
$$
);

SET status_template_id = '<status_template_id>';

-- Confirm that both data offerings and both templates are registered.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.VIEW_REGISTERED_DATA_OFFERINGS();
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.VIEW_REGISTERED_TEMPLATES();


-- ============================================================================
-- Create the collaboration with one data offering and one template.
-- ============================================================================

-- Replace <account_data_sharing_id> with:
--   SELECT CURRENT_ORGANIZATION_NAME() || '.' || CURRENT_ACCOUNT_NAME();
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.INITIALIZE(
  $$
  api_version: 2.0.0
  spec_type: collaboration
  name: single_user_demo
  owner: me
  collaborator_identifier_aliases:
    me: <account_data_sharing_id>
  analysis_runners:
    me:
      data_providers:
        me:
          data_offerings:
            - id: <data_offering_1_id>
      templates:
        - id: <age_band_template_id>
  $$,
  'APP_WH'
);

SET collaboration_name = '<collaboration_name>';

-- Verify that the owner has joined. Repeat until status is JOINED.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.GET_STATUS($collaboration_name);


-- ============================================================================
-- Link the remaining data offering and template into the collaboration.
-- ============================================================================

-- Link the second data offering.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.LINK_DATA_OFFERING(
  $collaboration_name, $data_offering_2_id, ['me']);

-- Add the status_count template to the collaboration.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.ADD_TEMPLATE_REQUEST(
  $collaboration_name, $status_template_id, ['me']);


-- ============================================================================
-- List resources and run analyses.
-- ============================================================================

-- List all data offerings in the collaboration.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_DATA_OFFERINGS($collaboration_name);

-- List all templates in the collaboration.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_TEMPLATES($collaboration_name);

-- Run the age_band_count template.
-- Replace placeholders with the template name/version and view names from
-- VIEW_TEMPLATES and VIEW_DATA_OFFERINGS.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.RUN(
  $collaboration_name,
  $$
  api_version: 2.0.0
  spec_type: analysis
  description: Count matching rows grouped by age_band.
  template: '<age_band_count_template_name_and_version>'
  template_configuration:
    view_mappings:
      source_tables:
        - '<data_offering_view_1>'
        - '<data_offering_view_2>'
  $$
);

-- Run the status_count template.
-- Replace placeholders with the template name/version and view names from
-- VIEW_TEMPLATES and VIEW_DATA_OFFERINGS.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.RUN(
  $collaboration_name,
  $$
  api_version: 2.0.0
  spec_type: analysis
  description: Count matching rows grouped by status.
  template: '<status_count_template_name_and_version>'
  template_configuration:
    view_mappings:
      source_tables:
        - '<data_offering_view_1>'
        - '<data_offering_view_2>'
  $$
);


-- ============================================================================
-- CLEANUP: Delete the collaboration, registered resources, and sample data.
-- ============================================================================

-- Teardown is a multi-step process. Call TEARDOWN, then wait for GET_STATUS
-- to report LOCAL_DROP_PENDING, then call TEARDOWN again.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.TEARDOWN($collaboration_name);
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.GET_STATUS($collaboration_name);

-- When GET_STATUS reports LOCAL_DROP_PENDING, call TEARDOWN again to complete.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.TEARDOWN($collaboration_name);

-- Unregister the data offerings and templates from the default registry.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.UNREGISTER_DATA_OFFERING($data_offering_1_id);
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.UNREGISTER_DATA_OFFERING($data_offering_2_id);
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.UNREGISTER_TEMPLATE($age_band_template_id);
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.UNREGISTER_TEMPLATE($status_template_id);

-- Drop the sample database.
DROP DATABASE IF EXISTS DEMO_DB;