Concevoir des modèles personnalisés

À propos des modèles de clean rooms

Les modèles de salle blanche sont écrits en `JinjaSQL<https://github.com/sripathikrishnan/jinjasql>`_ .JinjaSQL est une extension du langage de modélisation Jinja. Un modèle JinjaSQL est évalué selon une instruction SQL lorsqu’elle est exécutée dans une salle blanche. Le langage de modélisation JinjaSQL fournit des instructions logiques et le remplacement des variables d’exécution, ce qui permet de personnaliser le modèle au moment de l’exécution. Par exemple, un utilisateur peut fournir des noms de table et de colonne lorsqu’il exécute le modèle, et le modèle peut s’ajuster lui-même en fonction des valeurs transmises.

Il existe deux types généraux de modèles :

  • Modèles d’analyse, qui correspondent à une instruction SQL DQL (une instruction SELECT) qui renvoie immédiatement les résultats de la requête à l’exécutant du modèle.

  • Modèles d’activation, qui sont utilisés pour activer les résultats sur un compte Snowflake, plutôt que d’afficher les résultats dans l’environnement immédiat. Un modèle d’activation est très similaire à un modèle d’analyse avec quelques exigences supplémentaires, et il est évalué selon une instruction DDL (CREATE TABLE).

Création, partage et exécution d’un modèle personnalisé

Tout collaborateur peut enregistrer et partager des modèles avec des exécutants d’analyse spécifiques dans une collaboration.

Commençons par examiner une simple requête SQL et comment elle serait écrite en tant que modèle.

1. Le modèle JinjaSQL

Voici une simple requête SQL qui joint deux tables par e-mail et montre le nombre de chevauchements par ville :

SELECT COUNT(*), city FROM table_1
  INNER JOIN table_2
  ON table_1.hashed_email = table_2.hashed_email
  GROUP BY city;

Voici à quoi ressemblerait cette requête en tant que modèle JinjaSQL qui permet à l’appelant de choisir les colonnes JOIN et GROUP BY, ainsi que les tables utilisées. Le modèle comprend des filtres qui appliquent des politiques de Snowflake Data Clean Room.

SELECT COUNT(*), IDENTIFIER({{ group_by_col | column_policy }})
  FROM IDENTIFIER({{ source_table[0] }}) AS p1
  INNER JOIN IDENTIFIER({{ source_table[1] }}) AS p2
  ON IDENTIFIER({{ p1_join_col | join_policy }}) = IDENTIFIER({{ p2_join_col | join_policy }})
  GROUP BY IDENTIFIER({{ group_by_col | column_policy }});

Notes sur le modèle :

  • Les valeurs dans {{paires de crochets doubles }} sont des variables. Les valeurs sont renseignées par l’appelant.

  • group_by_col, source_table, p1_join_col et``p2_join_col`` sont toutes des variables alimentées par l’appelant. Ces variables ont des noms arbitraires choisis par le fournisseur de modèles.

  • source_table est une variable standard définie par Snowflake. Cette variable définit les vues à utiliser dans la requête. Ces vues sont des ensembles de données dans des offres de données qui sont liées à la salle blanche. Les collaborateurs peuvent répertorier les ensembles de données disponibles en appelant VIEW_DATA_OFFERINGS.

  • Un ensemble de données doit être aliasé en tant que minuscule p si vous souhaitez lui appliquer les politiques de Snowflake Data Clean Room. Si un modèle utilise plusieurs ensembles de données, le premier est``p`` ou p1 et les ensembles de données supplémentaires sont indexés comme p2, p3 et ainsi de suite.

  • IDENTIFIER est nécessaire pour tous les noms de colonnes et de tables, car les variables dans {{ double brackets }} évaluent des chaînes littérales, qui ne sont pas des identificateurs valides.

  • Les filtres JinjaSQL sont appliqués aux colonnes pour appliquer les politiques de Snowflake Data Clean Room à la colonne. Snowflake implémente des filtres personnalisés join_policy et column_policy, qui vérifient si une colonne est conforme aux politiques de jointure ou de colonne dans la salle blanche respectivement et font échouer la requête si ce n’est pas le cas. Un filtre est appliqué à un nom de colonne comme {{ column_name | filter_name }}.

