Vous ajoutez dans votre entrepôt de données une clé étrangère de fact_sales.customer_id vers dim_customer. Le DDL s'exécute, le chargement nocturne aussi, et le lendemain matin un lot de nouvelles lignes de ventes pointe vers des clients qui n'existent pas. Rien n'a échoué, parce que rien n'a été vérifié. Dans Snowflake, BigQuery, Amazon Redshift et Databricks, une clé étrangère n'est pas appliquée : la plateforme enregistre la déclaration sous forme de métadonnées, mais accepte des lignes dont la clé n'a aucune correspondance dans la table parente.

Il en va de même pour les clés primaires et les contraintes d'unicité. Cela surprend ceux qui ont fait leurs armes sur PostgreSQL, Oracle ou SQL Server, où une clé étrangère déclarée rejette la ligne erronée dès l'insertion. Dans un entrepôt de données cloud, la déclaration est une promesse que vous faites, pas une règle que la base de données fait respecter à votre place. Certains planificateurs de requêtes croient même cette promesse et s'en servent pour simplifier les jointures.

Cet article indique ce que chaque plateforme applique, avec des liens vers la documentation des éditeurs, explique pourquoi les entrepôts de données font ce compromis, montre comment une clé violée peut se traduire par un chiffre faux, et décrit ce qu'il faut exécuter à la place. Pour l'idée générale des lignes parentes et enfants et la définition d'un enregistrement orphelin, commencez par l'article de référence Vos données retrouvent-elles encore leurs parents ? Comprendre l'intégrité référentielle.

Points clés à retenir

• Snowflake (tables standard), BigQuery, Redshift et Databricks acceptent les déclarations de clés primaires, étrangères et uniques, mais ne les appliquent pas. Les tables hybrides de Snowflake font exception.

• NOT NULL est appliqué sur Snowflake, Redshift et Databricks ; Databricks applique aussi les contraintes CHECK.

• Redshift et BigQuery utilisent les clés déclarées pour planifier les requêtes. Si les clés sont fausses, certaines requêtes peuvent renvoyer des résultats incorrects sans la moindre erreur.

• Ne déclarez des clés que lorsque les données ont été validées, et validez-les à nouveau après chaque chargement avec un contrôle d'intégrité référentielle.

• Gardez NOT NULL comme une règle à part entière et surveillez les références qui traversent plusieurs systèmes, où aucune contrainte ne peut exister.

Table des matières

• Que signifie « clé étrangère non appliquée » ?

• Quels entrepôts de données appliquent les clés primaires et étrangères ?

• Pourquoi les entrepôts de données n'appliquent-ils pas les clés étrangères ?

• Comment une clé non appliquée peut-elle produire des résultats de requête erronés ?

• Comment protéger l'intégrité référentielle dans un entrepôt de données ?

  • Le contrôle par anti-jointure en SQL

• À quoi ressemble un contrôle d'intégrité référentielle dans digna ?

• Pour aller plus loin

Que signifie « clé étrangère non appliquée » ?

Une clé étrangère non appliquée est une déclaration que la base de données enregistre mais ne vérifie jamais. Vous pouvez écrire FOREIGN KEY (customer_id) REFERENCES dim_customer (customer_id), et la plateforme la stockera, l'affichera dans le catalogue et l'exposera aux outils, tout en chargeant malgré tout une ligne dont le customer_id n'a pas de parent.

Les éditeurs parlent de contraintes informatives (informational constraints). Elles décrivent la forme attendue des données : quelle colonne identifie une ligne, quelle colonne pointe vers quelle table. Les outils de BI, les catalogues de données et les outils de modélisation les lisent pour dessiner des diagrammes et suggérer des jointures. Certains optimiseurs de requêtes les lisent aussi. En revanche, elles n'arrêtent pas un chargement, ne déclenchent pas d'erreur et ne signalent pas d'orphelin.

Ainsi, « la clé est déclarée » et « la clé est respectée » sont deux affirmations différentes dans un entrepôt de données. La première est un morceau de DDL. La seconde est un fait concernant les données, que seul un contrôle peut établir, et qui peut changer à chaque chargement.

Quels entrepôts de données appliquent les clés primaires et étrangères ?

Aucun des quatre grands entrepôts de données cloud n'applique les clés primaires, étrangères ou uniques sur ses tables standard. Les tables hybrides de Snowflake sont la seule exception de cette liste. NOT NULL est appliqué sur Snowflake, Redshift et Databricks, et Databricks applique aussi les contraintes CHECK. Le tableau résume la documentation de chaque éditeur ; suivez les liens pour la formulation actuelle.

