Patterns de la couche intermédiaire dbt

Les modèles intermédiaires joignent des entités, ajoutent des champs calculés et encodent des règles métier. La contrainte critique : la granularité ne se réduit jamais. Si un modèle de base a une ligne par commande, le modèle intermédiaire a également une ligne par commande — plus large et enrichi, mais pas agrégé. Agréger des lignes franchit dans le territoire des marts.

Ce qui appartient aux modèles intermédiaires

Joindre des modèles de base ensemble. Une commande a besoin des données client. Une session a besoin des données de source de trafic. Un paiement a besoin du contexte d’abonnement. Ces jointures se produisent dans les intermédiaires, créant des modèles d’entités enrichis que plusieurs marts en aval peuvent référencer.

Calculs métier. Taux de marge, paliers de valeur, catégorisations, indicateurs dérivés. C’est ici que vous interprétez les données. Un CASE WHEN qui classe les commandes en valeur haute, moyenne ou faible selon des seuils de montant est un travail intermédiaire.

Fonctions de fenêtre. Numéros de commande par client, totaux cumulés, classements, indicateurs premier/dernier. Ce sont des patterns de fonctions de fenêtre qui ajoutent du contexte sans changer la granularité. ROW_NUMBER() OVER (PARTITION BY customer__id ORDER BY order__created_at) vous donne customer__order_number — la position de la commande dans l’historique d’un client.

Indicateurs booléens dérivés. order__is_first_order, customer__is_high_value, session__has_conversion. Ces indicateurs pré-calculés rendent les requêtes en aval simples et cohérentes. Chaque mart qui a besoin de savoir si une commande est une première commande référence le même champ intermédiaire plutôt que de réimplémenter la logique.

Ce qui n’appartient pas aux modèles intermédiaires

Les agrégations qui réduisent le nombre de lignes. Pas de GROUP BY comme sortie finale. Si votre modèle finit avec moins de lignes qu’il n’en a commencé, il appartient à un mart.

Le formatage spécifique aux consommateurs. Renommer les colonnes pour correspondre à ce que Braze ou Salesforce attend, sélectionner uniquement les champs dont un tableau de bord a besoin — c’est du travail de mart. Les modèles intermédiaires servent le projet, pas les consommateurs externes.

L’exception : les agrégations de lookup

Parfois vous avez besoin d’agréger des données pour créer un lookup, puis joindre ce lookup à votre granularité principale. C’est le seul endroit où GROUP BY apparaît dans les modèles intermédiaires — et c’est acceptable, car la sortie finale préserve la granularité d’origine.

Considérez un modèle intermédiaire au niveau client. Vous avez besoin de métriques de commandes (total des commandes, total dépensé, date de dernière commande) attachées à chaque client. L’agrégation se produit dans une CTE, mais la sortie finale est toujours une ligne par client :

WITH base__customers AS (
    SELECT
        customer__id,
        customer__email
    FROM {{ ref('base__shopify__customers') }}
),

customer_order_summary AS (
    SELECT
        customer__id,
        COUNT(DISTINCT order__id) AS customer__orders,
        SUM(order__amount_usd) AS customer__total_spent_usd,
        MAX(order__created_at) AS customer__last_ordered_at
    FROM {{ ref('int__order__enriched') }}
    WHERE order__is_completed = TRUE
    GROUP BY 1
),

joined AS (
    SELECT
        base__customers.customer__id,
        base__customers.customer__email,
        COALESCE(customer_order_summary.customer__orders, 0) AS customer__orders,
        COALESCE(customer_order_summary.customer__total_spent_usd, 0) AS customer__total_spent_usd,
        customer_order_summary.customer__last_ordered_at
    FROM base__customers
    LEFT JOIN customer_order_summary USING (customer__id)
)

SELECT
    customer__id,
    customer__email,
    customer__orders,
    customer__total_spent_usd,
    customer__last_ordered_at
FROM joined

La granularité est d’une ligne par client. Le GROUP BY dans la CTE a créé un lookup qui a été rejoint à la base client. Le résultat final ne réduit pas les lignes par rapport à la table de clients de base. C’est ce qui rend cela acceptable.

Exemple complet de modèle intermédiaire

Ce modèle d’enrichissement de commandes montre les patterns standards : jointure de modèles de base, ajout de calculs, application de fonctions de fenêtre, préservation de la granularité.

{{ config(
    materialized='table',
    tags=['intermediate', 'order']
) }}

WITH base__orders AS (
    SELECT
        order__id,
        customer__id,
        order__amount_usd,
        order__created_at,
        order__is_completed,
        channel__name
    FROM {{ ref('base__shopify__orders') }}
),

base__customers AS (
    SELECT
        customer__id,
        customer__email,
        customer__segment
    FROM {{ ref('base__shopify__customers') }}
),