Tous ces points seront examinés en détail ultérieurement.

2. Le modèle de collaboration

Un modèle est ajouté à une collaboration en l’intégrant dans une spécification YAML, en l’enregistrant, puis en le liant.

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_TEMPLATE(
  $$
  api_version: 2.0.0
  spec_type: template
  name: my_test_template
  version: 2026_01_12_V1
  type: sql_analysis
  description: A test template
  methodology: Join on single column with a single group by value
  parameters:
  - name: source_tables
    description: Tables from both sides which can be listed in any order, aliased with p1 or p2
    required: true
  - name: p1_join_col
    description: Column to join on from first table specified under source_tables, aliased with p1
    required: true
  - name: p2_join_col
    description: Column to join on from second table specified under source_tables, , aliased with p2
    required: true
  - name: group_by_col
    description: Column which results should be grouped group aliased with respective table p1 or p2
    required: true

  template:
    SELECT COUNT(*), IDENTIFIER({{ group_by_col | column_policy }})
    FROM IDENTIFIER({{ source_table[0] }}) AS p1
    INNER JOIN IDENTIFIER({{ source_table[1] }}) AS p2
    ON IDENTIFIER({{ p1_join_col | join_policy }}) = IDENTIFIER({{ p2_join_col | join_policy }})
    GROUP BY IDENTIFIER({{ group_by_col | column_policy }});

$$);

Vous devez demander à partager un modèle avec un exécutant d’analyse donné, qui peut accepter ou rejeter la demande. En outre, tous les fournisseurs de données pour cet exécutant d’analyse doivent accepter la demande de partage du modèle.

-- Request to share template with only Collaborator3.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.ADD_TEMPLATE_REQUEST(
  $collaboration_name,
  $template_id,
  ['Collaborator3']
);

3. Exécution du modèle

Voici comment un exécutant d’analyse pourrait exécuter ce modèle dans le code. Notez comment les noms de colonnes sont qualifiés par les alias de table déclarés dans le modèle.

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.RUN( $collaboration_name,
$$
api_version: 2.0.0
spec_type: analysis
name: example_run
description: Example run for template
template: $template_id

template_configuration:
  view_mappings:
    source_tables:
      - collaborator_1.data_offering_1.dataset_1
      - collaborator_2.data_offering_2.dataset_2
  arguments:
     p1_join_col: p1.hashed_email
     p2_join_col: p2.hashed_email
     group_by_col: p2.device_type

$$ );

Développement d’un modèle personnalisé

Les modèles de salle blanche sont des modèles JinjaSQL. Pour créer un modèle, vous devez connaître les rubriques suivantes :

Vous pouvez utiliser Cortex Code pour valider les sorties SQL de vos modèles JinjaSQL basés sur les entrées variables qui doivent être fournies. Voir des exemples d’invites ci-dessous que vous pouvez copier dans Cortex Code pour obtenir les sorties SQL finales que vous pouvez tester :

Exemple :

Resolve the following Jinja template into SQL based on the variables defined:

Jinja Template:
 SELECT IDENTIFIER({{ col1 | column_policy }}), IDENTIFIER({{ col2 | column_policy }})
  FROM IDENTIFIER({{ source_table[0] }}) AS p1
  JOIN IDENTIFIER({{ source_table[1] }}) AS p2
  ON  IDENTIFIER({{ p1_join_col | join_policy }}) = IDENTIFIER({{ p2_join_col | join_policy }})
  {% if where_phrase %} WHERE {{ where_phrase | sqlsafe }}{% endif %};

