dbt: Incremental refresh pattern avec table de staging

Ce paragraphe décrit une stratégie avancée d’ingénierie de données dans dbt, utilisée lorsqu’on travaille avec des matérialisations complexes (souvent en materialized_view ou table) contenant des agrégations lourdes, et qu’on souhaite à la fois :

• 📈 garantir de bonnes performances pour les utilisateurs finaux
• 🔁 maintenir des données fraîches
• ✅ assurer la sécurité du rafraîchissement (pas d’interruption, possibilité de rollback)

🎯 Problème traité

Tu as une vue matérialisée ou une table agrégée contenant des données historiques ou volumineuses, par exemple :

SELECT
  user_id,
  DATE(created_at) AS jour,
  COUNT(*) AS nb_commandes
FROM raw.commandes
GROUP BY user_id, DATE(created_at)

Et tu veux la rafraîchir quotidiennement avec les nouvelles données, sans recalculer toute l’historique, sans bloquer les utilisateurs, et sans corrompre les résultats si l’update échoue.

✅ Solution : incremental refresh pattern avec table de staging

Tu crées un modèle intermédiaire (staging) qui calcule les agrégations uniquement sur les nouvelles données,
puis tu les fusionnes proprement avec la table principale ou la materialized view.

🧱 Architecture du pattern

[raw données] → [staging_agg] → [table ou materialized_view finale]

Étapes :

1. ✅ Un modèle stg_agg_commandes calcule les nouvelles agrégations (par exemple, les données des 3 derniers jours)
2. ✅ Un modèle agg_commandes (matérialisé en table ou materialized_view) fusionne le staging avec les données existantes
3. ✅ Optionnel : tu peux supprimer les jours existants et les remplacer par les jours recalculés (technique de “merge by partition”)

🛠 Exemple dans dbt

1. stg_agg_commandes.sql — staging (temporaire, petits volumes)

{{ config(materialized='table') }}

SELECT
  user_id,
  DATE(created_at) AS jour,
  COUNT(*) AS nb_commandes
FROM raw.commandes
WHERE created_at >= CURRENT_DATE - INTERVAL '3 day'
GROUP BY user_id, DATE(created_at)

2. agg_commandes.sql — modèle final avec fusion logique

{{ config(
    materialized='incremental',
    unique_key='user_id || jour'
) }}

WITH base AS (
  SELECT * FROM {{ this }}
  {% if is_incremental() %}
  WHERE jour < CURRENT_DATE - INTERVAL '3 day'
  {% endif %}
),
updates AS (
  SELECT * FROM {{ ref('stg_agg_commandes') }}
)

SELECT * FROM base
UNION ALL
SELECT * FROM updates

✅ Résultat :

• Tu gardes les anciennes données
• Tu ajoutes ou remplaces les lignes récentes
• Le tout se fait de façon atomique, sans bloquer les utilisateurs

🔐 Avantages du pattern

🧠 Quand utiliser ce pattern ?

Souhaites-tu que je t’écrive ce modèle complet dbt (avec tests + YAML) prêt à coller dans un projet ?