Data With Baraa / données fictives

Entrepôt de données SQL

CRM + ERP → bronze → silver → gold

Deux systèmes, six fichiers, trois couches : les ventes consolidées en un schéma en étoile qu'on interroge directement.

Exécuté le 27 septembre 2026 · 5 contrôles gold · 0 anomalie

Réalisé en suivant le cours SQL Data Warehouse de Data With Baraa. Ce que j'y ai ajouté ou modifié :

Adaptation complète en français : scripts, commentaires, libellés des données, catalogue et conventions de nommage.

Dépôt GitHub ↗

Sources

CRM + ERP

3 fichiers CRM

3 fichiers ERP

Bronze

6 tables brutes, chargées par BULK INSERT

Silver

6 tables nettoyées par silver.load_silver

Gold

5 vues : schéma en étoile et reporting

Usage

Analyses

3 requêtes publiées

0 anomalie en gold

Architecture Medallion : chaque couche est une médaille. Le bronze garde les données telles qu'elles arrivent, l'argent les nettoie, l'or les organise pour le métier. Volumes mesurés le 27 septembre 2026.

Le système

Suivre chaque table

Survolez une table : son origine et tout ce qu'elle alimente s'allument, dans la couleur de leur couche.

Fig. 01

Lignage · volumes du 27 septembre 2026

Sources
Exports CSV de deux systèmes
- crm/cust_info.csv
- crm/prd_info.csv
- crm/sales_details.csv
- erp/CUST_AZ12.csv
- erp/LOC_A101.csv
- erp/PX_CAT_G1V2.csv
Bronze
Données brutes, telles quelles
- crm_cust_info18 493
- crm_prd_info397
- crm_sales_details60 398
- erp_cust_az1218 483
- erp_loc_a10118 484
- erp_px_cat_g1v237
Silver
Nettoyées, standardisées, normalisées
- crm_cust_info18 484
- crm_prd_info397
- crm_sales_details60 398
- erp_cust_az1218 483
- erp_loc_a10118 484
- erp_px_cat_g1v237
Gold
Prêtes pour le métier
- dim_customers18 484
- dim_products295
- fact_sales60 398
- report_customers18 482
- report_products130

fact_sales récupère aussi les clés des deux dimensions.

L'histoire · 1

Le modèle en chiffres

Un vendeur de vélos et d'accessoires, des ventes de fin 2010 à début 2014. Tous les chiffres sortent des vues gold.

Chiffre d'affaires: 29 356 250; unités monétaires entières
Commandes: 27 659
Clients: 18 484
Produits actifs: 295

Requête « indicateurs clés », exécutée le 27 septembre 2026.

Voir la requête et tous les indicateurs

measure_name	measure_value
Chiffre d'affaires total	29 356 250
Quantité totale vendue	60 423
Prix de vente moyen	486
Nombre de commandes	27 659
Nombre de produits	295
Nombre de clients	18 484
Nombre de clients ayant commandé	18 484

Voir la requête SQL

scripts/analytics/analyses_metier.sql

SELECT 'Chiffre d''affaires total' AS measure_name, SUM(sales_amount) AS measure_value FROM gold.fact_sales
UNION ALL SELECT 'Quantité totale vendue', SUM(quantity) FROM gold.fact_sales
UNION ALL SELECT 'Prix de vente moyen', AVG(price) FROM gold.fact_sales
UNION ALL SELECT 'Nombre de commandes', COUNT(DISTINCT order_number) FROM gold.fact_sales
UNION ALL SELECT 'Nombre de produits', COUNT(product_key) FROM gold.dim_products
UNION ALL SELECT 'Nombre de clients', COUNT(customer_key) FROM gold.dim_customers
UNION ALL SELECT 'Nombre de clients ayant commandé', COUNT(DISTINCT customer_key) FROM gold.fact_sales;

L'histoire · 2

Un client, une ligne

Le CRM contient des doublons et des clés vides. Silver garde l'enregistrement le plus récent de chaque client et écarte les autres.