Variable Inputs:
source_table: SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS, SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS
col1: p1.status
col2: p1.age_band
p1_join_col: p1.hashed_email
p2_join_col: p2.hashed_email
where_phrase: p1.household_size > 2

Le modèle rendu ressemble à ceci :

SELECT IDENTIFIER('p1.status'), IDENTIFIER('p1.age_band')
FROM IDENTIFIER('SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS') AS p1
JOIN IDENTIFIER('SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS') AS p2
ON  IDENTIFIER('p1.hashed_email') = IDENTIFIER('p2.hashed_email')
WHERE p1.household_size > 2;

Essayez d’exécuter l’instruction SQL ci-dessus dans votre environnement pour voir si cela fonctionne et permet d’obtenir les résultats attendus.

Testez ensuite votre modèle sans clause WHERE :

Resolve the following Jinja template into SQL based on the variables defined:

Jinja Template:
 SELECT IDENTIFIER({{ col1 | column_policy }}), IDENTIFIER({{ col2 | column_policy }})
  FROM IDENTIFIER({{ source_table[0] }}) AS p1
  JOIN IDENTIFIER({{ source_table[1] }}) AS p2
  ON  IDENTIFIER({{ p1_join_col | join_policy }}) = IDENTIFIER({{ p2_join_col | join_policy }})
  {% if where_phrase %} WHERE {{ where_phrase | sqlsafe }}{% endif %};

Variable Inputs:
source_table: SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS, SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS
col1: p1.status
col2: p1.age_band
p1_join_col: p1.hashed_email
p2_join_col: p2.hashed_email

Modèle renvoyé :

SELECT IDENTIFIER('p1.status'), IDENTIFIER('p1.age_band')
FROM IDENTIFIER('SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS') AS p1
JOIN IDENTIFIER('SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS') AS p2
ON  IDENTIFIER('p1.hashed_email') = IDENTIFIER('p2.hashed_email');

Ajoutez le modèle dans votre salle blanche et testez-le avec une spécification d’exécution d’analyse.

Protection des données

Les modèles ne peuvent accéder qu’aux ensembles de données liés à la salle blanche par les collaborateurs.

Les collaborateurs spécifient des politiques de jointure, de colonne et d’activation sur leurs ensembles de données pour que seules ces colonnes puissent être utilisées comme entrée pour une variable de modèle.

Important

Le modèle doit inclure le filtre de politique JinjaSQL approprié sur une colonne pour la politique à appliquer.

Syntaxe des modèles personnalisés

Snowflake Data Clean Rooms prend en charge la version 3 du site JinjaSQL, avec quelques extensions comme indiqué.

Cette section comprend les rubriques suivantes :

Règles de dénomination des modèles

Lors de la création d’un modèle, les noms doivent contenir uniquement des lettres, des nombres ou des traits de soulignement. Les noms des modèles sont attribués dans le champ name de la spécification du modèle lorsque vous enregistrez le modèle.

Exemples de noms valides :

  • my_template

  • activation_template_1

Exemple de noms non valides :

  • my template - Espaces non autorisés

  • my_template! - Les caractères spéciaux ne sont pas autorisés

Variables de modèles

Les appelants de modèles peuvent transmettre des valeurs aux variables de modèles. La syntaxe JinjaSQL permet la liaison de variables pour n’importe quel nom de variable dans {{accollades_doubles}}, mais Snowflake réserve quelques noms de variables que vous ne devez pas remplacer, comme décrit ci-dessous.

Prudence

Toutes les variables, qu’elles soient définies par Snowflake ou personnalisées, sont renseignées par l’utilisateur et doivent être traitées avec la précaution appropriée. Les modèles d’analyse doivent être résolus en tant qu’une seule instruction SELECT (les modèles d’activation sont résolus en un bloc de script). Rappelez-vous que toutes les variables sont transmises par l’appelant.

Variables définies par Snowflake

Tous les modèles de salle blanche ont accès aux variables globales suivantes définies par Snowflake, mais transmises par le gestionnaire d’analyse :

