Comment valider les données dans PostgreSQL

Un guide pratique pour valider la qualité des données dans PostgreSQL — les six contrôles dont chaque table a besoin, le SQL pour les écrire, les pièges liés aux NULL et aux verrous, et comment en faire un contrat de données versionné.

2026-08-27 · 8 min read

Pour valider des données dans PostgreSQL, écrivez chaque attente sous la forme d'une requête d'agrégation qui renvoie un nombre de violations, exécutez-les ensemble sur la table, et déclarez l'échec dès qu'un compteur dépasse son seuil. Six contrôles couvrent l'immense majorité des incidents réels : les valeurs nulles dans les colonnes obligatoires, les clés en doublon, les valeurs hors d'un ensemble autorisé, les nombres hors d'une plage plausible, les chaînes mal formées et les lignes périmées. Postgres vous offre de vraies expressions régulières et une sémantique honnête des valeurs nulles, ce qui rend l'exercice plus simple que dans la plupart des moteurs — mais il a ses propres pièges, essentiellement autour de NULL, des statistiques et des balayages longs sur une instance primaire.

Pourquoi PostgreSQL est un bon point de départ

Postgres offre la surface de validation la plus riche parmi les entrepôts courants :

• De vraies expressions régulières. L'opérateur ~ suit la norme POSIX étendue : motifs ancrés, alternatives et quantificateurs fonctionnent tous sans traduction.
• information_schema et pg_catalog. Les types de colonnes, la nullabilité et les clés primaires s'interrogent sous une forme standard : l'import du schéma est donc exact plutôt que déduit.
• Des comptages approximatifs peu coûteux. pg_class.reltuples fournit gratuitement une estimation du planificateur lorsqu'un COUNT(*) exact revient trop cher.

Le revers de cette richesse, c'est que Postgres est généralement votre base transactionnelle primaire, pas un entrepôt. Les requêtes de validation sont des balayages complets, et les balayages complets sur une instance primaire entrent en concurrence avec votre application. Tout ce qui n'est pas spécifique à Postgres — où exécuter les contrôles, comment les planifier, quelles tables couvrir en premier — est traité dans le guide complet de la validation des données.

Les six contrôles dont chaque table a besoin

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

SELECT count(*) AS violations
FROM sales.orders
WHERE order_id IS NULL;

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

SELECT count(*) AS violations
FROM (
    SELECT order_id
    FROM sales.orders
    WHERE order_id IS NOT NULL
    GROUP BY order_id
    HAVING count(*) > 1
) dupes;

Un index unique empêcherait le problème dès l'écriture. Dans un schéma analytique alimenté par un traitement ELT, cet index n'existe généralement pas : les modèles dbt sont recréés comme de simples tables, et CREATE TABLE AS SELECT ne transporte aucune contrainte. Le contrôle de doublons est le seul rempart qui reste.

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

SELECT count(*) AS violations
FROM sales.orders
WHERE status IS NOT NULL
  AND status NOT IN ('pending', 'paid', 'shipped', 'refunded');

La garde IS NOT NULL n'est pas facultative. NULL NOT IN (...) vaut NULL, pas true : les valeurs nulles disparaissent donc du résultat, et une colonne entièrement nulle affiche une conformité parfaite. C'est de loin la cause la plus fréquente de contrôle qui passe à tort dans du SQL de qualité des données écrit à la main.

4. Exactitude — nombres hors d'une plage plausible

SELECT count(*) AS violations
FROM sales.orders
WHERE total_amount IS NOT NULL
  AND (total_amount < 0 OR total_amount > 100000);

Utilisez numeric pour les montants. Les bornes en double precision se comparent avec une erreur d'arrondi, et un contrôle écrit > 100000 finira par se contredire lui-même.

5. Conformité — identifiants mal formés

SELECT count(*) AS violations
FROM sales.orders
WHERE reference IS NOT NULL
  AND reference !~ '^ORD-[0-9]{6}
#39;;

Utilisez ~ pour une correspondance sensible à la casse et ~* pour une correspondance insensible à la casse. Contrairement à SQL Server, aucune traduction n'est nécessaire : le motif que vous écrivez est le motif qui s'exécute.

6. Actualité — fraîcheur

SELECT count(*) AS violations
FROM sales.orders
WHERE created_at < now() - interval '24 hours';

Préférez timestamptz à timestamp. Une colonne timestamp nue n'a pas de fuseau : now() se compare alors au TimeZone de la session, quel qu'il soit, et le même contrôle donne des réponses différentes selon le client.

Intégrité référentielle entre tables

Postgres est l'un des rares moteurs où le contrôle inter-tables est réellement peu coûteux, parce que le planificateur effectue une jointure par hachage plutôt qu'une sous-requête corrélée :

SELECT count(*) AS violations
FROM sales.orders o
LEFT JOIN sales.customers c ON o.customer_id = c.id
WHERE o.customer_id IS NOT NULL
  AND c.id IS NULL;

Les clés étrangères orphelines sont le symptôme classique d'un rechargement partiel — une table rechargée, sa table parente non — et elles restent invisibles jusqu'à ce qu'un tableau de bord se mette à perdre discrètement des lignes dans une jointure interne.

Créer un rôle en lecture seule sans risque

CREATE ROLE catalyst_ro LOGIN PASSWORD '<generated>';
GRANT CONNECT ON DATABASE analytics TO catalyst_ro;
GRANT USAGE ON SCHEMA sales TO catalyst_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO catalyst_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
  GRANT SELECT ON TABLES TO catalyst_ro;

