Utilisation avancée de Jinja avec dbt

Étude de cas : créer des modèles de données e-commerce avec dbt

Susan Sun

Freelance Data Scientist

Instruction Jinja : boucle

Les instructions Jinja sont délimitées par {% %} :

Boucler sur des éléments

Modèle :
```
{% for ... %} ... {% endfor %}
```

Exemple :

{% for order_status in order_statuses %}
  SUM(
      CASE WHEN status = '{{ order_status }}' 
        THEN order_id 
      END)
{% endfor %}

Instruction Jinja : boucle

SQL répétitif :

SELECT 
    user_id,
    SUM(CASE WHEN status = 'Shipped' THEN 1 ELSE 0 END) 
        AS num_orders_Shipped,
    SUM(CASE WHEN status = 'Complete' THEN 1 ELSE 0 END) 
        AS num_orders_Complete,
    SUM(CASE WHEN status = 'Processing' THEN 1 ELSE 0 END) 
        AS num_orders_Processing,        
FROM {{ ref('stg_looker__orders') }}
GROUP BY 1

Instruction Jinja : boucle

{% set order_statuses = ['Shipped', 'Complete', 'Processing'] %}

SELECT 
    user_id,
    -- Boucle Jinja
    {% for order_status in order_statuses %}
        SUM(CASE WHEN status = '{{ order_status }}' THEN 1 ELSE 0 END) 
        -- Nom de colonne paramétré
        AS num_orders_{{ order_status }}
    {% endfor %}
FROM {{ ref('stg_looker__orders') }}
GROUP BY 1

Instruction Jinja : boucle

Sortie de dbt compile :

SELECT 
    user_id,
    SUM(CASE WHEN status = 'Shipped' THEN 1 ELSE 0 END) 
        AS num_orders_Shipped,
    SUM(CASE WHEN status = 'Complete' THEN 1 ELSE 0 END) 
        AS num_orders_Complete,
    SUM(CASE WHEN status = 'Processing' THEN 1 ELSE 0 END) 
        AS num_orders_Processing,        
FROM "dbt"."main"."stg_looker__orders"
GROUP BY 1

Instruction Jinja : macros

Une macro Jinja :

Est une fonction, écrite en Jinja
Est réutilisable
Est stockée dans le dossier /macros

COALESCE() :

renvoie la première valeur non nulle
syntaxe : COALESCE(value1, value2, ...)
p. ex. COALESCE(nickname, first_name)

Exemple de fonction macro :

Une capture de l'IDE, avec le fichier `coalesce_and_round.sql` ouvert dans l'éditeur, montrant la logique de la macro.

Instruction Jinja : macros

Les instructions Jinja sont délimitées par {% %} :

Une fonction macro réutilisable :

Modèle :
```
{% macro ... %} ... {% endmacro %}
```

Exemple :

{% macro coalesce_and_round(column_name, decimal_places = 2) %}
      ROUND(COALESCE({{ column_name }}, 0), {{ decimal_places }})
{% endmacro %}

Instruction Jinja : macros

SQL répétitif

ROUND(COALESCE(sales_amount, 0), 2) AS sales_amount,
ROUND(COALESCE(cost_of_goods_sold, 0), 2) AS cost_of_goods_sold,

DRY (Don't Repeat Yourself)

Créez la macro coalesce_and_round.sql
Stockez-la dans /macros

Appel :

{{ coalesce_and_round('sales_amount', 2) }} AS sales_amount,
{{ coalesce_and_round('cost_of_goods_sold', 2) }} AS cost_of_goods_sold,

Gérer les espaces Jinja

Tel qu'écrit :

{% set traffic_source_values = ['Adwords', 'Email', 'Facebook'] %}
{% set browser_values = ['Chrome', 'Firefox', 'Safari', 'IE', 'Other'] %}

SELECT 
    user_id,
    COUNT(DISTINCT session_id) AS num_web_sessions,
...

Gérer les espaces Jinja

Compilé :




SELECT 
    user_id,
    COUNT(DISTINCT session_id) AS num_web_sessions,
...

Gérer les espaces Jinja

{%- ... %} supprime l'espace blanc avant.
{% ... -%} supprime l'espace blanc après.
{%- ... -%} supprime les deux.
Exemple :

{%- set traffic_source_values = ['Adwords', 'Email', 'Facebook'] -%}
{%- set browser_values = ['Chrome', 'Firefox', 'Safari', 'IE', 'Other'] -%}

SELECT 
    user_id,
    COUNT(DISTINCT session_id) AS num_web_sessions,
...

Passons à la pratique !

Étude de cas : créer des modèles de données e-commerce avec dbt