Macros cross-database : écrire une fois, exécuter partout

Vous avez construit un projet dbt solide sur BigQuery. Les macros fonctionnent, les modèles tournent, votre équipe est productive. Puis votre entreprise rachète une startup sur Snowflake, ou vous décidez de publier vos macros en tant que package pour la communauté. Et d’un coup, plus rien ne marche.

Les dialectes SQL se ressemblent, jusqu’à ce qu’ils ne se ressemblent plus. Les fonctions de date prennent leurs arguments dans un ordre différent. Les opérations sur les arrays utilisent une syntaxe complètement différente. Ce qui compilait sans erreur sur une base de données échoue sur une autre.

Ce guide s’appuie sur les fondamentaux des macros dbt pour vous montrer comment écrire des macros qui s’adaptent automatiquement à différents data warehouses. Vous y découvrirez le pattern dispatch, les points de divergence entre les principales bases de données, et quand utiliser les fonctions cross-database intégrées à dbt plutôt que d’écrire les vôtres.

Les dialectes SQL ne se ressemblent pas tant que ça

La plupart des analytics engineers commencent sur une seule base de données et apprennent ses particularités. Le DATE_ADD de BigQuery paraît naturel. Le DATEADD de Snowflake a sa logique. On pourrait croire que passer de l’un à l’autre ne demande que quelques ajustements de syntaxe.

C’est vrai pour les requêtes simples (SELECT, WHERE, GROUP BY fonctionnent partout). Mais dès que vous écrivez des macros qui génèrent du SQL dynamiquement, trois catégories de fonctions vont vous poser problème :

Les opérations de date et d’heure ont des ordres d’arguments et des noms de fonctions différents selon les bases. BigQuery place le datepart en dernier ; Snowflake et Databricks le placent en premier. Une macro qui fonctionne sur l’un génèrera du SQL invalide sur l’autre.

La manipulation des arrays varie considérablement. BigQuery utilise UNNEST, Snowflake utilise LATERAL FLATTEN, et Databricks utilise EXPLODE. Il n’existe aucune syntaxe commune, ce qui oblige à écrire du SQL différent pour chaque base.

Le casting de types et le parsing JSON utilisent des noms de fonctions différents, et parfois des approches fondamentalement différentes. Le safe cast est SAFE_CAST sur BigQuery mais TRY_CAST partout ailleurs.

Une seule macro défaillante ne casse pas qu’un modèle. Si elle est utilisée par plusieurs modèles, c’est tout le DAG en aval qui échoue à la compilation. Comprendre ces différences compte, même si vous ne travaillez que sur une seule base de données aujourd’hui.

Comment fonctionne le pattern dispatch de dbt

Le pattern dispatch permet d’écrire une seule macro qui utilise automatiquement la bonne implémentation pour la base de données ciblée. Quand vous appelez la macro, dbt vérifie quel adapter est actif et route vers la version appropriée.

Une macro dispatch se compose de deux parties :

{% macro my_dateadd(datepart, interval, from_date) %}
    {{ return(adapter.dispatch('my_dateadd')(datepart, interval, from_date)) }}
{% endmacro %}

{% macro default__my_dateadd(datepart, interval, from_date) %}
    dateadd({{ datepart }}, {{ interval }}, {{ from_date }})
{% endmacro %}

{% macro bigquery__my_dateadd(datepart, interval, from_date) %}
    date_add({{ from_date }}, interval {{ interval }} {{ datepart }})
{% endmacro %}

La macro principale fait office de routeur : elle appelle adapter.dispatch() avec le nom de la macro pour obtenir l’implémentation appropriée. Les implémentations sont des macros séparées dont le préfixe indique l’adapter ciblé.

dbt recherche les implémentations dans un ordre précis :

1. {adapter}__macro_name (ex. bigquery__my_dateadd)
2. {parent_adapter}__macro_name (pour les adapters qui héritent d’un autre)
3. default__macro_name

Si vous exécutez sur BigQuery, dbt utilise bigquery__my_dateadd. Si vous êtes sur Redshift et que vous n’avez pas écrit de version spécifique, dbt se rabat sur default__my_dateadd.

Autrement dit, vous pouvez commencer par une implémentation par défaut qui couvre la plupart des bases, puis ajouter des versions spécifiques seulement là où c’est nécessaire.

Fonctions de date et d’heure : le premier obstacle

Les fonctions de date sont généralement le premier point de friction cross-database. Le problème central est l’ordre des arguments. BigQuery fait les choses différemment de tous les autres.

