Patterns de modélisation CRM dans dbt

Le pattern base-intermédiaire-mart s’applique naturellement aux données CRM. Chaque couche a une responsabilité claire, et le domaine CRM ajoute des patterns spécifiques à chaque couche qui vont au-delà de ce que vous verriez dans la modélisation e-commerce ou analytique d’événements.

Cette note couvre les patterns d’implémentation concrets pour les données Salesforce et HubSpot. Pour les principes architecturaux généraux, consultez les notes de patterns de couche individuels. Pour les défis d’extraction qui informent ces patterns, voir Défis d’extraction des données CRM.

Couche de base : nettoyer et normaliser

Un modèle par table source, matérialisé en tant que vues. Renommez les colonnes, castez les types, filtrez les enregistrements supprimés. Pas de jointures, pas d’agrégations. La couche de base est là où vous gérez les particularités de l’extraction — suppressions logicielles, préfixes de propriétés et suffixes de champs personnalisés.

Modèle de base Salesforce

-- base__salesforce__opportunity.sql
SELECT
    id                AS opportunity__id,
    account_id        AS opportunity__account_id,
    owner_id          AS opportunity__owner_id,
    name              AS opportunity__name,
    stage_name        AS opportunity__stage,
    amount            AS opportunity__amount,
    close_date        AS opportunity__close_at,
    is_won            AS opportunity__is_won,
    is_closed         AS opportunity__is_closed,
    created_date      AS opportunity__created_at,
    system_modstamp   AS opportunity__updated_at
FROM {{ source('salesforce', 'opportunity') }}
WHERE NOT _fivetran_deleted

Patterns clés :

• Le filtre WHERE NOT _fivetran_deleted est obligatoire sur chaque modèle de base CRM
• Renommez selon la convention de nommage de votre projet — le pattern double-underscore (entity__field) rend la granularité explicite
• Conservez system_modstamp comme opportunity__updated_at — vous en aurez besoin pour les stratégies de snapshot et la logique incrémentale

Modèle de base HubSpot

Le même pattern s’applique, mais avec le renommage des colonnes property_ de HubSpot :

-- base__hubspot__contact.sql
SELECT
    id                              AS contact__id,
    property_email                  AS contact__email,
    property_firstname              AS contact__first_name,
    property_lastname               AS contact__last_name,
    property_lifecyclestage         AS contact__lifecycle_stage,
    property_hs_lead_status         AS contact__lead_status,
    property_createdate             AS contact__created_at
FROM {{ source('hubspot', 'contact') }}
WHERE NOT _fivetran_deleted

Le préfixe property_ de HubSpot est cohérent sur tous les objets, ce qui rend les patterns de modèles de base prévisibles. Le préfixe existe parce que l’API HubSpot distingue les champs système (comme id) des propriétés définies par l’utilisateur.

Modèles de base pour les tables de liaison (HubSpot)

Pour les tables d’association de HubSpot, créez des modèles de base qui normalisent les données de liaison :

-- base__hubspot__deal_contact.sql
SELECT
    deal_id     AS deal__id,
    contact_id  AS contact__id,
    label       AS association__label,
    _fivetran_synced AS synced_at
FROM {{ source('hubspot', 'deal_contact') }}

Ces tables de liaison ont rarement des suppressions logicielles, mais vérifiez le comportement de votre outil d’extraction spécifique.

Couche intermédiaire : enrichir et joindre

C’est là que vit la logique métier. Matérialisée en tant que modèles éphémères ou vues. Les jointures, la déduplication, les fonctions fenêtre et les transformations plus complexes y ont leur place. La contrainte critique s’applique : ne réduisez jamais la granularité.

Enrichissement des opportunités Salesforce

-- int__opportunity_enriched.sql
WITH

opportunities AS (
    SELECT
        opportunity__id,
        opportunity__account_id,
        opportunity__owner_id,
        opportunity__name,
        opportunity__stage,
        opportunity__amount,
        opportunity__close_at,
        opportunity__is_won,
        opportunity__is_closed,
        opportunity__created_at
    FROM {{ ref('base__salesforce__opportunity') }}
),

accounts AS (
    SELECT
        account__id,
        account__name,
        account__industry,
        account__annual_revenue
    FROM {{ ref('base__salesforce__account') }}
)

SELECT
    opportunity__id,
    opportunity__owner_id,
    opportunity__name,
    opportunity__stage,
    opportunity__amount,
    opportunity__close_at,
    opportunity__is_won,
    opportunity__is_closed,
    opportunity__created_at,
    account__name,
    account__industry,
    account__annual_revenue
FROM opportunities
LEFT JOIN accounts
    ON opportunities.opportunity__account_id = accounts.account__id

La LEFT JOIN sur accounts est correcte parce que la relation Account-Opportunity de Salesforce est une Lookup (FK nullable), pas Master-Detail. Certaines opportunités peuvent n’avoir aucun compte associé.