18 493lignes clients en bronze

18 484clients en silver

Extrait de silver.load_silver : clients

SELECT
    cst_id,
    cst_key,
    TRIM(cst_firstname) AS cst_firstname,
    TRIM(cst_lastname)  AS cst_lastname,
    CASE
        WHEN UPPER(TRIM(cst_marital_status)) = 'S' THEN 'Célibataire'
        WHEN UPPER(TRIM(cst_marital_status)) = 'M' THEN 'Marié(e)'
        ELSE 'n/a'
    END AS cst_marital_status,
    CASE
        WHEN UPPER(TRIM(cst_gndr)) = 'F' THEN 'Féminin'
        WHEN UPPER(TRIM(cst_gndr)) = 'M' THEN 'Masculin'
        ELSE 'n/a'
    END AS cst_gndr,
    cst_create_date
FROM (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY cst_id ORDER BY cst_create_date DESC) AS flag_last
    FROM bronze.crm_cust_info
    WHERE cst_id IS NOT NULL
) t
WHERE flag_last = 1; -- l'enregistrement le plus récent par client

L'histoire · 3

Une entreprise de vélos

Quelle part du chiffre d'affaires chaque catégorie porte-t-elle ?

96,5 %du chiffre d'affaires vient des vélos

Fig. 02

analyses_metier.sql · 27 septembre 2026

category	total_sales	overall_sales	percentage_of_total
Bikes	28 316 272	29 356 250	96.46
Accessories	700 262	29 356 250	2.39
Clothing	339 716	29 356 250	1.16

Voir la requête SQL

scripts/analytics/analyses_metier.sql

WITH category_sales AS (
    SELECT p.category, SUM(f.sales_amount) AS total_sales
    FROM gold.fact_sales f
    LEFT JOIN gold.dim_products p ON p.product_key = f.product_key
    GROUP BY p.category
)
SELECT
    category,
    total_sales,
    SUM(total_sales) OVER () AS overall_sales,
    CAST(CAST(total_sales AS FLOAT) * 100 / SUM(total_sales) OVER () AS DECIMAL(5, 2)) AS percentage_of_total
FROM category_sales
ORDER BY total_sales DESC;

Les vélos font 96,5 % du chiffre d'affaires : l'activité dépend presque entièrement d'une seule catégorie.

L'histoire · 4

Peu de clients, beaucoup de ventes

Comment les clients se répartissent-ils entre VIP, réguliers et nouveaux ?

8,9 %des clients sont VIP

36,7 %des ventes

Fig. 03

analyses_metier.sql · 27 septembre 2026

customer_segment	total_customers	total_sales	avg_order_value
Nouveau	14 629	11 086 797	601
Régulier	2 200	7 503 991	1 682
VIP	1 653	10 760 470	2 633

Voir la requête SQL

scripts/analytics/analyses_metier.sql

SELECT
    customer_segment,
    COUNT(*)             AS total_customers,
    SUM(total_sales)     AS total_sales,
    AVG(avg_order_value) AS avg_order_value
FROM gold.report_customers
GROUP BY customer_segment
ORDER BY total_customers DESC;

Les 1 653 clients VIP (8,9 % des 18 482 clients de la vue) réalisent 36,7 % des ventes, avec un panier moyen plus de quatre fois supérieur à celui des nouveaux clients. VIP : au moins 12 mois d'historique et plus de 5 000 de ventes.

Sous le capot

Le schéma en étoile

Une table de faits au grain de la ligne de commande, reliée à deux dimensions par des clés de substitution.

Fig. 04

Vues SQL Server · clés de substitution par ROW_NUMBER()

Dimension

gold.dim_customers

Dimension client, enrichie des données démographiques et géographiques de l'ERP.

customer_keyPK
customer_idINT
customer_numberNVARCHAR
first_nameNVARCHAR
last_nameNVARCHAR
countryNVARCHAR
marital_statusNVARCHAR
genderNVARCHAR
birthdateDATE
create_dateDATE

