Cheatsheet - Phase 2

1. Normalisation - Reference Rapide (1NF a 5NF)

2. Kimball Star Schema - Checklist

Les 4 Etapes Kimball

Types de Tables de Faits

Template SQL - Star Schema

-- Table de dimension
CREATE TABLE dim_produit (
    produit_sk    INT PRIMARY KEY,       -- Surrogate key
    produit_nk    VARCHAR(20) NOT NULL,  -- Natural/business key
    nom           VARCHAR(200),
    categorie     VARCHAR(100),
    sous_categorie VARCHAR(100),
    marque        VARCHAR(100),
    prix_unitaire DECIMAL(10,2),
    date_debut    DATE,
    date_fin      DATE,
    est_actuel    BOOLEAN DEFAULT TRUE
);

-- Table de faits
CREATE TABLE fait_ventes (
    date_sk       INT REFERENCES dim_date(date_sk),
    produit_sk    INT REFERENCES dim_produit(produit_sk),
    client_sk     INT REFERENCES dim_client(client_sk),
    magasin_sk    INT REFERENCES dim_magasin(magasin_sk),
    quantite      INT,
    montant_ht    DECIMAL(12,2),
    montant_ttc   DECIMAL(12,2),
    remise        DECIMAL(10,2),
    cout          DECIMAL(12,2),
    marge         DECIMAL(12,2)
);

3. Slowly Changing Dimensions (SCD)

Template SCD Type 2

-- Insertion du nouvel enregistrement
INSERT INTO dim_client (client_sk, client_nk, nom, ville, date_debut, date_fin, est_actuel)
VALUES (nextval('seq_client'), 'C-1001', 'Jean Dupont', 'Lyon',
        CURRENT_DATE, '9999-12-31', TRUE);

-- Fermeture de l'ancien enregistrement
UPDATE dim_client
SET date_fin = CURRENT_DATE - 1, est_actuel = FALSE
WHERE client_nk = 'C-1001'
  AND est_actuel = TRUE
  AND client_sk != currval('seq_client');

4. Data Vault 2.0 - Composants

CREATE TABLE hub_client (
  hub_client_hk  CHAR(32) PRIMARY KEY,
  client_bk      VARCHAR(50) NOT NULL,
  load_date      TIMESTAMP NOT NULL,
  record_source  VARCHAR(100)
);

CREATE TABLE lnk_client_commande (
  lnk_cli_cmd_hk   CHAR(32) PRIMARY KEY,
  hub_client_hk     CHAR(32) NOT NULL,
  hub_commande_hk   CHAR(32) NOT NULL,
  load_date         TIMESTAMP NOT NULL,
  record_source     VARCHAR(100)
);

CREATE TABLE sat_client_details (
  hub_client_hk  CHAR(32) NOT NULL,
  load_date      TIMESTAMP NOT NULL,
  load_end_date  TIMESTAMP DEFAULT '9999-12-31',
  hash_diff      CHAR(32) NOT NULL,
  nom            VARCHAR(200),
  email          VARCHAR(200),
  ville          VARCHAR(100),
  record_source  VARCHAR(100),
  PRIMARY KEY (hub_client_hk, load_date)
);

5. DAMA-DMBOK 2.0 - 11 Knowledge Areas

6. Roles de la Gouvernance des Donnees

7. ETL vs ELT vs Reverse ETL

8. Medallion Architecture (Bronze / Silver / Gold)

Pipeline type

[Sources]     [Bronze]           [Silver]              [Gold]
APIs     -->  raw_orders    -->  clean_orders     -->  fact_ventes
DBs      -->  raw_clients   -->  clean_clients    -->  dim_client
Files    -->  raw_produits  -->  clean_produits   -->  dim_produit
Streams  -->  raw_events    -->  clean_events     -->  kpi_daily_sales

9. Data Mesh - 4 Principes

10. Templates SQL DDL

Table de Dimension (Star Schema)

CREATE TABLE dim_date (
    date_sk          INT PRIMARY KEY,
    date_complete    DATE NOT NULL UNIQUE,
    jour             INT,
    mois             INT,
    annee            INT,
    trimestre        INT,
    semaine_iso      INT,
    nom_jour         VARCHAR(10),     -- Lundi, Mardi...
    nom_mois         VARCHAR(15),     -- Janvier, Fevrier...
    est_weekend      BOOLEAN,
    est_ferie        BOOLEAN,
    saison           VARCHAR(15)
);

Table de Faits (Star Schema)

