ServicesÀ proposNotesContact Me contacter →
EN FR
Note

Patterns de configuration du partitionnement BigQuery

Configurations de partitionnement et clustering spécifiques par domaine pour BigQuery dans dbt -- données d'événements, marketing, SaaS multi-tenant, et patterns IoT avec justifications.

Planté
bigquerydbtdata engineeringdata modeling

Le cadre de décision détermine s’il faut partitionner, clustériser, ou les deux. Cette note couvre les implémentations spécifiques par domaine pour quatre types de données. Chaque pattern inclut un bloc de configuration dbt complet et la justification des choix de colonnes.

Données d’événements (GA4, clickstream)

Les exports GA4 créent des tables partitionnées quotidiennement (events_YYYYMMDD). Pour une référence complète sur la structure de cet export, voir Structure des données d’événements GA4. Pour les tables d’événements consolidées ou les modèles analytics personnalisés :

{{ config(
    materialized='incremental',
    partition_by={
      'field': 'event_date',
      'data_type': 'date',
      'granularity': 'day'
    },
    cluster_by=['event_name', 'user_pseudo_id', 'traffic_source'],
    require_partition_filter=true
) }}

Pourquoi ça fonctionne : Les requêtes filtrent d’abord par plage de dates (pruning de partition), puis descendent par type d’événement et utilisateur (clustering). La colonne de clustering traffic_source supporte l’analyse d’attribution sans scans supplémentaires.

require_partition_filter=true est non négociable pour les tables d’événements. Les tables GA4 croissent rapidement — un site à trafic modéré génère plusieurs Go par mois. Un scan complet accidentel d’un analyste explorant les données coûte de l’argent réel. Le filtre obligatoire rend cela impossible.

L’ordre des colonnes de clustering suit le chemin d’analyse typique : “montre-moi les événements page_view pour l’utilisateur X depuis le trafic organique.” Si votre équipe analyse principalement par utilisateur d’abord (par exemple, les charges de résolution d’identité), mettez user_pseudo_id en première position. L’ordre des colonnes doit correspondre à vos patterns de requêtes dominants, pas à une recommandation générique.

Données marketing et publicitaires

Les données Google Ads et Meta Ads arrivent typiquement avec une hiérarchie de campagne (campagne —> groupe d’annonces —> annonce) :

{{ config(
    materialized='incremental',
    partition_by={
      'field': 'report_date',
      'data_type': 'date',
      'granularity': 'day'
    },
    cluster_by=['campaign_id', 'ad_group_id', 'ad_id']
) }}

Pourquoi ça fonctionne : Les marketeurs analysent les performances dans des plages de dates, en descendant dans la hiérarchie de campagne. L’ordre des colonnes de clustering correspond au chemin de descente — les rapports au niveau campagne filtrent sur campaign_id, l’analyse au niveau groupe d’annonces ajoute ad_group_id, et les performances créatives ajoutent ad_id.

Cet ordonnancement hiérarchique est un exemple parfait de l’importance de l’ordre des colonnes de clustering. Une requête filtrant uniquement sur ad_id (en sautant campaign_id et ad_group_id) ne bénéficiera pas du clustering. Mais ce pattern de requête est rare en pratique — les analystes commencent presque toujours au niveau campagne et descendent.

Pour les rapports publicitaires multi-plateformes où vous avez unifié les données de Google Ads, Meta, LinkedIn et autres plateformes dans une seule table, envisagez d’ajouter platform comme première colonne de clustering :

cluster_by=['platform', 'campaign_id', 'ad_group_id']

Cela sert le pattern courant de filtre par plateforme d’abord (“montre-moi toutes les performances Google Ads”), et les trois colonnes restantes couvrent encore le chemin de descente.

SaaS multi-tenant

Pour les tables partagées servant plusieurs clients, la décision critique est le placement de tenant_id :

{{ config(
    materialized='incremental',
    partition_by={
      'field': 'created_date',
      'data_type': 'date',
      'granularity': 'day'
    },
    cluster_by=['tenant_id', 'user_id', 'entity_type'],
    require_partition_filter=true
) }}

Pourquoi ça fonctionne : tenant_id comme première colonne de clustering assure une isolation efficace des tenants sans nécessiter de tables séparées par client. Toutes les requêtes pour un tenant spécifique bénéficient du pruning par blocs. C’est plus pratique que le partitionnement par tenant_id (qui atteindrait rapidement la limite de 10 000 partitions avec de nombreux tenants) et plus maintenable que des tables séparées par tenant.

require_partition_filter est critique ici pour des raisons de sécurité autant que de coût. Sans lui, une requête mal écrite pourrait accidentellement scanner les données de tous les tenants — un cauchemar de conformité en plus d’un problème de coût.

Si vos tenants varient considérablement en taille (un tenant a 90 % des données), le bénéfice du clustering est inégal. Les données du grand tenant peuvent s’étendre sur de nombreux blocs, réduisant le taux de pruning. Dans les cas extrêmes, envisagez une table séparée pour les tenants les plus importants et une table partagée pour les autres.

Time-series et IoT

Pour les données de capteurs à haut volume avec des requêtes spécifiques à l’appareil :

{{ config(
    materialized='incremental',
    partition_by={
      'field': 'measurement_date',
      'data_type': 'date',
      'granularity': 'day'  -- or 'hour' for very high volume with <6 month retention
    },
    cluster_by=['sensor_id', 'device_type', 'location']
) }}

Pourquoi ça fonctionne : Les requêtes spécifiques à l’appareil dans des fenêtres temporelles sont le pattern dominant. Le clustering sur sensor_id d’abord gère la haute cardinalité des identifiants d’appareils — il peut y avoir des millions de capteurs, ce qui rend sensor_id inadapté au partitionnement (limite de 10 000 partitions) mais parfait pour le clustering.

La décision de granularité dépend du volume et de la rétention. Le partitionnement horaire est pertinent quand :

  • Le volume de données quotidien dépasse 100 Go (chaque partition horaire fait encore plusieurs Go)
  • La rétention est courte (moins de 6 mois, vous maintenant bien en dessous de la limite de 10 000 partitions)
  • Les requêtes ciblent typiquement des fenêtres temporelles étroites (quelques heures)

Pour les systèmes IoT à faible volume (moins de 10 Go/jour), le partitionnement quotidien est la valeur par défaut plus sûre. Si les partitions quotidiennes font moins de 1 Go, passez au partitionnement mensuel — le piège des petites partitions s’applique ici.

Choisir le bon pattern

Les configurations spécifiques par domaine ci-dessus suivent la même logique sous-jacente du cadre de décision :

  1. Partitionner sur la colonne temporelle qui apparaît dans 80 %+ des requêtes — généralement event_date, report_date, created_date ou measurement_date.
  2. Clustériser sur les colonnes qui apparaissent le plus dans les clauses WHERE, ordonnées de la plus fréquente à la moins fréquente.
  3. Faire correspondre l’ordre du clustering au chemin de descente naturel dans votre domaine — hiérarchie de campagne pour le marketing, tenant puis utilisateur pour le SaaS, capteur puis type d’appareil pour l’IoT.
  4. Activer require_partition_filter sur toute table susceptible de causer des accidents coûteux si scannée entièrement.

Vérifiez les patterns réels de requêtes dans INFORMATION_SCHEMA.JOBS avant d’appliquer ces patterns. La configuration appropriée dépend de la façon dont les données sont réellement interrogées, pas des hypothèses.

Pour les configurations de stratégie incrémentale qui complètent ces patterns de partitionnement (insert_overwrite, merge avec prédicats, microbatch), voir Patterns de configuration de la stratégie incrémentale dbt.