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

Você pode permitir que os consumidores executem consultas SQL de forma livre em conjuntos de dados selecionados em uma clean room usando a API ou UI de clean room.

Consultas de forma livre na API de clean rooms

Você pode configurar uma clean room para permitir que os colaboradores consultem conjuntos de dados específicos de fora da clean room. Os colaboradores podem executar consultas de forma livre nesses conjuntos de dados em qualquer ambiente em que possam acessar a clean room, incluindo o Snowsight ou a Snowflake API. Os conjuntos de dados de forma 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.

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 não é aplicada em dados expostos a consultas de forma livre. Isso inclui tanto a privacidade diferencial Snowflake quanto a privacidade diferencial de clean room.

Ativação de consultas de forma livre

Importante

Se uma clean room tiver sido criada antes de junho de 2025, o provedor deverá instalar o seguinte patch para permitir consultas de forma livre nessa clean room usando a API:

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

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 à clean room da maneira padrão. Observe que, atualmente, os dados devem ser registrados usando a API; não é possível registrar exibições na UI de clean room e usá-las para consultas de forma livre. Você deve aplicar qualquer política de agregação, junção ou outras políticas do Snowflake antes de compartilhar seus dados fora da clean room.

  3. Chame provider.enable_workflows_for_consumers para permitir que usuários específicos tenham acesso de forma livre às tabelas que você especificará na próxima etapa.

  4. Chame provider.enable_datasets_for_workflow para permitir que os usuários da etapa anterior tenham acesso de forma livre aos conjuntos de dados especificados aqui.

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

  6. Publique sua clean room.

  7. Se quiser revogar a permissão para consultar essas tabelas, você pode fazer isso no nível do usuário, chamando provider.disable_consumer_run_analysis ou provider.remove_consumers, no nível da exibição, chamando library.unregister_objects ou library.unregister_db, ou excluindo a clean room.

Se já existir uma clean room e os dados estiverem registrados, basta chamar provider.enable_workflows_for_consumers e provider.enable_datasets_for_workflow para expor as tabelas especificadas aos usuários especificados.

O código a seguir cria três tabelas de amostra, cria uma nova clean room, vincula as tabelas e concede acesso de consulta de forma livre a essas tabelas para os colaboradores da clean room por meio da clean room.

----------------- 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_FREEFORM.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_FREEFORM.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.
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);
Copy

Consumidor

Depois que o provedor tiver publicado uma clean room com fluxos de trabalho SQL de forma livre, os consumidores com acesso a essa clean room poderão executar consultas nas exibições expostas.

  1. Instale a clean room da maneira padrão. Não há necessidade de vincular dados, pois o consumidor pode acessar suas tabelas diretamente.

  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 as consultas SQL padrão com base nos dados. As políticas do produtor serão aplicadas aos 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;
Copy

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 aos colaboradores da clean room em consultas de forma livre. Por padrão, todas as colunas das tabelas selecionadas podem ser projetadas, e as políticas de agregação não precisam ser aplicadas. Para controlar quais colunas podem ser projetadas e quais devem ser agregadas, é necessário definir políticas de coluna na próxima seção.

    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.

  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;
Copy
SELECT gender, regions
  FROM TABLE sample_db.demo.customer;
Copy
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;
Copy
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
Copy
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  sample_database_preprod.demo.customers c
  INNER JOIN
  sample_database_preprod.demo.customers p
    ON  c.hashed_email = p.hashed_email
  GROUP BY ALL;
Copy
DATE_TRUNC

Permitido

SELECT COUNT(*),
  DATE_TRUNC('week', date_joined) AS week
  FROM consumer_sample_database.audience_overlap.customers
  GROUP BY week;
Copy
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;
Copy