CREATE TABLE fait_ventes (
    date_sk       INT NOT NULL REFERENCES dim_date(date_sk),
    produit_sk    INT NOT NULL REFERENCES dim_produit(produit_sk),
    client_sk     INT NOT NULL REFERENCES dim_client(client_sk),
    magasin_sk    INT NOT NULL REFERENCES dim_magasin(magasin_sk),
    promo_sk      INT REFERENCES dim_promotion(promo_sk),
    -- Mesures additives
    quantite      INT NOT NULL,
    montant_ht    DECIMAL(12,2) NOT NULL,
    montant_ttc   DECIMAL(12,2) NOT NULL,
    remise        DECIMAL(10,2) DEFAULT 0,
    cout_achat    DECIMAL(12,2),
    marge         DECIMAL(12,2),
    -- Mesures semi-additives
    stock_fin     INT
);

Hub (Data Vault)

CREATE TABLE hub_produit (
    hub_produit_hk   CHAR(32) PRIMARY KEY,  -- MD5(produit_bk)
    produit_bk       VARCHAR(50) NOT NULL,
    load_date        TIMESTAMP NOT NULL,
    record_source    VARCHAR(100) NOT NULL
);

Link (Data Vault)

CREATE TABLE lnk_vente (
    lnk_vente_hk     CHAR(32) PRIMARY KEY,
    hub_client_hk     CHAR(32) NOT NULL REFERENCES hub_client(hub_client_hk),
    hub_produit_hk    CHAR(32) NOT NULL REFERENCES hub_produit(hub_produit_hk),
    hub_date_hk       CHAR(32) NOT NULL REFERENCES hub_date(hub_date_hk),
    load_date         TIMESTAMP NOT NULL,
    record_source     VARCHAR(100) NOT NULL
);

Satellite (Data Vault)

CREATE TABLE sat_produit_details (
    hub_produit_hk    CHAR(32) NOT NULL,
    load_date         TIMESTAMP NOT NULL,
    load_end_date     TIMESTAMP DEFAULT '9999-12-31 23:59:59',
    hash_diff         CHAR(32) NOT NULL,
    nom_produit       VARCHAR(200),
    categorie         VARCHAR(100),
    prix_unitaire     DECIMAL(10,2),
    poids_kg          DECIMAL(8,3),
    record_source     VARCHAR(100),
    PRIMARY KEY (hub_produit_hk, load_date)
);

Requete Analytique Type

-- Top 10 produits par chiffre d'affaires mensuel
SELECT
    d.annee,
    d.nom_mois,
    p.categorie,
    p.nom AS produit,
    SUM(f.montant_ttc)   AS ca_total,
    SUM(f.quantite)      AS qte_totale,
    SUM(f.marge)         AS marge_totale,
    ROUND(SUM(f.marge)/NULLIF(SUM(f.montant_ht),0)*100, 1) AS taux_marge_pct
FROM fait_ventes f
JOIN dim_date d    ON f.date_sk    = d.date_sk
JOIN dim_produit p ON f.produit_sk = p.produit_sk
WHERE d.annee = 2024
GROUP BY d.annee, d.nom_mois, d.mois, p.categorie, p.nom
ORDER BY d.mois, ca_total DESC
FETCH FIRST 10 ROWS ONLY;

11. Inmon vs Kimball vs Data Vault - Decision

12. Lambda & Kappa Architecture

Lambda:  Sources --> [Batch Layer] + [Speed Layer] --> [Serving Layer]
Kappa:   Sources --> [Kafka Log] --> [Stream Processing] --> [Serving Layer]

13. OBT & Activity Schema

14. Master Data Management (MDM)

Match & Merge

-- Match probabiliste avec pg_trgm (PostgreSQL)
SELECT a.id, b.id,
       similarity(a.nom, b.nom) AS score
FROM clients a JOIN clients b ON a.id < b.id
WHERE similarity(a.nom, b.nom) > 0.7
   OR similarity(a.email, b.email) > 0.8;

15. Metadata & Data Catalog

Outils de Catalogage

16. RGPD & Compliance - Reference Rapide

8 Droits RGPD

Pseudonymisation vs Anonymisation

-- Pseudonymisation (reversible, reste sous RGPD)
SELECT md5(client_id || 'salt') AS pseudo_id, segment, ville FROM clients;

-- Anonymisation (irreversible, hors RGPD)
UPDATE clients SET nom='ANONYMISE', email=NULL WHERE date_suppression IS NOT NULL;

17. Data Fabric & Semantic Layer

18. dbt - Structure Projet & Commandes

my_project/
├── dbt_project.yml        # Config projet
├── models/
│   ├── staging/           # Silver (1:1 sources)
│   │   ├── _sources.yml
│   │   └── stg_orders.sql
│   ├── intermediate/      # Logique intermediaire
│   └── marts/             # Gold (business models)
│       └── fact_revenue.sql
├── tests/                 # Tests custom SQL
├── macros/                # Fonctions Jinja
├── seeds/                 # CSV de reference
└── snapshots/             # SCD Type 2 auto

Commandes Essentielles