Hiérarchie de comptes Salesforce avec des CTEs récursives

Les orgs Salesforce d’entreprise utilisent le champ auto-référentiel ParentAccountId pour modéliser les hiérarchies d’entreprise. Une filiale remonte vers une entité régionale, qui remonte vers un parent mondial. Les équipes finance ont besoin de cette hiérarchie résolue pour répondre à des questions comme « quel est notre revenu total sur l’ensemble du groupe Acme Corp ? » — une question qui nécessite de sommer les opportunités sur le compte parent et tous ses descendants.

Le ParentAccountId crée une structure arborescente dans une seule table. Chaque compte peut avoir un parent, et ce parent peut avoir son propre parent, jusqu’à une profondeur arbitraire. Résoudre cela en SQL requiert une CTE récursive.

Le pattern de CTE récursive

BigQuery supporte les CTEs récursives (depuis début 2023), ce qui rend la chose directe. Le pattern comporte deux parties : une requête d’ancrage qui trouve les comptes de premier niveau (sans parent), et une requête récursive qui descend dans l’arbre en joignant les enfants aux parents.

-- int__account_hierarchy.sql
WITH RECURSIVE

account_tree AS (
    -- Ancre : comptes de premier niveau (sans parent)
    SELECT
        account__id,
        account__name,
        account__parent_id,
        account__id AS account__ultimate_parent_id,
        account__name AS account__ultimate_parent_name,
        0 AS account__hierarchy_level
    FROM {{ ref('base__salesforce__account') }}
    WHERE account__parent_id IS NULL

    UNION ALL

    -- Récursif : comptes enfants
    SELECT
        child.account__id,
        child.account__name,
        child.account__parent_id,
        parent.account__ultimate_parent_id,
        parent.account__ultimate_parent_name,
        parent.account__hierarchy_level + 1
    FROM {{ ref('base__salesforce__account') }} AS child
    INNER JOIN account_tree AS parent
        ON child.account__parent_id = parent.account__id
)

SELECT
    account__id,
    account__name,
    account__parent_id,
    account__ultimate_parent_id,
    account__ultimate_parent_name,
    account__hierarchy_level
FROM account_tree

La sortie vous donne chaque compte avec son account__ultimate_parent_id — le sommet de sa chaîne hiérarchique — et son account__hierarchy_level (0 pour les comptes de premier niveau, 1 pour leurs enfants directs, 2 pour les petits-enfants, et ainsi de suite).

Comment fonctionne la récursion

La requête d’ancrage sélectionne tous les comptes où account__parent_id IS NULL. Ce sont les racines de l’arbre — les comptes sans parent. Pour chaque racine, son parent ultime est lui-même (d’où account__id AS account__ultimate_parent_id), et son niveau hiérarchique est 0.

La requête récursive joint base__salesforce__account (en tant que child) à l’account_tree déjà résolu (en tant que parent) sur child.account__parent_id = parent.account__id. Pour chaque enfant, il hérite du account__ultimate_parent_id du parent et incrémente le niveau hiérarchique de 1.

BigQuery exécute cela itérativement : le premier passage trouve toutes les racines, le deuxième passage trouve leurs enfants directs, le troisième passage trouve les petits-enfants, et ainsi de suite jusqu’à ce qu’aucune nouvelle ligne ne soit produite. Pour la plupart des orgs Salesforce, les hiérarchies font 3 à 5 niveaux de profondeur, donc la récursion se termine rapidement.

Rollup des revenus

Le cas d’usage principal pour la hiérarchie résolue est le rollup des revenus vers le parent ultime. Les équipes finance ont besoin de savoir qu’« Acme Corp » a 5 M$ de pipeline total sur Acme Corp, Acme Europe, Acme Japan et Acme Australia — même si chaque filiale a son propre enregistrement Account et ses propres Opportunities.

-- mrt__sales__account_family_revenue.sql
WITH

hierarchy AS (
    SELECT
        account__id,
        account__ultimate_parent_id,
        account__ultimate_parent_name
    FROM {{ ref('int__account_hierarchy') }}
),

