SCD Type 2 avec les snapshots dbt

Le SCD Type 2 (Slowly Changing Dimension Type 2) préserve chaque version d’un enregistrement en stockant une ligne par version d’enregistrement, chacune avec des horodatages de validité. Cela permet des requêtes à un instant précis et l’analyse historique de l’évolution des enregistrements dans le temps. dbt implémente le SCD Type 2 via sa fonctionnalité de snapshot.

Fonctionnement des snapshots dbt

Un snapshot dbt surveille une table source et, à chaque exécution, détecte les enregistrements qui ont changé. Pour les enregistrements modifiés, il clôture la version précédente (définit une date de fin) et insère la nouvelle version. Le résultat est une table d’historique avec trois colonnes supplémentaires :

• dbt_valid_from — Quand cette version est devenue active
• dbt_valid_to — Quand cette version a été remplacée (NULL pour la version courante)
• dbt_is_deleted — Si l’enregistrement source a été supprimé

Pour obtenir l’état actuel d’un enregistrement, filtrez sur dbt_valid_to IS NULL. Pour voir à quoi ressemblait un enregistrement à une date spécifique, filtrez sur dbt_valid_from <= target_date AND (dbt_valid_to > target_date OR dbt_valid_to IS NULL).

Depuis dbt 1.9+, les snapshots peuvent être définis en YAML plutôt qu’en SQL, en suivant la convention de nommage snap__[entity] :

snapshots:
  - name: snap__salesforce__opportunity
    relation: source('salesforce', 'opportunity')
    config:
      strategy: timestamp
      updated_at: system_modstamp
      unique_key: id

Stratégie timestamp

La stratégie timestamp utilise le champ updated_at d’un enregistrement pour détecter les modifications. Quand l’horodatage change entre deux exécutions de snapshot, dbt sait que l’enregistrement a été modifié.

config:
  strategy: timestamp
  updated_at: system_modstamp
  unique_key: id

Avantages :

• Rapide — compare une seule colonne, pas la ligne entière
• Simple à comprendre
• Fonctionne bien quand la source met à jour l’horodatage de manière fiable à chaque changement

Limites :

• Manque les changements qui ne mettent pas à jour l’horodatage. Dans Salesforce, [[fr/defis-extraction-donnees-crm|les changements de champs de formule ne mettent pas à jour SystemModStamp]]. Si un champ de formule est recalculé, l’horodatage reste identique et votre snapshot manque le changement complètement.
• Capture uniquement l’état au moment de l’exécution du snapshot. Si un enregistrement change trois fois entre des exécutions quotidiennes, vous obtenez une version, pas trois.

Pour Salesforce, la stratégie timestamp avec system_modstamp est l’approche standard pour la plupart des objets. Elle est rapide et fiable pour les changements de champs directs. Combinez-la avec le recalcul des champs de formule dans dbt si vous avez besoin d’un suivi précis des champs de formule.

Stratégie check

La stratégie check compare les valeurs réelles des colonnes entre les exécutions. Au lieu de regarder un horodatage, elle vérifie si des colonnes spécifiques ont des valeurs différentes de celles du snapshot précédent.

config:
  strategy: check
  check_cols:
    - stage_name
    - amount
    - close_date
    - owner_id
  unique_key: id

Vous pouvez utiliser check_cols: all pour surveiller chaque colonne, mais c’est lent sur les tables larges.

Avantages :

• Capture les changements qui ne mettent pas à jour les horodatages, y compris les changements de champs de formule
• Plus précis pour enregistrer chaque changement d’état significatif

Limites :

• Plus lent, surtout sur les tables larges — compare plusieurs valeurs de colonnes par ligne
• Capture toujours l’état au moment de l’exécution
• check_cols: all peut être coûteux et capture des changements non pertinents (horodatages de métadonnées, colonnes de synchronisation)

Quand utiliser check plutôt que timestamp : Quand les colonnes que vous suivez ne mettent pas à jour de manière fiable un champ horodatage. Les champs de formule Salesforce sont le cas classique. Si vous n’avez besoin de suivre que la progression des étapes et les changements de montant, listez ces colonnes spécifiques dans check_cols plutôt que d’utiliser all.

Fréquence des snapshots et ses conséquences

Les snapshots capturent l’état au moment de l’exécution. Si vous exécutez des snapshots quotidiennement à 2h et qu’un enregistrement change à 9h, change à nouveau à 13h, et encore à 17h, votre prochain snapshot à 2h le lendemain ne capture que l’état de 17h. Les versions de 9h et 13h sont perdues.

Cela importe surtout pour les données CRM où les changements intraday sont fréquents :

• Les opportunités peuvent changer d’étape plusieurs fois dans une journée lors de cycles de vente actifs
• Les informations de contact sont mises à jour pendant les heures ouvrées
• Les montants des deals sont révisés au fur et à mesure des négociations

