Execução de consultas SQL de forma livre nas tabelas de clean room

Você pode permitir que os consumidores executem consultas SQL de formato livre em conjuntos de dados selecionados em sua sala limpa usando a API ou a UI do Clean Rooms.

Consultas de formato livre usando a API do Clean Rooms

Você pode configurar uma sala limpa para permitir que os colaboradores consultem conjuntos de dados vinculados específicos de fora da sala limpa. Os colaboradores podem executar consultas de formato livre nesses conjuntos de dados em qualquer ambiente em que possam acessar a sala limpa, incluindo o Snowsight ou o Snowflake CLI. Os conjuntos de dados de formato livre se comportam como exibições padrão somente leitura que podem ser consultadas usando SQL, Python ou outras linguagens compatíveis com o Snowflake.

Nota

Quando você concede a um consumidor permissão para executar consultas SQL de formato livre em uma sala limpa, esse consumidor pode comparar os dados dessa sala limpa com outros dados que ele possa acessar na conta dele.

Políticas e suporte à privacidade diferencial

Quando você expõe dados de clean room para consultas de forma livre, todas as políticas do Snowflake são respeitadas. Políticas de clean room (políticas de junção, políticas de coluna) não são aplicadas em consultas de forma livre.

A privacidade diferencial da sala limpa não é ` aplicada aos dados expostos a consultas de formato livre. Isso inclui tanto a :doc:`privacidade diferencial do Snowflake </user-guide/diff-privacy/differential-privacy-overview> quanto a privacidade diferencial da sala limpa.

Ativação de consultas de forma livre

Importante

Se uma sala limpa foi criada antes de junho de 2025, o provedor deve corrigi-la executando o seguinte código para habilitar consultas de formato livre nela:

USE ROLE SAMOOHA_APP_ROLE;
CALL samooha_by_snowflake_local_db.provider.patch_cleanroom($cleanroom_name,TRUE);

Etapas do provedor

O provedor executa os seguintes passos para disponibilizar os conjuntos de dados em uma clean room aos colaboradores da clean room usando consultas de forma livre:

  1. Crie a clean room da maneira padrão.

  2. Registre e vincule os conjuntos de dados à sala limpa da maneira padrão usando a API. Observe que, atualmente, seus dados devem ser registrados usando a API; você não pode registrar exibições na UI da sala limpa e usá-las para consultas de formato livre. Você deve aplicar quaisquer políticas de agregação, junção ou outras do Snowflake antes de compartilhar seus dados fora da sala limpa.

  3. Chame provider.enable_workflows_for_consumers para permitir que usuários específicos acessem livremente as tabelas que você especificará na próxima etapa. Você deve nomear este fluxo de trabalho como freeform_sql.

  4. Chame provider.enable_datasets_for_workflow para especificar quais conjuntos de dados na sala limpa podem ser consultados.

  5. Adicione seus colaboradores da maneira padrão, chamando provider.add_consumers.

  6. Publique sua clean room.

  7. Se você quiser revogar a permissão para consultar essas tabelas, poderá fazer isso no nível do usuário chamando provider.disable_consumer_run_analysis ou provider.remove_consumers, no nível do conjunto de dados chamando library.unregister_objects ou library.unregister_db ou excluindo a sala limpa.

Se uma sala limpa já existir e os dados estiverem registrados, você poderá simplesmente chamar provider.enable_workflows_for_consumers e provider.enable_datasets_for_workflow para expor os conjuntos de dados especificados aos usuários especificados.

O código a seguir cria três tabelas de exemplo e aplica políticas do Snowflake a elas, cria uma nova sala limpa, vincula as tabelas e concede acesso a consultas de formato livre a essas tabelas para colaboradores da sala limpa por meio da sala limpa. O código destacado mostra onde você habilita consultas de formato livre na sala limpa.

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

Etapas do consumidor

Depois que o provedor publicar uma sala limpa com fluxos de trabalho SQL de formato livre, os consumidores com acesso a essa sala limpa poderão executar consultas nas exibições expostas seguindo estas etapas:

  1. Instale a sala limpa da maneira padrão. Não é necessário vincular os dados do consumidor, pois o consumidor acessará os dados no ambiente local, não na sala limpa.

  2. Chame consumer.get_provider_freeform_sql_views para listar as exibições SQL de forma livre disponíveis para a conta e função atuais.

  3. Execute consultas SQL padrão nos dados.

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

Consultas de forma livre na UI de clean rooms

O modelo de consulta SQL em uma clean room permite que os consumidores escrevam SQL de forma livre para consultar dados na clean room. Ao usar o modelo de consulta SQL, as consultas do consumidor devem atender a determinados requisitos para retornar resultados com sucesso. Esses requisitos são determinados pela forma como o provedor de dados protege suas tabelas com políticas de privacidade de dados.

Ao criar ou atualizar uma clean room na UI, adicione o modelo de consulta SQL à sua clean room e configure-o conforme descrito abaixo.

