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.
Qu est-ce que le grain d une table de faits ? Quelle est la différence entre un fait additif et semi-additif ?
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;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;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
);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;Qu est-ce qu une mini-dimension ? Quand l utilisez-vous à la place d un SCD2 ?
| 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 |
Premier entretien gratuit. Rapport GO/NO-GO sous 48h.