Accueil›Blog›Test technique conception de data warehouse : modélisation avancée

Guide recrutement data

Test technique conception de data warehouse : modélisation avancée

Concevoir un data warehouse robuste va au-delà du schéma en étoile basique. En entretien Data Architect ou Analytics Engineer Senior, on évalue la maîtrise des patterns avancés.

Data Builder·Juin 2025·7 min de lecture·Analytics Engineer · Data Architect

Sommaire

Rappel schéma en étoile
Tables de faits sans faits
Bridge tables
Junk dimensions
Role-playing dimensions
Mini-dimensions
Grille

1Révision : grain, additivité, conformité

Question discriminante

Qu est-ce que le grain d une table de faits ? Quelle est la différence entre un fait additif et semi-additif ?

Grain — le niveau de détail d une ligne dans la table de faits. Ex : 'une ligne par commande' ou 'une ligne par ligne de commande'
Fait additif — peut être additionné dans toutes les dimensions. Ex : montant de vente (OK par date, par région, par produit)
Fait semi-additif — peut être additionné sur certaines dimensions seulement. Ex : solde bancaire (OK par date mais pas cumulable dans le temps)
Fait non-additif — ne peut pas être additionné. Ex : taux de conversion, prix unitaire. Toujours calculé depuis des faits additifs
Dimensions conformes — même dimension partagée entre plusieurs tables de faits pour permettre des analyses croisées

2Tables de faits sans faits

Question discriminante

Qu est-ce qu une factless fact table ? Donnez deux exemples concrets.

-- Factless fact table type 1 : événements
-- 'L étudiant a assisté à ce cours ce jour'
CREATE TABLE fct_attendance (
    student_id INT,       -- FK vers dim_students
    course_id INT,        -- FK vers dim_courses
    date_id INT,          -- FK vers dim_date
    -- Pas de mesure ! La présence est le fait
    PRIMARY KEY (student_id, course_id, date_id)
);

-- Factless fact table type 2 : couverture
-- 'Ce produit était en promotion ce jour dans cette région'
-- Permet de calculer les ventes SANS promo (anti-join)
CREATE TABLE fct_promotions (
    product_id INT,
    region_id INT,
    date_id INT
    -- La présence dans la table = produit en promo
);

-- Utilisation : taux de conversion des promos
SELECT
    p.product_id,
    COUNT(DISTINCT v.order_id) AS ventes,
    COUNT(DISTINCT p.date_id) AS jours_en_promo
FROM fct_promotions p
LEFT JOIN fct_orders v ON p.product_id = v.product_id AND p.date_id = v.date_id
GROUP BY p.product_id;

3Bridge tables : relations many-to-many

Question discriminante

Comment modélisez-vous une relation many-to-many entre faits et dimensions ?

-- Problème : un compte peut avoir plusieurs propriétaires
-- et un propriétaire peut avoir plusieurs comptes
-- -> on ne peut pas mettre customer_id directement dans fct_transactions

-- Solution : bridge table avec weight factor
CREATE TABLE bridge_account_customer (
    account_id INT,
    customer_id INT,
    allocation_factor DECIMAL(5,4)  -- 1.0 si seul propriétaire, 0.5 si deux propriétaires
);

-- Requête : CA par client (avec pondération)
SELECT
    c.customer_name,
    SUM(t.amount * b.allocation_factor) AS allocated_revenue
FROM fct_transactions t
JOIN bridge_account_customer b ON t.account_id = b.account_id
JOIN dim_customers c ON b.customer_id = c.customer_id
GROUP BY c.customer_name;

Fanout problem — sans allocation_factor, les montants sont comptés plusieurs fois (une fois par client)
Weight factor — 1/N si attribution égale entre N parties, ou pondération métier spécifique

4Junk dimensions : regrouper les flags

Question discriminante

Qu est-ce qu une junk dimension ? Quand et comment la créer ?

-- Problème : la table de faits a de nombreux indicateurs booléens
-- is_promotional, is_online, is_returned, is_gift_wrap -> 4 colonnes dans les faits

-- Solution : junk dimension
-- Regroupe les combinaisons possibles de ces flags
CREATE TABLE dim_order_flags (
    flag_key INT PRIMARY KEY,
    is_promotional BOOLEAN,
    is_online BOOLEAN,
    is_returned BOOLEAN,
    is_gift_wrap BOOLEAN
);
-- Pré-peupler toutes les combinaisons (2^4 = 16 lignes)

