Accueil›Blog›Test technique dbt macros et Jinja avancé : générer du SQL dynamiquement

Guide recrutement data

Test technique dbt macros et Jinja avancé : générer du SQL dynamiquement

Les macros dbt permettent de générer du SQL dynamiquement et de créer des abstractions réutilisables. En entretien Expert, on évalue la capacité à écrire des macros sophistiquées.

Data Builder·Juin 2025·7 min de lecture·Analytics Engineer

Sommaire

Jinja2 dans dbt
run_query : SQL dynamique
generate_schema_name
Dispatch et adapter-specific
Créer un package dbt
Metadata et graph
Grille

1Jinja2 : les fonctionnalités avancées

Question discriminante

Quelles fonctionnalités Jinja2 utilisez-vous dans vos macros dbt ?

{# Macro avec logique conditionnelle complexe #}
{% macro generate_surrogate_key(fields) %}

{%- set field_list = [] -%}
{%- for field in fields -%}
    {%- do field_list.append("COALESCE(CAST(" ~ field ~ " AS VARCHAR), '')") -%}
{%- endfor -%}

MD5(CONCAT(
    {{ field_list | join(" || '-' || ") }}
))

{% endmacro %}

{# Macro de pivot dynamique #}
{% macro pivot(column, values, agg='sum', field='amount') %}

{%- for value in values %}
    {{ agg }}(CASE WHEN {{ column }} = '{{ value }}' THEN {{ field }} END) AS {{ value | replace(' ', '_') | lower }}
    {%- if not loop.last %},{% endif %}
{%- endfor %}

{% endmacro %}

2run_query : exécuter du SQL dans les macros

Question discriminante

Comment utilisez-vous run_query() pour générer du SQL dynamiquement ?

{# Générer un pivot dynamique sans connaître les valeurs à l avance #}
{% macro dynamic_pivot(relation, column, agg='SUM', value_col='amount') %}

{# Récupérer toutes les valeurs distinctes #}
{% set query %}
    SELECT DISTINCT {{ column }}
    FROM {{ relation }}
    ORDER BY 1
{% endset %}

{% set results = run_query(query) %}
{% set values = results.columns[0].values() %}

SELECT
    date_trunc('month', order_date) AS month,
    {% for value in values %}
    {{ agg }}(CASE WHEN {{ column }} = '{{ value }}' THEN {{ value_col }} END)
        AS {{ value | replace(' ', '_') | lower }}
    {% if not loop.last %},{% endif %}
    {% endfor %}
FROM {{ relation }}
GROUP BY 1

{% endmacro %}

3generate_schema_name : personnaliser les schémas

Question discriminante

Comment personnalisez-vous les schémas dbt par environnement et par équipe ?

{# macros/generate_schema_name.sql - surcharger le comportement par défaut #}
{% macro generate_schema_name(custom_schema_name, node) -%}

{%- set default_schema = target.schema -%}

{%- if target.name == 'prod' -%}
    {#- En prod : utiliser le schéma custom si défini, sinon le schéma cible -#}
    {%- if custom_schema_name is none -%}
        {{ default_schema }}
    {%- else -%}
        {{ custom_schema_name | trim }}
    {%- endif -%}

{%- elif target.name == 'dev' -%}
    {#- En dev : préfixer par l utilisateur pour isoler les environnements -#}
    {{ env_var('DBT_USER', default_schema) }}_{{ custom_schema_name or 'analytics' }}

{%- else -%}
    {{ default_schema }}
{%- endif -%}

{%- endmacro %}

4Dispatch : macros spécifiques par adapter

Question discriminante

Qu est-ce que dispatch dans dbt ? Comment l utilisez-vous pour du code adapté par warehouse ?

{# Macro qui se comporte différemment selon le warehouse #}

{# macros/date_diff.sql - implémentation par défaut #}
{% macro date_diff(date1, date2, unit='day') -%}
    {{ adapter.dispatch('date_diff', macro_namespace='my_project')(date1, date2, unit) }}
{%- endmacro %}

{# macros/snowflake__date_diff.sql - spécifique Snowflake #}
{% macro snowflake__date_diff(date1, date2, unit='day') -%}
    DATEDIFF('{{ unit }}', {{ date1 }}, {{ date2 }})
{%- endmacro %}

{# macros/bigquery__date_diff.sql - spécifique BigQuery #}
{% macro bigquery__date_diff(date1, date2, unit='day') -%}
    DATE_DIFF({{ date2 }}, {{ date1 }}, {{ unit }})
{%- endmacro %}

5Créer un package dbt interne

Question discriminante

Pourquoi et comment créez-vous un package dbt interne pour votre organisation ?

Pourquoi — centraliser les macros partagées entre plusieurs projets dbt, éviter le copier-coller
Structure — un repo Git séparé avec macros/, models/ (si applicable), packages.yml
Installation — dans packages.yml des projets consommateurs : git: https://github.com/org/dbt-utils-internal
Versionning — utiliser les tags Git pour les versions. Chaque projet épingle sa version
Contenu typique — macros de surrogate key, de date spine, de pivot, de tests custom, de generate_schema_name

6Metadata et graph dbt : introspection

Question discriminante

Comment accédez-vous au graph dbt dans une macro ?

{# Lister tous les modèles d un tag dans une macro #}
{% macro get_models_by_tag(tag) %}

{%- set models = [] -%}
{%- for node in graph.nodes.values() -%}
    {%- if node.resource_type == 'model' and tag in node.tags -%}
        {%- do models.append(node.name) -%}
    {%- endif -%}
{%- endfor -%}

{{ return(models) }}

{% endmacro %}

{# Générer une UNION de tous les modèles staging #}
{% macro union_staging_models() %}

{%- set stg_models = get_models_by_tag('staging') -%}

{%- for model in stg_models %}
SELECT '{{ model }}' AS source_model, * FROM {{ ref(model) }}
{%- if not loop.last %} UNION ALL {% endif %}
{%- endfor %}

{% endmacro %}

7Grille par niveau

Niveau	Maitrise	Signal GO	NO-GO
Senior	Macros avec boucles et conditions, run_query, generate_schema_name	A écrit des macros avec run_query, a personnalisé generate_schema_name	Ne sait pas ce qu est run_query
Expert	Dispatch, packages internes, introspection du graph	A créé un package dbt interne, utilise dispatch pour le multi-warehouse	Ne sait pas ce qu est dispatch

Vous recrutez un Analytics Engineer dbt Expert ?

Premier entretien gratuit. Rapport GO/NO-GO sous 48h.

Tester gratuitement Reserver un appel