source_table:

Un tableau de chaînes de base zéro de tables et vues provenant d’offres de données liées à la collaboration viaLINK_DATA_OFFERING et pouvant être utilisé par le modèle.

Exemple :** SELECT col1 FROM IDENTIFIER({{ source_table[0] }}) AS p;

my_table:

Dans une salle blanche de collaboration,``my_table`` est utilisé uniquement par les utilisateurs de Snowflake Standard Edition. Pour ces utilisateurs, my_table est un tableau de chaînes de base zéro d’ensembles de données que le exécutant d’analyse a lié en appelant LINK_LOCAL_DATA_OFFERING.

Exemple :** SELECT col1 FROM IDENTIFIER({{ my_table[0] }}) AS c;

Variables personnalisées

Les créateurs de modèles peuvent inclure des variables arbitraires dans un modèle qui peuvent être renseignées par l’exécutant d’analyse. Ces variables peuvent avoir n’importe quel nom Jinja compatible, à l’exception des variables définies par Snowflake ou des noms d’alias de table. Vous devez fournir des conseils dans la section Paramètres du modèle pour les variables obligatoires et facultatives.

Les variables personnalisées sont accessibles par votre modèle, comme indiqué ici pour la variable personnalisée max_income:

SELECT income FROM my_db.my_sch.customers WHERE income < {{ max_income }};

Les exécutions d’analyses transmettent des variables lors de l’appel de RUN tel que défini dans la spécification d’exécution d’analyse.

Résoudre correctement les variables

Les valeurs de chaîne passées dans le modèle se traduisent par une chaîne littérale dans le modèle final. Cela peut provoquer des erreurs d’analyse SQL ou des erreurs logiques si vous ne gérez pas les variables de liaison de manière appropriée :

  • SELECT {{ my_col }} FROM p; - Se résout en SELECT 'my_col' from p; qui renvoie simplement la chaîne « my_col » - ce qui n’est probablement pas ce que vous voulez.

  • SELECT age FROM {{ source_table[0] }} AS p; se résout en SELECT age FROM 'somedb.somesch.source_table' AS p;, ce qui provoque une erreur d’analyse, car une table doit être un identificateur et non une chaîne littérale.

  • SELECT age FROM IDENTIFIER({{ source_table[0] }}) AS p {{ where_clause }}; - Transmettre « WHERE age < 50 » donne comme résultat SELECT age FROM mytable AS p 'WHERE age < 50';, qui est une erreur d’analyse à cause de la clause WHERE de la chaîne littérale.

Par conséquent, le cas échéant, vous devez résoudre les variables. Voici comment résoudre correctement les variables dans votre modèle :

Résolution des noms de table et de colonne

Les variables qui spécifient des noms de tables ou de colonnes doivent être converties en identificateurs dans votre modèle de l’une des deux manières suivantes :

Votre utilisation particulière dicte quand utiliser IDENTIFIER ou sqlsafe. Par exemple,``p.{{ my_column | sqlsafe }}`` ne peut pas être facilement réécrit à l’aide de IDENTIFIER.

Résolution du SQL dynamique

Lorsque vous avez une variable chaîne qui doit être utilisée comme littéral SQL, telle qu’une clause WHERE, utilisez le filtre sqlsafe dans votre modèle. Par exemple :

SELECT age FROM IDENTIFIER({{ source_table[0] }}) AS p WHERE {{ where_clause }};

Si un utilisateur transmet « âge < 50 » à where_clause, la requête se résoudrait en SELECT age FROM sometable AS p WHERE 'age < 50';, qui n’est pas une instruction SQL en raison de la condition WHERE de la chaîne littérale. Dans ce cas, vous devriez utiliser le filtre sqlsafe :

SELECT age FROM IDENTIFIER( {{ source_table[0] }} ) as p {{ where_clause | sqlsafe }};

Alias de table requis