opportunities AS (
    SELECT
        opportunity__account_id,
        opportunity__amount,
        opportunity__is_won,
        opportunity__is_closed
    FROM {{ ref('base__salesforce__opportunity') }}
)

SELECT
    hierarchy.account__ultimate_parent_id,
    hierarchy.account__ultimate_parent_name,
    SUM(opportunities.opportunity__amount) AS family__total_pipeline,
    SUM(CASE WHEN opportunities.opportunity__is_won
        THEN opportunities.opportunity__amount ELSE 0
    END) AS family__won_revenue,
    COUNT(DISTINCT hierarchy.account__id) AS family__account_count
FROM hierarchy
INNER JOIN opportunities
    ON hierarchy.account__id = opportunities.opportunity__account_id
GROUP BY 1, 2

Cela produit une ligne par groupe familial d’entreprises avec le pipeline total et les revenus remportés sommés sur toutes les filiales.

Cas limites

Références circulaires

Salesforce n’impose pas l’acyclicité dans ParentAccountId. Un admin peu attentif ou une importation de données peut créer des références circulaires : le parent du compte A est le compte B, et le parent du compte B est le compte A. Les CTEs récursives dans BigQuery atteindront la limite d’itération (500 par défaut) et échoueront.

Protégez-vous avec une limite d’itération dans votre modèle :

-- Ajouter une vérification de sécurité dans la requête récursive
WHERE parent.account__hierarchy_level < 20  -- aucune vraie hiérarchie n'a 20 niveaux

Si ce filtre supprime jamais des lignes, investiguez les données source. Une hiérarchie de plus de 10 niveaux est inhabituelle ; au-delà de 20, cela indique presque certainement de mauvaises données.

Comptes orphelins

Les comptes dont le account__parent_id référence un parent qui n’existe pas dans la table Account (parce qu’il a été supprimé, par exemple) n’apparaîtront pas dans la sortie de la CTE récursive. La jointure INNER vers account_tree exige que le parent existe. Ces comptes orphelins disparaissent silencieusement de votre modèle de hiérarchie.

Gérez cela en exécutant un test de qualité de données :

models:
  - name: int__account_hierarchy
    tests:
      - dbt_utils.equal_rowcount:
          compare_model: ref('base__salesforce__account')

Si le modèle de hiérarchie a moins de lignes que le modèle de base, vous avez des comptes orphelins qui nécessitent une investigation.

Comptes sans parents qui devraient en avoir

Tous les comptes sans parent ne sont pas de vraies entités de premier niveau. Certains comptes n’ont simplement pas été liés à leur parent par l’équipe commerciale. Le modèle de hiérarchie les traite comme des comptes de premier niveau indépendants, ce qui gonfle votre nombre de « groupes d’entreprises » et sous-estime les revenus pour les groupes ayant des filiales non liées.

C’est un problème de qualité de données, pas de modélisation. Signalez les comptes sans parent présentant des caractéristiques suggérant qu’ils devraient être liés (par exemple, noms similaires, même domaine, même adresse de facturation) et routez-les vers votre admin Salesforce pour nettoyage.

Matérialisation

Ce modèle doit être matérialisé en table, pas en vue. Les CTEs récursives sont coûteuses en compute, et la hiérarchie change peu fréquemment (les comptes se reparentent rarement). Reconstruire la table une fois par exécution dbt est bien moins coûteux que d’exécuter la requête récursive à chaque fois qu’un modèle en aval la lit.

Si votre table Account est très grande (100 000+ comptes), la matérialisation incrémentale est possible, mais détecter quels chemins de hiérarchie ont changé est complexe. Le rebuild complet une fois par exécution est l’approche plus simple pour la plupart des orgs.

Comparaison HubSpot

HubSpot n’a pas de concept natif de hiérarchie de comptes. Le modèle d’associations supporte les relations parent-enfant entre entreprises via les associations, mais il n’y a pas de champ ParentCompanyId ni de structure hiérarchique intégrée. Construire des rollups hiérarchiques à partir des données d’association HubSpot est plus complexe que le pattern auto-référentiel Salesforce.