Pipeline de résolution d'identité dbt

Cette note couvre la structure de DAG dbt en production pour la résolution d’identité GA4. Deux modèles sont impliqués : une table de mapping d’identité incrémentale (maintenue via merge) et une table d’événements réconciliés (maintenue via insert_overwrite). Les deux utilisent une fenêtre de rétroaction de 3 jours pour les données tardives.

Structure du DAG

La couche de résolution d’identité se situe entre votre source GA4 brute et vos marts de sessions/attribution :

sources/ga4/events
    │
    ├── int__ga4__identity_mapping     (incrémental, merge)
    │
    └── int__ga4__events_stitched      (incrémental, insert_overwrite)
            │
            ├── int__ga4__sessions
            │
            └── mrt__analytics__user_journey

Deux modèles, deux stratégies incrémentielles différentes, pour deux raisons différentes :

• Le mapping d’identité est petit (une ligne par device ayant jamais été authentifié) et se met à jour en fusionnant les lignes nouvelles ou mises à jour. La stratégie merge est le bon choix.
• Les événements réconciliés sont volumineux (chaque événement de votre historique) et se mettent à jour en retraitant les partitions de date récentes. insert_overwrite est le bon choix.

Le modèle de mapping d’identité

-- models/intermediate/ga4/int__ga4__identity_mapping.sql

{{
    config(
        materialized='incremental',
        unique_key='user_pseudo_id',
        incremental_strategy='merge',
        partition_by={
            'field': 'last_seen_date',
            'data_type': 'date',
            'granularity': 'day'
        },
        cluster_by=['user_pseudo_id']
    )
}}

