La plupart des projets dbt que j’audite ont le même problème. Pas l’absence de tests ou une documentation insuffisante (même si ça existe aussi). Le vrai problème, c’est le flou des responsabilités entre les couches. De la logique métier éparpillée dans les modèles base. Des agrégations qui se font dans intermediate. Des marts qui ne sont que des intermediates renommés.

Le pattern à trois couches existe pour une raison. Chaque couche a un rôle. Quand vous respectez ces frontières, votre projet devient prévisible. Quand vous ne les respectez pas, vous vous retrouvez avec une codebase où personne ne sait où chercher quoi que ce soit.

Le résumé rapide

Avant les nuances, voici le résumé :

Les modèles base nettoient les données brutes sans en changer le sens. Les modèles intermediate combinent et enrichissent ces données tout en préservant chaque ligne. Les marts agrègent au grain dont vos consommateurs ont réellement besoin.

Couche Base : restez ennuyeux

Les modèles base sont la partie la moins intéressante de votre projet, et c’est le but. Leur rôle est de rendre les données sources brutes utilisables sans ajouter d’interprétation.

Ce qui a sa place ici :

• Renommer les colonnes selon vos conventions de nommage (par exemple, user_id devient customer__id)
• Caster les types de données (strings en timestamps, integers en booleans)
• Dédupliquer quand les sources ont des enregistrements en double
• Unnester les champs nested ou repeated (courant avec GA4, les payloads JSON)
• Filtrer les données de test ou les enregistrements invalides

Ce qui n’a pas sa place ici :

• Les jointures (avec une exception que j’expliquerai)
• La logique métier ou les calculs
• Les agrégations de quelque nature que ce soit

L’exception sur les jointures : certains outils ETL divisent ce qui est logiquement un seul endpoint API en plusieurs tables. Si votre source a orders et order_metadata qui auraient toujours dû être une seule table, les joindre dans base est acceptable. Vous corrigez un artefact ETL, pas de la logique métier.

Un modèle base typique ressemble à ceci :

{{ config(
    materialized='table',
    tags=['base', 'shopify']
) }}

WITH source AS (
    SELECT
        id,
        user_id,
        created_at,
        total,
        status,
        is_deleted,
        _loaded_at
    FROM {{ source('shopify', 'orders') }}
),

deduplicated AS (
    SELECT
        id,
        user_id,
        created_at,
        total,
        status,
        is_deleted,
        _loaded_at
    FROM source
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY id
        ORDER BY _loaded_at DESC
    ) = 1
),

renamed AS (
    SELECT
        id AS order__id,
        user_id AS customer__id,
        created_at AS order__created_at,
        CAST(total AS FLOAT64) AS order__amount_usd,
        status AS order__status,
        CAST(is_deleted AS BOOL) AS order__is_deleted,
        _loaded_at
    FROM deduplicated
)

SELECT
    order__id,
    customer__id,
    order__created_at,
    order__amount_usd,
    order__status,
    order__is_deleted,
    _loaded_at
FROM renamed
WHERE order__is_deleted = FALSE

Notez qu’il n’y a pas de logique métier ici. Je ne calcule pas de marges et je ne catégorise pas les commandes. Je rends simplement les données brutes propres et nommées de manière cohérente.

Les modèles base devraient être ennuyeux afin que n’importe qui peut les comprendre en trente secondes.

Couche Intermediate : là où vit la logique métier

Les modèles intermediate sont là où réside l’intelligence réelle de votre projet. C’est ici que vous joignez les modèles base ensemble, ajoutez des champs calculés et encodez les règles métier. La contrainte critique : vous ne réduisez jamais le grain.

Ce qui a sa place ici :

• Joindre les modèles base ensemble
• Ajouter des calculs métier (taux de marge, catégorisations)
• Les fonctions de fenêtrage (numéro de commande client, totaux cumulés, classements)
• Les flags booléens dérivés (order__is_first_order, customer__is_high_value)

