dbt (Data Build Tool) : Le Guide Complet

Analogie pour débutants

Imaginez une cuisine industrielle. Les ingrédients bruts (données sources) arrivent par camion (extraction). Ils sont stockés dans un immense frigo (data warehouse). dbt, c'est le chef cuisinier : il prend les ingrédients bruts et les transforme en plats finis (tableaux de bord, rapports, métriques) en suivant des recettes (modèles SQL). Et comme un bon chef, il documente ses recettes, teste la qualité des plats, et organise sa cuisine de manière reproductible.

Exemple : Un modèle simple

-- models/staging/stg_orders.sql
SELECT
    id AS order_id,
    user_id AS customer_id,
    order_date,
    status,
    amount / 100.0 AS amount_eur  -- centimes → euros
FROM {{ source('raw', 'orders') }}
WHERE status != 'cancelled'

Ce modèle prend les commandes brutes, renomme les colonnes, convertit les montants, et filtre les annulées.

# models/staging/_sources.yml
version: 2
sources:
  - name: raw
    database: my_warehouse
    schema: raw_data
    tables:
      - name: orders
        description: "Commandes brutes depuis Shopify"
        loaded_at_field: _loaded_at
        freshness:
          warn_after: { count: 12, period: hour }
          error_after: { count: 24, period: hour }

# models/staging/_schema.yml
version: 2
models:
  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'shipped', 'delivered', 'returned']
      - name: customer_id
        tests:
          - relationships:
              to: ref('stg_customers')
              field: customer_id

4 tests génériques natifs : unique, not_null, accepted_values, relationships. Vous pouvez aussi écrire des tests SQL custom.

-- Référencer un autre modèle
SELECT * FROM {{ ref('stg_orders') }}

-- Référencer une source
SELECT * FROM {{ source('raw', 'orders') }}

-- Condition selon l'environnement
{% if target.name == 'prod' %}
    WHERE created_at >= '2024-01-01'
{% endif %}

-- Boucle dynamique
{% set payment_methods = ['credit_card', 'paypal', 'bank'] %}
{% for method in payment_methods %}
    SUM(CASE WHEN payment_method = '{{ method }}'
        THEN amount ELSE 0 END) AS {{ method }}_amount
    {% if not loop.last %},{% endif %}
{% endfor %}

models/
├── staging/                  # Couche 1 : Nettoyage
│   ├── shopify/
│   │   ├── _shopify__sources.yml
│   │   ├── _shopify__models.yml
│   │   ├── stg_shopify__orders.sql
│   │   └── stg_shopify__customers.sql
│   └── stripe/
│       ├── _stripe__sources.yml
│       ├── stg_stripe__payments.sql
│       └── stg_stripe__refunds.sql
├── intermediate/             # Couche 2 : Logique métier
│   ├── int_orders_with_payments.sql
│   └── int_customer_lifetime.sql
└── marts/                    # Couche 3 : Tables finales
    ├── finance/
    │   ├── fct_revenue.sql         # Fact table
    │   └── dim_customers.sql       # Dimension table
    ├── marketing/
    │   ├── fct_campaigns.sql
    │   └── dim_channels.sql
    └── product/
        └── fct_user_activity.sql

Pipeline complet

-- models/marts/finance/fct_daily_revenue.sql
WITH orders AS (
    SELECT * FROM {{ ref('stg_shopify__orders') }}
),
payments AS (
    SELECT * FROM {{ ref('stg_stripe__payments') }}
)
SELECT
    o.order_date,
    COUNT(DISTINCT o.order_id) AS total_orders,
    COUNT(DISTINCT o.customer_id) AS unique_customers,
    SUM(p.amount) AS gross_revenue,
    SUM(p.amount) - SUM(p.fees) AS net_revenue,
    SUM(p.amount) / COUNT(DISTINCT o.order_id) AS avg_order_value
FROM orders o
LEFT JOIN payments p ON o.order_id = p.order_id
GROUP BY o.order_date

Ce modèle alimente directement un dashboard Metabase/Looker avec les KPIs de vente.

-- models/marts/finance/fct_mrr.sql
WITH subscriptions AS (
    SELECT * FROM {{ ref('int_active_subscriptions') }}
)
SELECT
    date_trunc('month', period_start) AS month,
    SUM(CASE WHEN type = 'new' THEN mrr ELSE 0 END) AS new_mrr,
    SUM(CASE WHEN type = 'expansion' THEN mrr ELSE 0 END) AS expansion_mrr,
    SUM(CASE WHEN type = 'contraction' THEN mrr ELSE 0 END) AS contraction_mrr,
    SUM(CASE WHEN type = 'churn' THEN mrr ELSE 0 END) AS churned_mrr,
    SUM(mrr) AS total_mrr
FROM subscriptions
GROUP BY 1

