Résolution des relations polymorphiques Salesforce

Les relations polymorphiques Salesforce sont des clés étrangères qui peuvent pointer vers différents types d’objets selon l’enregistrement. Il n’y a pas de contrainte de clé étrangère indiquant quelle table joindre — une seule colonne peut référencer un Contact dans une ligne et un Lead dans une autre. C’est ainsi que Salesforce modélise les activités pouvant se rapporter à n’importe quelle entité.

Où apparaissent les champs polymorphiques

Les deux champs polymorphiques les plus courants se trouvent sur Tasks et Events :

• WhoId — référence une personne. Peut pointer vers un Contact (préfixe d’ID 003) ou un Lead (00Q).
• WhatId — référence une chose. Peut pointer vers un Account (001), une Opportunity (006), un Case (500) ou un objet personnalisé.

Ces champs existent parce qu’une seule Task — disons, un appel téléphonique — peut être journalisée contre un Contact et liée à une Opportunity. Le WhoId vous dit avec qui était l’appel, et le WhatId vous dit sur quoi il portait. Les deux champs peuvent contenir des IDs de différentes tables, et la seule façon de savoir laquelle est de décoder le préfixe de l’ID.

Les lookups polymorphiques apparaissent également sur les objets personnalisés quand un admin Salesforce configure un champ comme type « Polymorphic Lookup ». Tout objet personnalisé avec un champ de type WhoId suit le même pattern de routage par préfixe.

Le système de préfixes d’ID

Les IDs Salesforce sont des chaînes de 18 caractères (ou 15 dans les contextes hérités). Les trois premiers caractères identifient le type d’objet. Ces préfixes sont déterministes et globalement cohérents dans toutes les orgs Salesforce :

Les objets personnalisés obtiennent leurs propres préfixes, assignés à leur création. Vous pouvez les trouver dans les paramètres Salesforce dans la définition de l’objet personnalisé, ou en interrogeant l’objet EntityDefinition via l’API.

Le pattern de résolution

L’approche standard est une instruction CASE sur LEFT(id, 3) qui mappe les préfixes aux noms de types d’objets. Cela va dans un modèle intermédiaire, pas dans un modèle de base — c’est de la logique métier (interpréter ce que signifie l’ID), qui appartient à la couche intermédiaire.

-- int__salesforce_activity_resolved.sql
WITH

activities AS (
    SELECT
        activity__id,
        activity__who_id,
        activity__what_id,
        activity__subject,
        activity__type,
        activity__occurred_at
    FROM {{ ref('base__salesforce__task') }}
)

SELECT
    activity__id,
    activity__subject,
    activity__type,
    activity__occurred_at,
    CASE LEFT(activity__who_id, 3)
        WHEN '003' THEN 'Contact'
        WHEN '00Q' THEN 'Lead'
    END AS activity__who_type,
    CASE LEFT(activity__what_id, 3)
        WHEN '001' THEN 'Account'
        WHEN '006' THEN 'Opportunity'
        WHEN '500' THEN 'Case'
    END AS activity__what_type,
    activity__who_id,
    activity__what_id
FROM activities

Les colonnes activity__who_type et activity__what_type indiquent maintenant aux modèles en aval contre quelle table effectuer la jointure. Sans cette étape de résolution, vous devriez tenter des LEFT JOINs contre chaque table potentiellement référencée et prendre celle qui correspond — cela fonctionne mais est coûteux et peu clair.

Jointure vers les tables référencées

Une fois le type résolu, joignez vers chaque table référencée dans des CTEs séparées selon le type résolu :

WITH

resolved AS (
    SELECT * FROM {{ ref('int__salesforce_activity_resolved') }}
),

contacts AS (
    SELECT contact__id, contact__full_name, contact__email
    FROM {{ ref('base__salesforce__contact') }}
),

leads AS (
    SELECT lead__id, lead__full_name, lead__email
    FROM {{ ref('base__salesforce__lead') }}
)

SELECT
    resolved.activity__id,
    resolved.activity__subject,
    resolved.activity__who_type,
    COALESCE(contacts.contact__full_name, leads.lead__full_name) AS activity__who_name,
    COALESCE(contacts.contact__email, leads.lead__email) AS activity__who_email
FROM resolved
LEFT JOIN contacts
    ON resolved.activity__who_type = 'Contact'
    AND resolved.activity__who_id = contacts.contact__id
LEFT JOIN leads
    ON resolved.activity__who_type = 'Lead'
    AND resolved.activity__who_id = leads.lead__id

Le détail clé : la condition de jointure inclut la vérification du type (resolved.activity__who_type = 'Contact'). Cela assure que vous ne tentez de joindre que contre la table correcte pour chaque ligne. Sans le filtre de type dans la condition de jointure, vous obtiendriez des correspondances parasites quand des IDs entrent en collision entre les tables (rare mais possible avec les objets personnalisés).

Rendre cela réutilisable

Si votre org Salesforce a de nombreux objets avec des champs polymorphiques, envisagez une macro dbt qui génère l’instruction CASE à partir d’une liste de configuration :

{% macro resolve_salesforce_polymorphic(column_name, mappings) %}
CASE LEFT({{ column_name }}, 3)
    {% for prefix, object_type in mappings.items() %}
    WHEN '{{ prefix }}' THEN '{{ object_type }}'
    {% endfor %}
END
{% endmacro %}

Appelée ainsi :

{{ resolve_salesforce_polymorphic('activity__who_id', {'003': 'Contact', '00Q': 'Lead'}) }}
    AS activity__who_type

Cela garde les mappings préfixe-vers-type en un seul endroit plutôt que dispersés dans plusieurs modèles intermédiaires. Quand votre admin Salesforce ajoute un nouvel objet personnalisé qui utilise des lookups polymorphiques, vous mettez à jour le dictionnaire de mapping de la macro une fois.

Erreurs courantes

Oublier les objets personnalisés. Les préfixes standards couvrent les objets Salesforce standards, mais si votre org a des objets personnalisés référencés par des champs polymorphiques (par exemple, un objet personnalisé Project__c lié via WhatId), l’instruction CASE retournera NULL pour ces lignes. Vérifiez toujours ce que votre org a réellement dans WhatId avant de supposer que la liste standard de préfixes est complète. Une requête rapide SELECT DISTINCT LEFT(what_id, 3) sur vos données brutes révèle l’ensemble complet.

Coder en dur dans plusieurs modèles. Si la logique de résolution polymorphique est dans trois modèles intermédiaires différents, ils divergeront inévitablement. Centralisez-la — soit dans un seul modèle intermédiaire que les modèles en aval référencent, soit dans une macro.

Ignorer les IDs NULL. WhoId et WhatId sont nullable. Une Task peut exister sans WhoId (elle n’est pas associée à une personne) ou sans WhatId (elle n’est pas liée à un objet spécifique). Votre modèle de résolution doit gérer les NULL gracieusement — l’instruction CASE retourne naturellement NULL pour les entrées NULL, mais assurez-vous que les modèles en aval ne filtrent pas les enregistrements avec des types résolus NULL sauf si c’est intentionnel.

Le pattern plus large

La résolution polymorphique est centrale au pattern de timeline d’activité unifiée, où Tasks et Events sont combinés en une seule vue avec des références d’entités résolues. Elle s’applique également à tout reporting qui doit répondre à « sur quoi portait cette activité ? » — ce qui est essentiellement chaque rapport d’activité commerciale.

Le pattern n’existe pas dans le modèle de données HubSpot, qui utilise des tables d’associations explicites à la place des clés étrangères polymorphiques. L’approche HubSpot est plus verbeuse mais évite entièrement la complexité du routage par préfixe.