Préparer un entretien : Data Analyst

Le grain de la table est la ligne d'article, mais le montant total de commande est une information de niveau commande, dupliquée sur chaque ligne. Sommer directement ce montant sur les 3 lignes donnerait 270 € au lieu de 90 €, un triple compte. La correction consiste à agréger d'abord au niveau commande avec une valeur par commande unique, par exemple via une jointure sur une table de commandes distincte ou un DISTINCT explicite sur l'identifiant de commande avant sommation, puis seulement ensuite additionner les montants par commande distincte.

Le double compte est expliqué par la confusion de grain, et la correction agrège d'abord à un niveau commande unique avant de sommer.

CREATE TABLE client (
  id_client   INTEGER PRIMARY KEY
);
CREATE TABLE commande (
  id_commande INTEGER PRIMARY KEY,
  id_client   INTEGER NOT NULL REFERENCES client(id_client),
  date_commande DATE NOT NULL
);
CREATE TABLE ligne_commande (
  id_ligne    INTEGER PRIMARY KEY,
  id_commande INTEGER NOT NULL REFERENCES commande(id_commande),
  produit     TEXT NOT NULL,
  quantite    INTEGER NOT NULL CHECK (quantite > 0),
  prix_unitaire_snapshot NUMERIC NOT NULL
);

La contrainte CHECK (quantite > 0) interdit à elle seule les valeurs négatives et zéro, mais n'interdit pas NULL en SQL, puisqu'une comparaison avec NULL n'est ni vraie ni fausse et laisse donc passer la ligne ; c'est pourquoi quantite est aussi déclarée NOT NULL. Les clés primaires id_client, id_commande et id_ligne identifient chaque ligne, et les clés étrangères id_client sur commande et id_commande sur ligne_commande relient les tables. Le prix_unitaire_snapshot est stocké sur la ligne plutôt que recalculé depuis un catalogue produit, car le prix au moment de la commande doit rester figé même si le prix catalogue change ensuite. Ce schéma pédagogique n'est présenté comme conforme à la troisième forme normale que sous l'hypothèse explicite que les seules dépendances fonctionnelles sont id_client -> attributs client, id_commande -> attributs commande, et id_ligne -> attributs de ligne ; sans expliciter ces dépendances, aucune garantie de forme normale n'est affirmée.

Le schéma SQL déclare NOT NULL en plus du CHECK sur quantite, les clés primaires et étrangères sont explicites, le prix est un instantané sur la ligne, et la conformité à une forme normale n'est affirmée que sous des dépendances fonctionnelles explicitées.

CREATE TABLE client_historique (
  client_version_id INTEGER PRIMARY KEY,
  id_client   INTEGER NOT NULL,
  ville       TEXT NOT NULL,
  date_debut  DATE NOT NULL,
  date_fin    DATE
);

L'intervalle de validité de chaque version est semi-ouvert [date_debut, date_fin) : la version est valide à partir de date_debut inclus et jusqu'à date_fin exclu ; date_fin NULL signifie que la version est la version courante, sans fin connue. Exemple pour le client 1 : version 101, ville 'Lyon', date_debut '2024-01-01', date_fin '2025-06-01' ; version 102, ville 'Nantes', date_debut '2025-06-01', date_fin NULL. Une commande passée le 2025-06-01 relève de la version 102 (Nantes), puisque cette date est incluse dans son intervalle et exclue de celui de la version 101 : la frontière n'appartient jamais à deux versions à la fois, ce qui évite un chevauchement à cette frontière dans cet exemple. La table de faits commande référence client_version_id :

CREATE TABLE commande (
  id_commande INTEGER PRIMARY KEY,
  date_commande DATE NOT NULL,
  client_version_id INTEGER NOT NULL REFERENCES client_historique(client_version_id)
);

La version est déterminée lors de l'insertion par une jointure de la forme : `h.id_client = entree.id_client AND entree.date_commande >= h.date_debut AND (entree.date_commande < h.date_fin OR h.date_fin IS NULL)`, où h désigne la dimension et entree la commande à charger. Utiliser BETWEEN date_debut AND date_fin serait incorrect ici car BETWEEN est inclusif aux deux bornes et compterait la date_fin comme appartenant encore à l'ancienne version, provoquant un double compte possible à la frontière entre deux versions consécutives.

La clé primaire seule ne garantit pas l’absence de chevauchement. Avant de charger un fait, contrôlez les bornes et exigez exactement une version correspondante pour ce client et cette date ; zéro ou plusieurs correspondances bloquent le chargement jusqu’à correction.

La table de dimension inclut une clé de version explicite (client_version_id), l'intervalle est semi-ouvert avec date_fin NULL pour la version courante, l'exemple à la date frontière est résolu de façon déterministe vers une seule version, et la condition de jointure est donnée explicitement en évitant BETWEEN.