Snowflake Clean Rooms: Privacy-Safe Data Collaboration

Snowflake Clean Rooms is a data collaboration platform that lets multiple organizations run joint analytics without ever exposing each other's raw data. Typical use cases include ad effectiveness measurement, customer overlap analysis, and data enrichment — all while preserving privacy and unlocking the value of shared data. With regulations like GDPR and CCPA tightening, and as third-party cookies fade away, Clean Rooms are gaining attention as the next-generation foundation for marketing analytics.

Provider / Consumer Model

Clean Rooms involve two parties: a Provider (data owner) and a Consumer (data user). The Provider defines the data and the analysis templates; the Consumer executes the templates like an API.

┌─────────────────────────────────┐
│        Provider（広告媒体社）       │
│                                   │
│  [広告インプレッションデータ]         │
│   ├─ user_hash (ハッシュ化ID)      │
│   ├─ campaign_id                  │
│   ├─ impression_date              │
│   └─ channel                      │
│                                   │
│  [分析テンプレート]                  │
│   ├─ Overlap Analysis             │
│   ├─ Reach & Frequency            │
│   └─ Conversion Attribution       │
└──────────────┬──────────────────┘
               │ Clean Room（集計結果のみ共有）
               │ ← 生データは渡さない
┌──────────────┴──────────────────┐
│       Consumer（広告主）           │
│                                   │
│  [顧客データ]                      │
│   ├─ user_hash (ハッシュ化ID)      │
│   ├─ purchase_date                │
│   ├─ product_category             │
│   └─ amount                       │
│                                   │
│  テンプレートを実行 → 集計結果を取得   │
└─────────────────────────────────┘

How It Works Under the Hood

Clean Rooms are built by combining several Snowflake features.

Template 1: Overlap Analysis (Customer Overlap)

The most common Clean Room use case. The Provider's customer list and the Consumer's customer list are matched on hashed IDs, returning only aggregated results such as overlap counts and segment-level distributions.

-- Provider側: Overlap Analysisテンプレートの定義（概念例）
CREATE OR REPLACE SECURE FUNCTION clean_room.overlap_analysis(
  consumer_table VARCHAR,
  join_column VARCHAR,
  group_by_column VARCHAR
)
RETURNS TABLE (segment VARCHAR, overlap_count NUMBER, overlap_pct FLOAT)
AS
$
  SELECT
    p.segment,
    COUNT(DISTINCT p.user_hash) AS overlap_count,
    ROUND(COUNT(DISTINCT p.user_hash) * 100.0
      / (SELECT COUNT(DISTINCT user_hash) FROM provider_data.customers), 2) AS overlap_pct
  FROM provider_data.customers p
  INNER JOIN IDENTIFIER(consumer_table) c
    ON p.user_hash = c.user_hash
  GROUP BY p.segment
  HAVING COUNT(DISTINCT p.user_hash) >= 25  -- 最小集計粒度（k-匿名性）
  ORDER BY overlap_count DESC
$;

-- Consumer側: テンプレートの実行
SELECT * FROM TABLE(
  shared_clean_room.clean_room.overlap_analysis(
    'my_db.my_schema.my_customers',
    'user_hash',
    'segment'
  )
);

Template 2: Data Enrichment

A use case where the Consumer's data is augmented with Provider data. For example, the Consumer's customer list is enriched with segment-level aggregates of demographic attributes held by the Provider. Individual-level attributes are never returned — only statistics at segment granularity.

-- Provider側: Enrichmentテンプレート（概念例）
CREATE OR REPLACE SECURE FUNCTION clean_room.enrich_demographics(
  consumer_table VARCHAR
)
RETURNS TABLE (age_group VARCHAR, income_bracket VARCHAR, user_count NUMBER)
AS
$
  SELECT
    p.age_group,
    p.income_bracket,
    COUNT(DISTINCT c.user_hash) AS user_count
  FROM IDENTIFIER(consumer_table) c
  INNER JOIN provider_data.demographics p
    ON c.user_hash = p.user_hash
  GROUP BY p.age_group, p.income_bracket
  HAVING COUNT(DISTINCT c.user_hash) >= 50
  ORDER BY user_count DESC
$;

Template 3: Conversion Attribution

A pattern that joins ad impression data (Provider) with purchase data (Consumer) to compute campaign-level conversion rates as aggregates.

-- 概念例: キャンペーン別コンバージョン集計
-- Provider: 広告インプレッション / Consumer: 購買データ

-- 結果のイメージ（集計データのみConsumerに返却）
-- campaign_id | impressions | converters | conversion_rate
-- CAMP_001    | 15,230      | 1,892      | 12.4%
-- CAMP_002    | 28,450      | 2,105      | 7.4%
-- CAMP_003    | 9,870       | 423        | 4.3%

Clean Rooms vs Data Sharing: Comparison

Privacy Control Mechanisms

Marketplace Integration

On Snowflake Marketplace, third-party vendors offer Clean Room solutions as Native Apps. LiveRamp, Habu (acquired by Snowflake in 2024), InfoSum, and others publish dedicated Clean Room apps — installing them into your Snowflake account is enough to start using Clean Room functionality.

Onboarding Flow

Step 1: Provider側
  ├─ データの準備（ハッシュ化、匿名化）
  ├─ 分析テンプレートの定義（Secure UDF / Stored Procedure）
  ├─ プライバシー制御の設定（最小粒度、Row Access Policy）
  └─ Clean Roomのデプロイ（Share作成 or Native App公開）

Step 2: Consumer側
  ├─ Shareの受信 or Native Appのインストール
  ├─ 自社データの準備（結合キーのハッシュ化）
  └─ テンプレートの実行と結果の取得

Step 3: 運用
  ├─ テンプレートの実行ログ監視
  ├─ 結果のプライバシー検証
  └─ テンプレートの更新・追加

Best Practices

• Always hash your join keys: Never JOIN on plaintext email addresses or phone numbers — hash them with SHA-256 first.
• Enforce strict minimum aggregation sizes: Require HAVING COUNT(*) >= 25-50 to prevent re-identification from low-count results.
• Audit template SQL on a regular cadence: Periodically review templates to confirm they have no unintended aggregation paths (queries that could leak individual-level data).
• Combine with Aggregation Policy: On top of HAVING clauses in templates, apply Aggregation Policy directly to the tables for defense in depth.
• Package with the Native App Framework: When delivering the same Clean Room to multiple Consumers, package it as a Native App and distribute it efficiently through Marketplace.

Check Your Understanding

Frequently Asked Questions