Ce qui n’a pas sa place ici :

• Les agrégations qui réduisent le nombre de lignes
• GROUP BY comme output final (avec une exception, expliquée ci-dessous)

L’exception sur les agrégations : parfois vous devez agréger pour créer une table de correspondance (lookup), puis joindre cette lookup à votre grain principal. C’est acceptable, car au final vous ne changez pas le grain de la table.

Un exemple qui illustre le pattern :

{{ 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

L’output 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.

Pour les modèles intermediate au niveau client, vous pourriez agréger les données de commande pour ramener des métriques au grain client. C’est le pattern lookup que j’ai mentionné :

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

Le grain reste une ligne par client. L’agrégation s’est faite dans une CTE pour créer une lookup, puis a été jointe à la base client. C’est le seul endroit où GROUP BY apparaît dans les modèles intermediate.

Couche Mart : construite pour les consommateurs

Les marts existent pour des cas d’usage spécifiques. Contrairement aux modèles base et intermediate, qui servent le projet en interne, les marts servent des consommateurs externes : dashboards, reverse ETL, pipelines ML.

Chaque mart a un consommateur spécifique et un grain défini. Vous ne construisez pas des tables “à usage général”. Vous répondez à des questions spécifiques à des niveaux d’agrégation spécifiques.

Les trois patterns principaux :

Les marts reporting agrègent au grain dont vos dashboards ont besoin. Performance quotidienne par canal. Rétention hebdomadaire par cohorte. Revenu mensuel par segment.

Les marts activation préparent les données pour les destinations de reverse ETL comme Braze ou Salesforce. Généralement une ligne par entité, avec tous les attributs que le système de destination attend.

Les marts ML créent des tables de features pour le machine learning. Format large, features temporelles, une ligne par cible de prédiction.

Un exemple de mart reporting :

{{ config(
    materialized='table',
    partition_by={
        "field": "date_day",
        "data_type": "date",
        "granularity": "day"
    },
    cluster_by=['channel__name'],
    tags=['mart', 'reporting', 'marketing']
) }}

WITH int__orders AS (
    SELECT
        order__id,
        order__created_at,
        order__amount_usd,
        customer__id,
        channel__name,
        order__margin_usd,
        order__is_first_order,
        order__is_completed
    FROM {{ ref('int__order__enriched') }}
    WHERE order__is_completed = TRUE
),

aggregated AS (
    SELECT
        DATE(order__created_at) AS date_day,
        channel__name,
        COUNT(DISTINCT order__id) AS orders,
        COUNT(DISTINCT customer__id) AS customers,
        SUM(CASE WHEN order__is_first_order THEN 1 ELSE 0 END) AS new_customers,
        SUM(order__amount_usd) AS revenue_usd,
        SUM(order__margin_usd) AS margin_usd,
        SUM(CASE WHEN order__is_first_order THEN order__amount_usd ELSE 0 END) AS new_customer_revenue_usd
    FROM int__orders
    GROUP BY ALL
),

enriched AS (
    SELECT
        *,
        SAFE_DIVIDE(margin_usd, revenue_usd) AS margin_rate,
        SAFE_DIVIDE(revenue_usd, orders) AS avg_order_value_usd,
        SAFE_DIVIDE(new_customers, customers) AS new_customer_rate
    FROM aggregated
)

SELECT
    date_day,
    channel__name,
    orders,
    customers,
    new_customers,
    revenue_usd,
    margin_usd,
    margin_rate,
    avg_order_value_usd,
    new_customer_revenue_usd,
    new_customer_rate
FROM enriched

C’est la première fois qu’on voit GROUP BY comme output final. Le mart agrège au grain jour + canal parce que c’est ce dont le dashboard marketing a besoin. Pas au niveau commande, pas horaire. Quotidien par canal.

Un mart activation est différent. Celui-ci est construit pour un outil CRM :

{{ config(
    materialized='table',
    tags=['mart', 'activation', 'braze']
) }}

WITH int__customers AS (
    SELECT
        customer__id,
        customer__email,
        customer__segment,
        customer__orders,
        customer__total_spent_usd,
        customer__last_ordered_at
    FROM {{ ref('int__customer__enriched') }}
),

enriched AS (
    SELECT
        customer__id,
        customer__email,
        customer__segment,
        customer__orders,
        customer__total_spent_usd,
        customer__last_ordered_at,
        CASE
            WHEN customer__orders = 0 THEN 'prospect'
            WHEN customer__orders = 1 THEN 'new'
            WHEN customer__last_ordered_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) THEN 'active'
            WHEN customer__last_ordered_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY) THEN 'lapsing'
            ELSE 'churned'
        END AS customer__lifecycle_stage,
        customer__orders >= 5 AS customer__is_vip,
        customer__total_spent_usd >= 1000 AS customer__is_high_ltv
    FROM int__customers
)