Le pattern est clair : BigQuery place le datepart à la fin, tandis que Snowflake et Databricks le placent au début. BigQuery utilise aussi le mot-clé INTERVAL, contrairement aux autres.

Pour la troncature de date, Snowflake et Databricks s’accordent entre eux mais pas avec BigQuery sur l’ordre des arguments. Ces petites différences s’accumulent quand on génère du SQL dynamiquement.

Utilisez les macros intégrées. Depuis dbt 1.8, les fonctions de date cross-database se trouvent dans le namespace dbt :

{{ dbt.dateadd(datepart="day", interval=7, from_date_or_timestamp="order_date") }}
{{ dbt.datediff("start_date", "end_date", "day") }}

Elles gèrent automatiquement la réorganisation des arguments. Si vous utilisiez dbt_utils.dateadd() auparavant, passez à dbt.dateadd() (la fonctionnalité a été intégrée au core).

Quand vous avez besoin d’une logique de date personnalisée qui dépasse ce que les fonctions intégrées proposent, utilisez dispatch :

{% macro get_fiscal_quarter_start(date_column) %}
    {{ return(adapter.dispatch('get_fiscal_quarter_start')(date_column)) }}
{% endmacro %}

{% macro default__get_fiscal_quarter_start(date_column) %}
    date_trunc('quarter', {{ date_column }})
{% endmacro %}

{% macro bigquery__get_fiscal_quarter_start(date_column) %}
    date_trunc({{ date_column }}, quarter)
{% endmacro %}

Opérations sur les arrays : là où les bases divergent vraiment

La manipulation des arrays est le domaine où les bases de données prennent des chemins radicalement différents. Il n’y a aucune similarité syntaxique sur laquelle s’appuyer, et il faut du SQL complètement différent pour chaque plateforme.

Même ARRAY_CONTAINS ne s’accorde pas sur l’ordre des arguments entre Snowflake et Databricks.

Voici une macro dispatch pour aplatir des arrays dans une clause FROM :

{% macro flatten_array(table_alias, array_column, element_alias) %}
    {{ return(adapter.dispatch('flatten_array', 'my_project')(table_alias, array_column, element_alias)) }}
{% endmacro %}

{% macro bigquery__flatten_array(table_alias, array_column, element_alias) %}
    {{ table_alias }}, UNNEST({{ array_column }}) AS {{ element_alias }}
{% endmacro %}

{% macro snowflake__flatten_array(table_alias, array_column, element_alias) %}
    {{ table_alias }}, LATERAL FLATTEN(input => {{ array_column }}) AS {{ element_alias }}
{% endmacro %}

{% macro databricks__flatten_array(table_alias, array_column, element_alias) %}
    {{ table_alias }} LATERAL VIEW EXPLODE({{ array_column }}) AS {{ element_alias }}
{% endmacro %}

Utilisation dans un modèle :

SELECT
    e.event_id,
    param.value AS param_value
FROM {{ ref('base__app__events') }} e
{{ flatten_array('e', 'e.event_params', 'param') }}

Le résultat compilé sera UNNEST sur BigQuery, LATERAL FLATTEN sur Snowflake, ou LATERAL VIEW EXPLODE sur Databricks.

Safe cast et gestion des types

Le casting de types semble simple, jusqu’à ce qu’une valeur ne puisse pas être convertie. Un CAST classique lève une erreur ; le safe cast renvoie NULL à la place. Les noms de fonctions diffèrent :

Les noms de types varient aussi. Ce que BigQuery appelle STRING, Snowflake l’appelle VARCHAR, et certains contextes utilisent TEXT. Les spécifications de longueur diffèrent également.

dbt fournit dbt.safe_cast() combiné à api.Column.translate_type() pour gérer ces deux aspects :

{{ dbt.safe_cast("user_input", api.Column.translate_type("string")) }}

La fonction translate_type() convertit les noms de types génériques en leur équivalent spécifique à l’adapter. Utilisez-la à chaque fois que vous générez du DDL ou du SQL typé.

Pour le type lui-même, dbt.type_string() renvoie le type chaîne correct pour l’adapter actif :

CAST({{ column }} AS {{ dbt.type_string() }})

Configurer dispatch pour les packages

Quand vous utilisez des packages comme dbt-utils, le système dispatch peut être personnalisé pour modifier l’ordre de recherche des implémentations.

Dans votre dbt_project.yml :

dispatch:
  - macro_namespace: dbt_utils
    search_order:
      - my_project      # Vérifier votre projet en premier
      - spark_utils     # Couche de compatibilité pour Spark/Databricks
      - dbt_utils       # Package d'origine

Le search_order contrôle où dbt cherche les implémentations spécifiques à chaque adapter. C’est utile dans deux cas :

