클린룸 테이블에서 자유 형식 SQL 쿼리 실행

클린룸 API 또는 UI를 사용해 소비자가 클린룸의 선택된 데이터셋에서 자유 형식 SQL 쿼리를 실행할 수 있게 할 수 있어요.

출처: Running free-form SQL queries on clean room tables

본문

지원 종료(EOL) 공지

레거시 Provider 및 Consumer 데이터 클린룸은 지원이 중단되고 있어요. 날짜와 마이그레이션 지침은 지원 종료 타임라인을 참고하세요.

클린룸 API 또는 UI를 사용해 소비자가 클린룸의 선택된 데이터셋에서 자유 형식 SQL 쿼리를 실행할 수 있게 할 수 있어요.

클린룸 API를 사용한 자유 형식 쿼리

콜라보레이터가 클린룸 밖에서 특정 링크된 데이터셋을 쿼리할 수 있도록 클린룸을 구성할 수 있어요. 콜라보레이터는 클린룸에 접근할 수 있는 모든 환경(Snowsight나 Snowflake CLI 포함)에서 이 데이터셋에 자유 형식 쿼리를 실행할 수 있어요. 자유 형식 데이터셋은 SQL, Python 또는 기타 지원되는 Snowflake 언어로 쿼리할 수 있는 표준 읽기 전용 뷰처럼 동작해요.

참고

클린룸에서 소비자에게 자유 형식 SQL 쿼리 실행 권한을 부여하면, 그 소비자는 그 클린룸의 데이터를 자신의 계정에서 접근할 수 있는 다른 어떤 데이터와도 조합해 쿼리할 수 있어요.

정책 및 차등 프라이버시 지원

자유 형식 쿼리용으로 클린룸 데이터를 노출하면 모든 Snowflake 정책이 존중돼요. 클린룸 정책(조인 정책, 컬럼 정책)은 자유 형식 쿼리에서 적용되지 않아요.

자유 형식 쿼리에 노출된 데이터에는 클린룸 차등 프라이버시가 적용되지 않아요. 여기에는 Snowflake 차등 프라이버시와 클린룸 차등 프라이버시가 모두 포함돼요.

자유 형식 쿼리 활성화

Provider 단계 / Consumer 단계

중요

2025년 6월 이전에 만든 클린룸이라면 제공자가 다음 코드를 실행해 그 클린룸에서 자유 형식 쿼리를 활성화하도록 클린룸을 패치해야 해요:
USE ROLE SAMOOHA_APP_ROLE;
CALL samooha_by_snowflake_local_db.provider.patch_cleanroom($cleanroom_name,TRUE);

Provider 단계

제공자는 다음 단계를 거쳐 자유 형식 쿼리를 사용해 클린룸 콜라보레이터가 사용할 수 있도록 클린룸의 데이터셋을 만든다:

표준 방식으로 클린룸을 만들어요.
API를 사용해 표준 방식으로 데이터셋을 클린룸에 등록하고 링크해요. 현재 데이터는 API로 등록해야 합니다. 클린룸 UI에서 뷰를 등록하고 자유 형식 쿼리에 사용할 수는 없어요. 데이터를 클린룸 밖에서 공유하기 전에 Snowflake 집계·조인 또는 기타 정책을 적용해야 해요.
provider.enable_workflows_for_consumers를 호출해 다음 단계에서 지정할 테이블에 특정 사용자에게 자유 형식 접근을 허용해요. 이 워크플로 이름은 반드시 freeform_sql로 지정해야 해요.
provider.enable_datasets_for_workflow를 호출해 클린룸에서 쿼리할 수 있는 데이터셋을 지정해요.
provider.add_consumers를 호출해 콜라보레이터를 표준 방식으로 추가해요.
클린룸을 게시해요.
이 테이블을 쿼리할 권한을 회수하려면 provider.disable_consumer_run_analysis 또는 provider.remove_consumers를 호출해 사용자 수준에서, library.unregister_objects 또는 library.unregister_db를 호출해 데이터셋 수준에서, 또는 클린룸을 삭제해서 할 수 있어요.

클린룸이 이미 존재하고 데이터가 등록되어 있다면 간단히 provider.enable_workflows_for_consumers와 provider.enable_datasets_for_workflow를 호출해 지정된 데이터셋을 지정된 사용자에게 노출할 수 있어요.

다음 코드는 세 개의 샘플 테이블을 만들고 Snowflake 정책을 적용하고, 새 클린룸을 만들고 테이블을 링크하며, 클린룸을 통해 클린룸 콜라보레이터에게 그 테이블에 대한 자유 형식 쿼리 접근을 부여해요. 강조된 코드가 클린룸에서 자유 형식 쿼리를 활성화하는 곳이에요.

----------------- 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.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.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.
-- The flow name must be 'freeform_sql'
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 단계