Au niveau supérieur de votre requête, tous les ensembles de données source_table doivent être aliasés comme p, et tous les ensembles de données my_table doivent être aliasés comme c, afin que Snowflake valide correctement les politiques de jointure et de colonne dans la requête. Toute colonne qui doit être vérifiée par rapport aux politiques de jointure ou de colonne doit avoir l’alias de table p ou c en minuscule.

Si vous utilisez plusieurs ensemble de données source_table ou my_table dans votre requête, ajoutez un suffixe numérique et séquentiel de base 1 à chaque alias de table après le premier. Donc : p ou p1, p2, p3, et ainsi de suite pour les premier, deuxième et troisième ensembles de données source_table, et c ou c1, c2, c3, et ainsi de suite pour les premier, deuxième et troisième ensembles de données my_table. L’index p ou c doit être séquentiel sans espaces (c’est-à-dire avec des alias p1, p2 et p3, pas p1, p2, et p4).

Exemple

SELECT p1.col1 FROM IDENTIFIER({{ source_table[0] }}) AS p1
UNION
SELECT p2.col1 FROM IDENTIFIER({{ source_table[1] }}) AS p2;

Filtres de modèles de salle blanche personnalisés

Snowflake prend en charge tous les filtres standard Jinja et la plupart des filtres JinjaSQL standard, ainsi que quelques extensions :

join_policy:

Réussit si la colonne est dans la politique de jointure du propriétaire des données ; échoue dans le cas contraire. Voir Application de politiques de protection des données aux offres de données.

column_policy:

Réussit si la colonne figure dans la politique de colonne du propriétaire des données ; échoue dans le cas contraire. Voir Application de politiques de protection des données aux offres de données.

activation_policy:

Réussit si la colonne se trouve dans la politique d’activation du propriétaire des données ; échoue dans le cas contraire. Voir Application de politiques de protection des données aux offres de données.

join_and_column_policy:

Réussit si la colonne figure dans la politique de jointure ou de colonne du propriétaire des données ; échoue dans le cas contraire. Voir Application de politiques de protection des données aux offres de données.

identifier:

Ce filtre JinjaSQL n’est pas pris en charge par les modèles Snowflake.

Astuce

L’évaluation des expressions JinjaSQL s’effectue de gauche à droite :

  • {{ my_col | column_policy }} Correct

  • {{ my_col | sqlsafe | column_policy }} Correct

  • {{ column_policy | my_col }} Incorrect

  • {{ my_col | column_policy | sqlsafe }} Incorrect : column_policy sera vérifié par rapport à la valeur de my_col en tant que chaîne, ce qui est une erreur.

Appliquer les politiques des clean rooms

Les salles blanches ne vérifient pas automatiquement les politiques de salle blanche par rapport aux colonnes utilisées dans un modèle. Si vous voulez appliquer une politique à une colonne :

  • Vous devez appliquer le Filtre de politique approprié à cette colonne du modèle. Par exemple :

FROM IDENTIFIER({{ source_table[0] }}) AS p1
JOIN IDENTIFIER({{ source_table[1] }}) AS p2
  ON IDENTIFIER({{ p1_join_col | join_policy }}) = IDENTIFIER({{ p2_join_col | join_policy }})

Les politiques sont vérifiées uniquement par rapport aux colonnes des tables référencées dans une variable source_table, qui se réfèrent aux vues partagées au sein de la salle blanche. Les politiques ne sont pas vérifiées par rapport aux colonnes des tables référencées dans une variable my_table, qui sont des tables locales non partagées au sein de la salle blanche.

Notez que les noms des colonnes ne peuvent pas être ambigus lors du test des politiques. Ainsi, si vous avez des colonnes portant le même nom dans deux tables, vous devez qualifier le nom de la colonne afin de tester la politique par rapport à cette colonne.

Considérations et meilleures pratiques relatives à l’accès

