Modélisation de données

Que fait Modélisation de données ?

Cibles : PostgreSQL, BigQuery, Snowflake. Transformation : dbt. Prototypage local : DuckDB 2.0. Pour les aspects mécaniques de dbt (configuration de modèle, YAML de tests, snapshots, couche sémantique, migration v2), utilisez les compétences officielles dbt Labs importées par la compétence de-vendors de ce dépôt. Cette compétence décide quoi modéliser, pas comment dbt l'écrit.

Procédure

1. Énoncer la question métier et le consommateur (tableau de bord, feature ML, clôture financière).
2. Écrire le grain en une phrase avant toute colonne : « Une ligne par ligne de commande par jour. » Si la phrase a besoin de « ou », ce sont deux tables.
3. Choisir le type de fait (ci-dessous), puis lister les dimensions qui sont vraies à ce grain.
4. Nommer la clé de grain (colonnes naturelles ou un hachage de celles-ci). Elle devient unique + not_null.
5. Prototyper dans DuckDB sur un échantillon, exécuter les vérifications de fan-out, puis porter.
6. Ajouter les tests listés sous « Quoi tester » avant de déclarer terminé.

Grain

• Le grain d'abord, les colonnes ensuite. Chaque mesure et chaque clé étrangère doit être à valeur unique au niveau du grain ; une colonne qui ne l'est pas appartient à une autre table, car elle multiplie silencieusement les lignes.
• Conserver le grain le plus bas supporté par la source. Les agrégats sont dérivables du détail, le détail n'est pas dérivable des agrégats. Construire les rollups comme des marts séparés.
• Ne jamais mélanger les grains dans une même table (totaux d'en-tête de commande répétés sur les lignes de commande). Les montants d'en-tête sommés sur les lignes sont double-comptés ; modéliser les faits d'en-tête et de ligne séparément.

Faits vs dimensions

• Fait : un événement ou une mesure ; numérique, additif si possible, clé par des clés de dimension plus des dimensions dégénérées (numéro de commande).
• Dimension : contexte descriptif utilisé pour filtrer et grouper. Large et dénormalisé est acceptable.
• Classifier chaque mesure : additive (somme sur toutes les dimensions), semi-additive (soldes : pas dans le temps), non-additive (ratios : stocker numérateur et dénominateur, diviser après agrégation).
• Types de faits :
  • Transaction : une ligne par événement. Choix par défaut.
  • Snapshot périodique : une ligne par entité par période (solde quotidien). À utiliser lorsque l'état compte et que les événements sont rares ou tardifs.
  • Snapshot cumulatif : une ligne par instance de processus avec une colonne de date par jalon, mise à jour sur place. À utiliser pour les pipelines avec un cycle de vie connu (commande passée, expédiée, livrée). S'attendre à des mises à jour ; rendre le chargement idempotent.
  • Fait sans mesure : événements ou couverture sans mesure (présence, éligibilité). Compter les lignes.

Forme du schéma

• Étoile par défaut : un fait, dimensions plates. Flocon (dimensions normalisées) seulement lorsqu'une sous-dimension est partagée et volumineuse ou maintenue par une autre équipe ; les jointures supplémentaires nuisent à la lisibilité et à la compatibilité avec les outils BI.
• Dimensions conformes : une dim_customer, dim_date partagée par chaque fait, mêmes clés et significations d'attributs. Les construire une fois ; une deuxième table client avec des règles différentes est un défaut, car les rapports croisés entre faits ne concordent alors pas.
• Dimensions multi-rôles (order_date, ship_date) réutilisent une dim_date via des colonnes FK séparées.
• Dimensions junk/mini pour les indicateurs à faible cardinalité ; ne pas laisser 8 booléens sur le fait.
• Membre inconnu : conserver une ligne (clé -1 ou hachage de 'unknown') dans chaque dimension pour que les faits arrivant tardivement conservent leurs lignes. Les jointures internes aux dimensions suppriment silencieusement les faits ; utiliser des jointures gauches avec un coalesce vers la clé inconnue.

Clés

• Clé naturelle : l'identifiant métier du système source. Toujours la conserver comme attribut.
• Clé substitut : en ajouter une lorsque les clés naturelles sont composites, peuvent changer, proviennent de plusieurs sources, ou que la dimension est SCD2 (une entité a plusieurs versions). Dans les entrepôts de style dbt, préférer un hachage déterministe de la clé naturelle (plus valid_from pour SCD2) plutôt que des séquences, afin que les ré-exécutions et les builds parallèles soient cohérents.
• Ne pas exposer les clés substituts aux utilisateurs métier comme identifiants ; elles changent lors d'une reconstruction.

Dimensions à évolution lente

• Type 1 (écrasement) : lorsque l'historique n'a pas de valeur analytique ou que le changement est une correction.
• Type 2 (nouvelle ligne par version, valid_from / valid_to / is_current) : lorsque les faits doivent être rapportés avec la valeur de l'attribut au moment de l'événement (niveau client lors de l'achat).
• Les snapshots suffisent lorsque la source ne conserve pas d'historique et que vous avez seulement besoin de capturer les changements à venir ; ils sont le mécanisme de capture, la dimension SCD2 reste un modèle par-dessus. Si la source a déjà un historique ou un journal d'événements, dériver les plages de validité à partir de celui-ci plutôt que de snapshotter.
• Joindre les faits aux SCD2 sur la clé plus event_ts >= valid_from and event_ts < valid_to (intervalles semi-ouverts, donc pas de chevauchement). Tester l'absence de plages chevauchantes ou avec trous par clé naturelle.
• Type 3 ou type 2 par attribut : uniquement lorsqu'une seule colonne « valeur précédente » répond à la question ; sinon ignorer.

Fan-out et double comptage

Cause : joindre des tables à des grains non appariés, de sorte que le côté « un » est en réalité plusieurs. Symptômes : totaux supérieurs à la source, count(*) augmente après une jointure gauche, les moyennes se déplacent.

• Détecter : comparer count(*) et count(distinct grain_key) avant et après chaque jointure ; ils doivent être égaux après une jointure à une dimension. Tester l'unicité de la clé de jointure du côté dimension (select key from dim group by 1 having count(*) > 1).
• Réconcilier : la somme de la mesure dans le mart est égale à la somme dans la source pour le même filtre.
• Corriger en agrégeant le côté multiple au grain du fait avant de joindre, ou en passant par une table pont avec des poids d'allocation ; jamais distinct sur le résultat pour le cacher.
• Plusieurs-à-plusieurs (client à compte, produit à catégorie) : table pont avec un facteur de pondération, ou choisir un principal et le mentionner.
• SQL pour ces vérifications : voir references/fan-out-checks.md.

Layering

• raw/staging : un modèle de staging par table source ; renommer, caster, dédoublonner, pas de jointures, pas de logique métier. Garder raw immuable.
• intermédiaire : jointures, remodelage, logique partagée réutilisée par 2+ marts. Non exposé à la BI.
• marts : faits et dimensions, un processus métier par mart, documenté, testé, contracté. Seuls les marts sont publics.
• Les dépendances circulent dans un sens ; les marts ne lisent pas raw, et staging ne lit jamais staging.
• Matérialiser staging en vues, marts en tables (incrémentales quand volumineuses et que la clé de grain est stable).

Nomination

• stg_<source>__<entité>, int_<entité>_<verbe>, fct_<processus>, dim_<entité> ; snake_case ; pluriel ou singulier de manière cohérente (choisir un).
• Clés <entité>_id (naturelle) et <entité>_key (substitut) ; horodatages <événement>_at en UTC ; dates <événement>_date ; booléens is_ / has_ ; montants portent une unité ou une devise (amount_usd).

Quoi tester

• Clé de grain : unique + not_null, sur chaque modèle ; pour les grains composites, tester la combinaison.
• Chaque FK sur un fait : not_null (ou membre inconnu) et un test de relation vers la dimension.
• Valeurs acceptées sur les colonnes de statut/type ; une nouvelle valeur échoue bruyamment plutôt que d'être mal catégorisée.
• Réconciliation du nombre de lignes et de la somme de chaque mart avec sa source, avec une tolérance énoncée (zéro pour la finance).
• SCD2 : pas de plages chevauchantes, exactement une ligne courante par clé naturelle.
• Fraîcheur sur les sources. Casser chaque test une fois sur un échantillon de données pour prouver qu'il peut échouer.

Révision de schéma OLTP (PostgreSQL)

• Normaliser en 3NF pour les tables transactionnelles ; dénormaliser uniquement pour un chemin de lecture mesuré. Chaque table a une clé primaire.
• Indexer les colonnes de clé étrangère : Postgres ne le fait pas automatiquement, et les FK non indexées rendent les suppressions parentes et les jointures coûteuses (scan de la table enfant).
• Utiliser timestamptz, numeric pour l'argent, text plutôt que des limites arbitraires de varchar ; appliquer les règles avec NOT NULL, CHECK, UNIQUE et les contraintes FK plutôt que par le seul code applicatif.
• Préférer bigint identity ou UUIDv7 plutôt que UUIDv4 aléatoire pour les tables volumineuses avec beaucoup d'insertions (localité d'index).
• Multi-tenant : mettre tenant_id sur chaque table propriétaire du tenant, en faire la colonne de tête des clés et index composites, l'inclure dans les FK (FK composite) pour rendre les références croisées entre tenants impossibles, et la soutenir avec une sécurité au niveau des lignes. Schéma partagé avec tenant_id par défaut ; schéma par tenant ou base par tenant uniquement pour un isolement fort ou des besoins de restauration par tenant.
• Suppressions logiques : index uniques partiels (where deleted_at is null) pour que l'unicité soit maintenue.

Pièges et modes de défaillance observés : references/gotchas.md