제공자가 자유 형식 SQL 워크플로가 있는 클린룸을 게시한 뒤에는, 그 클린룸에 접근할 수 있는 소비자가 다음 단계를 따라 노출된 뷰에 대해 쿼리를 실행할 수 있어요:

표준 방식으로 클린룸을 설치해요. 소비자는 클린룸이 아닌 자신의 로컬 환경에서 데이터에 접근하므로 소비자 데이터를 링크할 필요가 없어요.
consumer.get_provider_freeform_sql_views를 호출해 현재 계정과 역할에서 사용할 수 있는 자유 형식 SQL 뷰를 나열해요.
데이터에 대해 표준 SQL 쿼리를 실행해요.

-- 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;

클린룸 UI에서의 자유 형식 쿼리

클린룸의 SQL Query 템플릿은 소비자가 클린룸의 데이터를 쿼리하는 자유 형식 SQL을 작성할 수 있게 해줘요. SQL Query 템플릿을 사용할 때 소비자 쿼리는 결과를 성공적으로 반환하려면 특정 요구 사항을 충족해야 해요. 이 요구 사항은 데이터 제공자가 데이터 프라이버시 정책으로 테이블을 어떻게 보호하는지에 따라 결정돼요.

UI에서 클린룸을 만들거나 업데이트할 때 클린룸에 SQL Query 템플릿을 추가하고 아래 설명대로 구성해요.

Provider: 클린룸 만들기 및 정책 설정

클린룸을 만들거나 기존 클린룸을 편집하고, 테이블이나 뷰를 지정해요.
클린룸 생성 과정에서 지정한 조인 정책은 SQL Query 템플릿을 사용할 때는 무시되지만, 다른 템플릿에서는 존중돼요.
Configure Analysis & Query에서 Horizontal » SQL Query를 선택해요.
SQL Query 설정 섹션에서 다음 속성을 설정해요:
1. Tables 아래에서 자유 형식 쿼리에서 클린룸 콜라보레이터가 사용할 수 있어야 하는 테이블을 선택해요. 기본적으로 집계 정책을 적용할 필요는 없어요. 프로젝션할 수 있는 컬럼과 집계해야 하는 컬럼을 제어하려면 다음 섹션에서 컬럼 정책을 설정해야 해요.
중요

클린룸 UI의 자유 형식 쿼리에서는 이름이 "LIST"(대소문자 무관)로 끝나는 테이블을 사용할 수 없어요.
1. Column Policies 섹션에서 컬럼이 쿼리에서 사용될 수 있는지·어떻게 사용되는지 제어하는 다음 값을 설정해요:
  1. 집계 정책 컬럼(Aggregation policy columns): 쿼리 결과에 나타나기 위해 집계해야 하는 컬럼을 지정해요. 컬럼에 집계 정책을 적용하고 그 컬럼이 쿼리에 사용되면 결과가 집계되어야 해요. 여기에 나열된 컬럼은 Privacy settings 섹션에 추가돼요.
  2. 프로젝션 정책 컬럼(Projection policy columns): 프로젝션 정책이 있는 컬럼은 프로젝션할 수 없어요(즉, SELECT 문에 포함할 수 없어요). 하지만 소비자는 프로젝션 정책이 있는 컬럼으로 필터링하거나 조인할 수 있어요.
  3. 완전 허용 컬럼(Fully permitted columns): 소비자는 이 컬럼을 제한 없이(집계 여부와 무관하게) SELECT, 필터링, 조인할 수 있어요.
2. Privacy settings 섹션은 집계 정책이 적용된 모든 컬럼을 나열해요. Threshold 값은 그 값이 결과에 나타나기 위해 존재해야 하는 엔티티 수를 나타내요. 예를 들어 FIRST_NAME 컬럼에 임계값 5를 설정했는데 "Erasmus"라는 이름이 테이블에 4번만 나타난다면, "Erasmus"가 있는 모든 행은 어떤 처리도 일어나기 전에 필터링돼요(그래서 그런 테이블의 COUNT(*)는 임계값 미만 그룹 크기인 그 4행을 생략해요).

Consumer: 자유 형식 쿼리 실행

클린룸 UI에서 클린룸에 참여하거나 편집해요.
Configure Analysis & Query 섹션에서 자유 형식 쿼리에 사용할 테이블을 선택해요.

중요

클린룸 UI의 자유 형식 쿼리에서는 이름이 "LIST"(대소문자 무관)로 끝나는 테이블을 사용할 수 없어요.

Finish를 선택해 변경 사항을 저장해요.
쿼리를 실행하려면 SQL Query 템플릿이 있는 클린룸에서 Run을 선택하고 SQL Query 템플릿을 선택해요.

조인 및 필터링 컬럼 선택

정책이 있거나 완전히 허용된 모든 컬럼에 조인하고 필터링할 수 있어요. 컬럼이 조인되거나 필터에 사용될 수 있는지 확인하려면:

