Cadre de décision : Partitionnement vs. Clustering dans BigQuery

Ce guide explique quand utiliser le partitionnement, le clustering, ou les deux. La décision repose sur trois facteurs : les patterns de requêtes dominants, la taille de la table, et si des opérations au niveau des partitions sont nécessaires (expiration, suppressions ciblées, estimations de coûts prévisibles). Correctement appliquée, la combinaison peut réduire les coûts BigQuery de 90 à 99 %.

L’arbre de décision

flowchart TD
    A{Table > 64 Mo ?}
    A -->|Non| B[Pas d'optimisation]
    A -->|Oui| C{80 %+ des requêtes filtrent<br>sur une colonne de date ?}
    C -->|Oui| D[Partitionner par date]
    C -->|Non| E{Filtres à haute<br>cardinalité ?}
    D --> F{Partitions > 10 Go ?}
    F -->|Oui| G[Partitionnement + Clustering]
    F -->|Non| H[Partition mensuelle<br>+ Clustering]
    E -->|Oui| I[Clustering uniquement]
    E -->|Non| J[Cas par cas]

Parcourez-le de haut en bas. La première question élimine les tables trop petites pour bénéficier d’une optimisation. La deuxième distingue les workloads centrés sur les dates (où le partitionnement excelle) des patterns d’accès multidimensionnels (où le clustering convient mieux).

Utiliser le partitionnement seul quand

Vos requêtes filtrent systématiquement sur une seule colonne de date ou horodatage — 80 % ou plus des requêtes incluent un filtre sur une plage de dates. Le partitionnement offre trois avantages que le clustering ne peut pas fournir :

Estimations de coût prévisibles avant exécution. Le partition pruning indique exactement combien d’octets seront scannés à l’avance. Les estimations du clustering ne sont finalisées qu’à la fin de la requête. Si votre équipe a besoin de connaître le coût d’une requête avant de l’exécuter, le partitionnement le garantit.

Gestion du cycle de vie des partitions. Vous pouvez définir une expiration automatique (partition_expiration_days), supprimer efficacement des plages de dates entières, ou écrire dans des partitions spécifiques sans toucher aux autres. Cela est essentiel pour les politiques de rétention des données et les workflows de conformité.

Traitement incrémental efficace. La stratégie insert_overwrite dans dbt remplace des partitions entières de manière atomique — sans comparaison au niveau des lignes, sans scan complet de la table. Cela nécessite le partitionnement.

La condition : chaque partition doit peser en moyenne au moins 10 Go. Des partitions plus petites génèrent une surcharge de métadonnées sans bénéfice proportionnel. Si votre volume de données quotidien est inférieur à 10 Go par jour, envisagez un partitionnement mensuel combiné au clustering pour une granularité infra-mensuelle.

Utiliser le clustering seul quand

Le clustering convient quand le partitionnement ne correspond pas à vos patterns d’accès ou à la taille de la table :

Table entre 64 Mo et 10 Go. Trop petite pour des partitions significatives, mais assez grande pour bénéficier du block pruning. La surcharge de métadonnées des partitions n’est pas justifiée à cette échelle, mais le clustering offre une réduction mesurable du scan.

Plusieurs colonnes de filtre sans colonne dominante. Si les requêtes filtrent sur user_id, product_id, region et status dans diverses combinaisons, le clustering gère cela. Le partitionnement ne peut traiter qu’une seule colonne.

Filtres à haute cardinalité. Des colonnes comme user_id ou session_id ont trop de valeurs distinctes pour le partitionnement — vous atteignriez immédiatement la limite de 10 000 partitions. Le clustering gère la haute cardinalité facilement car il opère au niveau des blocs.

Le partitionnement créerait des partitions trop petites. Si le volume quotidien est de 100 Mo, les partitions quotidiennes sont inutiles. Chaque partition ajoute une surcharge de métadonnées, et BigQuery doit les assembler au moment de la requête. Préférez le clustering.

Utiliser les deux quand

Combinez les deux pour les grandes tables avec des patterns de requêtes multidimensionnels :

Tables dépassant 10 Go par partition où les requêtes filtrent sur la date et d’autres dimensions. C’est le scénario le plus courant pour les tables analytiques en production.

Tables d’événements où le pattern standard est : partitionner par date de transaction/événement, clusteriser par les clés de dimension fréquemment filtrées. La partition gère la dimension temporelle ; le clustering gère le reste.

Vous avez besoin de prévisibilité des coûts et d’un filtrage granulaire. Le partitionnement fournit des estimations d’octets prévisibles sur la dimension temporelle. Le clustering offre une réduction supplémentaire du scan sur les autres colonnes de filtre.

Les seuils de taille qui comptent vraiment

La documentation Google indique que les tables ou partitions de plus de 64 Mo bénéficient du clustering, et le système Recommender suggère le partitionnement pour les tables dépassant 100 Go et le clustering pour celles dépassant 10 Go. En pratique, les seuils qui orientent les décisions réelles ressemblent à ceci :

Le seuil de 1 Go est celui à partir duquel les améliorations du clustering deviennent significatives en production. Au-delà de 100 Go, l’absence d’optimisation représente un risque de coût important — une seule requête non filtrée sur une table de 100 Go coûte 0,63 $ en on-demand ; sur une table de 10 To, 62,50 $.

Le piège des petites partitions

Un partitionnement quotidien avec 100 Mo de données par jour crée 365 petites partitions par an. Chaque partition ajoute une surcharge de métadonnées, et BigQuery doit les assembler au moment de la requête. C’est l’une des erreurs les plus courantes avec le partitionnement.

La solution : utiliser un partitionnement mensuel combiné au clustering sur la colonne de date pour une granularité infra-mensuelle. Vous bénéficiez des avantages de gestion du cycle de vie du partitionnement (expiration, opérations au niveau des partitions) sans la surcharge de centaines de petites partitions. Les requêtes filtrant par dates spécifiques bénéficient encore du clustering à l’intérieur de chaque partition mensuelle.

Valider vos choix

Mesurez avant et après chaque modification.

Avant le déploiement

Vérifiez les statistiques actuelles de la table via INFORMATION_SCHEMA :

SELECT
  table_name,
  total_rows,
  total_logical_bytes / POW(1024, 3) AS size_gb,
  ARRAY_TO_STRING(clustering_columns, ', ') AS clustering
FROM `project.dataset.INFORMATION_SCHEMA.TABLE_STORAGE`
WHERE table_name = 'your_table'

Utilisez le Recommender de BigQuery (l’icône ampoule dans la console) pour des suggestions basées sur le ML à partir des patterns de requêtes réels. Ces recommandations sont des points de départ ; elles tendent vers des seuils conservateurs.

Effectuez des dry-runs pour voir les octets scannés avant l’exécution. Notez que les dry-runs reflètent uniquement les économies de partition pruning — ils ne montrent pas les bénéfices du clustering, qui ne sont visibles qu’à l’exécution réelle.

bq query --dry_run --use_legacy_sql=false 'SELECT ...'

Après le déploiement

Surveillez les octets scannés et le temps de slot dans l’historique des requêtes BigQuery. Comparez avant/après pour des requêtes représentatives :

SELECT
  total_bytes_processed,
  total_bytes_billed,
  total_slot_ms
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE job_id = 'your-job-id'

Surveillez les avertissements de filtre de partition dans vos logs dbt. Ils indiquent des requêtes qui n’ont pas pu élaguer les partitions — signe que vos expressions de filtre peuvent contrecarrer le pruning.

Si des requêtes avec des filtres appropriés scannent plus de 5 à 10 % d’une grande table partitionnée et clusterisée, vérifiez les anti-patterns dans BigQuery Partition Pruning Patterns — les fonctions sur les colonnes de partition, les sous-requêtes dans les filtres et le mauvais ordre des colonnes de clustering sont les causes courantes.

Pièges de configuration dbt

Trois pièges spécifiques à dbt lors du travail avec le partitionnement et le clustering :

_dbt_max_partition est du SQL BigQuery, pas du Jinja. Utilisez-le directement dans votre SQL sans doubles accolades :

-- Correct
WHERE event_date >= _dbt_max_partition

-- Incorrect : échouera
WHERE event_date >= {{ _dbt_max_partition }}

Changer la colonne de partition entraîne une perte de données. Lorsque vous modifiez partition_by sur une table incrémentale existante, l’insert_overwrite de dbt supprime les partitions basées sur les valeurs de la nouvelle colonne — qui peuvent ne pas correspondre aux données existantes. Exécutez toujours --full-refresh lors de la modification de la configuration de partition.

Les incompatibilités de type de données bloquent silencieusement le pruning. Si votre colonne de partition est de type DATE mais que vous filtrez avec un littéral TIMESTAMP, le pruning ne s’applique pas. BigQuery ne vous avertira pas — la requête s’exécute, retourne des résultats corrects, et coûte 10 à 100 fois plus cher que nécessaire. Soyez explicite sur les types dans vos conditions de filtre.