Schéma en étoile et conception d’un entrepôt de données
Tables de faits et de dimensions, compromis de dénormalisation et modélisation OLAP
Schéma en étoile et conception d’un entrepôt de données est une leçon Coding Interview Prep gratuite sur CoddyKit. Ceci est la leçon 3 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage Coding Interview Prep, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours Coding Interview Prep comprend 4 leçons au total.
OLTP contre OLAP
Les questions d’entrepôt de données commencent par une distinction que les recruteurs attendent de vous : OLTP contre OLAP.
- OLTP (transactionnel) : de nombreuses petites lectures/écritures, avec une forte normalisation pour garantir l’intégrité. Fait fonctionner l’application.
- OLAP (analytique) : quelques grandes lectures d’agrégation sur l’historique, dénormalisées délibérément pour gagner en rapidité. Sert aux rapports et aux tableaux de bord.
Les schémas en étoile sont une conception OLAP. Leur objectif est d’exécuter rapidement les requêtes analytiques, en acceptant une certaine redondance en contrepartie.
Faits et dimensions
Un schéma en étoile répartit les données entre deux types de tables :
- Table de faits : les événements ou transactions mesurables (une vente, un clic). Elle contient des mesures numériques et des clés étrangères vers les dimensions.
- Tables de dimensions : le contexte descriptif selon lequel vous analysez les données (date, produit, client, magasin).
La table de faits se trouve au centre ; les dimensions l’entourent comme les pointes d’une étoile, d’où son nom.
Anatomie d’une table de faits
Une table de faits contient principalement des clés étrangères et des mesures numériques. Elle est longue et étroite, et augmente continuellement.
Les mesures sont des nombres additifs que vous agrégez : quantité, chiffre d’affaires, coût. La granularité (une ligne = un ?) doit être clairement indiquée ; ici, une ligne représente une ligne de produit d’une vente.
CREATE TABLE fact_sales (
sale_id BIGINT PRIMARY KEY,
date_key INT NOT NULL, -- FK to dim_date
product_key INT NOT NULL, -- FK to dim_product
customer_key INT NOT NULL, -- FK to dim_customer
store_key INT NOT NULL, -- FK to dim_store
quantity INT, -- measure
revenue DECIMAL(12,2), -- measure
cost DECIMAL(12,2) -- measure
);Anatomie d’une table de dimensions
Les dimensions sont courtes et larges : elles comportent de nombreuses colonnes descriptives selon lesquelles vous filtrez et regroupez les données. Elles sont volontairement dénormalisées afin qu’une requête n’ait besoin que d’une seule jointure par dimension.
Remarquez que dim_product conserve la catégorie et la marque sur la même ligne, au lieu de les placer dans des tables distinctes. Cette redondance est précisément l’objectif : elle évite des jointures supplémentaires au moment de la requête.
CREATE TABLE dim_product (
product_key INT PRIMARY KEY, -- surrogate key
product_id INT, -- natural/business key
product_name VARCHAR(100),
category VARCHAR(50), -- denormalized
brand VARCHAR(50), -- denormalized
unit_price DECIMAL(10,2)
);Une requête sur un schéma en étoile
Voici ce que cette conception vous apporte. Une requête d’analyse classique effectue une jointure entre la table de faits et quelques dimensions, filtre les données, puis les agrège. Une jointure par dimension, sans chaînes de jointures profondes.
Les recruteurs vous demandent souvent d’écrire exactement ce type de requête sur un schéma en étoile.
SELECT d.category,
t.year,
SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_product d ON d.product_key = f.product_key
JOIN dim_date t ON t.date_key = f.date_key
WHERE t.year = 2025
GROUP BY d.category, t.year
ORDER BY total_revenue DESC;Clés de substitution
Les dimensions utilisent une clé de substitution : une clé primaire entière sans signification (comme product_key) générée par l’entrepôt, distincte de la clé naturelle du système source.
Pourquoi cela intéresse les recruteurs :
- Elle découple l’entrepôt des clés métier susceptibles de changer.
- Elle garde les tables de faits étroites (les jointures sur des entiers sont rapides).
- Elle est nécessaire pour suivre l’historique avec des dimensions à évolution lente (séquence suivante).
Dimensions à évolution lente
Un sujet d’entretien très apprécié dans le domaine des entrepôts : lorsqu’un attribut de dimension change (un client change de ville), comment le gérer ? Il s’agit de dimensions à évolution lente (SCD) :
- Type 1 : écraser l’ancienne valeur. Aucun historique.
- Type 2 : ajouter une nouvelle ligne avec des dates d’effet et un indicateur de version actuelle. Historique complet ; cela nécessite des clés de substitution.
- Type 3 : conserver une colonne « valeur précédente ». Historique limité.
Le Type 2 est la réponse la plus souvent attendue pour suivre les changements au fil du temps.
-- SCD Type 2 dimension
CREATE TABLE dim_customer (
customer_key INT PRIMARY KEY, -- surrogate
customer_id INT, -- natural key
city VARCHAR(50),
valid_from DATE,
valid_to DATE,
is_current BOOLEAN
);Étoile contre flocon
Attendez-vous à cette question de comparaison. Un schéma en flocon normalise les dimensions en sous-tables (produit -> catégorie -> département), tandis qu’un schéma en étoile les conserve à plat.
- Étoile : moins de jointures, lectures plus rapides, une certaine redondance. À privilégier pour les performances des requêtes.
- Flocon : moins d’espace de stockage et maintenance plus simple des dimensions, mais davantage de jointures par requête.
Dites : « Préférez par défaut l’étoile pour accélérer les requêtes ; utilisez le flocon uniquement lorsque les dimensions sont volumineuses et réutilisées. »
La dimension de dates
Presque tous les schémas en étoile disposent d’une dimension de dates dédiée plutôt que d’une simple colonne de date. Elle précalcule l’année, le trimestre, le mois, le jour de la semaine, les indicateurs de jours fériés et les périodes fiscales.
Les analystes peuvent ainsi regrouper les données par « trimestre fiscal » ou par « est_un_week_end » au moyen d’une simple jointure, plutôt que d’éparpiller des fonctions de date. Mentionner spontanément une dimension de dates est un signal fort que vous avez conçu des entrepôts.
CREATE TABLE dim_date (
date_key INT PRIMARY KEY, -- e.g. 20250131
full_date DATE,
year INT,
quarter INT,
month INT,
day_of_week VARCHAR(10),
is_weekend BOOLEAN,
fiscal_qtr VARCHAR(6)
);Choisir la granularité
La décision la plus importante concernant une table de faits est sa granularité : ce que représente une ligne. Déclarez-la avant toute autre chose.
- Trop grossière (une ligne par jour et par magasin), elle vous fait perdre des détails.
- Trop fine (une ligne par article scanné), elle fait exploser la taille de la table.
Une définition claire de la granularité, comme « une ligne par produit et par ligne de commande », détermine les dimensions et les mesures à inclure. Les recruteurs sont attentifs à cette rigueur.
Quand dénormaliser
Reliez cela à la normalisation. Les systèmes OLTP sont normalisés en 3NF pour garantir l’intégrité ; les entrepôts dénormalisent délibérément les dimensions pour accélérer les lectures.
Le compromis que vous devez savoir formuler :
- La redondance des données de dimension est acceptable, car l’entrepôt est alimenté par un processus ETL contrôlé, et non par des écritures applicatives improvisées.
- Moins de jointures signifie des agrégations plus rapides sur des milliards de lignes de faits.
C’est le discernement, et non la règle, qui distingue ici les réponses de niveau confirmé.
Vérification rapide
Vous concevez un entrepôt de données de ventes et devez conserver l’historique complet de la ville d’un client lorsqu’il déménage.
Récapitulatif : schéma en étoile et conception d’entrepôt
Vous pouvez maintenant répondre aux questions de modélisation d’entrepôts :
- OLTP normalise les données pour garantir l’intégrité ; OLAP les dénormalise pour accélérer les lectures.
- Un schéma en étoile possède une table de faits centrale (clés étrangères + mesures numériques), entourée de dimensions à plat.
- Utilisez des clés de substitution et une dimension de dates dédiée.
- Suivez les changements avec SCD Type 2 ; déclarez d’abord la granularité de la table de faits.
- Préférez l’étoile au flocon pour les performances des requêtes.
Questions Fréquemment Posées
La leçon « Schéma en étoile et conception d’un entrepôt de données » est-elle gratuite ?
Oui — le texte complet de « Schéma en étoile et conception d’un entrepôt de données » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours Coding Interview Prep, passe à CoddyKit PRO. Le cours Coding Interview Prep comprend 4 leçons au total.
Qu'est-ce que j'apprendrai dans « Schéma en étoile et conception d’un entrepôt de données » ?
Tables de faits et de dimensions, compromis de dénormalisation et modélisation OLAP Tu pratiques Coding Interview Prep avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.
Dois-je avoir de l'expérience pour commencer Coding Interview Prep ?
Aucune expérience préalable n'est requise. Coding Interview Prep sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 3 sur 4.
Combien de temps prend la leçon « Schéma en étoile et conception d’un entrepôt de données » ?
La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.
Peux-tu écrire et exécuter du code dans cette leçon Coding Interview Prep ?
Oui. Chaque leçon Coding Interview Prep inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.
Toutes les leçons de ce cours
- Normalisation jusqu’à la 3NF
- Modélisation ER et cardinalité des relations
- Schéma en étoile et conception d’un entrepôt de données
- Série complète d’exercices d’entretien blanc