Diagramme de schéma en étoile : tables de faits et de dimensions, avec un exemple de ventes

Un product manager demande le chiffre d'affaires par catégorie et par région pour le dernier trimestre, comparé au même trimestre un an plus tôt. Dans la base de commandes, cela veut dire joindre cinq tables, construire un calendrier à la volée et se rappeler que les clients changent de région. Chaque nouvelle question demande de nouvelles jointures, et deux analystes obtiennent deux chiffres.

Un schéma en étoile règle cela en remodelant les données autour d'un processus métier : une table de faits de mesures au centre, des tables de dimensions qui les décrivent autour. Cet article conçoit pas à pas un schéma en étoile des ventes, de la granularité au SQL, y compris les dimensions à évolution lente, étoile contre flocon et les entrepôts en colonnes.

Ce qu'est un schéma en étoile

Un schéma en étoile a deux types de tables :

• Les tables de faits enregistrent des mesures d'un événement métier, comme une ligne de commande : des clés étrangères vers les dimensions plus des mesures numériques. Elles sont étroites et longues.
• Les tables de dimensions portent le contexte, le qui, quoi, où et quand : des tables larges, plates et courtes d'attributs textuels comme la catégorie ou la région.

Le Kimball Group, qui a nommé beaucoup de ces techniques, le décrit comme des tables de faits reliées à des tables de dimensions par des clés primaires et étrangères. Les recommandations de Power BI de Microsoft résument simplement l'intérêt : les tables de dimensions permettent de filtrer et de regrouper, les tables de faits permettent d'agréger.

La concevoir en quatre étapes

Le processus de conception dimensionnelle en quatre étapes de Kimball consiste en quatre décisions, dans cet ordre :

1. Choisir le processus métier : les ventes au détail, en magasin et sur une boutique en ligne.
2. Déclarer la granularité : une ligne par ligne de commande, c'est-à-dire un produit dans une commande. Kimball appelle la granularité un contrat contraignant, déclaré avant toute dimension ou tout fait.
3. Identifier les dimensions : date, produit, magasin, client et promotion, chacune avec une valeur par ligne de commande.
4. Identifier les faits : quantité, prix unitaire, remise, montant net et coût, tous vrais à cette granularité.

Partez de la granularité atomique, le niveau le plus bas que la source capture ; les synthèses viennent des lignes atomiques, jamais l'inverse. Ne mélangez jamais les granularités : des frais de port facturés par commande mais stockés sur chaque ligne sont comptés une fois par ligne. Répartissez-les sur les lignes avec une règle métier, comme le conseille Kimball, ou donnez-leur leur propre table de faits.

Les tables de l'exemple

Les dimensions sont volontairement plates : dim_product porte la marque, la sous-catégorie et la catégorie sur une seule ligne, et dim_store traite la boutique en ligne comme un magasin de plus avec channel = 'online'. order_number est une dimension dégénérée : le contexte de l'en-tête vit dans d'autres dimensions, il ne reste que le numéro, toujours utile pour regrouper des paniers.

Le prix unitaire est le piège classique : sommé sur les lignes, il ne veut rien dire, divisez plutôt des parties additives, comme dans SUM(net_amount) / SUM(quantity).

Clés de substitution et dimension de date

Chaque dimension reçoit une clé de substitution, un entier sans signification attribué en séquence à partir de 1, parce qu'une dimension qui suit l'historique a plusieurs lignes par clé naturelle et que les sources ne s'accordent souvent pas sur les clés. Gardez la clé naturelle (sku, store_code, customer_id) comme attribut pour retracer les lignes jusqu'à la source.

La dimension de date fait exception : stable et prévisible, sa clé peut donc être significative, comme 20261120, ce qui aide aussi le partitionnement. Filtrez et regroupez sur ses attributs (year, quarter, is_holiday), pas sur de l'arithmétique sur la clé. Vingt ans ne font qu'environ 7 305 lignes (20 × 365,25).

Les clés étrangères des faits ne doivent jamais être nulles : une clé nulle fait disparaître la ligne des jointures internes, et les totaux rétrécissent en silence. Utilisez des lignes de dimension par défaut pour les valeurs inconnues ou sans objet ; ici, promotion_key 0 signifie « Pas de promotion ».

Dimensions à évolution lente