Table de faits

gold.fact_sales

Table de faits : une ligne par ligne de commande (commande × produit).

order_numberNVARCHAR
product_keyFK → dim_products
customer_keyFK → dim_customers
order_dateDATE
shipping_dateDATE
due_dateDATE
sales_amountINT
quantityINT
priceINT

Dimension

gold.dim_products

Dimension produit : seuls les produits actifs sont conservés, sans l'historique des versions.

product_keyPK
product_idINT
product_numberNVARCHAR
product_nameNVARCHAR
category_idNVARCHAR
categoryNVARCHAR
subcategoryNVARCHAR
maintenanceNVARCHAR
costINT
product_lineNVARCHAR
start_dateDATE

Deux vues de reporting, report_customers et report_products, agrègent ensuite les indicateurs par client et par produit.

§ 01Le datasetSix fichiers CSV, un CRM et un ERP

Six fichiers CSV fournis avec le cours, issus de deux systèmes : le CRM (clients, produits et leur historique, lignes de vente) et l'ERP (informations complémentaires sur les clients, pays, catégories de produits). Les données sont fictives, figées, et volontairement imparfaites : doublons, espaces parasites, codes à la place des libellés, dates invalides, montants incohérents.

§ 02La questionConsolider deux systèmes aux clés différentes

Comment consolider deux systèmes qui ne partagent pas les mêmes clés en un modèle unique, prêt pour l'analyse ? Le client s'appelle cst_key dans le CRM et cid dans l'ERP, avec un préfixe ou des tirets en plus ; la catégorie d'un produit est cachée dans les cinq premiers caractères de sa clé.

§ 03L'intégration des deux systèmesCinq relations, rétablies en silver

De	Vers	Clé	Transformation
crm_sales_details	crm_prd_info	sls_prd_key = prd_key	—
crm_sales_details	crm_cust_info	sls_cust_id = cst_id	—
crm_prd_info	erp_px_cat_g1v2	cat_id = id	cat_id : 5 premiers caractères de prd_key, « - » remplacé par « _ »
crm_cust_info	erp_cust_az12	cst_key = cid	préfixe NAS retiré de cid
crm_cust_info	erp_loc_a101	cst_key = cid	tirets retirés de cid

§ 04Le catalogue de la couche goldChaque colonne, son type, son sens

gold.dim_customers

Dimension client, enrichie des données démographiques et géographiques de l'ERP. Sources : silver.crm_cust_info, erp_cust_az12, erp_loc_a101.

Colonne	Type	Description
customer_keyPK	BIGINT	Clé de substitution
customer_id	INT	Identifiant du client dans le CRM
customer_number	NVARCHAR(50)	Identifiant alphanumérique (ex. AW00011000)
first_name	NVARCHAR(50)	Prénom
last_name	NVARCHAR(50)	Nom de famille
country	NVARCHAR(50)	Pays de résidence, n/a si inconnu
marital_status	NVARCHAR(50)	Marié(e), Célibataire
gender	NVARCHAR(50)	CRM prioritaire, ERP en repli
birthdate	DATE	Date de naissance
create_date	DATE	Création de la fiche client

gold.fact_sales

Table de faits : une ligne par ligne de commande (commande × produit). Sources : silver.crm_sales_details, dim_products, dim_customers.

Colonne	Type	Description
order_number	NVARCHAR(50)	Numéro de commande (ex. SO54496)
product_keyFK	BIGINT	→ dim_products
customer_keyFK	BIGINT	→ dim_customers
order_date	DATE	Date de commande
shipping_date	DATE	Date d'expédition
due_date	DATE	Échéance du paiement
sales_amount	INT	quantity × price
quantity	INT	Unités commandées
price	INT	Prix unitaire

gold.dim_products

Dimension produit : seuls les produits actifs sont conservés, sans l'historique des versions. Sources : silver.crm_prd_info, erp_px_cat_g1v2.

