Exécution de requêtes SQL de forme libre sur les tables des clean rooms

You can enable consumers to run free-form SQL queries on selected datasets in your clean room using either the clean room API or UI.

Free-form queries using the clean rooms API

You can configure a clean room to allow collaborators to query specific linked datasets from outside the clean room. Collaborators can run free-form queries on these datasets in any environment where they can access the clean room, including Snowsight or Snowflake CLI. Free-form datasets behave as standard, read-only views that can be queried using SQL, Python, or other supported Snowflake languages.

Note

When you grant a consumer permission to run free-form SQL queries in a clean room, that consumer can query the data from that clean room against any other data that they can access from their account.

Politiques et soutien différentiel en matière de protection de la vie privée

Lorsque vous exposez des données de clean room pour des requêtes de forme libre, toutes les politiques de Snowflake Data Clean Rooms sont respectées. Les politiques Clean room (politiques de jointure, politiques de colonne) ne sont pas appliquées dans les requêtes de forme libre.

Clean room differential privacy is not enforced on data exposed to free-form queries. This includes both Snowflake differential privacy and clean room differential privacy.

Permettre des requêtes de forme libre

Important

If a clean room was created before June, 2025 the provider must patch their clean room by running the following code to enable free-form queries in that clean room:

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

Provider steps

Le fournisseur prend les mesures suivantes pour mettre les ensembles de données d’une clean room à la disposition des collaborateurs de la clean room à l’aide de requêtes en forme libre :

  1. Créez la clean room de la manière habituelle.

  2. Register and link the datasets into the clean room in the standard way using the API. Note that currently your data must be registered using the API; you cannot register views in the clean room UI and use them for free-form queries. You should apply any Snowflake aggregation, join, or other policies before sharing your data outside the clean room.

  3. Call provider.enable_workflows_for_consumers to allow specific users free-form access to the tables that you will specify in the next step. You must name this work flow freeform_sql.

  4. Call provider.enable_datasets_for_workflow to specify which datasets in the clean room can be queried.

  5. Ajoutez vos collaborateurs de manière standard en appelant provider.add_consumers.

  6. Publiez votre clean room.

  7. If you want to revoke permission to query these tables, you can do this at the user level by calling provider.disable_consumer_run_analysis or provider.remove_consumers, at the dataset level by calling library.unregister_objects or library.unregister_db, or by deleting the clean room.

If a clean room already exists and data is registered, you can simply call provider.enable_workflows_for_consumers and provider.enable_datasets_for_workflow to expose the specified datasets to the specified users.

The following code creates three sample tables and applies Snowflake policies to them, creates a new clean room, links in the tables, and grants free-form query access to those tables for clean room collaborators via the clean room. The highlighted code shows where you enable free-form queries in the 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.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);
Copy

Consumer steps

After the provider has published a clean room with free-form SQL work flows, consumers with access to that clean room can run queries against the exposed views by following these steps:

  1. Install the clean room in the standard way. No need to link in consumer data, as the consumer will access their data in their local environment, not in the clean room.

  2. Appelez consumer.get_provider_freeform_sql_views pour dresser la liste des vues SQL de forme libre disponibles pour le compte et le rôle actuels.

  3. Run standard SQL queries against the data.

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

Requêtes en forme libre dans les UI de clean rooms

Le modèle de requête SQL dans une clean room permet aux consommateurs d’écrire une forme libre SQL pour interroger les données de la clean room. Lors de l’utilisation du modèle de requête SQL, les requêtes des consommateurs doivent répondre à certaines exigences pour renvoyer des résultats. Ces exigences sont déterminées par la manière dont le fournisseur de données protège ses tables avec des politiques de confidentialité des données.

Lors de la création ou de la mise à jour d’une clean room dans l’UI, ajoutez le modèle de requête SQL à votre clean room et configurez-la comme décrit ci-dessous.

Fournisseur : Créez une clean room et définissez des politiques

  1. Créez une clean room ou modifiez une clean room existante, et spécifiez des tables ou des vues pour votre table.

  2. Les politiques de jonction spécifiées au cours du processus de création de la clean room sont ignorées lors de l’utilisation du modèle de requête SQL, mais respectées pour tous les autres modèles.

  3. Dans Configure Analysis & Query, sélectionnez Horizontal » SQL Query.

  4. Dans la section des paramètres de SQL Query, définissez les propriétés suivantes :

    1. Under Tables, select tables that should be available to clean room collaborators in free-form queries. By default, aggregation policies do not need to be applied. To control which columns can be projected, and which must be aggregated, you must set column policies in the next section.

      Important

      Dans les requêtes de forme libre dans l’UI des salles blanches, vous ne pouvez pas utiliser une table dont le nom se termine par « LIST« » (majuscules ou minuscules).

    2. Dans la section Column Policies, définissez les valeurs suivantes pour contrôler si ou comment vos colonnes peuvent être utilisées dans une requête :

      1. Aggregation policy columns: indiquez quelles colonnes doivent être agrégées pour apparaître dans les résultats de la requête. Si vous appliquez une politique d’agrégation à une colonne et qu’une colonne est utilisée dans une requête, les résultats doivent être agrégés. Toutes les colonnes annoncées ici seront ajoutées à la section Privacy settings.

      2. Projection policy columns: les colonnes dotées d’une politique de projection ne peuvent pas être projetées (c’est-à-dire incluses dans une SELECT instruction). Toutefois, les consommateurs peuvent filtrer ou effectuer une jointure sur une colonne à l’aide d’une politique de projection.

      3. Fully permitted columns: le consommateur peut SELECT, filtrer ou effectuer une jointure sur ces colonnes sans restriction (agrégation ou autre).

    3. La section Privacy settings dresse la liste de toutes les colonnes auxquelles une politique d’agrégation a été appliquée. La valeur Threshold indique combien d’entités doivent exister pour que cette valeur apparaisse dans les résultats. Par exemple, si vous avez fixé un seuil de 5 pour la colonne FIRST_NAME et que le nom Erasmus n’apparaît que 4 fois dans la table, toutes les lignes contenant Erasmus seront filtrées avant tout traitement (ainsi, par exemple, un COUNT(*) sur une telle table omettra les 4 lignes dont la taille du groupe est inférieure au seuil).