Provedor: crie uma clean room e defina as políticas

  1. Crie uma clean room ou edite uma clean room existente e especifique tabelas ou exibições para sua tabela.

  2. As políticas de junção especificadas durante o processo de criação da clean room são ignoradas ao usar o modelo de consulta SQL, mas são respeitadas em qualquer outro modelo.

  3. Em Configure Analysis & Query, selecione Horizontal » SQL Query.

  4. Na seção de configurações SQL Query, defina as seguintes propriedades:

    1. Em Tables, selecione as tabelas que devem estar disponíveis para os colaboradores da sala limpa em consultas de formato livre. Por padrão, as políticas de agregação não precisam ser aplicadas. Para controlar quais colunas podem ser projetadas e quais devem ser agregadas, você deve definir as políticas de coluna na próxima seção.

      Importante

      Em consultas de forma livre na UI de salas limpas, você não poderá usar uma tabela com um nome que termine em “LIST» (maiúsculas ou minúsculas).

    2. Na seção Column Policies, defina os seguintes valores para controlar se ou como suas colunas podem ser usadas em uma consulta:

      1. Aggregation policy columns: especifique quais colunas devem ser agregadas para que apareçam nos resultados de consulta. Se você aplicar uma política de agregação a uma coluna e uma coluna for usada em uma consulta, os resultados deverão ser agregados. Todas as colunas listadas aqui serão adicionadas à seção Privacy settings.

      2. Projection policy columns: as colunas com uma política de projeção não podem ser projetadas (ou seja, incluídas em uma instrução SELECT). No entanto, os consumidores podem filtrar ou fazer junções com uma coluna que tenha uma política de projeção.

      3. Fully permitted columns: o consumidor pode usar SELECT, filtrar ou unir essas colunas sem restrições (agregação ou não).

    3. A seção Privacy settings lista todas as colunas com uma política de agregação aplicada. O valor Threshold indica quantas entidades devem existir para que esse valor apareça nos resultados. Por exemplo, se você definir um limite de 5 em uma coluna FIRST_NAME e o nome “Erasmus” aparecer apenas 4 vezes na tabela, todas as linhas com “Erasmus” serão filtradas antes de qualquer processamento (portanto, por exemplo, um COUNT(*) nessa tabela omitirá as 4 linhas com o tamanho de grupo abaixo do limite).

Consumidor: execute uma consulta de forma livre

  1. Participe ou edite a clean room na UI de clean rooms.

  2. Na seção Configure Analysis & Query, escolha as tabelas que serão usadas para consultas de forma livre.

    Importante

    Em consultas de forma livre na UI de salas limpas, você não poderá usar uma tabela com um nome que termine em “LIST» (maiúsculas ou minúsculas).

  3. Selecione Finish para salvar suas alterações.

  4. Para executar uma consulta, selecione Run na clean room com o modelo de consulta SQL e selecione o modelo de consulta SQL.

Selecione as colunas de junção e filtragem

É possível unir e filtrar qualquer coluna que tenha uma política ou que seja totalmente permitida. Para determinar se uma coluna pode ser unida ou usada em um filtro:

  1. Na seção Query Configurations, encontre o bloco Tables.

  2. Use a lista suspensa para selecionar uma tabela. É possível unir e filtrar todas as colunas listadas.

Selecione as colunas de projeção

Consultas executadas usando o modelo de consulta SQL têm restrições sobre quais colunas podem ser projetadas (usadas em uma instrução SELECT).

Para determinar se sua consulta pode projetar uma coluna:

  1. Na seção Query Configurations, encontre o bloco Tables.

  2. Use a lista suspensa para selecionar uma tabela.

  3. Procure colunas que tenham um rótulo de política de projeção, o que significa que você não pode projetá-las. É possível projetar todas as colunas, exceto as que têm o rótulo da política de projeção.

Requisitos de agregação

Se o provedor atribuiu uma política de agregação a uma coluna, todas as consultas executadas usando o modelo de consulta SQL devem retornar resultados agregados.

Para determinar se sua consulta deve agregar resultados:

  1. Na seção Query Configurations, encontre o bloco Tables.

  2. Use a lista suspensa para selecionar uma tabela.

  3. Procure colunas que tenham um rótulo de política de agregação. Se houver pelo menos um rótulo de política de agregação, é necessário usar um agregado em sua consulta.

Para obter diretrizes sobre como escrever uma consulta bem-sucedida em dados protegidos por uma política de agregação, consulte:

Requisitos de gráficos

Para que o Snowflake consiga gerar um gráfico:

  • A tabela de resultados deve incluir pelo menos uma coluna de medida (numérica) e uma coluna de dimensão (categoria).

  • O nome da coluna de medida deve ter o seguinte prefixo ou sufixo (sem distinção entre maiúsculas e minúsculas):

    • Prefixos de nome de coluna:

      • COUNT

      • SUM

      • AVG

      • MIN

      • MAX

      • OUTPUT

      • OVERLAP

    • Sufixo do nome da coluna:

      • _OVERLAP

O Snowflake gera um gráfico usando a primeira coluna de medida elegível e a primeira coluna de dimensão em uma tabela de resultados.

Limitações

  • Uma cláusula ORDER BY não tem efeito sobre como os resultados da análise são exibidos.

Consultas de exemplo

Use esta seção para entender melhor o que uma consulta pode e não pode incluir ao executar uma análise com o modelo de consulta SQL.

Consultas sem uma função de agregação

Em algumas circunstâncias, você pode retornar valores sem usar uma função de agregação.

Permitido

Não permitido

 
SELECT gender, regions
  FROM TABLE sample_db.demo.customer
  GROUP BY gender, region;

SELECT gender, regions
  FROM TABLE sample_db.demo.customer;
Expressões de tabela comuns (CTEs)

Permitido

Não permitido

 
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;

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

Uma consulta não pode usar CREATE, ALTER ou TRUNCATE.

Consulta com junções

Permitido

 
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

Permitido

 
SELECT COUNT(*),
  DATE_TRUNC('week', date_joined) AS week
  FROM consumer_sample_database.audience_overlap.customers
  GROUP BY week;
Identificadores entre aspas

Permitido

 
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;