joined AS (
    SELECT
        base__orders.order__id,
        base__orders.customer__id,
        base__orders.order__amount_usd,
        base__orders.order__created_at,
        base__orders.order__is_completed,
        base__orders.channel__name,
        base__customers.customer__email,
        base__customers.customer__segment
    FROM base__orders
    LEFT JOIN base__customers USING (customer__id)
),

enriched AS (
    SELECT
        *,
        order__amount_usd * 0.7 AS order__margin_usd,

        CASE
            WHEN order__amount_usd >= 500 THEN 'high'
            WHEN order__amount_usd >= 100 THEN 'medium'
            ELSE 'low'
        END AS order__value_tier,

        order__amount_usd >= 100 AS order__is_significant
    FROM joined
),

windowed AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY customer__id
            ORDER BY order__created_at
        ) AS customer__order_number,

        ROW_NUMBER() OVER (
            PARTITION BY customer__id
            ORDER BY order__created_at
        ) = 1 AS order__is_first_order
    FROM enriched
)

SELECT
    order__id,
    customer__id,
    order__created_at,
    order__amount_usd,
    order__margin_usd,
    order__value_tier,
    order__is_completed,
    order__is_first_order,
    order__is_significant,
    channel__name,
    customer__email,
    customer__segment,
    customer__order_number
FROM windowed

La sortie a le même nombre de lignes que base__shopify__orders. Chaque commande est toujours là, maintenant enrichie avec les données client, les champs calculés et les informations de séquence. C’est le contrat intermédiaire : même granularité, plus de colonnes.

Structure des CTE pour les modèles intermédiaires

Le flux de CTE suit un pattern logique :

CTE d’import — Une par modèle de base référencé, sélectionnant uniquement les colonnes nécessaires.
CTE de jointure — Combiner les modèles de base. Utiliser des LEFT JOIN explicites plutôt que des jointures implicites.
CTE d’enrichissement — Ajouter la logique métier, les champs calculés, les catégorisations.
CTE de fenêtrage — Appliquer les fonctions de fenêtre pour le séquençage, les classements, les indicateurs.
SELECT final — Lister toutes les colonnes explicitement dans un ordre propre.

Les modèles intermédiaires simples peuvent combiner l’enrichissement et le fenêtrage ; les modèles complexes peuvent avoir plusieurs étapes de jointure. Le pattern n’est pas rigide.

Organisation centrée sur les entités

Les modèles intermédiaires s’organisent autour des entités métier — commandes, clients, produits, sessions — et non autour de bundles de données spécifiques aux consommateurs. Un modèle intermédiaire unique joignant quinze tables pour les besoins d’une équipe crée des problèmes de maintenance et réduit la réutilisabilité. Des modèles spécifiques aux entités à la place :

int__order__enriched — Granularité commande, enrichi avec les données client et canal
int__customer__enriched — Granularité client, enrichi avec les métriques de commandes agrégées
int__session__enriched — Granularité session, enrichi avec les données de conversion et d’attribution

Chaque modèle d’entité est testable indépendamment, déboguable indépendamment et réutilisable dans plusieurs marts. Quand l’équipe marketing a besoin de données de commandes et que l’équipe finance a besoin de données de commandes, les deux référencent int__order__enriched. Une seule source de vérité.

Définition unique par métrique

Deux modèles intermédiaires qui calculent customer__lifetime_value différemment (l’un incluant les remboursements, l’autre non ; l’un sur toute la durée, l’autre sur 12 mois) créent de l’ambiguïté. Une définition doit être canonique dans la couche intermédiaire ; les variantes en dérivent dans la couche mart où les ajustements spécifiques aux consommateurs appartiennent. Cette prévisibilité — « la valeur vie client est dans int__customer__enriched » — est l’objectif de l’architecture dbt en trois couches.

Tester les modèles intermédiaires

Les modèles intermédiaires bénéficient de patterns de tests spécifiques :

Tests de clé primaire (unique + not_null) sur la colonne de granularité de l’entité pour confirmer que la granularité n’a pas été accidentellement modifiée par une jointure
Tests de relation pour vérifier que les jointures n’ont pas produit des enregistrements orphelins
Comparaisons de nombre de lignes (via dbt_utils.equal_rowcount) par rapport au modèle de base pour confirmer que la granularité est préservée
Tests unitaires pour la logique métier complexe — les classifications par paliers de valeur, les indicateurs de première commande et la logique CASE WHEN similaire bénéficient de la vérification par entrée mockée

Le test du nombre de lignes est particulièrement important. Si int__order__enriched a plus de lignes que base__shopify__orders, une jointure a créé un fan-out. S’il en a moins, un filtre ou une jointure interne a supprimé des enregistrements. Dans les deux cas, le contrat de granularité est cassé.