0Pricing
SQL Academy · Leçon

Schémas en étoile et en flocon

Modélisez les données pour accélérer les analyses.

Schémas en étoile et en flocon est une leçon SQL Academy 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 SQL Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Academy comprend 4 leçons au total.

Qu’est-ce qu’un schéma d’entrepôt de données ?

Dans une base de données transactionnelle (OLTP), vous normalisez les données pour éviter la redondance. Dans un entrepôt de données, vous les dénormalisez souvent intentionnellement, en échangeant de l’espace de stockage contre une meilleure vitesse des requêtes. Deux modèles classiques d’organisation des tables d’entrepôt sont le schéma en étoile et le schéma en flocon.

Les deux reposent sur une table de faits centrale entourée de tables de dimensions. La différence réside dans le degré de normalisation appliqué à ces dimensions.

Tables de faits et tables de dimensions

Une table de faits stocke des événements mesurables : ventes, clics et expéditions. Elle est longue (beaucoup de lignes) et contient des mesures numériques ainsi que des clés étrangères vers les dimensions.

Une table de dimensions décrit le contexte de chaque événement : qui, quoi, quand et où. Les dimensions sont plus courtes (moins de lignes), mais plus riches en colonnes descriptives.

CREATE TABLE fact_sales (
  sale_id      SERIAL PRIMARY KEY,
  date_key     INT NOT NULL,
  product_key  INT NOT NULL,
  customer_key INT NOT NULL,
  store_key    INT NOT NULL,
  quantity     INT NOT NULL,
  revenue      NUMERIC(12, 2) NOT NULL
);

CREATE TABLE dim_product (
  product_key  SERIAL PRIMARY KEY,
  product_name VARCHAR(200),
  category     VARCHAR(100),
  brand        VARCHAR(100),
  unit_price   NUMERIC(10, 2)
);

Le schéma en étoile

Dans un schéma en étoile, chaque table de dimensions est reliée directement à la table de faits. Si vous dessinez les relations sur papier, l’ensemble ressemble à une étoile : la table de faits en est le centre et les dimensions en sont les pointes.

Les tables de dimensions sont entièrement dénormalisées : tous les attributs descriptifs résident dans une seule table, même si certains se répètent d’une ligne à l’autre.

-- Star schema: all product info in one flat dimension table
CREATE TABLE dim_product (
  product_key    SERIAL PRIMARY KEY,
  product_name   VARCHAR(200),
  category_name  VARCHAR(100),   -- denormalized
  subcategory    VARCHAR(100),   -- denormalized
  brand_name     VARCHAR(100),   -- denormalized
  brand_country  VARCHAR(100),   -- denormalized
  unit_price     NUMERIC(10, 2)
);

CREATE TABLE dim_date (
  date_key   INT PRIMARY KEY,   -- e.g. 20240315
  full_date  DATE,
  year       INT,
  quarter    INT,
  month      INT,
  month_name VARCHAR(20),
  week       INT,
  day_of_week VARCHAR(10)
);

Requête sur un schéma en étoile

Les tables de dimensions plates rendent les requêtes simples. Vous joignez la table de faits à une ou plusieurs dimensions, puis vous agrégez les données. Il n’y a pas de jointures secondaires à travers des chaînes de tables normalisées.

C’est pourquoi les schémas en étoile permettent d’exécuter rapidement les requêtes analytiques : le graphe des jointures est peu profond.

SELECT
  d.year,
  d.quarter,
  p.category_name,
  SUM(f.revenue)   AS total_revenue,
  SUM(f.quantity)  AS units_sold
FROM fact_sales f
JOIN dim_date    d ON d.date_key    = f.date_key
JOIN dim_product p ON p.product_key = f.product_key
WHERE d.year = 2024
GROUP BY d.year, d.quarter, p.category_name
ORDER BY d.quarter, total_revenue DESC;

Le schéma en flocon

Un schéma en flocon normalise davantage les tables de dimensions en les divisant en sous-dimensions. Par exemple, au lieu de stocker category_name et brand_name dans dim_product, vous créez des tables distinctes dim_category et dim_brand.

Le diagramme obtenu ressemble à un flocon — des ramifications de tables associées.

-- Snowflake schema: product dimension is normalized
CREATE TABLE dim_brand (
  brand_key     SERIAL PRIMARY KEY,
  brand_name    VARCHAR(100),
  brand_country VARCHAR(100)
);

CREATE TABLE dim_category (
  category_key   SERIAL PRIMARY KEY,
  category_name  VARCHAR(100),
  subcategory    VARCHAR(100)
);

