dbt-utils : chaque fonction expliquee

dbt-utils est le package le plus utilise dans l’ecosysteme de packages dbt. Pratiquement tous les projets dbt l’installent. Mais la plupart des equipes se contentent de generate_surrogate_key, peut-etre union_relations, et s’arretent la. Le package contient plus de 50 macros et tests generiques qui couvrent la generation SQL, l’introspection des donnees, la validation de qualite et la compatibilite cross-warehouse.

Ceci est une reference complete pour dbt-utils v1.3.3 (la version actuelle, compatible avec dbt Core 1.x et Fusion 2.x). Si vous cherchez une selection des plus utiles, consultez mon guide des macros dbt essentielles.

La migration v1.0 : ce qui a migre vers dbt-core

Le changement le plus important de l’histoire de dbt-utils s’est produit avec la v1.0. Toutes les macros cross-database ont ete retirees de dbt-utils et deplacees vers le namespace dbt dans dbt-core. Si vous appelez encore dbt_utils.datediff(), vous utilisez un chemin qui n’existe plus dans les versions actuelles. Remplacez-le par dbt.datediff().

Voici la liste complete des macros migrees (utilisez dbt.* au lieu de dbt_utils.*) :

Date et heure : dateadd, datediff, date_trunc, last_day, current_timestamp

Chaines de caracteres : concat, length, position, replace, right, split_part, string_literal, escape_single_quotes

Conversion de types : safe_cast, cast (dbt v1.8+), type_bigint, type_float, type_int, type_numeric, type_string, type_timestamp

Agregation : any_value, bool_or, listagg

Operations ensemblistes : except, intersect

Autres : hash, cast_bool_to_text, array_append, array_concat, array_construct (dbt-core 1.3+), date (dbt v1.8+)

Ces macros n’existent plus du tout dans dbt-utils :

• surrogate_key() remplacee par generate_surrogate_key()
• Les tests unique_where et not_null_where remplaces par la configuration native where sur les tests integres
• La materialisation insert_by_period deplacee vers le repo experimental-features
• L’argument condition sur expression_is_true remplace par la configuration native where

Helpers de generation SQL

Ces macros generent des fragments SQL ou des requetes completes. C’est la partie la plus couramment utilisee de dbt-utils.

date_spine

Genere une serie complete de dates ou d’horodatages. Utile pour combler les trous dans les donnees temporelles.

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

Accepte les valeurs datepart : day, week, month, year, hour, minute.

Piege : la date de fin est exclusive. La serie ci-dessus s’arrete au 2024-12-31, pas au 2025-01-01.

deduplicate

Supprime les lignes en double en conservant une ligne par partition, ordonnee selon vos criteres :

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

Changements cassants depuis la v1.0 : group_by a ete renomme en partition_by, relation_alias a ete supprime, et order_by est desormais obligatoire (passez 1 si l’ordre vous est indifferent). Sur Snowflake et BigQuery, cela compile en clause QUALIFY. Sur les autres warehouses, cela utilise une sous-requete ROW_NUMBER.

star

Selectionne 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') }}

Supporte relation_alias, prefix, suffix et quote_identifiers (par defaut True). Execute une introspection a la compilation, donc la relation doit deja etre materialisee.

union_relations

Unite plusieurs relations en gerant automatiquement les colonnes qui existent dans l’une mais pas dans l’autre (remplit les colonnes manquantes avec 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'
) }}

Les parametres include et exclude sont mutuellement exclusifs. Combinez avec get_relations_by_pattern pour la decouverte dynamique de tables (utile pour les tables shardees).

generate_surrogate_key

Cree une cle de substitution basee sur le hachage a partir de plusieurs colonnes :

SELECT
  {{ dbt_utils.generate_surrogate_key(['order__id', 'order__line_number']) }} AS order_line__surrogate_key,
  order__id,
  order__line_number,
  product__id,
  line__quantity,
  line__price
FROM {{ ref('base__shopify__order_lines') }}

Piege critique : si vous migrez depuis l’ancienne macro surrogate_key() vers generate_surrogate_key(), les valeurs de hachage changent pour les lignes contenant des nulls. Cela casse les modeles incrementaux et les snapshots qui utilisaient l’ancienne cle. Pour maintenir la retrocompatibilite, ajoutez ceci a votre dbt_project.yml :

vars:
  surrogate_key_treat_nulls_as_empty_strings: true

pivot et unpivot

pivot cree des tableaux croises en utilisant des expressions CASE :

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

unpivot realise l’operation inverse. Note : l’argument table a ete renomme en relation dans la v1.0.

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

Autres generateurs SQL

Macros introspectives

Ces macros interrogent votre base de donnees a la compilation pour informer votre SQL. Elles sont puissantes mais comportent un piege : elles executent des requetes avant l’execution de vos modeles, donc les relations cibles doivent deja exister.

get_column_values

Recupere les valeurs distinctes d’une colonne. Souvent utilisee avec pivot :

{% set status_values = dbt_utils.get_column_values(
  table=ref('base__shopify__orders'),
  column='order__status',
  order_by='COUNT(*) DESC',
  max_records=50,
  default=['pending', 'completed', 'cancelled']
) %}

