Générateurs SQL de dbt-utils

Les générateurs SQL sont la partie la plus utilisée de dbt-utils. Ils ne requêtent pas votre base de données — ils génèrent des fragments SQL ou des requêtes complètes à la compilation, en se basant sur les paramètres que vous leur passez. Contrairement aux macros introspectives, ils n’ont pas de dépendances à la compilation sur des relations existantes.

Cette note couvre les générateurs qui transforment les données : date_spine, deduplicate, star, union_relations, pivot et unpivot. Pour generate_surrogate_key, voir la note dédiée — son piège de gestion des nulls est assez significatif pour justifier un traitement séparé.

date_spine

Génère une série de dates ou d’heures complète. Le cas d’usage canonique : un modèle de série temporelle qui doit afficher des zéros pour les jours sans activité, plutôt que d’omettre simplement ces jours.

SELECT *
FROM (
  {{ dbt_utils.date_spine(
    datepart="day",
    start_date="CAST('2024-01-01' AS DATE)",
    end_date="CAST('2025-01-01' AS DATE)"
  ) }}
)

Les valeurs datepart acceptées sont : day, week, month, year, hour, minute.

Piège : la date de fin est exclusive. La série ci-dessus s’arrête au 31 décembre 2024, pas au 1er janvier 2025. Si vous souhaitez une année complète incluant le dernier jour, ajoutez une unité à votre date de fin. Ce piège surprend à chaque fois.

Le pattern typique est de joindre cette série à votre table de faits et d’utiliser COALESCE pour remplir les valeurs manquantes par zéro :

WITH spine AS (
  {{ dbt_utils.date_spine(
    datepart="day",
    start_date="CAST('2024-01-01' AS DATE)",
    end_date="CAST('2025-01-01' AS DATE)"
  ) }}
),
daily_orders AS (
  SELECT DATE(order__created_at) AS order__date, COUNT(*) AS order__count
  FROM {{ ref('base__shopify__orders') }}
  GROUP BY 1
)

SELECT
  spine.date_day,
  COALESCE(daily_orders.order__count, 0) AS order__count
FROM spine
LEFT JOIN daily_orders ON spine.date_day = daily_orders.order__date

Pour des séries de dates plus complexes (calendriers fiscaux, jours ouvrés), envisagez le package dbt-date, qui s’appuie sur date_spine.

deduplicate

Supprime les lignes dupliquées en conservant une ligne par partition, triée selon vos critères :

SELECT *
FROM (
  {{ dbt_utils.deduplicate(
    relation=ref('base__crm__contacts'),
    partition_by='contact__id',
    order_by='contact__updated_at DESC'
  ) }}
)

C’est la manière standard de dédupliquer les tables sources brutes où le même enregistrement apparaît plusieurs fois — souvent en raison du comportement de réplication. Vous conservez la version la plus récente de chaque entité.

Changements incompatibles de v0.x à v1.0 :

• group_by a été renommé en partition_by
• relation_alias a été entièrement supprimé
• order_by est maintenant obligatoire — passez 1 si l’ordre n’a pas d’importance

Sur Snowflake et BigQuery, cela compile vers une clause QUALIFY, qui est la manière native et efficace d’exprimer le filtrage basé sur des fonctions de fenêtre sur ces plateformes :

SELECT *
FROM base__crm__contacts
QUALIFY ROW_NUMBER() OVER (PARTITION BY contact__id ORDER BY contact__updated_at DESC) = 1

Sur Postgres et Redshift (sans QUALIFY), cela compile vers une sous-requête ROW_NUMBER(). La sémantique est identique ; seule la forme SQL diffère.

Utilisez deduplicate dans les modèles de la couche base où vous importez une table snapshot brute et devez établir une ligne par granularité. Ne l’utilisez pas pour masquer un modèle qui produit des doublons — trouvez et corrigez la source de la duplication à la place.

star

Sélectionne toutes les colonnes d’une relation avec des exclusions optionnelles :

SELECT
  {{ dbt_utils.star(from=ref('base__shopify__orders'), except=["_fivetran_synced", "_fivetran_deleted"]) }}
FROM {{ ref('base__shopify__orders') }}

C’est précieux pour les modèles de la couche base où vous souhaitez faire passer toutes les colonnes amont en supprimant les métadonnées ou les colonnes staging dont vous n’avez pas besoin en aval.

Paramètres supplémentaires :

• relation_alias — ajoute un préfixe d’alias de table à chaque colonne (ex. orders.order__id)
• prefix — préfixe une chaîne à chaque nom de colonne
• suffix — ajoute une chaîne à chaque nom de colonne
• quote_identifiers — par défaut True ; passez False si votre entrepôt est insensible à la casse et que vous ne souhaitez pas de noms entre guillemets

Contrainte critique : la relation doit déjà être matérialisée. star exécute une introspection à la compilation pour découvrir la liste des colonnes. Si vous exécutez un modèle pour la première fois dans un environnement vierge, la relation amont n’existe pas encore et la macro échouera. C’est la même contrainte que toutes les macros introspectives.