CREATE TABLE dim_product (
  product_key  SERIAL PRIMARY KEY,
  product_name VARCHAR(200),
  category_key INT REFERENCES dim_category(category_key),
  brand_key    INT REFERENCES dim_brand(brand_key),
  unit_price   NUMERIC(10, 2)
);

Requête sur un schéma en flocon

Interroger un schéma en flocon nécessite davantage de jointures pour reconstituer les données de dimension réparties entre les tables. L’optimiseur de requêtes doit parcourir les niveaux supplémentaires, ce qui peut ajouter de la latence par rapport à un schéma en étoile.

Cependant, les dimensions normalisées sont plus petites et cohérentes — mettre à jour un nom de marque dans une ligne de dim_brand s’applique automatiquement partout.

SELECT
  d.year,
  c.category_name,
  b.brand_name,
  SUM(f.revenue) AS total_revenue
FROM fact_sales    f
JOIN dim_date      d ON d.date_key    = f.date_key
JOIN dim_product   p ON p.product_key = f.product_key
JOIN dim_category  c ON c.category_key = p.category_key
JOIN dim_brand     b ON b.brand_key    = p.brand_key
WHERE d.year = 2024
GROUP BY d.year, c.category_name, b.brand_name
ORDER BY total_revenue DESC;

Clés de substitution ou clés naturelles

Les tables de dimensions utilisent généralement une clé de substitution — un entier généré par l’entrepôt (par ex. SERIAL) — plutôt qu’une clé naturelle provenant du système source.

Les clés de substitution restent stables même lorsque la source change, occupent peu d’espace dans les grandes tables de faits et prennent en charge les dimensions à évolution lente dont l’historique doit être conservé.

-- Surrogate key (product_key) vs natural key (sku)
INSERT INTO dim_product (product_name, category_key, brand_key, unit_price)
VALUES ('Wireless Headphones', 3, 7, 89.99);
-- product_key is assigned by SERIAL -- the natural key (SKU) lives elsewhere

-- Natural key would be:
-- INSERT INTO dim_product (sku, product_name, ...)
-- VALUES ('WH-1000XM5', 'Wireless Headphones', ...);
-- Risky: SKU can be reused or reassigned by the source system

La dimension de date

La dimension de date est particulière — elle est presque toujours présente et est généralement préremplie pour plusieurs années. Stocker les attributs dérivés (année, trimestre, nom du mois, période fiscale, indicateur de jour férié) dans la table de dimensions évite de les recalculer au moment de la requête.

-- Populate dim_date for one year using generate_series
INSERT INTO dim_date (date_key, full_date, year, quarter, month, month_name, week, day_of_week)
SELECT
  TO_CHAR(d, 'YYYYMMDD')::INT  AS date_key,
  d                             AS full_date,
  EXTRACT(YEAR    FROM d)::INT  AS year,
  EXTRACT(QUARTER FROM d)::INT  AS quarter,
  EXTRACT(MONTH   FROM d)::INT  AS month,
  TO_CHAR(d, 'Month')           AS month_name,
  EXTRACT(WEEK    FROM d)::INT  AS week,
  TO_CHAR(d, 'Day')             AS day_of_week
FROM generate_series('2024-01-01'::DATE, '2024-12-31'::DATE, '1 day') AS d;

Dimensions à évolution lente (SCD type 2)

Que se passe-t-il lorsqu’un client change de ville ou qu’un produit change de catégorie ? Vous devez suivre l’historique. SCD type 2 insère une nouvelle ligne de dimension à chaque changement tout en clôturant la précédente avec une date de fin. La ligne de la table de faits pointe toujours vers l’ancienne clé de dimension, ce qui préserve l’exactitude historique.

-- SCD Type 2 customer dimension
CREATE TABLE dim_customer (
  customer_key  SERIAL PRIMARY KEY,
  customer_id   INT NOT NULL,
  customer_name VARCHAR(200),
  city          VARCHAR(100),
  country       VARCHAR(100),
  valid_from    DATE NOT NULL,
  valid_to      DATE,
  is_current    BOOLEAN DEFAULT TRUE
);

-- When a customer moves, close old row and insert new one:
UPDATE dim_customer
   SET valid_to = CURRENT_DATE - 1, is_current = FALSE
 WHERE customer_id = 42 AND is_current = TRUE;

INSERT INTO dim_customer (customer_id, customer_name, city, country, valid_from, is_current)
VALUES (42, 'Alice Muller', 'Berlin', 'Germany', CURRENT_DATE, TRUE);

