dbt-utils generate_surrogate_key

generate_surrogate_key crée une clé de substitution basée sur un hash à partir d’une ou plusieurs colonnes métier. C’est l’une des macros les plus utilisées de l’écosystème dbt. Il existe un piège lors de la migration — la gestion des nulls a changé entre l’ancienne macro surrogate_key() et generate_surrogate_key() — qui peut casser les modèles incrémentaux et les snapshots lors des mises à niveau.

Ce qu’elle fait

La macro concatène les colonnes spécifiées, ajoute une gestion des nulls et hache le résultat avec MD5 :

SELECT
  {{ dbt_utils.generate_surrogate_key(['order__id', 'order__line_number']) }} AS order_line__surrogate_key,
  order__id,
  order__line_number,
  product__id,
  line__quantity,
  line__price
FROM {{ ref('base__shopify__order_lines') }}

Le SQL généré sur BigQuery ressemble approximativement à :

TO_HEX(MD5(CONCAT(
  COALESCE(CAST(order__id AS STRING), ''),
  '-',
  COALESCE(CAST(order__line_number AS STRING), '')
)))

Le séparateur ('-') empêche deux colonnes séparées de produire le même hash qu’une seule colonne combinée. Sans séparateur, order__id = '12' et order__line_number = '3' produiraient le même hash que order__id = '1' et order__line_number = '23'.

Pourquoi passer une liste

L’entrée est toujours une liste de noms de colonnes sous forme de chaînes. Les colonnes sont castées en string avant la concaténation, vous pouvez donc passer des colonnes de type entier, date ou tout autre type. Les valeurs nulles dans toute colonne sont traitées comme des chaînes vides par défaut — voir plus bas.

Pour les clés composites (plusieurs colonnes définissant la granularité), listez-les toutes :

{{ dbt_utils.generate_surrogate_key(['customer__id', 'product__sku', 'order__date']) }}

Pour une clé sur une seule colonne, la liste contient tout de même un seul élément :

{{ dbt_utils.generate_surrogate_key(['order__id']) }}

Le piège de la gestion des nulls

C’est là que les projets rencontrent des problèmes. Il existe deux comportements pour les valeurs nulles, contrôlés par une variable dbt :

Comportement actuel (par défaut en v1.x) : Les valeurs nulles sont castées en chaînes vides. order__id = NULL contribue '' à la chaîne concaténée avant le hachage.

Comportement hérité (pré-v1.0) : Les valeurs nulles étaient traitées différemment par l’ancienne macro surrogate_key(). Le comportement exact était incohérent et considéré comme un bug.

Lorsque vous migrez de l’ancienne surrogate_key() vers generate_surrogate_key(), les valeurs de hash changent pour toute ligne où l’une des colonnes clé est null. Le hash calculé par generate_surrogate_key() sera différent de celui calculé par l’ancienne macro pour ces lignes.

Pourquoi cela casse les modèles incrémentaux

Les modèles incrémentaux utilisent les clés de substitution comme unique_key pour les opérations de merge. La logique de merge compare les lignes entrantes avec la table existante en utilisant la clé de substitution pour décider d’effectuer une mise à jour ou une insertion.

Si vous migrez la macro et régénérez les clés de substitution, chaque ligne avec un null dans n’importe quelle colonne clé obtient un nouveau hash. Le modèle incrémental voit ces lignes comme nouvelles (aucune clé correspondante dans la table existante) et les insère comme doublons. Votre table a maintenant deux versions de la même entité réelle — une avec l’ancien hash, une avec le nouveau. L’ancienne ligne n’est jamais mise à jour car rien ne lui correspond.

Les snapshots dbt sont affectés de la même manière : le snapshot stocke la clé de substitution utilisée pour suivre les versions d’enregistrement. Un changement de hash brise la chaîne historique.

Comment migrer en toute sécurité

Si vous avez des modèles incrémentaux ou des snapshots existants qui utilisaient l’ancienne macro surrogate_key(), ajoutez ceci à votre dbt_project.yml avant de passer à generate_surrogate_key() :

vars:
  surrogate_key_treat_nulls_as_empty_strings: true

Cela indique à generate_surrogate_key() d’utiliser le même traitement des nulls que la macro héritée, produisant des hashs identiques pour les mêmes données d’entrée. C’est un shim de compatibilité qui vous permet de mettre à niveau la macro sans toucher à vos données.

Si vous démarrez un nouveau projet ou si vous êtes prêt à effectuer un full refresh pour reconstruire tous les modèles incrémentaux et snapshots affectés, vous n’avez pas besoin de la variable — le comportement par défaut est correct.

Quand utiliser les clés de substitution

Utilisez generate_surrogate_key lorsque :

• Votre granularité est définie par plusieurs colonnes métier et vous avez besoin d’une seule clé primaire (courant pour les tables de faits : order__id + order__line_number)
• Vous chargez depuis une source qui ne fournit pas de clé unique naturelle
• Vous avez besoin d’un identifiant stable qui survit aux changements de clés en amont

Ne l’utilisez pas comme substitut pour corriger des problèmes de qualité des données. Si votre source a des enregistrements dupliqués, déduplication d’abord (voir Générateurs SQL de dbt-utils) et génèrez ensuite la clé de substitution sur les données propres. Une clé de substitution sur une ligne dupliquée vous donne juste un identifiant unique par doublon, ce qui ne résout rien.

Vérifier vos clés

Combinez generate_surrogate_key avec un test unique sur la colonne de sortie et un test not_null sur chaque colonne d’entrée :

models:
  - name: fct__shopify__order_lines
    columns:
      - name: order_line__surrogate_key
        data_tests:
          - unique
          - not_null
      - name: order__id
        data_tests:
          - not_null
      - name: order__line_number
        data_tests:
          - not_null

Les tests not_null sur les colonnes d’entrée importent car les nulls dans les colonnes clé sont toujours suspects — soit les données n’ont vraiment pas de valeur pour cette colonne (un problème de modélisation) soit quelque chose en amont a cassé (un problème de pipeline). Dans les deux cas, un hash qui inclut silencieusement un null est pire qu’un échec de test qui rend le problème visible.