Un modèle est toujours exécuté dans le contexte du rôle d’application de la salle blanche. Un collaborateur n’a pas d’accès direct aux données de la salle blanche qui sont limitées à l’accès aux modèles uniquement ; tout accès se fait via les rôles de l’application native et les sorties de modèle.

En tant que meilleure pratique, vous devez suivre les lignes ci-dessous pour les modèles que vous créez ou utilisez dans une salle blanche :

  • Assurez-vous qu’un filtre de politique est appliqué chaque fois qu’une variable de colonne est utilisée dans un modèle, afin que les politiques des collaborateurs soient respectées.

  • Entourez les variables fournies par l’utilisateur de IDENTIFIER() lorsque cela est possible pour renforcer vos modèles contre les attaques par injection de SQL.

Modèles d’activation

Un modèle peut également être utilisé pour enregistrer les résultats de la requête dans une table extérieure à la salle blanche. c’est ce qu’on appelle l’activation. Un modèle d’activation est un modèle d’analyse avec les exigences supplémentaires suivantes :

  • Les modèles d’activation sont des instructions JinjaSQL qui évaluent un bloc de script SQL, contrairement aux modèles d’analyse, qui peuvent être de simples instructions SELECT.

  • Les modèles d’activation doivent créer une table interne dans la salle blanche pour stocker les résultats. La table générée par le modèle doit avoir le préfixe``cleanroom.activation_data_``, par exemple : cleanroom.activation_data_my_results

  • Toutes les colonnes de la table de résultats interne doivent avoir la valeur activation_allowed: TRUE dans leur spécification d’offre de données.

  • Le bloc de script doit se terminer par une instruction RETURN qui renvoie le nom de la table générée sans le préfixe cleanroom.activation_data_, par exemple : RETURN 'my_results'.

  • Le modèle lui-même n’a aucune exigence de dénomination.

Voici un exemple de spécification du modèle d’activation :

api_version: 2.0.0
spec_type: template
name: my_activation_template
version: v0
type: sql_activation
description: Activation template that creates segment data
template: |
  BEGIN
      CREATE OR REPLACE TABLE cleanroom.activation_data_analysis_results AS
      SELECT
          {{ group_by_column | sqlsafe }} AS bucket_label,
          {{ activation_column | sqlsafe | activation_policy }} AS activation_label,
          COUNT(DISTINCT {{ join_column | sqlsafe }}) AS overlap_count
      FROM IDENTIFIER({{ source_table[0] }}) AS p
      GROUP BY {{ group_by_column | sqlsafe }},
               {{ activation_column | sqlsafe }};
      RETURN 'analysis_results';
  END;
parameters:
  - name: join_column
    description: Join column name
    required: true
    default: "p.IP_ADDRESS"
  - name: group_by_column
    description: Group by column name
    required: true
    default: "p.CAMPAIGN_NAME"
  - name: activation_column
    description: Activation column name
    required: true
    default: "p.DEVICE_TYPE"

Découvrez comment mettre en œuvre l’activation dans une collaboration : Activation des résultats de la requête.

Prochaines étapes

Une fois que vous aurez maîtrisé le système de création de modèles, lisez les détails relatifs à la mise en place d’une clean room avec votre type de modèle :

  • Les modèles d’activation créent une table de résultats après une exécution réussie et qui est partagée en dehors de la salle blanche. Selon la spécification de la collaboration, la table des résultats peut être partagée avec l’exécutant de l’analyse ou d’autres collaborateurs.

  • Les bundles de code sont utilisés pour charger des UDFs et UDTFs Python personnalisées dans une collaboration. Les modèles de la collaboration peuvent exécuter ces fonctions pour effectuer des actions de données complexes.

  • Les tables internes sont utilisés pour stocker des résultats intermédiaires ou persistants, qui peuvent être utilisés en aval pour prendre en charge des workflows à plusieurs étapes. Ces tables sont accessibles aux modèles ou au code personnalisé téléchargé dans la salle blanche.

Plus d’informations