Étoile ou flocon — compromis

Aucun des deux schémas n’est universellement meilleur. Faites votre choix selon vos priorités :

  • Étoile — moins de jointures, requêtes plus rapides, ETL plus simple, coût de stockage plus élevé. Idéal pour les outils d’analyse à forte charge de lecture (Tableau, Power BI).
  • Flocon — dimensions normalisées, moins de redondance, mises à jour des dimensions plus faciles, mais davantage de jointures. Préférable lorsque les dimensions sont volumineuses ou partagées par plusieurs tables de faits.
-- Checking how much storage the denormalized category column costs
-- in a large dim_product (star schema) vs a separate dim_category (snowflake)
SELECT
  COUNT(*)                               AS total_products,
  COUNT(DISTINCT category_name)          AS unique_categories,
  pg_size_pretty(
    SUM(pg_column_size(category_name))
  )                                      AS category_storage
FROM dim_product;

Schéma en galaxie (constellation de faits)

Lorsqu’un entrepôt contient plusieurs tables de faits qui partagent des tables de dimensions, le résultat est appelé schéma en galaxie (ou constellation de faits). Par exemple, un entrepôt de données de commerce de détail peut contenir des tables de faits distinctes pour les ventes et les retours, qui référencent toutes deux les mêmes dim_product et dim_date.

Les dimensions partagées imposent un filtrage cohérent et facilitent les comparaisons entre faits.

CREATE TABLE fact_returns (
  return_id     SERIAL PRIMARY KEY,
  date_key      INT NOT NULL REFERENCES dim_date(date_key),
  product_key   INT NOT NULL REFERENCES dim_product(product_key),
  customer_key  INT NOT NULL,
  quantity      INT NOT NULL,
  refund_amount NUMERIC(12, 2) NOT NULL
);

-- Cross-fact query: net revenue = sales - refunds
SELECT
  d.year,
  d.month,
  SUM(s.revenue)       AS gross_revenue,
  SUM(r.refund_amount) AS total_refunds,
  SUM(s.revenue) - COALESCE(SUM(r.refund_amount), 0) AS net_revenue
FROM dim_date d
LEFT JOIN fact_sales   s ON s.date_key = d.date_key
LEFT JOIN fact_returns r ON r.date_key = d.date_key
WHERE d.year = 2024
GROUP BY d.year, d.month
ORDER BY d.month;

Schéma en étoile ou en flocon

Évaluez votre compréhension des schémas en étoile et en flocon.

Récapitulatif de la leçon

Dans cette leçon, vous avez étudié deux modèles fondamentaux de conception d’entrepôts de données :

  • Schéma en étoile — une table de faits centrale entourée de tables de dimensions plates et dénormalisées. Moins de jointures, requêtes plus rapides, stockage légèrement plus important.
  • Schéma en flocon — les tables de dimensions sont davantage normalisées en sous-dimensions. Moins de redondance, mises à jour plus faciles, mais davantage de jointures nécessaires.
  • Les tables de faits contiennent des événements mesurables ; les tables de dimensions fournissent le contexte (qui, quoi, quand, où).
  • Les clés de substitution préservent l’exactitude historique et découplent l’entrepôt des modifications du système source.
  • SCD type 2 suit l’historique des dimensions en ajoutant de nouvelles lignes avec des dates de validité au lieu de remplacer les anciennes.
  • Lorsque plusieurs tables de faits partagent des dimensions, la conception devient un schéma en galaxie (constellation de faits).

Choisissez l’étoile pour sa simplicité et sa rapidité ; choisissez le flocon lorsque les dimensions sont volumineuses, fréquemment mises à jour ou partagées par de nombreuses tables de faits.

Questions Fréquemment Posées

La leçon « Schémas en étoile et en flocon » est-elle gratuite ?

Oui — le texte complet de « Schémas en étoile et en flocon » 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 SQL Academy, passe à CoddyKit PRO. Le cours SQL Academy comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Schémas en étoile et en flocon » ?

Modélisez les données pour accélérer les analyses. Tu pratiques SQL Academy 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 SQL Academy ?

Aucune expérience préalable n'est requise. SQL Academy 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émas en étoile et en flocon » ?

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 SQL Academy ?

Oui. Chaque leçon SQL Academy 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

  1. OLTP ou OLAP
  2. Tables de faits et de dimensions
  3. Schémas en étoile et en flocon
  4. Écrire des requêtes analytiques
← Retour à SQL Academy