Projet Data Warehouse avec PostgreSQL

Informations générales

Durée : 6 semaines et quelques (travail personnel)
Type : Projet individuel
Rendu : Code SQL + Documentation technique
Date limite : 🚨 09/11/2025 23h59 (heure de Paris) (au-delà = zéro) 🚨
Note : /20 points Lien pour déposer votre travail : ici (fichier ZIP uniquement) (le mot de passe est mon nom de famille en minuscule)

Contexte métier

RetailChain est une chaîne de magasins européenne souhaitant créer un data warehouse pour analyser ses performances commerciales.

Données disponibles :

• 50 magasins répartis dans 5 pays européens
• Base clients de 100,000+ personnes avec historique d’achats
• Catalogue de 1,000+ produits organisés en catégories
• 500,000+ transactions sur une période de 2 ans

Objectifs analytiques :

• Analyser les ventes par période, géographie, et produit
• Identifier les tendances saisonnières et comportements clients
• Calculer les performances comparatives des magasins
• Supporter des analyses ad-hoc pour la direction

Livrables attendus

Structure obligatoire du rendu

NOM_Prenom_ProjetDW/
├── sql/
│   ├── 01_setup.sql
│   ├── 02_create_tables.sql
│   ├── 03_sample_data.sql
│   ├── 04_etl_functions.sql
│   ├── 05_optimizations.sql
│   └── 06_analytics.sql
├── docs/
│   ├── architecture.md
│   ├── choix_techniques.md
│   └── guide_utilisation.md
└── README.md

Compressez le répertoir dans un fichier ZIP (pas RAR ou autre) avant de l’envoyer.

Contenu minimum requis

Code SQL :

• Schéma dimensionnel complet (étoile ou flocon)
• Processus ETL fonctionnels
• Données de test cohérentes
• Requêtes analytiques métier

Documentation :

• Justification des choix de modélisation
• Explication des stratégies ETL et SCD
• Guide d’utilisation des analyses créées

Starter Kit

Structure des tables (OBLIGATOIRE)

-- 01_setup.sql - Template fourni
DROP SCHEMA IF EXISTS staging CASCADE;
DROP SCHEMA IF EXISTS dwh CASCADE;
DROP SCHEMA IF EXISTS marts CASCADE;

CREATE SCHEMA staging;
CREATE SCHEMA dwh;
CREATE SCHEMA marts;

-- 02_create_tables.sql - Structure minimum à respecter

-- Dimension Date (structure EXACTE requise)
CREATE TABLE dwh.dim_date (
    date_key INTEGER PRIMARY KEY,
    full_date DATE NOT NULL,
    year_number INTEGER NOT NULL,
    month_number INTEGER NOT NULL,
    month_name VARCHAR(20),
    quarter_number INTEGER NOT NULL,
    day_of_week INTEGER NOT NULL,
    is_weekend BOOLEAN NOT NULL
    -- Ajoutez vos colonnes additionnelles ici
);

-- Dimension Customer (colonnes minimum)
CREATE TABLE dwh.dim_customer (
    customer_key INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_business_key INTEGER NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    country VARCHAR(50)
    -- Ajoutez gestion SCD et autres colonnes
);

-- Dimension Store (colonnes minimum)
CREATE TABLE dwh.dim_store (
    store_key INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    store_business_key INTEGER NOT NULL UNIQUE,
    store_name VARCHAR(200),
    city VARCHAR(100),
    country VARCHAR(50)
    -- Ajoutez autres colonnes
);

-- Dimension Product (colonnes minimum)
CREATE TABLE dwh.dim_product (
    product_key INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    product_business_key INTEGER NOT NULL UNIQUE,
    product_name VARCHAR(300),
    category VARCHAR(100),
    subcategory VARCHAR(100)
    -- Ajoutez autres colonnes
);

-- Fact Sales (structure EXACTE requise)
CREATE TABLE dwh.fact_sales (
    sales_key INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    date_key INTEGER NOT NULL,
    customer_key INTEGER NOT NULL,
    store_key INTEGER NOT NULL,
    product_key INTEGER NOT NULL,
    transaction_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    
    CONSTRAINT fk_sales_date FOREIGN KEY (date_key) REFERENCES dwh.dim_date(date_key),
    CONSTRAINT fk_sales_customer FOREIGN KEY (customer_key) REFERENCES dwh.dim_customer(customer_key),
    CONSTRAINT fk_sales_store FOREIGN KEY (store_key) REFERENCES dwh.dim_store(store_key),
    CONSTRAINT fk_sales_product FOREIGN KEY (product_key) REFERENCES dwh.dim_product(product_key)
);

Générateur de données fourni

-- Fonction pour dim_date (FOURNIE - ne pas modifier)
CREATE OR REPLACE FUNCTION dwh.populate_test_date(start_date DATE, end_date DATE)
RETURNS VOID AS $$
DECLARE
    curr_date DATE;