Le type de dimension à évolution lente (SCD) décide de ce que montrent les rapports après le changement d'un attribut :

Les types 4 à 7 de Kimball ajoutent des mini-dimensions et des hybrides de ceux-ci.

Le type 2 est le cheval de bataille. Le client C-1042 déménage d'Austin à Denver le 20 novembre 2026 :

Les ventes antérieures gardent la clé 1001 et restent à Austin ; les ventes suivantes reçoivent 2417. Le chargement des faits choisit la ligne dont la plage contient la date de commande (valid_from <= order_date < valid_to). La définition du type 2 de Kimball demande au moins une date d'effet, une date d'expiration et un indicateur de ligne courante ; un valid_to très lointain plutôt que null garde les filtres de plage simples. Les snapshots dbt implémentent le type 2 pour vous.

Pour rapporter tout l'historique selon la ville actuelle, joignez la ligne courante avec le même customer_id. La capture de données de changement alimente bien le type 2, car chaque mise à jour arrive comme un événement ; voir le diagramme de change data capture.

Schéma en étoile, schéma en flocon et table unique

Kimball déconseille les dimensions en flocon : elles sont plus difficiles à parcourir pour les utilisateurs métier, peuvent nuire aux performances des requêtes, et une dimension aplatie contient exactement les mêmes informations. Les recommandations de Power BI vont dans le même sens.

Une table unique n'a besoin d'aucune jointure, et la compression en colonnes garde les valeurs répétées peu coûteuses. Le coût est la maintenance : les corrections deviennent des réécritures et les nouveaux attributs des reconstructions, donc générez-la à partir de l'étoile.

Une requête que le modèle facilite

SELECT d.year_month,
       p.category,
       SUM(f.net_amount)                                AS revenue,
       SUM(f.net_amount - f.cost_amount)                AS gross_margin,
       SUM(f.net_amount) / NULLIF(SUM(f.quantity), 0)   AS avg_selling_price
FROM fact_sales AS f
JOIN dim_date    AS d ON d.date_key    = f.date_key
JOIN dim_product AS p ON p.product_key = f.product_key
JOIN dim_store   AS s ON s.store_key   = f.store_key
WHERE d.year = 2026
  AND s.channel = 'online'
GROUP BY d.year_month, p.category
ORDER BY d.year_month, revenue DESC;

Chaque jointure va du fait vers une dimension sur une clé entière ; les filtres portent sur des attributs de dimension et les sommes sur des colonnes de faits. Une nouvelle question change le WHERE et le GROUP BY, pas les jointures, ce qui explique pourquoi les outils BI génèrent ces requêtes de façon fiable.

Performance dans les entrepôts en colonnes

Les entrepôts en colonnes ne lisent que les colonnes qu'une requête utilise, ce qui convient à une table de faits étroite, et les petites dimensions se joignent à faible coût : dans une jointure broadcast, BigQuery envoie la plus petite table à chaque worker. Avec des clés INT64 et des quantités à 8 octets logiques et des montants NUMERIC à 16, la requête ci-dessus lit 64 octets par ligne de commande dans la table de faits, soit environ 32 Go pour 500 millions de lignes, quelle que soit la largeur de la table.

• Élaguer par date. Partitionnez, clusterisez ou triez la table de faits sur date_key. BigQuery élague les partitions quand la colonne de partitionnement elle-même a un filtre constant ; les gros scans doivent donc aussi filtrer date_key avec des constantes, pas seulement dim_date.year.
• Déclarer les clés, même non appliquées. BigQuery utilise les clés primaires et étrangères déclarées NOT ENFORCED pour éliminer et réordonner les jointures, et Snowflake élimine les jointures pour les contraintes marquées RELY. Aucun des deux ne vérifie les données, et des contraintes violées peuvent renvoyer de mauvais résultats ; testez donc les clés dans le pipeline.
• Co-localiser dans Redshift. Les recommandations de Redshift sont de distribuer la table de faits et une dimension sur leur clé commune, d'utiliser la distribution ALL pour d'autres dimensions, ou de laisser AUTO choisir.

Les schémas en étoile vivent généralement dans la couche gold d'une architecture medallion ; le diagramme d'architecture de pipeline de données montre comment les données y arrivent, et ETL vs ELT où s'exécutent les transformations.

Le diagramme du schéma en étoile des ventes

Comment le lire :