Un modèle intermédiaire devrait idéalement alimenter un seul modèle de mart en aval. Si le même enrichissement sert plusieurs marts, c’est acceptable — mais si vous construisez des modèles intermédiaires “fourre-tout” avec toutes les jointures possibles, divisez-les en modèles d’entités ciblés.

Couche mart : entités prêtes pour les métiers

Entités prêtes pour les métiers à une granularité spécifique. Matérialisées en tant que tables ou modèles incrémentaux. Ce sont les données que votre outil BI interroge.

-- mrt__sales__opportunity_enhanced.sql
WITH

enriched AS (
    SELECT
        opportunity__id,
        opportunity__owner_id,
        opportunity__name,
        opportunity__stage,
        opportunity__amount,
        opportunity__close_at,
        opportunity__is_won,
        opportunity__is_closed,
        opportunity__created_at,
        account__name,
        account__industry
    FROM {{ ref('int__opportunity_enriched') }}
),

owners AS (
    SELECT
        user__id,
        user__full_name
    FROM {{ ref('base__salesforce__user') }}
)

SELECT
    enriched.opportunity__id,
    enriched.opportunity__name,
    enriched.opportunity__stage,
    enriched.opportunity__amount,
    enriched.opportunity__close_at,
    enriched.opportunity__is_won,
    enriched.opportunity__is_closed,
    enriched.account__name,
    enriched.account__industry,
    owners.user__full_name AS opportunity__owner_name,
    DATE_DIFF(
        COALESCE(enriched.opportunity__close_at, CURRENT_DATE()),
        enriched.opportunity__created_at,
        DAY
    ) AS opportunity__days_open
FROM enriched
LEFT JOIN owners
    ON enriched.opportunity__owner_id = owners.user__id

Le calcul opportunity__days_open est un bon exemple d’enrichissement au niveau mart — c’est une métrique pour les tableaux de bord, pas de la logique métier réutilisable. Si plusieurs marts en avaient besoin, elle appartiendrait à la couche intermédiaire.

Progression de la matérialisation

Commencez par des vues, promouvez en tables quand les requêtes deviennent lentes, puis promouvez en modèles incrémentaux quand les reconstructions complètes de tables deviennent coûteuses. Pour les données CRM, cette progression arrive souvent plus rapidement qu’avec les données d’événements parce que les tables CRM croissent par mutabilité (plus de colonnes, plus d’historique) plutôt que par pur volume.

Le point de basculement pour les tables CRM est typiquement les tables d’activité et d’historique — historique des opportunités, chronologies d’activité, journaux d’engagement — qui peuvent atteindre des millions de lignes.

Stratégies incrémentales pour le CRM sur BigQuery

Les tables de faits CRM grossissent rapidement. La bonne stratégie incrémentale fait une vraie différence en termes de coût et de temps de build.

Merge (par défaut dans dbt) nécessite un unique_key. Il scanne à la fois les tables source et destination, ce qui devient coûteux à mesure que les tables grossissent. Meilleur pour les tables de dimension avec des mises à jour fréquentes — les tables d’entités CRM principales (Account, Contact, Opportunity) où les enregistrements individuels changent.

Insert_overwrite (optimisé pour BigQuery) remplace des partitions entières sans scan complet de table. Avec copy_partitions: true, BigQuery effectue un remplacement de partition à coût zéro. Meilleur pour les tables d’activité et d’événements CRM où les données arrivent en lots bornés dans le temps.

Pour les données CRM sur BigQuery, partitionnez par champs de date (opportunity__created_at, opportunity__close_at) et clusterisez par colonnes fréquemment filtrées (opportunity__account_id, opportunity__owner_id, opportunity__stage). Le clustering peut réduire les données scannées de 88%+ pour les modèles incrémentaux.

{{
    config(
        materialized='incremental',
        incremental_strategy='insert_overwrite',
        partition_by={
            'field': 'activity__occurred_at',
            'data_type': 'date',
            'granularity': 'day'
        },
        cluster_by=['activity__account_id', 'activity__owner_id']
    )
}}

La stratégie insert_overwrite seule peut réduire les coûts de 96 % sur les grandes tables d’événements comparé à merge. Cela est particulièrement pertinent pour les données d’activité CRM où vous traitez des journaux d’engagement, des ouvertures d’emails, des journaux d’appels et des achèvements de tâches qui s’accumulent rapidement.

La question de conception de schéma

Pour structurer les modèles de mart CRM finaux — star schema séparé par entité vs tables larges dénormalisées — voir Star Schema vs One Big Table. La réponse pratique pour le CRM : modèles intermédiaires séparés par entité pour la réutilisabilité, modèles mart larges pré-jointurés pour les performances BI. Les packages dbt Fivetran suivent ce pattern avec des modèles comme salesforce__opportunity_enhanced.