Jinja et macros dbt : factoriser et réutiliser sa logique SQL

Depuis le début de la formation, on écrit du SQL parsemé de petites accolades : {{ ref() }}, {{ source() }}, {{ config() }}, {% if is_incremental() %}. Ces accolades, c'est du Jinja, et tu en utilises déjà sans forcément t’en rendre compte.

Le jour où tu copies-colles une logique de nettoyage dans cinq modèles, tu sens qu'il manque quelque chose. Ce quelque chose, ce sont les macros avec des blocs de logique SQL que tu écris une fois et que tu réutilises partout et c'est l'une des vraies forces de dbt.

Tu fais déjà du Jinja

Jinja est un langage de templating. dbt s'en sert pour transformer ton SQL avant de l'envoyer à Snowflake. Quand tu écris {{ ref('stg_orders') }}, dbt remplace ça par le vrai nom de la table au moment de l'exécution. Quand tu écris {% if is_incremental() %}, dbt décide d'inclure ou non un bout de SQL.

Tu l’as peut-être deviné, mais le fichier .sql n'est pas du SQL pur mais un template que dbt compile en SQL pur. Comprendre Jinja, c'est comprendre ce qui se passe entre ton fichier et la requête réellement exécutée.

Les bases de Jinja

Il y a trois syntaxes à connaître :

• {{ ... }} : une expression, qui produit une valeur insérée dans le SQL. C'est ce que fait {{ ref(...) }}.
• {% ... %} : une instruction, de la logique qui ne produit rien directement (une boucle, une condition, une variable).
• {# ... #} : un commentaire Jinja, qui n'apparaît pas dans le SQL final.

On peut définir une variable, faire une condition, boucler :

{% set seuil = 100 %}

select
    order_id,
    montant,
    case when montant > {{ seuil }} then 'grosse commande' else 'standard' end as segment
from {{ ref('stg_orders') }}

Ici, {% set seuil = 100 %} crée une variable, et {{ seuil }} l'insère dans le SQL. dbt compilera ça en case when montant > 100 .... Simple pour l'instant, mais c'est la base de tout le reste.

La première macro

Une macro, c'est une fonction Jinja réutilisable. Prenons un cas concret, par exemple dans stg_customers, on nettoie l'email avec lower(...) et on veut nettoyer des emails dans plusieurs modèles, toujours pareil (minuscules et espaces retirés). Plutôt que de copier la même expression partout, on va faire une macro.

Crée un fichier macros/clean_email.sql :

{% macro clean_email(column_name) %}
    trim(lower({{ column_name }}))
{% endmacro %}

Puis dans stg_customers.sql, appelle-la :

{{ clean_email('email') }} as email

dbt remplace l'appel par trim(lower(email)). Le jour où tu décides d'ajouter une règle de nettoyage (retirer les accents, par exemple), tu modifies uniquement la macro, et tous les modèles qui utilisent la macro en profitent.

Exemple 2 : Macro (division par 0)

Prenons un autre exemple, une division qui renvoie NULL au lieu de planter quand le dénominateur est zéro. C'est un besoin qui revient tout le temps en analytics (panier moyen, taux de conversion).

Crée macros/safe_divide.sql :

{% macro safe_divide(numerator, denominator) %}
    {{ numerator }} / nullif({{ denominator }}, 0)
{% endmacro %}

Et utilise-la dans le modèle mart_clients_commandes.sql pour calculer le panier moyen :

{{ safe_divide('total_depense', 'nb_commandes') }} as panier_moyen

dbt génère :

total_depense / nullif(nb_commandes, 0) as panier_moyen

Le nullif(nb_commandes, 0) transforme le zéro en NULL, et diviser par NULL donne NULL au lieu d'une erreur. Donc un client sans commande obtient un panier moyen vide, au lieu de faire planter toute la requête.

La vraie puissance : générer du SQL avec une boucle

Une macro qui remplace une logique, c'est pratique mais une macro qui génère du SQL répétitif à ta place, c'est un game changer.

Imagine que tu veux pour les clients une colonne pour les completed, une autre pour les pending. À la main, c'est un count(case when ...) par statut. Avec dix statuts, c'est insupportable à écrire et à maintenir.

Crée macros/count_by_status.sql :

{% macro count_by_status(statuses) %}
    {% for s in statuses %}
    count(case when statut = '{{ s }}' then 1 end) as nb_{{ s }}{% if not loop.last %},{% endif %}
    {% endfor %}
{% endmacro %}

Puis on va créer un nouveau modèle mart_clients_commandes_status.sql :

select
    customer_id,
    {{ count_by_status(['completed', 'pending']) }}
from {{ ref('stg_orders') }}
group by 1

dbt déroule la boucle et génère ce SQL :

select
    customer_id,
    count(case when statut = 'completed' then 1 end) as nb_completed,
    count(case when statut = 'pending' then 1 end) as nb_pending
from analytics.dev.stg_orders
group by 1

C'est très puissant. Le loop.last gère la virgule entre les colonnes sans en mettre une de trop à la fin. Ajoute un statut à la liste, et la colonne apparaît toute seule.

Voir le SQL généré

Quand on commence le Jinja, on a besoin de vérifier ce que dbt produit vraiment. La commande pour ça :

dbt compile

dbt transforme tous tes templates en SQL complié, sans créer de table ni exécuter tes modèles, et écrit le résultat dans le dossier target/compiled/.

Ouvre le fichier de ton modèle là-dedans et tu y vois le SQL final, macros déroulées et ref() résolus. C'est le meilleur moyen pour déboguer une macro qui ne fait pas ce que tu crois.

Avant d'écrire une macro

Avant d'écrire une macro, pose-toi une question : est-ce que quelqu'un ne l'a pas déjà écrite ? La communauté dbt maintient une bibliothèque de macros prêtes à l'emploi, dbt_utils, avec des classiques comme generate_surrogate_key (créer une clé technique propre) ou star (sélectionner toutes les colonnes sauf certaines) etc....

On verra comment installer et utiliser ces packages dans le prochain article. Pour l'instant, retiens le réflexe que ta logique métier mérite ta propre macro, mais les besoins techniques courants sont souvent déjà couverts.

Ce qu'on a fait, et la suite

Récap. de cet article :

• Comprendre que les accolades qu'on utilise depuis le début, c'est du Jinja, et que dbt compile un template en SQL pur
• Voir les trois syntaxes : {{ }} pour une expression, {% %} pour une instruction, {# #} pour un commentaire
• Ecrire une première macro pour factoriser une logique répétée
• Générer du SQL avec une boucle, pour ne plus écrire de colonnes à répétition,
• Inspecter le SQL produit avec dbt compile.

Dans le prochain article, on enchaîne logiquement : les packages dbt, pour récupérer des centaines de macros déjà écrites (à commencer par dbt_utils) au lieu de réinventer la roue.

Aller plus loin

Cet article fait partie de la formation dbt complète, du premier modèle au déploiement en production.

👉 Suivre toute la formation dbt

dbt tourne sur Snowflake dans ce parcours. Pour maîtriser le socle (warehouses, rôles, ingestion) :

👉 Accéder à la Formation Snowflake

Et pour t'entraîner en conditions d'examen, la certification dbt Analytics Engineering teste précisément Jinja et les macros.

👉 Préparer la certification dbt sur DataCertification.fr

Tu veux que je t'accompagne sur ton projet data (Snowflake, dbt, modélisation, industrialisation) ?

👉 Réserver un appel de 30 minutes

Questions fréquentes