Pour la plupart des cas d’usage reporting, des snapshots quotidiens sont suffisants. Vous avez besoin d’une précision à l’instant précis aux frontières journalières, pas d’un suivi minute par minute. Si vous avez vraiment besoin d’une granularité plus fine, envisagez le History Mode Fivetran ou des snapshots plus fréquents (mais ayez conscience du coût de performance).

Le History Mode Fivetran

Fivetran propose une alternative aux snapshots dbt avec le History Mode. Au lieu de capturer l’état au moment de l’exécution du snapshot, le History Mode enregistre chaque version d’un enregistrement telle que Fivetran l’observe lors des synchronisations.

Le History Mode ajoute des colonnes à la table synchronisée :

• _fivetran_start — Quand cette version a été observée pour la première fois
• _fivetran_end — Quand cette version a été remplacée
• _fivetran_active — Si c’est la version courante

Le package dbt_salesforce prend en charge le History Mode avec des modèles d’historique quotidiens dédiés pour les comptes, contacts et opportunités :

vars:
  salesforce__account_history_enabled: true
  salesforce__opportunity_history_enabled: true
  global_history_start_date: '2024-01-01'

History Mode Fivetran vs snapshots dbt

La différence clé : le History Mode Fivetran capture les changements au fur et à mesure lors des synchronisations, tandis que les snapshots dbt ne voient l’état qu’au moment de l’exécution du snapshot.

Si vous synchronisez Salesforce toutes les 15 minutes avec le History Mode activé, vous capturez les changements à une granularité de 15 minutes. Si un enregistrement change deux fois entre deux synchronisations Fivetran, vous manquez quand même l’état intermédiaire — mais la granularité est bien plus fine que des snapshots dbt quotidiens.

Quand utiliser l’un ou l’autre :

• Utilisez les snapshots dbt quand vous voulez un contrôle total, quand vous suivez des champs de formule avec la stratégie check, ou quand vous n’utilisez pas Fivetran
• Utilisez le History Mode Fivetran quand vous voulez un suivi plus granulaire des changements correspondant à votre fréquence de synchronisation, particulièrement pour les objets à haute vélocité comme les opportunités

Vous pouvez utiliser les deux simultanément. Le History Mode capture les changements fréquents à la granularité de synchronisation, tandis que les snapshots dbt peuvent suivre les changements de champs de formule que le History Mode manque.

Interroger les données de snapshot

Les requêtes à un instant précis suivent un pattern standard. Pour reconstruire l’état des opportunités à une date spécifique :

SELECT
    id AS opportunity__id,
    stage_name AS opportunity__stage,
    amount AS opportunity__amount,
    dbt_valid_from,
    dbt_valid_to
FROM {{ ref('snap__salesforce__opportunity') }}
WHERE dbt_valid_from <= '2025-06-30'
  AND (dbt_valid_to > '2025-06-30' OR dbt_valid_to IS NULL)

Pour l’analyse de durée par étape — combien de temps les opportunités passent dans chaque étape — utilisez des fonctions de fenêtre pour comparer des versions consécutives :

WITH versioned AS (
    SELECT
        id AS opportunity__id,
        stage_name AS opportunity__stage,
        dbt_valid_from AS version__started_at,
        COALESCE(dbt_valid_to, CURRENT_TIMESTAMP()) AS version__ended_at
    FROM {{ ref('snap__salesforce__opportunity') }}
)

SELECT
    opportunity__id,
    opportunity__stage,
    DATE_DIFF(version__ended_at, version__started_at, DAY) AS days_in_stage
FROM versioned
ORDER BY opportunity__id, version__started_at

Ce pattern répond à des questions comme « combien de temps les deals passent-ils en négociation avant de se conclure ? »

Considérations pratiques

Croissance du stockage. Les tables de snapshot grossissent à chaque changement. Une org Salesforce avec 50 000 opportunités qui connaît en moyenne 5 changements d’étape par opportunité produit 250 000 lignes de snapshot. Sur des années, ces tables deviennent volumineuses. Envisagez de partitionner les tables de snapshot par dbt_valid_from et d’appliquer des politiques de rétention pour les versions très anciennes qui ne servent plus aux besoins de reporting.

Snapshot initial. La première exécution de snapshot capture l’état actuel de chaque enregistrement comme version « initiale ». Il n’y a pas d’historique avant la première exécution. Si vous avez besoin de données historiques antérieures à l’activation des snapshots, le History Mode Fivetran peut effectuer un backfill s’il capturait déjà les changements.

Enregistrements supprimés. Quand un enregistrement source est supprimé (Fivetran marque _fivetran_deleted = TRUE), les snapshots dbt clôturent la dernière version et définissent dbt_is_deleted = TRUE. Les versions historiques restent intactes, ce qui est utile pour les pistes d’audit.

Tester les snapshots. Testez les tables de snapshot avec la même rigueur que les modèles ordinaires. unique sur (unique_key, dbt_valid_from) assure qu’il n’existe pas de versions en double. not_null sur dbt_valid_from détecte les problèmes d’initialisation. Vérifiez que dbt_valid_to IS NULL comporte exactement une ligne par clé unique (la version courante).