-- models/marts/marketing/fct_attribution.sql
SELECT
    campaign_id,
    channel,
    SUM(first_touch_revenue) AS first_touch_attributed,
    SUM(last_touch_revenue) AS last_touch_attributed,
    SUM(linear_revenue) AS linear_attributed,
    COUNT(DISTINCT customer_id) AS customers_acquired,
    SUM(spend) AS total_spend,
    SUM(linear_revenue) / NULLIF(SUM(spend), 0) AS roas
FROM {{ ref('int_touchpoints_attributed') }}
GROUP BY campaign_id, channel

-- models/intermediate/int_bank_reconciliation.sql
SELECT
    b.transaction_id,
    b.amount AS bank_amount,
    e.amount AS erp_amount,
    ABS(b.amount - e.amount) AS difference,
    CASE
        WHEN e.transaction_id IS NULL THEN 'unmatched_bank'
        WHEN ABS(b.amount - e.amount) > 0.01 THEN 'amount_mismatch'
        ELSE 'matched'
    END AS reconciliation_status
FROM {{ ref('stg_bank__transactions') }} b
FULL OUTER JOIN {{ ref('stg_erp__transactions') }} e
    ON b.transaction_id = e.transaction_id

-- snapshots/snap_customers.sql
{% snapshot snap_customers %}
{{
    config(
        target_schema='snapshots',
        unique_key='customer_id',
        strategy='timestamp',
        updated_at='updated_at'
    )
}}
SELECT * FROM {{ source('raw', 'customers') }}
{% endsnapshot %}

Chaque fois qu'un client change d'adresse ou de plan, dbt crée une nouvelle ligne avec des colonnes dbt_valid_from et dbt_valid_to.

-- models/marts/fct_events.sql
{{
    config(
        materialized='incremental',
        unique_key='event_id',
        incremental_strategy='merge'
    )
}}
SELECT
    event_id,
    user_id,
    event_type,
    created_at
FROM {{ ref('stg_events') }}

{% if is_incremental() %}
    WHERE created_at > (SELECT MAX(created_at) FROM {{ this }})
{% endif %}

Au lieu de recréer la table entière à chaque run (qui peut prendre des heures sur des milliards de lignes), le modèle incrémental ne traite que les nouvelles données.

# packages.yml
packages:
  - package: dbt-labs/dbt_utils        # Utilitaires SQL essentiels
    version: [">=1.0.0", "<2.0.0"]
  - package: dbt-labs/codegen          # Génération automatique de staging
    version: [">=0.12.0", "<1.0.0"]
  - package: calogica/dbt_expectations # Tests avancés (regex, ranges, distributions)
    version: [">=0.10.0", "<1.0.0"]
  - package: elementary-data/elementary # Monitoring & observabilité
    version: [">=0.13.0", "<1.0.0"]

# seeds/country_codes.csv
code,name,region
FR,France,Europe
US,United States,North America
JP,Japan,Asia Pacific

# Puis dans un modèle :
SELECT * FROM {{ ref('country_codes') }}

# models/_exposures.yml
exposures:
  - name: weekly_revenue_dashboard
    type: dashboard
    maturity: high
    url: https://metabase.company.com/dashboard/42
    description: "Dashboard hebdomadaire des revenus"
    depends_on:
      - ref('fct_daily_revenue')
      - ref('dim_customers')
    owner:
      name: Équipe Finance
      email: [email protected]

-- Créer une clé de substitution avec dbt_utils
SELECT
    {{ dbt_utils.generate_surrogate_key(['order_id', 'product_id']) }}
        AS order_line_id,
    order_id,
    product_id,
    quantity,
    unit_price
FROM {{ ref('stg_order_lines') }}

-- Combiner plusieurs sources du même type
WITH shopify AS (
    SELECT *, 'shopify' AS source_system
    FROM {{ ref('stg_shopify__orders') }}
),
amazon AS (
    SELECT *, 'amazon' AS source_system
    FROM {{ ref('stg_amazon__orders') }}
)
SELECT * FROM shopify
UNION ALL
SELECT * FROM amazon

Commencez avec dbt Core si vous avez des compétences techniques et un orchestrateur existant (Airflow, Dagster). Passez à dbt Cloud si votre équipe grandit et que vous voulez un IDE web, un scheduler intégré, et un Slim CI sans configuration.

# profiles.yml
my_project:
  target: dev                    # Environnement par défaut
  outputs:
    dev:
      type: postgres
      schema: dev_{{ env_var('USER') }}   # Schéma isolé par dev
    staging:
      type: postgres
      schema: staging
    prod:
      type: postgres
      schema: analytics          # Le schéma final

Chaque développeur travaille dans son propre schéma (dev_alice, dev_bob). Le staging est partagé. La prod est protégée par CI/CD.