Comment valider les données dans BigQuery

Un guide pratique de la validation de la qualité des données dans Google BigQuery — les six contrôles dont toute table a besoin, comment les écrire sans parcourir des téraoctets, et comment en faire un contrat de données versionné exécuté selon une planification.

2026-08-29 · 9 min read

Pour valider les données dans BigQuery, écrivez chaque attente sous forme de requête d'agrégation renvoyant un nombre de violations, et exécutez le lot après chaque chargement. Les six contrôles qui attrapent l'essentiel des défaillances sont : valeurs nulles dans les colonnes obligatoires, clés en double, valeurs hors de l'ensemble autorisé, nombres hors d'une plage plausible, chaînes malformées et partitions périmées. Ce qui rend BigQuery différent, c'est que chaque contrôle coûte de l'argent : vous êtes facturé à l'octet parcouru, si bien qu'une suite de validation naïve qui lit une table de faits entière à chaque exécution devient une ligne de facture, pas seulement un problème de latence. L'essentiel du travail consiste à rendre les contrôles peu coûteux.

Pourquoi la validation sur BigQuery est d'abord un problème de coût

BigQuery n'a ni index ni chemin d'accès au niveau de la ligne. Une clause WHERE sur une colonne non partitionnée ne réduit pas le volume parcouru — elle filtre après la lecture. Deux mécanismes réduisent réellement le coût :

L'élagage de partitions. Un filtre sur la colonne de partitionnement (ou sur _PARTITIONTIME / _PARTITIONDATE en partitionnement par date d'ingestion) restreint les partitions lues. C'est le grand levier.
L'élagage de colonnes. BigQuery est orienté colonnes : SELECT COUNT(*) FROM t WHERE col IS NULL ne lit donc que col, pas la ligne. N'écrivez jamais SELECT * dans un contrôle.

Un troisième levier est gratuit : INFORMATION_SCHEMA.PARTITIONS et les métadonnées de table portent le nombre de lignes et les dates de dernière modification sans parcourir la moindre donnée.

Fixez de toute façon un plafond strict. Chaque requête de validation devrait s'exécuter avec maximum_bytes_billed, de sorte qu'un filtre mal saisi fasse échouer le job au lieu de parcourir 40 To. Le reste du flux de travail — les six contrôles, la planification, le contrat qui les contient — est le même que partout ailleurs et se trouve exposé dans le guide complet de la validation des données ; ce qui est spécifique à BigQuery, c'est le coût.

Les six contrôles dont toute table a besoin

Supposons que orders soit partitionnée sur DATE(created_at) et que vous validiez le chargement du dernier jour.

1. Complétude — valeurs nulles dans les colonnes obligatoires

SELECT COUNT(*) AS violations
FROM `proj.sales.orders`
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND order_id IS NULL;

C'est le filtre de partition qui fait le vrai travail ici — sans lui, cette requête lit tous les octets de la table.

2. Unicité — clés métier en double

SELECT COUNT(*) AS violations
FROM (
    SELECT order_id
    FROM `proj.sales.orders`
    WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
      AND order_id IS NOT NULL
    GROUP BY order_id
    HAVING COUNT(*) > 1
);

BigQuery n'a pas de clés primaires qu'il fasse respecter, et les insertions en streaming sont au moins une fois. Les clés en double ne sont pas un cas limite ici — c'est le mode de défaillance attendu d'un chargement rejoué, et ce contrôle est souvent la règle à plus forte valeur de tout le contrat.

Une réserve à connaître : un contrôle de doublons limité à une partition ne verra pas une ligne dupliquée sur deux jours différents. Si vos clés doivent être uniques globalement, ce contrôle doit parcourir toute la table : exécutez-le donc quotidiennement plutôt que toutes les heures, et prévoyez le budget correspondant.

3. Conformité — valeurs hors de l'ensemble autorisé

SELECT COUNT(*) AS violations
FROM `proj.sales.orders`
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND status IS NOT NULL
  AND status NOT IN ('pending', 'paid', 'shipped', 'refunded');

NULL NOT IN (...) vaut NULL en GoogleSQL comme partout ailleurs : gardez donc la protection IS NOT NULL, sinon les valeurs nulles disparaissent du compte.

4. Exactitude — nombres hors d'une plage plausible