WITH source_events AS (
    SELECT
        user_pseudo_id,
        user_id,
        event_timestamp,
        PARSE_DATE('%Y%m%d', event_date) AS event_date
    FROM {{ source('ga4', 'events') }}
    WHERE user_pseudo_id IS NOT NULL
      AND user_id IS NOT NULL
      AND user_id NOT IN ('null', 'undefined', 'none', '')
    {% if is_incremental() %}
      AND PARSE_DATE('%Y%m%d', event_date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
    {% endif %}
),

aggregated AS (
    SELECT
        user_pseudo_id,
        ARRAY_AGG(DISTINCT user_id IGNORE NULLS) AS all_user_ids,
        ARRAY_AGG(user_id ORDER BY event_timestamp DESC LIMIT 1)[SAFE_OFFSET(0)] AS latest_user_id,
        MAX(event_timestamp) AS last_seen_timestamp,
        MAX(event_date) AS last_seen_date
    FROM source_events
    GROUP BY user_pseudo_id
)

SELECT
    user_pseudo_id,
    latest_user_id AS resolved_user_id,
    all_user_ids,
    ARRAY_LENGTH(all_user_ids) AS user_id_count,
    ARRAY_LENGTH(all_user_ids) > 1 AS has_multiple_users,
    TIMESTAMP_MICROS(last_seen_timestamp) AS last_seen_at,
    last_seen_date
FROM aggregated

La fenêtre de rétroaction de 3 jours dans is_incremental() gère les événements GA4 tardifs. GA4 peut livrer des événements jusqu’à 72 heures après leur occurrence ; la fenêtre de 3 jours assure qu’ils sont capturés dans le mapping d’identité.

Le filtre user_id supprime les valeurs parasites connues avant qu’elles ne polluent le mapping. Étendez cette liste en fonction de votre propre audit user_id.

resolved_user_id prend le user_id le plus récent comme identité canonique pour le device. all_user_ids préserve l’historique complet pour l’auditabilité. has_multiple_users signale les devices partagés pour le traitement en aval.

Le modèle d’événements réconciliés

-- models/intermediate/ga4/int__ga4__events_stitched.sql

{{
    config(
        materialized='incremental',
        incremental_strategy='insert_overwrite',
        partition_by={
            'field': 'event_date',
            'data_type': 'date',
            'granularity': 'day'
        },
        cluster_by=['resolved_user_id', 'session_id']
    )
}}

WITH events AS (
    SELECT
        user_pseudo_id,
        user_id,
        event_name,
        event_timestamp,
        event_params,
        PARSE_DATE('%Y%m%d', event_date) AS event_date,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
    FROM {{ source('ga4', 'events') }}
    WHERE TRUE
    {% if is_incremental() %}
      AND PARSE_DATE('%Y%m%d', event_date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
    {% endif %}
),

identity_mapping AS (
    SELECT
        user_pseudo_id,
        resolved_user_id,
        has_multiple_users
    FROM {{ ref('int__ga4__identity_mapping') }}
)

SELECT
    e.user_pseudo_id,
    e.user_id,
    e.event_name,
    e.event_timestamp,
    e.event_date,

    -- Résolution d'identité
    COALESCE(m.resolved_user_id, e.user_pseudo_id) AS resolved_user_id,
    m.resolved_user_id IS NOT NULL AS is_identified,
    COALESCE(m.has_multiple_users, FALSE) AS is_shared_device,

    -- Identifiant de session (composite pour éviter les collisions)
    e.ga_session_id,
    CONCAT(
        COALESCE(m.resolved_user_id, e.user_pseudo_id),
        '_',
        CAST(e.ga_session_id AS STRING)
    ) AS session_id

FROM events e
LEFT JOIN identity_mapping m USING (user_pseudo_id)

Le fallback COALESCE(m.resolved_user_id, e.user_pseudo_id) garantit que chaque événement a un resolved_user_id non nul. Les événements anonymes utilisent leur ID de device comme identité plutôt que de devenir nuls — ce qui ferait apparaître tous les événements anonymes comme appartenant à un seul utilisateur.

La construction du session_id utilise l’identité réconciliée, pas le user_pseudo_id brut. Cela signifie que les sessions de la même personne sur le même device avant et après l’authentification partagent un espace de noms de session, ce qui importe pour un comptage précis des sessions.

is_shared_device expose le drapeau de device partagé pour que les marts en aval puissent traiter ces événements de manière appropriée — soit en les filtrant, soit en pondérant leur contribution plus faiblement.

Tests de schéma

Les tests pour le modèle de mapping d’identité se concentrent sur l’unicité et la garde de borne supérieure :

models:
  - name: int__ga4__identity_mapping
    columns:
      - name: user_pseudo_id
        tests:
          - unique
          - not_null
      - name: resolved_user_id
        tests:
          - not_null
      - name: user_id_count
        tests:
          - dbt_utils.accepted_range:
              min_value: 1
              max_value: 100  # Alerter si un device mappe vers un nombre implausible d'utilisateurs

  - name: int__ga4__events_stitched
    columns:
      - name: session_id
        tests:
          - not_null
      - name: resolved_user_id
        tests:
          - not_null

Le test accepted_range sur user_id_count est la garde automatisée contre les bugs de tracking qui causent une explosion d’identité. Un device mappant vers plus de 100 valeurs user_id est presque certainement un bug de null en chaîne ou un problème de compte de test, pas un vrai utilisateur. Le test qui échoue en CI détecte cela avant que cela ne corrompe votre mapping de production.

Pourquoi merge pour le mapping, insert_overwrite pour les événements

Le choix de stratégie incrémentale est important ici car les deux modèles ont des patterns de mise à jour différents.

Le mapping d’identité a user_pseudo_id comme clé unique. Quand un device qui mappait vers user_id = A le mois dernier mappe maintenant vers user_id = B (changement de compte, device partagé), vous souhaitez que la ligne soit mise à jour sur place. merge avec unique_key = 'user_pseudo_id' fait exactement cela.

La table d’événements réconciliés n’a pas de clé unique — c’est une table de faits avec des milliards de lignes. La fusion à cette échelle est prohibitivement coûteuse. Au lieu de cela, insert_overwrite remplace les partitions de date récentes en bloc. Quand de nouveaux mappings d’identité résolvent des événements qui étaient précédemment anonymes, la fenêtre de rétroaction de 3 jours retraite ces partitions avec le mapping mis à jour — corrigeant la réconciliation rétrospectivement.

C’est pourquoi le modèle de mapping d’identité doit s’exécuter avant le modèle d’événements réconciliés dans votre DAG. La dépendance ref('int__ga4__identity_mapping') applique cela dans dbt automatiquement.