Skip to content

Cleanroom provider publishing — Husky × Buyer overlap

How to publish the first Husky-bound Snowflake Data Clean Room cleanroom ("Husky × Buyer-CRM Overlap Estimator") on account WIB77365. Mixed-mode playbook: 5 min in Snowsight Provider Studio UI for the shell, then SQL to inject the analysis template and policy.

Why mixed-mode

PROVIDER_CREATE_CLEANROOM_EXEC works via Provider Studio UI but fails when called via snow sql CLI (Native App TRANSFER_CLEANROOM_CODE handler hits a Snowpark session.file.put sandbox quirk on manifest.yml upload). Verified 2026-04-27 on snow CLI 2.2.0 / app patch 120 — same proc succeeds when invoked from the UI session. Subsequent template / policy / consumer ops work fine via SQL.

Pre-flight (already done 2026-04-27)

-- These grants are required for cleanroom creation. Already applied:
GRANT CREATE DATABASE ON ACCOUNT TO APPLICATION SNOWFLAKE_DATA_CLEAN_ROOMS;
GRANT EXECUTE TASK    ON ACCOUNT TO APPLICATION SNOWFLAKE_DATA_CLEAN_ROOMS;
GRANT USAGE ON WAREHOUSE DEV TO APPLICATION SNOWFLAKE_DATA_CLEAN_ROOMS;
-- Optional, only if you want the app to run on a dedicated warehouse:
-- CREATE WAREHOUSE DCR_WH WITH WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60;
-- GRANT USAGE ON WAREHOUSE DCR_WH TO APPLICATION SNOWFLAKE_DATA_CLEAN_ROOMS;

Verify:

SHOW GRANTS TO APPLICATION SNOWFLAKE_DATA_CLEAN_ROOMS;
-- Expect: CREATE DATABASE, EXECUTE TASK, USAGE on WAREHOUSE

Step 1 — Create cleanroom shell (UI, 5 min)

  1. Open https://app.snowflake.com/ as HUSKY (ACCOUNTADMIN).
  2. Apps → Snowflake Data Clean Rooms → Provider Studio.
  3. Click + Create cleanroom. Fill in:
Field Value
Name Husky_x_Buyer_Overlap_v1
Display name Husky × Buyer-CRM Overlap Estimator
Description Pre-purchase overlap analysis. Buyer attaches CRM hashed-email or MAID list; cleanroom returns aggregate overlap count + percentage against Husky's APAC + US audience. k-anonymity ≥ 100 enforced.
Distribution INTERNAL
  1. Click Create. UI provisions database samooha_cleanroom_husky_x_buyer_overlap_v1 and uploads app code (~30s). Wait until status shows Created.

Step 2 — Bind Husky data + add analysis template (SQL)

Copy-paste the script below as a single CALL sequence. Replace the cleanroom name string only if step 1 used a different name.

USE ROLE ACCOUNTADMIN;
USE WAREHOUSE DEV;

-- 2a. Set join policy: only MAID column joinable, no other PII columns
CALL SNOWFLAKE_DATA_CLEAN_ROOMS.APP_SCHEMA.UPDATE_JOIN_POLICY(
  'Husky_x_Buyer_Overlap_v1',
  ARRAY_CONSTRUCT('HUSKY_DATA.SHARE.V_MAID_APAC_PLUS_US:MAID'),
  ARRAY_CONSTRUCT()
);

-- 2b. Add the overlap analysis template (k-anon ≥ 100 baked in)
CALL SNOWFLAKE_DATA_CLEAN_ROOMS.APP_SCHEMA.ADD_CUSTOM_SQL_TEMPLATE(
  'Husky_x_Buyer_Overlap_v1',
  'overlap_analysis',
  $$
    WITH husky AS (
      SELECT DISTINCT MAID
      FROM HUSKY_DATA.SHARE.V_MAID_APAC_PLUS_US
      WHERE COUNTRY_ISO3 IN (SELECT VALUE FROM TABLE(FLATTEN(input => PARSE_JSON({{ countries | sqlsafe }}))))
    ),
    buyer AS (
      SELECT DISTINCT MAID FROM IDENTIFIER({{ buyer_maid_table }})
    )
    SELECT
      COUNT_IF(b.MAID IS NOT NULL)                                        AS overlap_count,
      COUNT(b.MAID)                                                        AS buyer_size,
      COUNT(h.MAID)                                                        AS husky_size,
      ROUND(100.0 * COUNT_IF(b.MAID IS NOT NULL) / NULLIF(COUNT(b.MAID),0), 2) AS overlap_pct
    FROM buyer b
    LEFT JOIN husky h ON b.MAID = h.MAID
    HAVING COUNT_IF(b.MAID IS NOT NULL) >= 100  -- k-anonymity floor
  $$,
  0.0,         -- DP epsilon (0 = no DP noise on this template)
  TRUE         -- secure_request_required
);