-- La table de faits n a plus qu une seule FK
CREATE TABLE fct_orders (
    order_id INT,
    flag_key INT,  -- FK vers dim_order_flags
    amount DECIMAL
);

Avantages — réduit le nombre de colonnes dans la table de faits, groupe les flags logiquement liés
Quand — 3+ flags boolean faiblement cardinals, souvent filtrés ensemble

5Role-playing dimensions

Question discriminante

Qu est-ce qu une role-playing dimension ? Montrez avec un exemple date.

-- La même dimension peut jouer plusieurs rôles dans une table de faits
-- Ex : une commande a une date de création ET une date de livraison

-- Une seule table de dimension date
CREATE TABLE dim_date (
    date_id INT PRIMARY KEY,
    full_date DATE,
    year INT, quarter INT, month INT, day_of_week INT
);

-- La table de faits référence dim_date deux fois avec des noms différents
CREATE TABLE fct_orders (
    order_id INT,
    order_date_id INT,     -- FK vers dim_date (rôle : date commande)
    delivery_date_id INT,  -- FK vers dim_date (rôle : date livraison)
    amount DECIMAL
);

-- Requête avec alias pour chaque rôle
SELECT
    od.year AS order_year,
    dd.year AS delivery_year,
    AVG(DATEDIFF('day', od.full_date, dd.full_date)) AS avg_delivery_days
FROM fct_orders o
JOIN dim_date od ON o.order_date_id = od.date_id
JOIN dim_date dd ON o.delivery_date_id = dd.date_id
GROUP BY 1, 2;

6Mini-dimensions : gérer les grandes dimensions qui changent

Question discriminante

Qu est-ce qu une mini-dimension ? Quand l utilisez-vous à la place d un SCD2 ?

Problème — une dimension client a 50M de lignes et des attributs qui changent souvent (segment, score de crédit). SCD2 ferait exploser la table
Mini-dimension — extraire les attributs qui changent fréquemment dans une petite dimension séparée
Structure — dim_customers (attributs stables) + dim_customer_profile (segment, score, statut) + bridge si nécessaire
Avantages — les requêtes sur les attributs stables restent rapides, seule la mini-dimension utilise le SCD2

7Grille par niveau

Niveau	Maitrise	Signal GO	NO-GO
Confirmé	Grain, additivité, schéma en étoile complet	Définit le grain avant de modéliser, distingue additif et semi-additif	Ne sait pas ce qu est le grain
Senior	Factless facts, bridge tables, junk dimensions, role-playing	A utilisé une bridge table pour un M:N, explique les junk dimensions	Ne connaît pas les patterns avancés de Kimball

1Review: grain, additivity, conformance

Discriminating question

What is the grain of a fact table? What is the difference between an additive and semi-additive fact?

Grain — the level of detail of a row in the fact table. Ex: 'one row per order' or 'one row per order line'
Additive fact — can be summed across all dimensions. Ex: sales amount (OK by date, by region, by product)
Semi-additive fact — can be summed across some dimensions only. Ex: bank balance (OK by date but not cumulative over time)
Non-additive fact — cannot be summed. Ex: conversion rate, unit price. Always calculated from additive facts
Conformed dimensions — same dimension shared across multiple fact tables to enable cross-analysis

2Factless fact tables

Discriminating question

What is a factless fact table? Give two concrete examples.

-- Factless fact table type 1: events
-- 'The student attended this course on this day'
CREATE TABLE fct_attendance (
    student_id INT,       -- FK to dim_students
    course_id INT,        -- FK to dim_courses
    date_id INT,          -- FK to dim_date
    -- No measure! Attendance is the fact
    PRIMARY KEY (student_id, course_id, date_id)
);

-- Factless fact table type 2: coverage
-- 'This product was on promotion on this day in this region'
-- Allows calculation of sales WITHOUT promo (anti-join)
CREATE TABLE fct_promotions (
    product_id INT,
    region_id INT,
    date_id INT
    -- Presence in the table = product on promotion
);

-- Usage: promo conversion rate
SELECT
    p.product_id,
    COUNT(DISTINCT v.order_id) AS sales,
    COUNT(DISTINCT p.date_id) AS days_on_promo