BEGIN
    curr_date := start_date;
    WHILE curr_date <= end_date LOOP
        INSERT INTO dwh.dim_date (
            date_key, full_date, year_number, month_number, 
            month_name, quarter_number, day_of_week, is_weekend
        ) VALUES (
            TO_CHAR(curr_date, 'YYYYMMDD')::INTEGER,
            curr_date,
            EXTRACT(YEAR FROM curr_date),
            EXTRACT(MONTH FROM curr_date),
            TRIM(TO_CHAR(curr_date, 'Month')),
            EXTRACT(QUARTER FROM curr_date),
            EXTRACT(DOW FROM curr_date),
            EXTRACT(DOW FROM curr_date) IN (0,6)
        );
        curr_date := curr_date + INTERVAL '1 day';
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Génération automatique des dates
SELECT dwh.populate_test_date('2022-01-01', '2023-12-31');

Spécifications techniques

Contraintes obligatoires

• PostgreSQL version 16+
• Respect de la structure de fichiers fournie
• Tables principales avec noms et colonnes minimum spécifiés
• Volume minimum : 1000 clients, 100k transactions, 2 ans de dates
• Au moins 3 pays représentés dans les données

Fonctionnalités attendues par niveau

Niveau Suffisant (10-12/20)

• Modèle dimensionnel de base fonctionnel
• ETL simple qui charge les données
• Quelques requêtes analytiques basiques
• Documentation minimale

Niveau Satisfaisant (13-15/20)

• Modélisation cohérente avec justifications
• ETL avec gestion d’erreurs basique
• Gestion SCD Type 1 OU Type 2
• Optimisations de base (quelques index)
• Requêtes analytiques avec GROUP BY et agrégations
• Documentation technique claire

Niveau Bon (16-17/20)

• Modélisation sophistiquée et justifiée
• ETL robuste avec logs et gestion d’erreurs
• Gestion SCD Type 1 ET Type 2
• Optimisations multiples (index, vues matérialisées)
• Requêtes avec window functions et CTE
• Documentation complète et professionnelle

Niveau Excellent (18-20/20)

• Innovation dans la modélisation ou processus
• ETL avancé avec monitoring et métriques
• Optimisations avancées (partitioning)
• Analyses complexes (cohortes, RFM, saisonnalité)
• Tests automatisés de qualité
• Code réutilisable et maintenable

Grille d’évaluation détaillée

1. Architecture et Modélisation (5 points)

2. Processus ETL (4 points)

3. Optimisations et Performance (3 points)

4. Analyses et Requêtes (4 points)

5. Documentation (3 points)

6. Fonctionnalités avancées (1 point bonus)

Note finale = min(20, Total des points)

Tests automatiques (Information)

Votre projet sera soumis à des tests automatiques vérifiant :

• La structure et nomenclature des tables
• L’intégrité référentielle des données
• Le volume minimum de données
• Le bon fonctionnement des requêtes de base
• La présence de fonctionnalités avancées

Ces tests représentent environ 40% de la note finale.

Conseils pour réussir

Planification

1. Semaine 1 : Setup + modélisation + génération données
2. Semaine 2 : ETL + SCD + chargement complet
3. Semaine 3 : Optimisations + requêtes analytiques
4. Semaine 4 : Documentation + tests + finalisation
5. Semaine 5 : Finir ce que vous n’avez pas pu finir.
6. Semaine 6 : Il est vraiment temps de finir ce projet !

Technique

• Commencez simple puis enrichissez progressivement
• Testez fréquemment vos scripts
• Documentez au fur et à mesure
• Privilégiez la robustesse à la complexité

Méthodologie

• Suivez les 4 étapes de Kimball pour la modélisation
• Implémentez d’abord les ETL de base
• Optimisez seulement après avoir un système fonctionnel

Utilisation de scripts externes

Autorisé :

• Scripts de génération de données (Python, Shell, etc.)
• Outils de documentation automatique
• Scripts de test et validation

Conditions :

• Le coeur fonctionnel DOIT être en SQL PostgreSQL
• Tous les scripts externes doivent être fournis et documentés
• Le projet doit fonctionner sans les scripts externes (données minimum en SQL)
• Justification obligatoire dans la documentation

Interdit :

• ETL externes remplaçant les fonctions PL/pgSQL
• Requêtes analytiques en dehors de PostgreSQL
• Outils modifiant l’architecture du data warehouse

Remarques

Q: Puis-je modifier la structure des tables obligatoires ? R: Vous pouvez ajouter des colonnes mais pas supprimer celles spécifiées.

Q: Quel volume de données dois-je générer ? R: Minimum 1000 clients, 100k transactions, mais plus c’est mieux pour tester les performances.

Q: Comment gérer les SCD si ce n’est pas dans le starter kit ? R: Comme vous voulez, mais il faut justifier dans la documentation !

Bon travail !