SELECT
    customer__email AS email,
    customer__id AS external_id,
    customer__segment,
    customer__lifecycle_stage AS lifecycle_stage,
    customer__orders AS orders,
    customer__total_spent_usd AS total_spent_usd,
    customer__is_vip AS is_vip,
    customer__is_high_ltv AS is_high_ltv
FROM enriched

Les noms de colonnes correspondent à ce que Braze attend. La table expose uniquement ce dont le système de destination a besoin, rien de plus.

Le framework de décision

Quand vous n’êtes pas sûr de l’endroit où quelque chose appartient, posez-vous ces questions :

Est-ce que je ne fais que nettoyer des données sources ? Couche base. Renommer, caster, dédupliquer, unnester. Pas d’interprétation.

Est-ce que je combine des tables ou ajoute des calculs tout en gardant chaque ligne ? Couche intermediate. Jointures, logique métier, fonctions de fenêtrage. Préserver le grain.

Est-ce que je réduis les lignes pour un consommateur spécifique ? Couche mart. Agrégations, formatage final, noms de colonnes spécifiques au consommateur.

Une référence plus détaillée :

Erreurs courantes que je vois

Logique métier dans les modèles base. Calculer is_high_value ou customer_tier dans base. Ça appartient à intermediate. Les modèles base doivent être simples.

Agrégations dans intermediate. Construire un int__daily_orders qui groupe par date. Ça appartient à un mart. Intermediate préserve le grain.

Marts qui n’agrègent pas. Un mart qui est juste SELECT * FROM int__order__enriched avec un nom différent. Si vous ne changez pas le grain ou ne formatez pas pour un consommateur, vous n’avez probablement pas besoin d’un mart.

Un seul modèle intermediate géant. Joindre quinze tables dans un seul modèle intermediate. Construisez des modèles intermediate autour d’entités métier (commande, client, produit), pas autour de “toutes les données dont je pourrais avoir besoin.”

Propriété floue. Deux modèles intermediate qui calculent tous deux customer__lifetime_value différemment. Choisissez une source de vérité. Référencez-la partout.

Commencez simple

Vous n’avez pas besoin des trois couches dès le premier jour. Si vous avez un petit projet avec des sources simples, commencez avec base et marts. Ajoutez des modèles intermediate quand vous vous retrouvez à dupliquer des jointures ou des calculs dans plusieurs marts.

Le pattern monte en charge à mesure que votre projet grandit. Un projet mature pourrait avoir :

• 30 modèles base (un par table source)
• 10 modèles intermediate (un par entité métier)
• 20 marts (divers cas d’usage reporting, activation et ML)

Les ratios n’ont pas d’importance. Ce qui compte, c’est que chaque couche ait des responsabilités claires, et que tout le monde dans votre équipe sache où trouver les choses.

Quand quelqu’un demande “où est calculée la lifetime value client ?” la réponse devrait être évidente : int__customer__enriched. Pas “quelque part dans les marts” ou “je crois qu’il y a plusieurs versions différentes.”

Cette prévisibilité, c’est tout l’intérêt.