Deux détails comptent en pratique. Premièrement, NOT NULL est la contrainte à laquelle on peut généralement se fier : sur Snowflake, Redshift et Databricks, un NULL dans une colonne NOT NULL est rejeté. Deuxièmement, les tables hybrides de Snowflake sont un type de table différent, avec un comportement différent ; une clé étrangère sur une table Snowflake ordinaire ne vous offre aucune de ces garanties.

Les bases de données OLTP classiques comme PostgreSQL, Oracle, SQL Server et MySQL avec InnoDB appliquent bel et bien les clés étrangères déclarées. Mais un entrepôt de données alimenté à partir de ces bases ne reprend généralement pas ces contraintes, et de nombreux traitements de chargement désactivent les contraintes pour gagner en vitesse. L'intégrité dont vous disposiez dans le système source ne voyage pas d'elle-même avec les données.

Pourquoi les entrepôts de données n'appliquent-ils pas les clés étrangères ?

Appliquer une clé étrangère signifie rechercher chaque clé entrante dans la table parente avant d'accepter la ligne. Les entrepôts de données sont conçus pour charger de très gros lots rapidement et en parallèle, et cette recherche ligne par ligne va à l'encontre de ces deux objectifs. La documentation des éditeurs décrit le comportement ; les raisons ci-dessous relèvent du compromis d'ingénierie général, pas de déclarations des éditeurs.

• Vitesse de chargement. Un chargement en masse de millions de lignes de faits nécessiterait des millions de recherches dans la table parente. Les éviter garde des temps de chargement prévisibles.

• Chargements distribués et parallèles. Le stockage et le calcul sont répartis sur de nombreux nœuds et fichiers. Vérifier une clé par rapport à une table parente elle-même en cours de chargement parallèle exige une coordination qui ralentit l'ensemble.

• Ordre de chargement. Les pipelines chargent souvent les faits avant les dimensions, ou reçoivent des lignes de dimension en retard. Une application stricte rejetterait des lignes qui auraient été valides une heure plus tard.

• Pipelines principalement en ajout. La plupart des tables d'un entrepôt de données sont alimentées par ajout, pas modifiées ligne par ligne. Le modèle suppose que les données ont été préparées en amont ; la base de données ne les revérifie donc pas.

Le compromis est raisonnable. Le problème, c'est que le contrôle ne disparaît pas ; il se déplace. Quelqu'un doit l'exécuter après le chargement, et dans beaucoup d'équipes, personne ne le fait.

Comment une clé non appliquée peut-elle produire des résultats de requête erronés ?

Une clé non appliquée devient dangereuse lorsque le planificateur de requêtes lui fait confiance. Amazon Redshift indique dans sa documentation que son planificateur suppose que les clés sont valides telles que chargées, et que si votre application autorise des clés étrangères ou primaires invalides, certaines requêtes peuvent renvoyer des résultats incorrects. Une clé déclarée mais violée est pire que pas de clé du tout.

BigQuery utilise les clés primaires et étrangères déclarées pour éliminer des jointures internes et externes et pour réordonner les jointures, et sa documentation vous en laisse la responsabilité : « Il vous incombe de maintenir les contraintes en permanence. »

Voici le mécanisme en termes généraux. Prenez une requête qui joint fact_sales à dim_customer mais ne sélectionne que des colonnes de fact_sales. Si le planificateur fait confiance à la clé étrangère, il peut décider que la jointure ne peut supprimer aucune ligne et la sauter. Exécutez la même requête sans la clé déclarée, et la jointure interne écarte chaque vente orpheline. Le même rapport donne alors deux totaux différents selon le plan, et aucune des deux exécutions ne déclenche d'erreur.

Les clés primaires en double posent un problème voisin : une jointure dont tout le monde pense qu'elle renvoie une ligne par clé en renvoie plusieurs, et les totaux doublent. Dans les deux cas, le tableau de bord a l'air normal. Le chiffre est simplement faux, et vous le découvrez lors d'un rapprochement des semaines plus tard, si vous le découvrez.

Comment protéger l'intégrité référentielle dans un entrepôt de données ?

Vous protégez l'intégrité référentielle dans un entrepôt de données en traitant les clés déclarées comme des affirmations et en les vérifiant après chaque chargement. Ne déclarez une clé que lorsque les données ont été validées, exécutez un contrôle d'intégrité référentielle à chaque exécution du pipeline, et alertez l'équipe responsable en cas d'échec. Ces étapes fonctionnent sur chacune des quatre plateformes.