En pratique, star fonctionne bien dans les modèles de la couche base qui s’appuient directement sur les tables sources (qui existent toujours avant que dbt s’exécute), mais peut être fragile dans les modèles intermediate qui dépendent d’autres modèles dbt qui n’ont peut-être pas encore été construits.

union_relations

Effectue l’union de plusieurs relations, en gérant automatiquement les colonnes qui existent dans l’une mais pas dans une autre en remplissant les colonnes manquantes par NULL :

{{ dbt_utils.union_relations(
  relations=[ref('base__stripe__charges_us'), ref('base__stripe__charges_eu')],
  exclude=["_loaded_at"],
  source_column_name='_dbt_source_relation'
) }}

Le paramètre source_column_name ajoute une colonne indiquant de quelle relation provient chaque ligne — essentiel pour tracer les enregistrements jusqu’à leur source après l’union.

Quelques détails à connaître :

• include et exclude sont mutuellement exclusifs. Vous pouvez lister les colonnes à conserver, ou les colonnes à supprimer, mais pas les deux.
• La macro gère l’ordre des colonnes : elle construit une liste cohérente de colonnes dans toutes les relations et émet NULL AS column_name pour toute colonne qui n’existe pas dans une relation donnée.
• La sortie est un UNION ALL complet, pas un UNION DISTINCT. La déduplication est de votre responsabilité en aval.

L’usage le plus puissant de union_relations est de le coupler avec get_relations_by_pattern pour la découverte dynamique de tables. C’est l’approche standard pour consolider les tables date-shardées BigQuery :

{% set sharded_tables = dbt_utils.get_relations_by_pattern(
  'raw_data_%',
  'events_%',
  exclude='%deprecated'
) %}

{{ dbt_utils.union_relations(relations=sharded_tables) }}

Cela découvre toutes les tables correspondant au pattern à la compilation et les unit dans un seul modèle. Au fur et à mesure que de nouveaux shards sont créés, le modèle les intègre automatiquement à la prochaine exécution. Voir Macros introspectives de dbt-utils pour les détails de get_relations_by_pattern.

pivot et unpivot

pivot crée des tables pivot en utilisant des expressions CASE, transformant les valeurs d’une colonne catégorielle en colonnes séparées. L’usage typique : compter ou sommer une métrique par catégorie, où chaque catégorie devient sa propre colonne.

SELECT
  order__date,
  {{ dbt_utils.pivot(
    column='order__status',
    values=dbt_utils.get_column_values(ref('base__shopify__orders'), 'order__status'),
    agg='COUNT',
    then_value=1,
    else_value=0,
    prefix='status_'
  ) }}
FROM {{ ref('base__shopify__orders') }}
GROUP BY 1

Le paramètre values accepte soit une liste codée en dur, soit la sortie de get_column_values. Utiliser get_column_values rend le pivot dynamique — il s’adapte lorsque de nouvelles valeurs de statut apparaissent dans les données. Le compromis est la dépendance à la compilation sur une relation existante.

unpivot fait l’inverse : prend des données larges avec de nombreuses colonnes et les convertit au format long avec une colonne nom-de-colonne et une colonne valeur :

{{ dbt_utils.unpivot(
  relation=ref('mrt__sales__quarterly_revenue'),
  cast_to='FLOAT64',
  exclude=['region__name'],
  field_name='quarter',
  value_name='revenue'
) }}

Note de migration depuis v0.x : L’argument table a été renommé en relation en v1.0. Si vous avez des appels unpivot existants avec table=, mettez-les à jour.

Les helpers plus petits

Ceux-ci sont réellement utiles mais apparaissent moins souvent :

safe_add et safe_subtract méritent une note : depuis v1.0 ils prennent un argument liste. L’ancienne forme à deux arguments ne fonctionne plus. Utilisez {{ dbt_utils.safe_add(['col_a', 'col_b', 'col_c']) }} au lieu de {{ dbt_utils.safe_add('col_a', 'col_b') }}.

haversine_distance est de niche mais il y a généralement un moment dans le travail analytics où vous devez mesurer des distances géographiques — zones de chalandise retail, analyse de rayon de livraison, matching de proximité. Cela évite d’écrire la formule depuis zéro.

Compilation cross-warehouse

La plupart des générateurs SQL compilent de manière identique selon les adaptateurs. L’exception est deduplicate, qui utilise QUALIFY sur Snowflake et BigQuery mais une sous-requête ROW_NUMBER() sur Postgres et Redshift. Cela est géré automatiquement par le mécanisme de dispatch de dbt-utils — vous n’avez rien à configurer à moins d’être sur un adaptateur non standard comme Databricks, auquel cas vous aurez besoin de spark_utils dans votre configuration de dispatch. Voir Configuration du dispatch dbt pour les détails d’installation.