• fact_sales au centre contient cinq clés étrangères, le order_number dégénéré, line_number et cinq mesures.
• Des pieds de corbeau sur chaque ligne : zéro ou plusieurs lignes de faits d'un côté, exactement une ligne de dimension de l'autre. Les étiquettes nomment les clés de jointure.
• Les cartes turquoise sont des dimensions, avec dim_date en haut et les autres à gauche et à droite.
• La carte ambre, dim_customer, conserve l'historique en SCD de type 2.
• Le bloc Markdown indique la granularité et les mesures que vous pouvez sommer.

Dessiner clairement les diagrammes en étoile

• Écrivez la granularité sur le diagramme. Les relecteurs la demandent en premier, et une granularité mal lue provoque des doubles comptages.
• Placez le fait au milieu et chaque dimension à un saut. Une ligne entre deux dimensions est un flocon ; dessinez-la volontairement ou aplatissez.
• Marquez les rôles des clés : PK, FK, NK pour les clés naturelles et DD pour les dimensions dégénérées.
• Montrez le type de SCD sur les dimensions qui conservent l'historique.
• Laissez de côté les jobs de chargement, les tables de staging et les colonnes sur lesquelles personne ne filtre.
• Dessinez un processus métier par diagramme. Quand des tables de faits partagent des dimensions conformes, ajoutez une matrice de bus : les processus en lignes, les dimensions en colonnes.

Voir l'exemple de diagramme ER pour la notation en pied de corbeau.

Le construire dans Archboard

1. Connectez-vous avec Google ou GitHub et créez un diagramme.
2. Si les tables existent, collez leurs instructions CREATE TABLE dans l'outil SQL vers diagramme ER, ou le DBML dans l'outil DBML vers diagramme ER, pour obtenir un premier brouillon.
3. Ajoutez une Entité du pack Entité-association nommée fact_sales, un attribut par ligne : cinq clés marquées FK, order_number marqué DD, line_number, puis les mesures. Donnez-lui un accent bleu.
4. Ajoutez cinq autres nœuds Entité : dim_date au-dessus du fait, dim_customer et dim_promotion à gauche, dim_product et dim_store à droite. Marquez les clés de substitution PK et les clés naturelles NK ; utilisez des accents turquoise, ambre pour dim_customer.
5. Reliez fact_sales à chaque dimension avec des pieds de corbeau, zéro ou plusieurs côté fait et exactement un côté dimension, et étiquetez chaque ligne avec sa clé.
6. Ajoutez des notes Texte du pack Base : une légende des clés, la règle du SCD de type 2, la clé de date intelligente, la ligne de promotion par défaut et la boutique en ligne.
7. Ajoutez un bloc Markdown du pack Base sous le fait, avec la granularité et les règles d'additivité.

L'exemple utilise 7 nœuds de diagramme et reste dans les limites de l'offre Free.

FAQ

Qu'est-ce qu'un schéma en étoile, en termes simples ? Une table de faits centrale de mesures, comme des montants de ventes, reliée à des tables de dimensions qui les décrivent, comme la date, le produit et le client. Dessiné, il ressemble à une étoile.

Quelle est la différence entre une table de faits et une table de dimensions ? Une table de faits stocke des mesures d'événements à une granularité déclarée, plus des clés étrangères. Une table de dimensions stocke les attributs sur lesquels vous filtrez et regroupez, comme la catégorie ou la région.

Quelle est la différence entre un schéma en étoile et un schéma en flocon ? Une étoile garde chaque dimension dans une table plate ; un flocon la normalise en chaînes comme produit, sous-catégorie et catégorie. Les étoiles demandent moins de jointures et sont plus faciles à utiliser.

Un schéma en étoile est-il normalisé ? Pas entièrement. Les tables de dimensions sont volontairement dénormalisées, répétant des valeurs comme les noms de catégories sur chaque ligne de produit, tandis que la table de faits reste étroite.

Les schémas en étoile comptent-ils encore dans les entrepôts en colonnes ? Oui. Les tables larges se scannent à faible coût dans les moteurs en colonnes, mais une étoile vous donne toujours un seul endroit pour corriger un attribut, un historique grâce aux dimensions à évolution lente et des réponses cohérentes d'un rapport à l'autre.

Commencez gratuitement — un siège, sans carte bancaire.