ServicesÀ proposNotesContact Me contacter →
EN FR
Note

Modèle de base GA4 : shardé vers partitionné

Comment convertir l'export BigQuery date-shardé de GA4 en un modèle dbt incrémentiel correctement partitionné, et pourquoi le pattern de lookback statique est critique pour la correction.

Planté
ga4dbtbigquerydata modelingincremental processing

GA4 exporte les données vers BigQuery sous forme de tables date-shardées : events_20260101, events_20260102, etc. Ce n’est pas une table unique avec une partition de date — ce sont des tables séparées par jour, toutes référencées via un pattern wildcard (events_*). Cette distinction compte énormément pour la construction de modèles incrémentiels.

Le problème avec les patterns incrémentiels standard

L’approche incrémentielle typique interroge la table de destination pour trouver la date traitée la plus récente, puis filtre la source pour n’inclure que les enregistrements plus récents :

{% if is_incremental() %}
WHERE created_at > (SELECT MAX(created_at) FROM {{ this }})
{% endif %}

Cela échoue avec les tables shardées de GA4. Lorsque vous filtrez avec WHERE event_date > '2026-01-28' sur une table wildcard, BigQuery ne peut pas éliminer les shards basés sur ce filtre de colonne — il doit scanner toutes les tables events_* pour évaluer la clause WHERE. Un historique de 3 ans signifie scanner 1000+ shards à chaque exécution incrémentielle, quelle que soit la récence du filtre.

La solution est le lookback incrémentiel statique : filtrer par _TABLE_SUFFIX plutôt que par la colonne de date d’événement. BigQuery sait quelles tables physiques ouvrir d’après le suffixe, permettant un élagage réel de partitions avant la lecture des données.

Le pattern du modèle de base

Le modèle de base GA4 gère quatre responsabilités : conversion shardé-vers-partitionné, nettoyage, typage et extraction de paramètres. Tout cela se produit dans un seul modèle.

-- models/base/ga4/base__ga4__events.sql


{{
    config(
        materialized='incremental',
        incremental_strategy='insert_overwrite',
        partition_by={
            "field": "event__date",
            "data_type": "date",
            "granularity": "day"
        },
        cluster_by=['user__pseudo_id', 'session__key', 'event__name']
    )
}}


{% set lookback_days = var('ga4_static_incremental_days', 3) %}


WITH source AS (


    SELECT
        event_date,
        event_timestamp,
        event_name,
        event_params,
        user_pseudo_id,
        user_id,
        device,
        geo,
        ecommerce,
        items,
        collected_traffic_source,
        is_active_user
    FROM {{ source('ga4', 'events') }}
    WHERE _TABLE_SUFFIX >= '{{ var("ga4_start_date", "20230101") }}'


    {% if is_incremental() %}
        AND _TABLE_SUFFIX >= FORMAT_DATE(
            '%Y%m%d',
            DATE_SUB(CURRENT_DATE(), INTERVAL {{ lookback_days }} DAY)
        )
    {% endif %}


    -- Exclure les tables intraday
    AND _TABLE_SUFFIX NOT LIKE '%intraday%'


)

Décisions clés :

Filtre sur _TABLE_SUFFIX, pas sur event_date. Le filtre sur le suffixe indique à BigQuery exactement quelles tables physiques ouvrir. Sur un historique de 3 ans, cette différence peut représenter la lecture de 3 jours de données au lieu de 1000+ shards.

insert_overwrite avec partitionnement par date. La table de destination est correctement partitionnée par event__date. À chaque exécution incrémentielle, dbt remplace uniquement les partitions correspondant à la fenêtre de lookback. C’est atomique (pas d’état incohérent transitoire) et efficace (pas de comparaison ligne par ligne comme avec merge).

Lookback statique CURRENT_DATE() - N, pas MAX(event_date) - N. L’approche statique a un avantage subtil : elle fonctionne même si la table de destination est vide ou si les données d’aujourd’hui ne sont pas encore arrivées. L’approche dynamique (MAX(event_date) depuis la destination) peut planter dans des cas limites et déclenche un scan complet de la table de destination.

Pourquoi 3 jours ?

Les données GA4 ne sont pas finalisées lors de leur premier atterrissage. Plusieurs processus mettent à jour rétroactivement des événements déjà exportés :

  • Réconciliation des conversions Google Ads — Les conversions depuis les campagnes Ads peuvent ne pas être attribuées avant que le système Ads ait traité le clic, ce qui peut prendre 24 à 72 heures
  • Mises à jour de réconciliation inter-appareils — Lorsque le user_id d’un utilisateur est résolu entre appareils, Google peut remplir rétroactivement l’attribution de session
  • Ajustements du Consent Mode — La modélisation comportementale pour les sessions consenties se met à jour à mesure que davantage de données s’accumulent

Un lookback de 3 jours capture la grande majorité de ces mises à jour rétroactives. Certaines équipes utilisent 4 à 5 jours pour les campagnes payantes où l’exactitude de l’attribution est critique pour le reporting.

L’exclusion des tables intraday

GA4 exporte des tables intraday (events_intraday_YYYYMMDD) en continu tout au long de la journée, puis les consolide dans une table quotidienne finale le lendemain. Les tables intraday sont utiles pour le reporting quasi-temps-réel mais créent un problème pour les modèles incrémentiels : si vous traitez une table intraday et que la table quotidienne finale arrive avec des données différentes, vous aurez des incohérences.

Exclure les tables intraday avec AND _TABLE_SUFFIX NOT LIKE '%intraday%' maintient le modèle simple et cohérent. Acceptez le décalage de 24 heures en échange de données fiables.

Convention de nommage des colonnes

Le modèle de base établit la convention de nommage pour l’ensemble du projet. L’utilisation du pattern double underscore (entité__attribut) rend le modèle de données auto-documenté :

  • event__date, event__timestamp_utc — attributs à portée d’événement
  • user__pseudo_id, user__id — identité utilisateur
  • session__ga_id, session__key — identité de session
  • page__location, page__title — attributs de page
  • device__category, geo__country — dimensions contextuelles

Cette convention s’adapte à l’échelle : lorsque vous voyez session__landing_page dans un modèle en aval, vous savez immédiatement que c’est un attribut à portée de session relatif à une page, calculé depuis le contexte de session — pas un champ GA4 brut.

Configuration de la source

Associez le modèle de base à une configuration de source qui exprime les attentes de fraîcheur des données :

sources:
  - name: ga4
    database: "{{ var('ga4_project_id') }}"
    schema: "{{ var('ga4_dataset') }}"


    tables:
      - name: events
        identifier: "events_*"
        freshness:
          warn_after: {count: 24, period: hour}
          error_after: {count: 48, period: hour}
        loaded_at_field: "TIMESTAMP_MICROS(event_timestamp)"

L’identifier: "events_*" informe dbt du pattern wildcard. Les vérifications de fraîcheur sur le loaded_at_field vous alerteront si le pipeline d’export de GA4 s’est arrêté — un mode de défaillance réel qui serait autrement silencieux.

Relation avec la structure du projet

Le modèle de base alimente tout le reste. Sa sortie — une table d’événements correctement partitionnée, nettoyée, typée avec tous les paramètres extraits — devient le fondement que le modèle d’événements sessionisés GA4 et le modèle d’articles au niveau article référencent tous deux. La concentration de la complexité en un seul endroit signifie que les modèles en aval restent lisibles.

C’est la philosophie de la couche base appliquée à un défi spécifique à GA4 : concentrer la complexité en un seul endroit pour que chaque modèle en aval hérite de données propres et fiables.