Colonne	Type	Description
product_keyPK	BIGINT	Clé de substitution
product_id	INT	Identifiant interne
product_number	NVARCHAR(50)	Code produit (ex. BK-R93R-62)
product_name	NVARCHAR(50)	Nom descriptif
category_id	NVARCHAR(50)	Lien vers la catégorie (ex. BI_RB)
category	NVARCHAR(50)	Bikes, Components…
subcategory	NVARCHAR(50)	Road Bikes…
maintenance	NVARCHAR(50)	Yes, No
cost	INT	Coût
product_line	NVARCHAR(50)	Route, Montagne, Touring, Autres ventes
start_date	DATE	Début de commercialisation

§ 05La qualitéContrôles silver et gold, 0 anomalie le 27 septembre 2026

Contrôles silver

Un script de requêtes à relire après chaque chargement. Chaque requête annonce son résultat attendu, le plus souvent « aucune ligne ».

Clés primaires nulles ou en doublon
Espaces indésirables en début ou fin de texte
Valeurs standardisées : genre, statut marital, gamme de produit, pays
Coûts nuls ou négatifs
Dates incohérentes : début après fin, commande après expédition ou échéance
Cohérence des ventes : montant = quantité × prix
Dates de naissance hors plage (avant 1924 ou dans le futur)

Contrôles gold · exécutés le 27 septembre 2026

Contrôle	Attendu	Obtenu
Unicité de customer_key dans dim_customers	aucune ligne	0 ligne
Valeurs de gender après intégration CRM / ERP	Féminin, Masculin, n/a	Féminin, Masculin, n/a
Unicité de product_key dans dim_products	aucune ligne	0 ligne
Un seul enregistrement actif par product_number	aucune ligne	0 ligne
Intégrité référentielle entre fact_sales et les dimensions	aucune ligne	0 ligne

§ 06Les décisions techniquesMedallion, vues, chargement complet

Trois couches, un rôle chacune: Bronze garde la trace de ce qui est arrivé, silver concentre le nettoyage, gold ne contient que de la logique métier. Un problème se localise à une couche.
Gold en vues: Aucune donnée n'est dupliquée : les vues sont calculées à partir de silver à chaque requête, toujours à jour après un chargement.
Chargement complet: Vider puis recharger chaque table est simple et idempotent, sur des volumes de quelques dizaines de milliers de lignes.
Clés de substitution: Les dimensions reçoivent leur propre clé, indépendante des identifiants des systèmes sources, et la table de faits ne référence que ces clés.
Le CRM prioritaire sur l'ERP: Quand les deux systèmes donnent le genre d'un client, le CRM l'emporte ; l'ERP sert de repli quand le CRM n'en a pas.
Produits actifs seulement: dim_products ne garde que la version courante de chaque produit : l'historique des versions n'est pas demandé dans ce projet.

§ 07Ce que j'amélioreraisClés stables, contrôles automatiques, encodage

Des clés stables. Les clés de substitution sont calculées par ROW_NUMBER() dans des vues : un nouveau client inséré au milieu de l'ordre décale toutes les clés suivantes. Matérialiser les dimensions avec une colonne IDENTITY les rendrait stables.
Des contrôles qui échouent tout seuls. Les contrôles qualité sont des requêtes à relire. Les transformer en tests qui échouent (tSQLt, ou dbt comme dans le projet Prénoms) permettrait de bloquer un chargement défectueux.
Un encodage explicite. Lancés avec sqlcmd sans l'option -f 65001, les scripts en UTF-8 sont lus dans la page de code Windows et les libellés écrits par silver deviennent « FÃ©minin ». Constaté en préparant cette page.
Une exécution en une commande. Les scripts se lancent un par un, dans l'ordre, avec des chemins de fichiers codés en dur dans BULK INSERT.
L'historique. Le chargement complet écrase l'état précédent. Garder l'historique des clients et des produits (dimensions à évolution lente) serait l'étape suivante.

Tout le détail sur GitHub ↗