Le contrôleur de gestion relit la première démo : « Votre panier moyen, il divise quoi par quoi ? ». La réponse honnête était « ça dépend de la question ». Cette leçon supprime ce « ça dépend » : chaque indicateur reçoit une définition unique, écrite une fois, relue comme du code, et tout le SQL en découle.
Le mécanisme : un modèle en étoile réduit à un fichier
thelook_ecommerce compte sept tables. L'assistant n'en expose que quatre, organisées autour d'une table de faits (une ligne = un événement mesurable, ici un article commandé) et de dimensions reliées par des jointures plusieurs-vers-un.
flowchart TB OI["order_items (faits, alias oi)<br/>status, sale_price, created_at"] P["products (p)<br/>category, brand, department, cost"] U["users (u)<br/>country, traffic_source, age, gender"] DC["distribution_centers (dc)<br/>name"] OI -->|"p.id = oi.product_id"| P OI -->|"u.id = oi.user_id"| U P -->|"dc.id = p.distribution_center_id"| DC X["events, inventory_items, orders<br/>email, adresse, coordonnées"]:::exclu classDef exclu stroke-dasharray: 5 5
Toutes les jointures partent des faits vers une table où la clé est unique. C'est la règle qui protège de l'explosion de lignes (fan-out) : joindre order_items à inventory_items par produit multiplierait chaque vente par le nombre d'articles en stock, et le chiffre d'affaires avec.
Le fichier semantique.yaml contient quatre blocs.
faits: {table: order_items, alias: oi}
jointures:
products: {alias: p, "on": "p.id = oi.product_id"}
users: {alias: u, "on": "u.id = oi.user_id"}
distribution_centers: {alias: dc, "on": "dc.id = p.distribution_center_id", requiert: products}
temps: oi.created_at
filtre_permanent: "oi.created_at < CURRENT_TIMESTAMP()"
metriques:
chiffre_affaires:
libelle: "Chiffre d'affaires"
sql: "SUM(IF(oi.status NOT IN ('Cancelled', 'Returned'), oi.sale_price, 0))"
format: montant
definition: "Somme des prix de vente des articles ni annulés ni retournés..."
taux_retour:
sql: "SAFE_DIVIDE(COUNTIF(oi.status = 'Returned'), COUNTIF(oi.status IN ('Complete', 'Returned')))"
format: pourcentage
definition: "Articles retournés parmi les articles livrés (Complete + Returned)..."
dimensions:
pays:
sql: "u.country"
tables: [users]
valeurs: [Australia, Austria, Belgium, Brasil, China, ...]
definition: "Noms anglais ; le Brésil s'écrit Brasil dans les données."Trois choix méritent d'être défendus :
definitionest obligatoire : c'est le texte que lit le modèle et que l'interface affiche au survol. Une métrique sans définition lisible par un humain ne passe pas en revue.valeurspour les dimensions de faible cardinalité : le compilateur refuseBrésilavant d'interroger BigQuery et renvoie la liste exacte. Sans cela, le filtre ne trouve rien, la requête renvoie 0, et le modèle annonce « aucune vente au Brésil ».- Le taux de retour divise par les articles livrés, pas par les articles commandés : les commandes en cours n'ont pas encore eu l'occasion d'être retournées. Ce genre de décision se prend une fois, avec le métier, et s'écrit dans le fichier.
La mise en œuvre : le compilateur
assistant/semantique.py reçoit une RequeteMetier (Pydantic) et produit un SqlCompile : le SQL, les paramètres, les tables lues, la description des colonnes. Le cœur tient en une boucle par bloc.
for i, f in enumerate(r.filtres):
d = self.dimensions[f.dimension]
hors_liste = [v for v in f.valeurs if d.get("valeurs") and v not in d["valeurs"]]
if hors_liste:
raise ErreurSemantique(
f"Valeur(s) absente(s) des données pour {f.dimension} : {', '.join(hors_liste)}. "
f"Valeurs exactes : {' | '.join(d['valeurs'])}.")
op = "IN" if f.operateur == "in" else "NOT IN"
where.append(f"{d['sql']} {op} UNNEST(@f{i})") # la valeur ne touche jamais le texte SQL
params[f"f{i}"] = list(f.valeurs)Les noms (métriques, dimensions, tri) sont vérifiés contre le YAML ; les valeurs passent par des paramètres de requête BigQuery (@f0, @date_debut). Rien de ce qu'écrit le modèle n'est concaténé dans le SQL. Une tentative comme France') OR 1=1 -- est rejetée par la liste des valeurs, et le serait de toute façon par le paramétrage.
Les jointures sont calculées : le compilateur collecte les tables requises par chaque métrique et dimension utilisée, puis ajoute les jointures dans l'ordre du YAML (qui respecte requiert). Une question sans attribut produit n'a pas de jointure products, donc 10 Mo facturés au lieu de 20.
Voyez le résultat sans modèle ni réseau :
.venv/bin/python -m assistant.semantiqueRésultat obtenu le 2026-10-15 (chiffre d'affaires et taux de marge par catégorie, France, 2025, top 5) :
SELECT
p.category AS categorie,
SUM(IF(oi.status NOT IN ('Cancelled', 'Returned'), oi.sale_price, 0)) AS chiffre_affaires,
SAFE_DIVIDE(SUM(IF(oi.status NOT IN ('Cancelled', 'Returned'), oi.sale_price - p.cost, 0)), SUM(IF(oi.status NOT IN ('Cancelled', 'Returned'), oi.sale_price, 0))) AS taux_marge
FROM `bigquery-public-data.thelook_ecommerce.order_items` AS oi
LEFT JOIN `bigquery-public-data.thelook_ecommerce.products` AS p ON p.id = oi.product_id
LEFT JOIN `bigquery-public-data.thelook_ecommerce.users` AS u ON u.id = oi.user_id
WHERE oi.created_at < CURRENT_TIMESTAMP()
AND oi.created_at >= TIMESTAMP(@date_debut)
AND oi.created_at < TIMESTAMP(@date_fin)
AND u.country IN UNNEST(@f0)
GROUP BY 1
ORDER BY chiffre_affaires DESC
LIMIT 5
Paramètres : {'date_debut': '2025-01-01', 'date_fin': '2026-01-01', 'f0': ['France']}La période est un intervalle semi-ouvert : date_fin est exclue. « 2025 » s'écrit [2025-01-01, 2026-01-01[, sans se demander si le 31 décembre à 23 h 59 est inclus. Le test test_chaque_metrique_et_dimension_compile compile les 8 × 15 combinaisons à chaque make test.
Ce qui casse en production
| Symptôme | Cause | Parade |
|---|---|---|
| Le CA par pays dépasse le CA total | jointure vers une table où la clé n'est pas unique (fan-out) | n'autoriser que des jointures plusieurs-vers-un depuis les faits ; vérifier l'unicité de la clé par une requête de contrôle dans la CI |
| Le panier moyen annuel ≠ moyenne des paniers mensuels | métrique non additive (ratio, COUNT DISTINCT) recombinée | recalculer à la bonne maille, jamais additionner ou moyenner des ratios ; le dire dans definition |
| Le nombre de clients par mois somme à plus que l'année | un même client achète plusieurs mois | idem : nb_clients n'est pas additif |
| Une nouvelle catégorie n'est jamais filtrable | valeurs figées dans le YAML | régénérer les listes par une requête planifiée, ou retirer valeurs des dimensions qui bougent (marque : ~2 700 valeurs) |
| Deux métriques « CA » divergentes après six mois | copier-coller de l'expression SQL | une seule expression par métrique ; les ratios réutilisent la même formule, testée |
La règle de décision
- Ajoutez une métrique quand une question revient et que sa définition fait consensus ; pas avant. Le refus explicite (« cette donnée n'est pas dans le catalogue ») vaut mieux qu'une métrique approximative.
- Mettez
valeurssur toute dimension de moins d'une centaine de modalités stables ; au-delà, laissez le tri et la limite faire le travail. - Faites relire le YAML par le métier : c'est le seul fichier du dépôt qu'un contrôleur de gestion peut et doit valider.
Pratique
Un analyste propose d'ajouter la jointure order_items -> inventory_items (sur product_id) pour exposer la date d'entrée en stock. Que répondez-vous ?
La jointure par product_id est plusieurs-vers-plusieurs : chaque vente est dupliquée autant de fois qu'il y a d'articles en stock pour ce produit. order_items.inventory_item_id pointe vers une ligne unique, c'est la bonne clé. COUNT DISTINCT masquerait le problème pour certaines métriques mais pas pour SUM(sale_price). Le coût (81 Mo) est un vrai sujet, mais secondaire face à un chiffre faux.
Le modèle appelle interroger avec le filtre pays=Brésil. Que se passe-t-il dans ce projet ?
La vérification se fait avant le SQL, contre la liste valeurs du YAML. Le message d'erreur est rédigé pour le modèle : il contient la valeur attendue. Le premier choix est exactement le comportement d'un système sans liste de valeurs. Le dry-run ne voit pas les valeurs des paramètres, il ne peut pas détecter ce cas.
Exercice. Ajoutez une métrique taux_annulation (articles annulés sur articles commandés) et une dimension statut_client impossible à exprimer proprement : « nouveau client » si c'est sa première commande. Pour la première, écrivez l'entrée YAML et lancez make test. Pour la seconde, expliquez pourquoi elle ne tient pas dans ce compilateur.
Voir la solution
taux_annulation:
libelle: "Taux d'annulation"
sql: "SAFE_DIVIDE(COUNTIF(oi.status = 'Cancelled'), COUNT(*))"
format: pourcentage
definition: "Articles annulés parmi tous les articles commandés sur la période."make test reste vert et test_chaque_metrique_et_dimension_compile couvre automatiquement la nouvelle métrique.
statut_client dépend d'une autre ligne que la ligne courante (la première commande du client, peut-être hors période). Il faut une fonction de fenêtre ou une table dérivée (MIN(created_at) OVER (PARTITION BY user_id)), donc un niveau de modélisation que ce compilateur n'a pas. La bonne réponse est de créer une vue matérialisée « clients » avec la date de première commande, de l'ajouter en jointure plusieurs-vers-un, puis d'exposer la dimension. Les outils de couche sémantique du marché appellent cela une table dérivée.
- Je sais écrire une métrique avec une définition métier non ambiguë
- Je sais pourquoi seules les jointures plusieurs-vers-un sont permises
- Je sais pourquoi les valeurs passent en paramètres et les noms par liste blanche
- J'ai lancé
python -m assistant.semantiqueet lu le SQL produit