khalilbenaz/claude-skills-collection/data-dbt-guide

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

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/

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

# 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]

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

dbt source freshness

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

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

-- models/marts/fct_events.sql
{{ config(materialized='incremental', unique_key='event_id') }}
 
select * from {{ ref('stg_events') }}
 
{% if is_incremental() %}
  where event_at > (select max(event_at) from {{ this }})
{% endif %}

# models/marts/fct_orders.yml
version: 2
models:
  - name: fct_orders
    description: "Faits commandes enrichis"
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'confirmed', 'shipped', 'cancelled']
      - name: customer_id
        tests:
          - relationships:
              to: ref('stg_customers')
              field: customer_id

-- tests/assert_no_negative_amount.sql
select order_id
from {{ ref('fct_orders') }}
where amount < 0

dbt test                          # tous les tests
dbt test --select fct_orders      # tests d'un modèle
dbt test --store-failures         # stocker les lignes en erreur dans le warehouse

-- macros/generate_surrogate_key.sql
{% macro generate_surrogate_key(fields) %}
    {{ dbt_utils.generate_surrogate_key(fields) }}
{% endmacro %}
 
-- macros/audit_columns.sql
{% macro audit_columns() %}
    current_timestamp as dbt_loaded_at,
    '{{ invocation_id }}' as dbt_run_id
{% endmacro %}

select
    {{ generate_surrogate_key(['order_id', 'customer_id']) }} as sk_order,
    order_id,
    {{ audit_columns() }}
from {{ ref('stg_orders') }}

models:
  - name: fct_orders
    description: >
      Faits commandes joints avec les clients.
      Utilisé par le dashboard Revenue dans Metabase.
    columns:
      - name: order_id
        description: "Identifiant unique de la commande (source CRM)"

dbt docs generate   # génère catalog.json
dbt docs serve      # ouvre le site sur localhost:8080

mon_projet:
  target: dev
  outputs:
    dev:
      type: snowflake
      schema: "dbt_{{ env_var('DBT_USER', 'dev') }}"
      ...
    prod:
      type: snowflake
      schema: marts
      ...

- run: dbt build --target prod --select state:modified+  # uniquement les modèles modifiés et leurs dépendants
- run: dbt test  --target prod --select state:modified+

dbt build           # compile + run + test en une commande
dbt build --select +fct_orders   # modèle + tous ses ancêtres
dbt run --exclude tag:slow       # exclure les modèles lourds