Vous avez installé dbt-utils. Vous avez parcouru la liste des macros disponibles. Et pourtant, la plupart des projets finissent par utiliser les trois ou quatre mêmes, tout en écrivant du SQL à la main pour des patterns qui ont déjà des solutions.
Ce qui suit est une sélection de macros qui méritent leur place dans les projets en production : celles qui éliminent le travail répétitif et préviennent les bugs subtils. Certaines viennent de packages, d’autres vous les écrirez vous-même, mais toutes résolvent des problèmes que vous rencontrerez encore et encore.
Les macros de packages qui justifient l’installation
Ces macros justifient à elles seules l’ajout de dbt-utils et dbt-expectations à votre packages.yml. Elles résolvent des problèmes qu’il est étonnamment difficile de traiter correctement soi-même.
1. generate_surrogate_key
Créer des clés hashées cohérentes à partir de clés métier semble simple, jusqu’à ce que vous tombiez sur votre première valeur null. Différentes approches de concaténation gèrent les nulls différemment, ce qui provoque des bugs de collision qui n’apparaissent que des mois plus tard.
SELECT {{ dbt_utils.generate_surrogate_key(['customer_id', 'order_date']) }} AS order__key, customer_id, order_date, amountFROM {{ ref('base__shopify__orders') }}La macro gère les valeurs null de manière cohérente, utilise un délimiteur pour éviter les collisions entre ('a', 'bc') et ('ab', 'c'), et fonctionne sur BigQuery, Snowflake et Databricks. L’algorithme de hachage varie selon l’adaptateur, mais le comportement reste identique.
Un point d’attention : si vous migrez depuis l’ancienne macro surrogate_key, la gestion des nulls a changé. Testez vos clés avant de faire la bascule.
2. star
Sélectionner toutes les colonnes sauf quelques-unes est un pattern courant, notamment quand il faut exclure des colonnes d’audit ou des champs sensibles sans lister toutes les colonnes à conserver.
SELECT {{ dbt_utils.star(from=ref('base__stripe__customers'), except=['_loaded_at', '_source_file']) }}, CURRENT_TIMESTAMP() AS _processed_atFROM {{ ref('base__stripe__customers') }}La macro génère la liste complète des colonnes à la compilation, en excluant celles que vous spécifiez. Quand les modèles en amont ajoutent des colonnes, votre modèle les récupère automatiquement. Quand ils en suppriment, la compilation échoue (ce qui est le comportement souhaité). La dérive silencieuse des schémas cause plus d’incidents en production que les erreurs explicites.
Si votre projet tourne exclusivement sur BigQuery, SELECT * EXCEPT(...) donne le même résultat nativement. La macro star prend tout son sens quand vous avez besoin de compatibilité cross-database ou que vous voulez des listes de colonnes explicites dans votre SQL compilé pour faciliter l’audit.
3. get_column_values
Le SQL dynamique basé sur les valeurs réelles des données permet des patterns qui nécessiteraient autrement du hardcoding ou des mises à jour manuelles. Le cas d’usage classique est le pivot d’une colonne en plusieurs colonnes.
{% set payment_methods = dbt_utils.get_column_values( table=ref('base__stripe__payments'), column='payment_method', order_by='COUNT(*) DESC', max_records=10) %}
SELECT order_id, {% for method in payment_methods %} SUM(CASE WHEN payment_method = '{{ method }}' THEN amount ELSE 0 END) AS order__{{ method }}_amount {{ ',' if not loop.last }} {% endfor %}FROM {{ ref('base__stripe__payments') }}GROUP BY order_idQuand un nouveau moyen de paiement apparaît en production, votre modèle le prend en charge au prochain run. Aucune modification de code nécessaire.
La macro exécute une requête pendant la phase execute, elle a donc besoin d’une connexion active à la base de données. En utilisation normale de dbt, aucun problème, mais elle retourne une liste vide pendant dbt compile ou lors de la construction du DAG.
4. date_spine
Les modèles de séries temporelles avec des trous dans les dates ont besoin d’une séquence complète. Le faire soi-même implique de jongler avec des CTEs récursives, des fonctions generate_series ou des cross joins, et tout ça varie selon la base de données.
{{ dbt_utils.date_spine( datepart="day", start_date="CAST('2020-01-01' AS DATE)", end_date="CAST('2026-12-31' AS DATE)") }}Vous obtenez une ligne par jour. Joignez-la à vos modèles principaux pour faire apparaître les dates manquantes, calculer des totaux cumulés sans sauter les trous, ou construire des visualisations de séries temporelles qui affichent les zéros.
Vous pouvez aussi utiliser week, month ou hour comme datepart. Pour la plupart des cas d’usage analytiques, un seul modèle de référence dates utilisant cette macro suffit.
5. union_relations
Combiner des tables dont les schémas diffèrent légèrement (tables shardées, données multi-tenant ou tables réparties entre environnements) nécessite d’aligner des colonnes qui n’existent pas forcément dans toutes les sources.
{{ dbt_utils.union_relations( relations=[ ref('base__shopify_us__orders'), ref('base__shopify_eu__orders'), ref('base__shopify_apac__orders') ]) }}La macro identifie toutes les colonnes à travers toutes les relations, remplit les colonnes manquantes avec des nulls, et ajoute une colonne _dbt_source_relation pour savoir de quelle table provient chaque ligne. Fini la maintenance manuelle des listes de colonnes quand les schémas dérivent entre régions.
6. expect_column_values_to_match_regex
Les tests de qualité de données ont leur place au niveau du schéma, pas dans la logique de transformation. dbt-expectations apporte les tests façon Great Expectations à dbt, et la validation par regex est l’un des plus utiles.
columns: - name: email tests: - dbt_expectations.expect_column_values_to_match_regex: regex: '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$' row_condition: "email is not null"Ce test intercepte les données malformées avant qu’elles n’atteignent les modèles en aval. Quand il échoue, vous savez exactement quelle colonne et quel pattern ont été violés. Comparez ça avec la découverte d’emails invalides quand les envois d’une campagne marketing sont rejetés.
Le package inclut plus de 40 types de tests. Pattern matching, contrôles de fraîcheur, plages de valeurs et validation inter-colonnes couvrent la plupart des scénarios de qualité de données sans SQL personnalisé. Je détaille les tests les plus utiles dans mon guide dbt-expectations.
Les macros personnalisées que vous écrirez vous-même
Certains patterns n’existent pas dans les packages parce qu’ils sont trop spécifiques pour être standardisés. Mais ils reviennent projet après projet. Voici ceux qui valent le coup.
7. Colonnes d’audit
Chaque ligne devrait porter des métadonnées sur quand et comment elle a été créée. Une macro simple permet de garder ça cohérent sur des centaines de modèles.
-- macros/add_audit_columns.sql{% macro add_audit_columns() %} CURRENT_TIMESTAMP() AS _loaded_at, '{{ invocation_id }}' AS _dbt_invocation_id, '{{ target.name }}' AS _dbt_target{% endmacro %}Utilisez-la dans n’importe quel SELECT :
SELECT customer_id, customer_name, {{ add_audit_columns() }}FROM {{ ref('base__stripe__customers') }}L’invocation ID permet de remonter à un run dbt précis. Le target name indique si les données viennent de dev ou de prod. Quand vous debuggez un incident en production à 2h du matin, vous serez content d’avoir ces colonnes.
8. Échantillonnage adapté à l’environnement
Les runs de développement sur la totalité des données de production prennent une éternité. Échantillonner en dev accélère l’itération sans changer le comportement en production.
-- macros/limit_data_in_dev.sql{% macro limit_data_in_dev(column_name='created_at', days=3) %} {% if target.name == 'dev' %} AND {{ column_name }} >= DATE_SUB(CURRENT_DATE(), INTERVAL {{ days }} DAY) {% endif %}{% endmacro %}Ajoutez-la à la clause WHERE de n’importe quel modèle :
SELECT event_id, event_name, event_timestamp, user_idFROM {{ source('raw', 'events') }}WHERE 1=1 {{ limit_data_in_dev('event_timestamp', 7) }}En dev, vous obtenez sept jours de données. En prod, vous obtenez tout. Le code du modèle reste identique d’un environnement à l’autre. Fini les filtres à commenter et décommenter avant chaque commit.
9. Conversions d’unités
Centimes en dollars, bytes en gigaoctets, millisecondes en secondes. Ces conversions sont triviales individuellement, mais les appliquer de manière incohérente d’un modèle à l’autre provoque des cauchemars de réconciliation.
-- macros/cents_to_dollars.sql{% macro cents_to_dollars(column_name, scale=2) %} ROUND({{ column_name }} / 100.0, {{ scale }}){% endmacro %}SELECT order_id, {{ cents_to_dollars('amount_cents') }} AS order__amount_dollarsFROM {{ ref('base__shopify__orders') }}La macro est d’une simplicité presque gênante. C’est précisément l’intérêt. Quand la finance demande pourquoi deux rapports affichent des totaux différents, vous pouvez confirmer que tous les modèles utilisent la même conversion. Quand il faut changer la logique d’arrondi, vous la changez à un seul endroit.
10. Génération de schéma
Par défaut, dbt concatène votre target schema avec tout custom schema que vous spécifiez. La plupart des équipes veulent un comportement différent : en prod, utiliser directement le custom schema ; en dev, tout préfixer avec le target schema.
-- macros/generate_schema_name.sql{% macro generate_schema_name(custom_schema_name, node) %} {% if target.name == 'prod' and custom_schema_name is not none %} {{ custom_schema_name | trim }} {% else %} {{ target.schema }}{% if custom_schema_name is not none %}_{{ custom_schema_name | trim }}{% endif %} {% endif %}{% endmacro %}Avec cette macro, un modèle configuré avec schema: marts atterrit dans :
- Dev :
dbt_yourname_marts - Prod :
marts
Les environnements de dev restent isolés, et la prod bénéficie de noms de schéma propres. La macro est appelée automatiquement par dbt, vous ne l’invoquez pas directement.
Avant d’écrire une nouvelle macro
La meilleure macro est souvent celle que vous n’écrivez pas.
Attendez la règle de trois. La première fois que vous écrivez du code similaire, écrivez-le. La deuxième fois, notez le pattern. La troisième fois, extrayez-le dans une macro. L’abstraction prématurée crée plus de problèmes que la duplication.
Privilégiez la lisibilité au DRY. Le style guide de dbt le dit : “Favor readability when mixing Jinja with SQL, even if it means repeating some lines.” Si comprendre un modèle nécessite d’ouvrir cinq fichiers de macros, vous avez optimisé pour le mauvais objectif. J’explore cet équilibre dans mon article sur les principes DRY dans les macros dbt.
Attention à l’explosion de paramètres. Quand une macro a besoin de sept paramètres pour gérer tous les cas, elle en fait probablement trop. Les macros à responsabilité unique se composent mieux que les couteaux suisses.
Testez le SQL généré, pas la macro. Lancez dbt compile --select model_name et regardez ce qui atterrit dans target/compiled/. Si le SQL ne va pas, la macro ne va pas. Le SQL compilé est la source de vérité. Pour tester les données elles-mêmes, consultez mon cadre de stratégie de tests dbt.
Pour commencer
Ajoutez les packages essentiels à votre packages.yml :
packages: - package: dbt-labs/dbt_utils version: 1.3.3 - package: calogica/dbt_expectations version: [">=0.10.0", "<0.11.0"]Lancez dbt deps pour les installer.
Pour les macros personnalisées, créez une arborescence dans macros/ qui reflète votre organisation du code :
macros/├── _macros.yml # Documentation de toutes les macros├── generate_schema_name.sql├── utils/│ ├── add_audit_columns.sql│ ├── cents_to_dollars.sql│ └── limit_data_in_dev.sqlUn fichier par macro, avec un nom de fichier qui correspond au nom de la macro. Documentez les arguments dans _macros.yml pour que votre équipe sache ce qui est disponible. C’est la même logique que dans mon guide de structure de projet dbt.
La macro de colonnes d’audit est un bon point de départ. Le jour où vous pourrez remonter exactement quel run dbt a créé une ligne pendant un incident en production, vous comprendrez pourquoi ces petits investissements s’accumulent.