dbt-guide

Guide dbt

Workflow

1. Initialiser et structurer le projet

dbt init mon_projet        # crée le squelette
cd mon_projet
dbt debug                  # vérifie la connexion au warehouse

Arborescence cible :

models/
  staging/         ← nettoyage source, renommage, cast ; materialisation = view
  intermediate/    ← jointures, logique métier complexe ; materialisation = view ou ephemeral
  marts/           ← modèles finaux consommables ; materialisation = table
macros/
tests/
seeds/
snapshots/

dbt_project.yml — materialisations par dossier :

models:
  mon_projet:
    staging:
      +materialized: view
      +schema: staging
    intermediate:
      +materialized: view
    marts:
      +materialized: table
      +schema: marts

---

2. Déclarer les sources

# models/staging/sources.yml
version: 2
sources:
  - name: raw_crm
    schema: raw
    freshness:
      warn_after: {count: 12, period: hour}
      error_after: {count: 24, period: hour}
    loaded_at_field: _loaded_at
    tables:
      - name: orders
        description: "Commandes brutes issues du CRM"
        columns:
          - name: order_id
            tests: [unique, not_null]

Référencer dans un modèle :

select * from {{ source('raw_crm', 'orders') }}

Tester la fraîcheur :

dbt source freshness

---

3. Développer les modèles

Staging — un modèle par table source, préfixe stg_ :

-- models/staging/stg_orders.sql
with source as (
    select * from {{ source('raw_crm', 'orders') }}
),
renamed as (
    select
        order_id::varchar      as order_id,
        customer_id::int       as customer_id,
        created_at::timestamp  as created_at,
        status::varchar        as status
    from source
)
select * from renamed

Mart — utilise ref() pour chaque dépendance :

-- models/marts/fct_orders.sql
with orders as (
    select * from {{ ref('stg_orders') }}
),
customers as (
    select * from {{ ref('stg_customers') }}
)
select
    o.order_id,
    o.created_at,
    c.customer_name,
    o.status
from orders o
left join customers c on o.customer_id = c.customer_id