Que garantissent vraiment les macros cross-database de dbt ?

En bref

En clair : Un même projet de transformation de données peut tourner sur deux bases différentes, mais « même code » ne veut pas dire « même résultat » : voici cinq cas où ça diffère.

Les « macros cross-database » de dbt sont censées écrire le bon SQL pour chaque moteur (BigQuery, DuckDB…). Sur mon projet, elles tiennent leur promesse pour la syntaxe. Mais cinq cas réels montrent qu’elles ne garantissent ni le même résultat, ni la même configuration : une erreur sur un moteur, un NULL sur l’autre, un nombre arrondi sans aucun message. L’enjeu : pouvoir exécuter le même projet sur DuckDB, en local ou dans un job peu coûteux, sans toucher à la production, en sachant ce que les macros garantissent.

Quel est le contexte ?

Mon projet personnel collecte chaque jour les prix des carburants de toutes les stations-service de France (données publiques) et les transforme avec dbt sur BigQuery. Je voulais pouvoir l’exécuter aussi sur DuckDB, en local ou dans un job peu coûteux, sans toucher à la production.

Par exemple, {{ dbt.safe_cast('x', dbt.type_int()) }} produit safe_cast(x as INT64) sur BigQuery et cast(x as integer) sur DuckDB (documentation dbt). Une vingtaine de modèles et plusieurs macros de mon projet contenaient du SQL écrit pour BigQuery seul.

Quel était le problème ?

Remplacer à la main des dizaines d’occurrences est fragile : on en oublie, et la prochaine modification en réintroduit. Surtout, un SQL qui se compile sur le second moteur peut y donner un autre résultat, sans erreur. La question de ce retour d’expérience : que garantissent vraiment ces macros ?

Qu’ai-je fait ?

J’ai écrit un petit outil qui repère le SQL propre à BigQuery et le remplace par des macros, uniquement quand la correspondance est mécanique. Puis j’ai comparé le SQL produit sur les deux moteurs, et exécuté les cas où un écart était possible. Côté syntaxe, les macros font leur travail :

Voici les cinq cas où ça ne suffit pas.

Cas 1 : le même nom de macro n’a pas le même contrat selon le moteur (une erreur). Mes fichiers de prix sont rangés par dossiers année, mois, jour : j’en tire une date avec date(year, month, day) sur BigQuery. Avec dbt.date(), j’ai passé des expressions SQL (des colonnes converties en entiers). Sur BigQuery, ça compile : la macro insère les arguments tels quels dans le SQL. Sur DuckDB, l’analyse échoue avec « Year argument required » (dbt 2.0.8, reproduit sur un projet minimal) : la macro y construit un littéral (to_date('2023-10-04', 'YYYY-MM-DD')), ce qui exige des entiers connus à la compilation, comme dans l’exemple de la documentation, dbt.date(2023, 10, 4). Même nom, même signature, deux contrats. Piège voisin : avec des identifiants nus au lieu de chaînes, BigQuery compile sans erreur en date(, , ), un SQL invalide qu’on ne découvre qu’à l’exécution. Mon premier contournement (convertir les colonnes en entiers) n’a pas tenu sur DuckDB, ces conversions restant des expressions SQL. J’ai écrit ma propre macro de trois lignes : make_date(...) sur DuckDB, date(...) ailleurs.

Cas 2 : la configuration de partitionnement n’est pas portable (une erreur). Sur BigQuery, je déclare le partitionnement d’une table avec un bloc partition_by (champ, type, granularité). Le même nom de configuration est lu par une version récente de l’adapter DuckDB pour son propre partitionnement physique, et il rejette ce bloc. J’ai dû neutraliser cette configuration pour DuckDB dans les quatre modèles concernés. La portabilité ne concerne pas que le SQL : la configuration aussi.

Cas 3 : safe_cast n’est pas « sûr » partout (un résultat différent). dbt.safe_cast("'abc'", dbt.type_int()) renvoie NULL sur BigQuery, mais lève l’erreur « Conversion Error: Could not convert string ‘abc’ to INT32 » sur DuckDB, où la macro génère un cast ordinaire. La documentation le dit à demi-mot : la macro renvoie NULL en cas d’échec « pour les bases qui le supportent ». Une donnée sale fait donc tomber un job sur un moteur et passe sur l’autre. Piège voisin : api.Column.translate_type('double') ne traduit rien (clé non reconnue, l’argument est renvoyé tel quel). BigQuery répond « Type not found: double » alors que DuckDB l’accepte : l’erreur n’apparaît que sur le moteur où je développe le moins.