FROM fct_promotions p
LEFT JOIN fct_orders v ON p.product_id = v.product_id AND p.date_id = v.date_id
GROUP BY p.product_id;

3Bridge tables: many-to-many relationships

Discriminating question

How do you model a many-to-many relationship between facts and dimensions?

-- Problem: an account can have multiple owners
-- and an owner can have multiple accounts
-- -> customer_id cannot be placed directly in fct_transactions

-- Solution: bridge table with weight factor
CREATE TABLE bridge_account_customer (
    account_id INT,
    customer_id INT,
    allocation_factor DECIMAL(5,4)  -- 1.0 if sole owner, 0.5 if two owners
);

-- Query: revenue by customer (with weighting)
SELECT
    c.customer_name,
    SUM(t.amount * b.allocation_factor) AS allocated_revenue
FROM fct_transactions t
JOIN bridge_account_customer b ON t.account_id = b.account_id
JOIN dim_customers c ON b.customer_id = c.customer_id
GROUP BY c.customer_name;

Fanout problem — without allocation_factor, amounts are counted multiple times (once per customer)
Weight factor — 1/N for equal attribution among N parties, or specific business weighting

4Junk dimensions: grouping flags

Discriminating question

What is a junk dimension? When and how do you create one?

-- Problem: the fact table has many boolean indicators
-- is_promotional, is_online, is_returned, is_gift_wrap -> 4 columns in facts

-- Solution: junk dimension
-- Groups all possible combinations of these flags
CREATE TABLE dim_order_flags (
    flag_key INT PRIMARY KEY,
    is_promotional BOOLEAN,
    is_online BOOLEAN,
    is_returned BOOLEAN,
    is_gift_wrap BOOLEAN
);
-- Pre-populate all combinations (2^4 = 16 rows)

-- The fact table only needs a single FK
CREATE TABLE fct_orders (
    order_id INT,
    flag_key INT,  -- FK to dim_order_flags
    amount DECIMAL
);

Advantages — reduces the number of columns in the fact table, groups logically related flags
When — 3+ low-cardinality boolean flags, often filtered together

5Role-playing dimensions

Discriminating question

What is a role-playing dimension? Show an example with a date.

-- The same dimension can play multiple roles in a fact table
-- Ex: an order has a creation date AND a delivery date

-- A single date dimension table
CREATE TABLE dim_date (
    date_id INT PRIMARY KEY,
    full_date DATE,
    year INT, quarter INT, month INT, day_of_week INT
);

-- The fact table references dim_date twice with different names
CREATE TABLE fct_orders (
    order_id INT,
    order_date_id INT,     -- FK to dim_date (role: order date)
    delivery_date_id INT,  -- FK to dim_date (role: delivery date)
    amount DECIMAL
);

-- Query with alias for each role
SELECT
    od.year AS order_year,
    dd.year AS delivery_year,
    AVG(DATEDIFF('day', od.full_date, dd.full_date)) AS avg_delivery_days
FROM fct_orders o
JOIN dim_date od ON o.order_date_id = od.date_id
JOIN dim_date dd ON o.delivery_date_id = dd.date_id
GROUP BY 1, 2;

6Mini-dimensions: handling large changing dimensions

Discriminating question

What is a mini-dimension? When do you use it instead of a SCD2?

Problem — a customer dimension has 50M rows and frequently changing attributes (segment, credit score). SCD2 would cause the table to explode
Mini-dimension — extract frequently changing attributes into a small separate dimension
Structure — dim_customers (stable attributes) + dim_customer_profile (segment, score, status) + bridge if needed
Advantages — queries on stable attributes remain fast, only the mini-dimension uses SCD2

7Level grid

Level	Mastery	GO signal	NO-GO
Mid-level	Grain, additivity, complete star schema	Defines grain before modeling, distinguishes additive and semi-additive	Does not know what grain is
Senior	Factless facts, bridge tables, junk dimensions, role-playing	Has used a bridge table for M:N, explains junk dimensions	Does not know Kimball advanced patterns

Vous recrutez un Data Architect ou Analytics Engineer ?

Premier entretien gratuit. Rapport GO/NO-GO sous 48h.

Tester gratuitement Reserver un appel