-- 2c. Set policy constraints (k-anon = 100, max queries per session = 5)
CALL SNOWFLAKE_DATA_CLEAN_ROOMS.APP_SCHEMA.PROVIDER_SET_POLICY_CONSTRAINTS(
  'Husky_x_Buyer_Overlap_v1',
  TO_JSON(OBJECT_CONSTRUCT(
    'k_anonymity_threshold', 100,
    'max_queries_per_session', 5
  ))
);

-- 2d. Allow provider to test-run the template (so we can demo)
CALL SNOWFLAKE_DATA_CLEAN_ROOMS.APP_SCHEMA.ENABLE_PROVIDER_RUN_ANALYSIS(
  'Husky_x_Buyer_Overlap_v1'
);

-- 2e. Verify
SELECT * FROM TABLE(SNOWFLAKE_DATA_CLEAN_ROOMS.APP_SCHEMA.GET_CLEANROOM_METADATA('Husky_x_Buyer_Overlap_v1'));

Step 3 — Invite a buyer (when ready)

For each buyer interested in a 30-minute overlap test:

CALL SNOWFLAKE_DATA_CLEAN_ROOMS.APP_SCHEMA.ADD_CONSUMER(
  'Husky_x_Buyer_Overlap_v1',
  '<BUYER_SNOWFLAKE_ACCOUNT_LOCATOR>',  -- e.g. 'XYZ12345'
  '<BUYER_ORG_NAME>',
  '<BUYER_DISPLAY_NAME>'
);

Buyer receives the cleanroom invitation in their Snowflake account under Apps → Data Clean Rooms → Available cleanrooms. Buyer accepts, attaches their hashed-MAID table, runs overlap_analysis template with buyer_maid_table and countries arguments, gets the 4-row aggregate output.

Step 4 — Provider-side test run (sanity check before invite)

To rehearse before inviting a real buyer, prepare a synthetic buyer table in a non-share schema and run ENABLE_PROVIDER_RUN_ANALYSIS mode:

-- Create a synthetic buyer table (~10K MAIDs sampled from APAC)
CREATE OR REPLACE TABLE DATACOPILOT.PUBLIC.SYNTH_BUYER_MAIDS AS
SELECT MAID FROM HUSKY_DATA.SHARE.V_MAID_APAC_PLUS_US
WHERE COUNTRY_ISO3 = 'SGP' AND PLATFORM = 'ios'
LIMIT 10000;

-- Test-run the template as the provider
CALL SNOWFLAKE_DATA_CLEAN_ROOMS.APP_SCHEMA.RUN_PROVIDER_ANALYSIS(
  TO_JSON(OBJECT_CONSTRUCT(
    'cleanroom_name',     'Husky_x_Buyer_Overlap_v1',
    'template_name',      'overlap_analysis',
    'buyer_maid_table',   'DATACOPILOT.PUBLIC.SYNTH_BUYER_MAIDS',
    'countries',          '["SGP"]'
  ))
);

Expected output structure:

overlap_count | buyer_size | husky_size | overlap_pct
        9847  |     10000  | 33622626   |       98.47

Step 5 — Production checklist

  • Cleanroom shell created via Snowsight UI
  • Step 2 SQL ran without error; GET_CLEANROOM_METADATA returns the template
  • Step 4 synthetic test produced expected output
  • Provider Studio UI shows cleanroom status = Active
  • First real buyer invited via Step 3 (replace placeholder)
  • Screenshot of test run added to docs/strategy/2026-04-27-cleanroom-positioning.md as social proof
  • /cleanroom page footer updated with "Live cleanroom: Husky × Buyer-CRM Overlap Estimator (publish date)"

Future templates (Phase 1.5 +)

Same shell + new templates via ADD_CUSTOM_SQL_TEMPLATE:

Template name Purpose DP epsilon
overlap_analysis Pre-purchase overlap (this doc) 0
lookalike_build Seed → expansion identifiers 0.5
attribution_lift Test-vs-control campaign lift 1.0

Provenance

Drafted 2026-04-27 after hands-on attempt on snow CLI 2.2.0. CLI-side PROVIDER_CREATE_CLEANROOM_EXEC failed with Snowpark file-streaming sandbox error; UI-side path verified to work in earlier sessions (ONE_TIME_SETUP success same day). Subsequent steps (template, policy, consumer) all SQL-driven.