Surcharger le comportement d’un package. Si une macro de dbt-utils ne convient pas tout à fait à votre setup, vous pouvez écrire votre propre version dans votre projet. Placez votre projet en premier dans l’ordre de recherche, et dbt utilisera votre implémentation.

Ajouter le support Databricks via spark_utils. Beaucoup de packages ont été écrits avant que Databricks ne soit répandu. Le package spark_utils fournit des implémentations compatibles Spark pour les macros courantes. L’ajouter à l’ordre de recherche comble les lacunes.

Quand vous écrivez vos propres macros dispatch, incluez le nom du projet comme second argument de adapter.dispatch() :

{{ adapter.dispatch('my_macro', 'my_project')(args) }}

Cela garantit que dbt cherche dans le bon namespace lors de la résolution de l’implémentation.

Tester sur plusieurs adapters

Si vos macros doivent fonctionner sur plusieurs bases de données, testez-les sur chacune d’elles. Une stratégie de matrice CI/CD rend cela gérable :

jobs:
  test:
    strategy:
      matrix:
        adapter: [bigquery, snowflake, databricks]
    steps:
      - run: pip install dbt-${{ matrix.adapter }}
      - run: dbt build --target ${{ matrix.adapter }}

Cette configuration exécute l’ensemble de vos tests sur chaque adapter en parallèle. Vous aurez besoin de profils de connexion séparés dans votre profiles.yml pour chaque cible.

Que faut-il tester ? Deux aspects comptent :

La validité syntaxique. Le SQL généré compile-t-il sans erreur ? Le pipeline CI le vérifie automatiquement. Si dbt build réussit, le SQL est syntaxiquement valide.

L’équivalence sémantique. La macro produit-elle le même résultat logique sur chaque base ? Cela nécessite des modèles de test avec des entrées connues et des résultats attendus. Créez un modèle simple qui utilise votre macro, puis ajoutez un test vérifiant que le résultat correspond aux attentes.

Pour les macros complexes, pensez à créer un modèle de test dédié :

-- models/tests/test_flatten_array.sql
{{ config(materialized='table', tags=['test']) }}

WITH test_data AS (
    SELECT 1 AS id, {{ array_construct([1, 2, 3]) }} AS values
)

SELECT
    id,
    element
FROM test_data
{{ flatten_array('test_data', 'test_data.values', 'element') }}

Ajoutez ensuite des tests de schéma vérifiant que le résultat contient trois lignes avec les valeurs attendues.

Macros cross-database intégrées à utiliser

Avant d’écrire des macros dispatch personnalisées, vérifiez si dbt fournit déjà ce dont vous avez besoin. Depuis la version 1.8, ces macros se trouvent dans le namespace dbt :

Note de migration : Beaucoup de ces macros se trouvaient auparavant dans dbt_utils. Si vous mettez à jour un ancien projet, remplacez vos références de dbt_utils.dateadd() par dbt.dateadd(). Les versions dbt-utils fonctionnent encore mais sont dépréciées. Pour une liste complète des macros recommandées, consultez les macros essentielles pour chaque projet.

La macro dbt.concat() est particulièrement utile car elle gère les différences entre les appels à la fonction CONCAT() et les opérateurs || selon les bases :

{{ dbt.concat(["first_name", "' '", "last_name"]) }} AS full_name

Pour tout ce qui n’est pas couvert par les fonctions intégrées (opérations sur les arrays, parsing JSON, optimisations spécifiques à une base), écrivez vos propres macros dispatch en suivant les patterns de ce guide.

Rendre vos macros portables

Développer des macros cross-database demande de savoir où les dialectes SQL divergent et d’avoir une stratégie pour gérer ces différences. Le pattern dispatch vous donne cette stratégie.

Commencez par auditer vos macros existantes pour repérer les fonctions de date codées en dur, les opérations sur les arrays et les casts de type. Remplacez-les par les fonctions dbt.* intégrées quand elles existent. Pour le reste, créez des wrappers dispatch avec des implémentations spécifiques à chaque adapter, en appliquant les principes DRY pour des macros réutilisables.

L’effort en vaut la peine à plusieurs titres : des migrations simplifiées quand votre entreprise change de base de données, la possibilité de publier des packages utiles à toute la communauté, et un code plus propre qui sépare les spécificités de chaque base de la logique métier.

Même si vous n’utilisez que BigQuery aujourd’hui, écrire des macros portables est une forme de documentation. Les implémentations dispatch rendent explicite ce qui est propre au dialecte SQL de chaque base, ce qui aide toute personne qui maintiendra le code par la suite.