Query Configurations 섹션에서 Tables 타일을 찾아요.
드롭다운 목록을 사용해 테이블을 선택해요. 나열된 모든 컬럼에 조인하고 필터링할 수 있어요.

프로젝션 컬럼 선택

SQL Query 템플릿으로 실행된 쿼리는 프로젝션할 수 있는(SELECT 문에 사용할 수 있는) 컬럼에 제한이 있어요.

쿼리가 컬럼을 프로젝션할 수 있는지 확인하려면:

Query Configurations 섹션에서 Tables 타일을 찾아요.
드롭다운 목록을 사용해 테이블을 선택해요.
프로젝션 정책 라벨이 있는 컬럼을 찾아보세요. 그것은 프로젝션할 수 없다는 뜻이에요. 프로젝션 정책 라벨이 있는 컬럼을 제외한 모든 컬럼을 프로젝션할 수 있어요.

집계 요구 사항

제공자가 컬럼에 집계 정책을 배정했다면, SQL Query 템플릿으로 실행된 모든 쿼리는 집계된 결과를 반환해야 해요.

쿼리가 결과를 집계해야 하는지 확인하려면:

Query Configurations 섹션에서 Tables 타일을 찾아요.
드롭다운 목록을 사용해 테이블을 선택해요.
집계 정책 라벨이 있는 컬럼을 찾아보세요. 집계 정책 라벨이 하나 이상 있으면 쿼리에서 집계를 사용해야 해요.

집계 정책으로 보호된 데이터에 성공적인 쿼리를 작성하는 지침은 다음을 참고하세요:

집계 정책 쿼리 요구 사항. 예를 들어 이 섹션을 사용해 MIN과 MAX 집계 함수가 쿼리 요구 사항을 충족하지 않아 사용할 수 없다는 점을 확인할 수 있어요.
집계 정책 제한 사항

그래프 요구 사항

Snowflake가 그래프를 생성하려면:

결과 테이블에 측정(숫자) 컬럼이 하나 이상, 차원(카테고리) 컬럼이 하나 이상 포함되어야 해요.
측정 컬럼 이름이 다음 접두사 또는 접미사를 가져야 해요(대소문자 무관):
- 컬럼 이름 접두사: COUNT, SUM, AVG, MIN, MAX, OUTPUT, OVERLAP
- 컬럼 이름 접미사: _OVERLAP

Snowflake는 결과 테이블의 첫 번째 적격 측정 컬럼과 첫 번째 차원 컬럼을 사용해 차트를 생성해요.

제한 사항

ORDER BY 절은 분석 결과가 표시되는 방식에 영향을 주지 않아요.

샘플 쿼리

이 섹션을 사용해 SQL Query 템플릿으로 분석을 실행할 때 쿼리에 무엇을 포함할 수 있고 포함할 수 없는지 더 잘 이해하세요.

집계 함수가 없는 쿼리: 어떤 상황에서는 집계 함수를 사용하지 않고 값을 반환할 수 있어요.

허용됨	허용되지 않음
`sql SELECT gender, regions FROM TABLE sample_db.demo.customer GROUP BY gender, region;`	`sql SELECT gender, regions FROM TABLE sample_db.demo.customer;`

공통 테이블 표현식(CTE)

허용됨	허용되지 않음
`sql WITH audience AS (SELECT COUNT(DISTINCT t1.hashed_email), t1.status FROM provider_db.overlap.customers t1 JOIN consumer_db.overlap.customers t2 ON t1.hashed_email = t2.hashed_email GROUP BY t1.status); SELECT * FROM audience;`	`sql WITH audience AS (SELECT t1.hashed_email, t1.status FROM provider_db.overlap.customers quoted t1 JOIN consumer_db.overlap.customers t2 ON t1.hashed_email = t2.hashed_email GROUP BY t1.status) SELECT * FROM audience`

CREATE, ALTER, TRUNCATE: 쿼리는 CREATE, ALTER 또는 TRUNCATE를 사용할 수 없어요.

조인이 있는 쿼리

허용됨
`sql SELECT p.education_level, c.status, AVG(p.days_active), COUNT(DISTINCT p.age_band) FROM samooha_sample_database.demo.customers c INNER JOIN samooha_sample_database.demo.customers p ON c.hashed_email = p.hashed_email GROUP BY ALL;`

DATE_TRUNC

허용됨
`sql SELECT COUNT(*), DATE_TRUNC('week', date_joined) AS week FROM consumer_sample_database.audience_overlap.customers GROUP BY week;`

따옴표로 묶인 식별자

허용됨
`sql SELECT COUNT(DISTINCT t1."hashed_email") FROM provider_sample_database.audience_overlap."customers quoted" t1 INNER JOIN consumer_sample_database.audience_overlap.customers t2 ON t1."hashed_email" = t2.hashed_email;`

클린룸 테이블에서 자유 형식 SQL 쿼리 실행