La ligne ALTER DEFAULT PRIVILEGES est celle que tout le monde oublie : sans elle, chaque table que votre traitement ELT recréera demain sera invisible pour le rôle de validation, et les contrôles commenceront à échouer avec des erreurs de permissions qui ressemblent à des problèmes de données.

Protégez ensuite l'instance primaire :

ALTER ROLE catalyst_ro SET statement_timeout = '60s';
ALTER ROLE catalyst_ro SET idle_in_transaction_session_timeout = '30s';

Mieux encore : pointez la connexion vers un réplica de lecture. Les requêtes de validation sont des balayages d'agrégation en lecture seule, sans exigence de tri — exactement la charge de travail pour laquelle les réplicas existent.

Du SQL ponctuel au contrat de données

Les scripts ponctuels finissent par diverger des tables qu'ils contrôlent. Exprimer les attentes de manière déclarative règle le problème : les règles vivent à côté du schéma, dans un format lisible aussi bien par un humain que par un moteur d'exécution. Catalyst s'appuie sur l'Open Data Contract Standard :

apiVersion: v3.0.0
kind: DataContract
info:
  title: orders
  version: 1.4.0
  owner: data-platform
schema:
  - name: orders
    physicalName: orders
    physicalType: table
    properties:
      - name: order_id
        logicalType: string
        physicalType: uuid
        required: true
        primaryKey: true
        quality:
          - rule: nullCount
            dimension: completeness
            severity: error
            mustBe: "0"
          - rule: duplicateCount
            dimension: uniqueness
            severity: error
            mustBe: "0"
      - name: customer_id
        logicalType: string
        quality:
          - rule: referentialIntegrity
            dimension: consistency
            severity: error
            mustBe: "customers.id"
      - 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: created_at
        logicalType: timestamp
        physicalType: timestamptz
        quality:
          - rule: freshness
            dimension: timeliness
            severity: error
            mustBe: "<= 24h"
    quality:
      - rule: rowCount
        dimension: consistency
        severity: warning
        mustBe: "> 0"

Catalyst importe les colonnes depuis information_schema, propose un contrat de référence à partir des types et de la nullabilité qu'il y trouve, compile chaque entrée quality vers le SQL Postgres présenté plus haut, et consigne le résultat de chaque contrôle. Comme le YAML se relit sans perte — commentaires et ordre des clés survivent aux modifications faites dans l'éditeur visuel —, le contrat que vous relisez dans une pull request est, octet pour octet, le contrat qui s'exécute.

Les pièges PostgreSQL à connaître

Planification et alertes

Exécutez les contrôles immédiatement après le chargement qui produit les données, et non sur un cron calé sur une heure ronde. Une table quotidienne validée à 06:00 alors que l'ELT se termine à 06:40 échouera tous les matins pour une raison qui n'a rien à voir avec la qualité.

Alertez sur les transitions plutôt que sur les états. « Orders vient de passer en échec » est actionnable ; « orders est toujours en échec » répété toutes les heures, c'est ainsi qu'un canal finit en sourdine. Répartissez la sévérité en conséquence : error pour les contrôles qui doivent bloquer les consommateurs en aval, warning pour ceux que vous voulez suivre comme une tendance.

Questions fréquentes

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

Oui. Déclarez l'attente — obligatoire, unique, valeurs autorisées, plage numérique, motif d'expression régulière, âge maximal, clé étrangère — et laissez l'outil la compiler. Catalyst lit vos colonnes et vos types depuis information_schema, suggère un jeu de règles de départ et génère les requêtes. Le SQL écrit à la main reste réservé à la logique véritablement sur mesure, via une règle customSql.

La validation a-t-elle besoin d'un accès en écriture à ma base ?

Non. Chaque contrôle est un SELECT qui renvoie un seul nombre. CONNECT sur la base, USAGE sur le schéma et SELECT sur les tables constituent l'intégralité des permissions nécessaires. Catalyst ne stocke que des métadonnées et des résultats de contrôles ; les lignes ne quittent jamais votre serveur.

Faut-il exécuter les validations sur un réplica de lecture ?

Oui, si vous en avez un. Les contrôles sont des balayages d'agrégation complets, sans autre exigence de cohérence que « récent » : un retard de réplication de quelques secondes est donc sans importance, et les sortir de l'instance primaire lève la principale objection opérationnelle à les exécuter souvent.

Comment vérifier une expression régulière dans Postgres ?

Utilisez l'opérateur ~ pour une correspondance POSIX sensible à la casse, ou ~* pour une correspondance insensible à la casse, et niez avec !~. Postgres n'exige aucune traduction de motif, contrairement à SQL Server, où les règles d'expression régulière doivent être réécrites avec LIKE.

En quoi est-ce différent des tests dbt ou des contraintes CHECK ?

Les contraintes CHECK rejettent les mauvaises lignes dès l'écriture, ce qui est le bon comportement pour une table transactionnelle et le mauvais pour un entrepôt, où l'on préfère charger les données puis les mettre en quarantaine. Les tests dbt s'exécutent à l'intérieur d'un build dbt : ils ne couvrent donc que les modèles dont dbt est propriétaire. Un contrat de données se place au-dessus des deux : il décrit les garanties de la table dans un format portable, versionné indépendamment de tout outil de pipeline, et s'applique aussi bien aux tables produites par dbt, par Airflow ou par un chargeur maison.