Cas 4 : un nombre arrondi sans aucun message (un résultat différent). dbt.type_float() produit un flottant sur 4 octets sur DuckDB (simple précision, documentation DuckDB), contre 8 octets sur BigQuery. Exécutée sur les deux moteurs, la latitude 48,856614 revient à 48,856613 sur DuckDB, intacte sur BigQuery : un millionième de degré, de l’ordre de dix centimètres (un degré de latitude fait environ 111 km). dbt.type_numeric() n’est pas identique non plus : 6 chiffres après la virgule sur DuckDB, 9 sur BigQuery. Pour mon usage (placer une station sur une carte), dix centimètres ne comptent pas, j’ai donc accepté l’écart. Pour une distance ou une jointure géographique, il faudrait forcer un type sur 8 octets (double) : la substitution n’est pas sûre en général.

dbt show --target <cible_duckdb> --inline \
  "select cast(cast(48.856614 as {{ dbt.type_float() }}) as varchar) as valeur"

Cas 5 : un dbt compile réussi ne prouve rien (des erreurs silencieuses). Compiler vérifie que les templates Jinja se génèrent, pas que le SQL est valide ni équivalent. Comparer côte à côte le SQL compilé des deux moteurs a révélé trois choses que le compilateur ne signalait pas. D’abord, des noms de colonnes entre accents graves, valides sur BigQuery et invalides sur DuckDB. Ensuite, l’ordre des deux dates inversé entre DATE_DIFF de BigQuery et dbt.datediff : il faut permuter les deux premiers arguments, sinon le signe du résultat change. Enfin, dans mon propre outil, sqlglot (traducteur de dialectes SQL) prend le template {{ column_name }} pour une structure de données et le réécrit en {'_0': {...}} sans erreur. Masquer chaque bloc {{ ... }} avant la traduction, puis le restaurer, corrige le problème. Le même masquage sert pour une migration à plus grande échelle, décrite dans Comment migrer 1 000 modèles dbt de Trino vers BigQuery sans régression ?.

La limite de cette validation. J’ai compilé les 20 modèles du projet (54 tests) sur DuckDB, mais je ne les ai pas exécutés de bout en bout sur ce moteur : leurs sources sont des fichiers Parquet sur un stockage distant.

Qu’ai-je appris, et à quoi cela renvoie-t-il ?

Quelles règles en retenir ?

• Une macro cross-database garantit la syntaxe, pas la sémantique. Même nom, comportement différent : erreur ou NULL, précision, ordre des arguments.
• « Une macro existe » et « on peut réécrire automatiquement » sont deux seuils différents. Corriger ce qui est mécanique, signaler le reste à un humain.
• La configuration se teste comme le SQL. Un même nom de réglage peut signifier deux choses selon l’adaptateur.
• Compiler est nécessaire, pas suffisant. Il faut comparer le SQL produit sur le second moteur et exécuter les cas où un écart est possible.

Quels principes connus ce cas illustre-t-il ?

• La portabilité du SQL est vraie pour la syntaxe et fausse pour la sémantique. Ordre des arguments, précision des types, gestion des valeurs invalides : c’est pour cela que les suites de tests de conformité existent.
• Les outils qui ignorent le templating. Un analyseur de SQL qui rencontre du Jinja fait le même contresens qu’un lecteur de JSON qui rencontre des commentaires : sans erreur, avec un résultat faux.

Que ferais-je autrement ?

• Écrire d’abord des golden tests par moteur avant l’outil. Le cas safe_cast aurait été attrapé par un test et non découvert à l’exécution. Coût : des jeux de données valides pour chaque moteur, un travail déjà lourd pour les seuls casts.
• Définir mes propres macros par moteur pour les écarts de sémantique (un safe_cast qui renvoie NULL partout, via le mécanisme adapter.dispatch de dbt). Gain : un comportement identique et testé. Coût : du code de compatibilité à maintenir, qui double ce que dbt fournit déjà.
• Lancer un dbt build complet sur DuckDB dès le départ, avec un jeu de données de test, au lieu de me contenter de la compilation. C’est la seule façon de lever la limite énoncée plus haut.