1. Recensez les clés que vous avez déclarées. Sachez quelles clés étrangères et primaires existent dans le catalogue, car ce sont celles auxquelles un planificateur peut se fier et sur lesquelles un outil de BI peut faire des jointures.

2. Ne déclarez des clés qu'une fois validées. Avant d'ajouter une clé, prouvez qu'elle est respectée sur les données actuelles. Si vous ne pouvez pas continuer à la vérifier, réfléchissez à deux fois avant de la déclarer.

3. Validez après chaque chargement. Exécutez un contrôle d'intégrité référentielle pour chaque paire enfant-parent importante dans le cadre du pipeline, et non comme un audit ponctuel. Un chargement qui introduit des orphelins doit être visible le jour même.

4. Gardez NOT NULL comme une règle distincte. Un contrôle référentiel ignore généralement les clés NULL, car un NULL ne pointe vers rien. Si chaque ligne doit avoir un parent, testez customer_id IS NOT NULL séparément, ou appuyez-vous sur la contrainte NOT NULL appliquée lorsque la plateforme la propose.

5. Surveillez les références entre systèmes. Le référentiel produits peut se trouver dans une base de données et les transactions dans une autre. Aucune clé étrangère ne peut couvrir deux systèmes : ces références ne sont donc jamais protégées que par un contrôle.

6. Définissez un seuil et un responsable. Décidez si un seul orphelin constitue un échec ou si une faible proportion est tolérable, et transmettez le résultat à l'équipe responsable des données.

Le contrôle par anti-jointure en SQL

Le cœur de tout contrôle d'intégrité référentielle est une anti-jointure : trouver les lignes enfants dont la clé n'a aucune correspondance dans la table parente.

SELECT s.*
FROM fact_sales s
LEFT JOIN dim_customer c
  ON c.customer_id = s.customer_id
WHERE s.customer_id IS NOT NULL
  AND c.customer_id IS NULL

SELECT s.*
FROM fact_sales s
LEFT JOIN dim_customer c
  ON c.customer_id = s.customer_id
WHERE s.customer_id IS NOT NULL
  AND c.customer_id IS NULL

SELECT s.*
FROM fact_sales s
LEFT JOIN dim_customer c
  ON c.customer_id = s.customer_id
WHERE s.customer_id IS NOT NULL
  AND c.customer_id IS NULL

Le filtre IS NOT NULL exclut les clés NULL du décompte des orphelins, conformément à l'étape 4. Pour les clés composites, les variantes avec NOT EXISTS, les performances sur les grandes tables et la manière d'empêcher les orphelins d'entrer dès le chargement, consultez notre article Enregistrements orphelins : les trouver en SQL et les tenir à l'écart.

Écrire la requête est la partie facile. L'exécuter après chaque chargement, pour chaque clé, enregistrer les nombres, alerter quelqu'un et garder les lignes en échec à disposition de la personne qui doit les corriger : c'est là que le SQL écrit à la main a tendance à décrocher.

À quoi ressemble un contrôle d'intégrité référentielle dans digna ?

Dans digna, un contrôle d'intégrité référentielle est une règle de digna Data Validation : vous choisissez la colonne d'une source de données, choisissez la colonne correspondante d'une autre et définissez un seuil. Aucun SQL à écrire. digna génère le contrôle et l'exécute dans votre entrepôt de données à chaque inspection de cette source de données.

La configuration tient en quelques champs de la boîte de dialogue Add Data Validation Rule (Configuration → source de données → Data Validation → Add Rule) :

• Name (nom) et Description, par exemple hc_product_in_master : « Chaque produit administré existe dans le référentiel produits de la pharmacie ».

• Type : Referential Integrity (les autres types sont Rule et Uniqueness).

• Attributes (attributs) : la colonne de cette source de données, ici product_code.

• must exist in (doit exister dans) : la Data Source parente (hospital_medications) et ses Attributes (product_code). Pour une clé composite, choisissez plusieurs colonnes des deux côtés, dans le même ordre ; les listes de colonnes non concordantes sont rejetées.

• Threshold Mode (mode de seuil) Absolute ou Relative, avec un Info threshold et un Warn threshold. Absolute, Info 0, Warn 1 signifie qu'un seul orphelin donne déjà un statut Uncertain et que deux ou plus font échouer la règle ; laissez Warn à 0 si un seul orphelin doit provoquer l'échec.

La règle d'intégrité référentielle dans digna : chaque product_code de la source de données des administrations doit exister dans hospital_medications.