SELECT COUNT(*) AS violations
FROM `proj.sales.orders`
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND total_amount IS NOT NULL
  AND (total_amount < 0 OR total_amount > 100000);

Utilisez NUMERIC (ou BIGNUMERIC) pour les montants. FLOAT64 suit la norme IEEE 754 et sera en désaccord avec une borne, précisément à la limite.

5. Conformité — identifiants malformés

SELECT COUNT(*) AS violations
FROM `proj.sales.orders`
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND reference IS NOT NULL
  AND NOT REGEXP_CONTAINS(reference, r'^ORD-[0-9]{6}
#39;);

REGEXP_CONTAINS utilise RE2 : il n'y a donc ni références arrière ni assertions de contexte — mais tout le reste de ce que vous écrirez vraisemblablement fonctionne, et la garantie de temps linéaire de RE2 fait qu'un motif pathologique ne peut pas bloquer la requête.

6. Ponctualité — fraîcheur

La version économique ne lit aucune donnée de table :

SELECT COUNT(*) AS violations
FROM `proj.sales.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_name = 'orders'
  AND last_modified_time < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND partition_id = FORMAT_DATE('%Y%m%d', CURRENT_DATE());

La version au niveau des lignes — compter les lignes dont le created_at est plus ancien que le seuil — mesure quelque chose de subtilement différent : non pas « la table s'est-elle chargée », mais « les données qu'elle contient sont-elles récentes ». Les deux valent la peine. Le contrôle sur les métadonnées attrape un pipeline qui n'a pas tourné ; le contrôle sur les lignes attrape un pipeline qui a tourné et produit des lignes périmées.

Un piège propre à BigQuery : les lignes présentes dans le tampon de streaming sont interrogeables mais ne mettent pas immédiatement à jour last_modified_time, si bien qu'un contrôle de fraîcheur fondé uniquement sur les métadonnées peut accuser du retard sur une table alimentée en streaming.

Un compte de service en lecture seule

La validation a besoin de deux rôles sur le projet qui détient les données :

roles/bigquery.dataViewer — lire les données et métadonnées des tables
roles/bigquery.jobUser — exécuter des jobs de requête

Accordez dataViewer au niveau du jeu de données plutôt que du projet si vous voulez restreindre l'accès à certaines tables. Fixez ensuite le plafond de coût sur la connexion, pour qu'aucun contrôle ne puisse s'emballer :

-- Enforced per job by the client, not in SQL:
--   maximum_bytes_billed = 10_000_000_000   (10 GB)

Catalyst se connecte avec une clé de compte de service, applique un plafond d'octets facturés par contrôle, et signale un contrôle qui le dépasse comme une error plutôt qu'un fail — une limite d'infrastructure ne devrait jamais être rapportée comme un problème de données.

Du SQL ponctuel au contrat de données

Les requêtes ci-dessus sont correctes et impossibles à maintenir : le filtre de partition est copié-collé six fois, les seuils sont invisibles, et rien ne vous dit quels contrôles tournent encore. Déclarer les attentes règle cela. Catalyst utilise l'Open Data Contract Standard :

apiVersion: v3.0.0
kind: DataContract
info:
  title: orders
  version: 2.0.0
  owner: data-platform
schema:
  - name: orders
    physicalName: orders
    physicalType: table
    properties:
      - name: order_id
        logicalType: string
        required: true
        primaryKey: true
        quality:
          - rule: nullCount
            dimension: completeness
            severity: error
            mustBe: "0"
          - rule: duplicateCount
            dimension: uniqueness
            severity: error
            mustBe: "0"
      - name: status
        logicalType: string
        quality:
          - rule: validValues
            dimension: conformity
            severity: error
            mustBe: "['pending', 'paid', 'shipped', 'refunded']"
      - name: total_amount
        logicalType: number
        physicalType: NUMERIC
        quality:
          - rule: between
            dimension: accuracy
            severity: error
            mustBe: "[0, 100000]"
      - name: reference
        logicalType: string
        quality:
          - rule: regex
            dimension: conformity
            severity: warning
            mustBe: "'^ORD-[0-9]{6}#39;"
      - name: created_at
        logicalType: timestamp
        physicalType: TIMESTAMP
        quality:
          - rule: freshness
            dimension: timeliness
            severity: error
            mustBe: "<= 24h"
    quality:
      - rule: rowCount
        dimension: consistency
        severity: warning
        mustBe: "> 0"

Catalyst importe le schéma depuis INFORMATION_SCHEMA.COLUMNS, compile chaque règle en GoogleSQL, et enregistre le résultat de chaque contrôle avec un échantillon des lignes en échec. Comme le YAML fait foi et qu'il fait l'aller-retour avec le constructeur visuel sans altération, le contrat relu en pull request est exactement celui qui s'exécute.

Pièges BigQuery à connaître

Piège	Ce qui se passe	Que faire
Pas de filtre de partition	Chaque contrôle parcourt toute la table	Filtrer sur la colonne de partitionnement
`SELECT *` dans un contrôle	Lit toutes les colonnes	Ne référencer que la colonne contrôlée
Streaming « au moins une fois »	Clés en double silencieuses	Toujours inclure une règle `duplicateCount`
Tampon de streaming	La fraîcheur des métadonnées accuse du retard	Combiner fraîcheur métadonnées et niveau ligne
Montants en `FLOAT64`	Les bornes divergent à la limite	Utiliser `NUMERIC`
Expressions régulières RE2	Ni assertions de contexte ni références arrière	Réécrire le motif, ou utiliser `customSql`
Pas de clés appliquées	Les contraintes sont purement déclaratives	Valider l'unicité explicitement

Planification et alertes

Déclenchez la validation à la fin du chargement, pas à une heure donnée. Dans BigQuery, cela compte doublement : un contrôle qui s'exécute avant la fin du chargement signale un faux échec et vous facture le parcours.

Séparez la suite selon le coût. Les contrôles peu coûteux, limités à une partition, peuvent tourner à chaque chargement ; les contrôles coûteux sur la table entière — unicité globale, intégrité référentielle inter-tables — relèvent d'une planification quotidienne. Alertez sur la transition de pass à fail plutôt que sur la répétition de l'état, et gardez les contrôles de motif en gravité warning, pour qu'ils informent au lieu de réveiller quelqu'un.

Questions fréquentes

Combien coûte la validation des données dans BigQuery ?

Le coût correspond aux octets que chaque contrôle parcourt, facturés au tarif à la demande, ou au temps de slot sur une réservation. Un contrôle limité à une partition et à une seule colonne se chiffre généralement en mégaoctets ; le même contrôle sans filtre de partition peut atteindre des téraoctets. Intégrez des filtres de partition dans chaque règle, plafonnez chaque requête avec maximum_bytes_billed, et utilisez INFORMATION_SCHEMA pour tout ce qui ne demande que des métadonnées.

Puis-je valider des données BigQuery sans écrire de SQL ?

Oui. Déclarez l'attente et laissez l'outil générer le GoogleSQL. Catalyst importe les colonnes et les types depuis INFORMATION_SCHEMA, propose un ensemble de règles de base, et les compile selon le dialecte — vous n'écrivez du SQL que pour une logique réellement sur mesure, via une règle customSql.

De quelles permissions un outil de validation a-t-il besoin ?

roles/bigquery.dataViewer sur les jeux de données que vous voulez contrôler, plus roles/bigquery.jobUser sur le projet pour pouvoir exécuter des requêtes. Aucun accès en écriture n'est requis — chaque contrôle est un SELECT d'agrégation.

Pourquoi ai-je des lignes en double dans BigQuery alors qu'une clé primaire est déclarée ?

Les contraintes de clé primaire et de clé étrangère de BigQuery sont des métadonnées non appliquées, utilisées par l'optimiseur de requêtes ; elles ne rejettent pas les insertions en double. Combinées à une livraison « au moins une fois » sur l'API de streaming, elles font des doublons une issue normale d'un chargement rejoué, et non un bug rare. Une règle duplicateCount n'est pas facultative ici.

Cela fonctionne-t-il avec les vues et les tables externes BigQuery ?

Les vues se valident sans problème — le contrôle s'exécute sur le résultat de la vue. Les tables externes (Cloud Storage, Sheets, BigLake) fonctionnent aussi, mais la plupart ne bénéficient pas de l'élagage de partitions : chaque contrôle lit donc toute la source. Budgétez en conséquence, ou matérialisez d'abord la table externe.