Piege : comme cette macro s’execute a la compilation, elle echoue si le modele n’existe pas encore (premier run, nouvel environnement). Fournissez toujours une liste default en secours.

get_filtered_columns_in_relation

Retourne les noms de colonnes d’une relation, avec des exclusions optionnelles. Ne fonctionne que sur les relations materialisees, pas les CTEs. Retourne une liste vide en mode parsing (corrige en v1.3.2).

get_relations_by_pattern

Decouvre les relations correspondant a des wildcards SQL :

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

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

C’est l’approche standard pour consolider des tables shardees (courant avec les exports BigQuery shardes par date).

get_query_results_as_dict et get_single_value

get_query_results_as_dict execute du SQL arbitraire et retourne les resultats sous forme de dictionnaire. get_single_value (nouveau dans la v1.0) retourne une valeur scalaire unique. Les deux s’executent a la compilation.

{% set row_count = dbt_utils.get_single_value(
  "SELECT COUNT(*) FROM " ~ ref('base__shopify__orders')
) %}

Utilisez-les avec parcimonie. Chaque macro introspective ajoute une requete a votre etape de compilation, ce qui ralentit dbt compile et dbt run.

Tests generiques

dbt-utils fournit des tests generiques qui vont bien au-dela des tests integres unique, not_null, accepted_values et relationships. Pour Fusion (dbt 2.0) et dbt Core 1.10.6+, les arguments des tests doivent etre imbriques sous une cle arguments: dans le YAML.

Tests principaux

unique_combination_of_columns teste l’unicite composite :

models:
  - name: mrt__finance__daily_revenue
    data_tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - revenue__date
            - revenue__currency

accepted_range valide que les valeurs se situent dans des bornes :

columns:
  - name: order__total_amount
    data_tests:
      - dbt_utils.accepted_range:
          min_value: 0
          inclusive: true

expression_is_true evalue n’importe quelle expression SQL par ligne :

models:
  - name: mrt__finance__invoices
    data_tests:
      - dbt_utils.expression_is_true:
          expression: "invoice__total_amount >= invoice__tax_amount"
          where: "invoice__status != 'voided'"

Note : l’ancien argument condition a ete supprime dans la v1.0. Utilisez la configuration native where a la place.

recency verifie la fraicheur des donnees :

models:
  - name: base__stripe__charges
    data_tests:
      - dbt_utils.recency:
          datepart: hour
          field: charge__created_at
          interval: 24

Reference complete des tests

Plusieurs tests supportent un parametre group_by_columns pour la validation segmentee : equal_rowcount, fewer_rows_than, recency, at_least_one, not_constant, sequential_values et not_null_proportion.

Pour une vision plus large de la facon dont ces tests s’integrent dans une strategie de tests, j’ai traite le sujet dans un article separe.

Macros web et helpers Jinja

dbt-utils inclut trois macros d’extraction d’URL etonnamment utiles pour l’analytics marketing :

• get_url_parameter(field, url_parameter) extrait les parametres de requete des URLs
• get_url_host(field) extrait le nom d’hote (gere les protocoles http, https et android-app)
• get_url_path(field) extrait le chemin de la page

SELECT
  {{ dbt_utils.get_url_host('page__url') }} AS page__host,
  {{ dbt_utils.get_url_path('page__url') }} AS page__path,
  {{ dbt_utils.get_url_parameter('page__url', 'utm_source') }} AS session__utm_source
FROM {{ ref('base__ga4__events') }}

Pour le developpement et le debogage Jinja, log_info(message) affiche dans la console pendant la compilation, pretty_time(format) retourne un horodatage formate, et slugify(string) convertit du texte en slugs URL-safe.

Comportement cross-warehouse

dbt-utils utilise adapter.dispatch() pour generer du SQL specifique a chaque warehouse sous le capot. La plupart du temps c’est transparent, mais certaines macros compilent differemment selon votre adaptateur :

Si vous utilisez un adaptateur non-core comme Spark ou Databricks, vous devrez probablement configurer dispatch dans votre dbt_project.yml pour mapper dbt_utils vers le package shim de votre adaptateur (comme spark_utils). Consultez mon guide de l’ecosysteme de packages pour les details sur la configuration du dispatch.

Reference rapide : ou se trouve quoi

Si vous ne savez pas si une macro vit dans dbt-utils ou dbt-core, voici la regle generale :

Toujours dans dbt-utils : les generateurs SQL (date_spine, deduplicate, star, union_relations, generate_surrogate_key, pivot, unpivot), les macros introspectives (get_column_values, get_relations_by_pattern), les tests generiques, les macros web et les helpers Jinja.

Migre vers dbt-core (dbt.*) : toutes les conversions de type cross-database, les fonctions date/heure, les fonctions de chaines, les fonctions d’agregation et les operations ensemblistes.

Supprime definitivement : surrogate_key(), unique_where, not_null_where, insert_by_period et l’argument condition sur expression_is_true.

En cas de doute, consultez le changelog de dbt-utils sur GitHub pour le guide de migration v1.0.0.