L'exemple utilise des données de démonstration fictives de Danubia Kliniken, un groupe hospitalier autrichien fictif.

Le 22 avril 2026, 82 administrations de Coavira 2.5 mg (code produit 3858646) ont été enregistrées dans les services avant que le produit n'arrive dans le référentiel produits de la pharmacie. La règle a signalé 4 244 lignes réussies sur 4 326 et est passée à Failed. Dans la vue Invalid Records, vous filtrez sur Failed, choisissez le contrôle et voyez chaque ligne orpheline avec son hôpital, son service et son code produit, prête à être exportée pour la personne qui corrige les données de référence. Sans ce contrôle, chaque rapport joignant les doses aux produits aurait affiché zéro dose de ce produit.

Trois propriétés correspondent aux étapes ci-dessus. Le contrôle s'exécute dans l'entrepôt de données lui-même : digna envoie du SQL, reçoit des nombres et ne récupère les lignes en échec que lorsque vous les demandez ; aucune table n'est copiée à l'extérieur, et vos données ne quittent jamais votre infrastructure. Les clés NULL sont ignorées ; la présence fait donc l'objet d'une règle distincte de type Rule, par exemple product_code IS NOT NULL. Enfin, depuis la Release 2026.01, le parent peut se trouver dans un autre schéma, voire sur une autre connexion de base de données du même projet, ce qui couvre les références entre systèmes qu'aucune contrainte ne peut atteindre. Pour une vue d'ensemble de la consolidation des sources dans un entrepôt de données, consultez notre guide sur l'intégration d'un entrepôt de données ; la description complète de la règle, champ par champ, se trouve dans notre article sur la configuration d'un contrôle d'intégrité référentielle.

Pour aller plus loin

Une clé étrangère non appliquée n'est pas un bug de votre entrepôt de données. C'est un choix de conception qui vous renvoie la responsabilité du contrôle. Continuez à déclarer des clés là où elles aident les outils et les planificateurs, mais seulement une fois qu'il est prouvé que les données les respectent, et prouvez-le à nouveau après chaque chargement. Un contrôle d'intégrité référentielle qui s'exécute à chaque inspection, dans l'entrepôt de données, avec un seuil et un responsable, comble l'écart que la plateforme a laissé ouvert.

Si vous voulez voir cela fonctionner sur les tables de votre propre entrepôt de données, réservez une démo avec l'équipe digna.

Questions fréquentes

Snowflake applique-t-il les clés étrangères ?

Pas sur les tables standard. Snowflake documente les clés primaires, étrangères et uniques de ces tables comme facultatives et non appliquées, tandis que NOT NULL est appliqué. Les tables hybrides font exception : sur celles-ci, les contraintes PK, FK et UNIQUE sont appliquées. Les lignes orphelines d'une table standard se chargent donc sans la moindre erreur.

Des clés étrangères non appliquées peuvent-elles produire des résultats de requête erronés ?

Oui, lorsque le planificateur leur fait confiance. Amazon Redshift indique que son planificateur suppose que les clés sont valides telles que chargées ; des clés invalides peuvent donc amener certaines requêtes à renvoyer des résultats incorrects. BigQuery utilise les clés déclarées pour éliminer et réordonner des jointures, et précise qu'il vous incombe de maintenir les contraintes.

Que sont les contraintes informatives dans un entrepôt de données ?

Les contraintes informatives sont des clés primaires, étrangères ou uniques que la plateforme enregistre comme métadonnées mais ne vérifie pas. Databricks et Amazon Redshift décrivent leurs contraintes de clé comme uniquement informatives. Les outils et les planificateurs peuvent les lire, mais les lignes qui les violent se chargent quand même ; un contrôle de validation distinct est donc nécessaire.

Faut-il encore déclarer des clés étrangères dans Redshift ou BigQuery ?

Déclarez-les uniquement lorsque les données ont été validées et que vous continuez à les valider. Les deux plateformes utilisent les clés déclarées pour planifier les requêtes ; une clé déclarée mais violée peut donc modifier les résultats. Exécutez un contrôle d'intégrité référentielle après chaque chargement avant de vous fier à la déclaration.

Comment vérifier l'intégrité référentielle lorsque l'entrepôt de données ne l'applique pas ?

Exécutez une anti-jointure après chaque chargement : sélectionnez les lignes enfants dont la clé n'a aucun parent correspondant, en excluant les NULL. Dans digna Data Validation, il s'agit d'une règle Referential Integrity avec un seuil, exécutée dans l'entrepôt de données à chaque inspection, et les lignes en échec apparaissent dans la vue Invalid Records.