Consommateur : effectuez une requête de forme libre

  1. Rejoignez ou modifiez la clean room dans l’UI de clean room.

  2. Dans la section Configure Analysis & Query, choisissez les tables que vous utiliserez pour les requêtes en forme libre.

    Important

    Dans les requêtes de forme libre dans l’UI des salles blanches, vous ne pouvez pas utiliser une table dont le nom se termine par « LIST« » (majuscules ou minuscules).

  3. Sélectionnez Finish pour enregistrer vos modifications.

  4. Pour exécuter une requête, sélectionnez Run dans la clean room avec le modèle de requête SQL et sélectionnez le modèle de requête SQL.

Sélectionnez les colonnes de jointure et de filtrage

Vous pouvez effectuer des jointures et des filtres sur n’importe quelle colonne qui a une politique ou qui est entièrement autorisée. Pour déterminer si une colonne peut être jointe ou utilisée dans un filtre :

  1. Dans la section Query Configurations, trouvez la vignette Tables.

  2. Utilisez la liste déroulante pour sélectionner une table. Vous pouvez effectuer des jointures et des filtres sur toutes les colonnes de la liste.

Sélectionnez les colonnes de projection

Les requêtes exécutées à l’aide des modèles de requête SQL ont des restrictions sur les colonnes qui peuvent être projetées (utilisées dans une instruction SELECT).

Pour déterminer si votre requête peut projeter une colonne :

  1. Dans la section Query Configurations, trouvez la vignette Tables.

  2. Utilisez la liste déroulante pour sélectionner une table.

  3. Recherchez les colonnes qui portent un label de politique de projection, ce qui signifie que vous ne pouvez pas les projeter. Vous pouvez projeter toutes les colonnes à l’exception de celles qui portent l’étiquette de la politique de projection.

Exigences en matière d’agrégation

Si le fournisseur a attribué une politique d’agrégation à une colonne, toutes les requêtes exécutées à l’aide du modèle de requête SQL doivent renvoyer des résultats agrégés.

Pour déterminer si votre requête doit regrouper les résultats :

  1. Dans la section Query Configurations, trouvez la vignette Tables.

  2. Utilisez la liste déroulante pour sélectionner une table.

  3. Recherchez les colonnes qui ont une étiquette de politique d’agrégation. S’il existe au moins une étiquette de politique d’agrégation, vous devez utiliser un agrégat dans votre requête.

Pour obtenir des instructions sur la manière d’écrire une requête réussie sur des données protégées par une politique d’agrégation, consultez :

Exigences graphiques

Pour que Snowflake puisse générer un graphique :

  • Le tableau des résultats doit inclure au moins une colonne de mesure (numérique) et une colonne de dimension (catégorie).

  • Le nom de la colonne de mesure doit avoir le préfixe ou le suffixe suivant (insensible à la casse) :

    • Préfixes de nom de colonne :

      • COUNT

      • SUM

      • AVG

      • MIN

      • MAX

      • OUTPUT

      • OVERLAP

    • Suffixe du nom de la colonne :

      • _OVERLAP

Snowflake génère un graphique en utilisant la première colonne de mesure éligible et la première colonne de dimension dans une table de résultats.

Limitations

  • Une clause ORDER BY n’a aucun effet sur la manière dont les résultats de l’analyse sont affichés.

Exemple de requêtes

Utilisez cette section pour mieux comprendre ce qu’une requête peut et ne peut pas inclure lors de l’exécution d’une analyse avec le modèle de requête SQL.

Requêtes sans fonction d’agrégation

Dans certaines circonstances, vous pouvez renvoyer des valeurs sans utiliser de fonction d’agrégation.

Autorisé

Non autorisé

 
SELECT gender, regions
  FROM TABLE sample_db.demo.customer
  GROUP BY gender, region;
Copy
 
SELECT gender, regions
  FROM TABLE sample_db.demo.customer;
Copy
Expressions de table communes (CTEs)

Autorisé

Non autorisé

 
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

Une requête ne peut pas utiliser CREATE, ALTER ou TRUNCATE.

Requête avec jointures

Autorisé

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

Autorisé

 
SELECT COUNT(*),
  DATE_TRUNC('week', date_joined) AS week
  FROM consumer_sample_database.audience_overlap.customers
  GROUP BY week;
Copy
Identificateurs avec guillemets

Autorisé

 
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
Langage: Français