Les modèles incrémentaux sont la principale stratégie d’optimisation de dbt. Bien configurés, ils réduisent les coûts de traitement d’un facteur 10 à 200 et font passer les temps d’exécution de plusieurs heures à quelques minutes. Mal configurés, ils créent des problèmes de qualité de données silencieux qui s’accumulent au fil du temps.

Ce guide couvre tout ce qu’il faut savoir pour réussir avec les modèles incrémentaux : sélection de la stratégie, comportements propres à chaque warehouse, patterns pour les données tardives, et cadres de décision pour choisir la bonne approche.

Comment fonctionnent les modèles incrémentaux

À la première exécution, un modèle incrémental se comporte comme une matérialisation table (il traite toutes les données source). Aux exécutions suivantes, il traite uniquement les lignes que vous spécifiez, puis les fusionne ou les insère dans la table existante.

La macro is_incremental() retourne True lorsque trois conditions sont réunies :

1. Le modèle existe déjà en tant que table dans le warehouse
2. Le flag --full-refresh n’est pas passé
3. Le modèle est configuré avec materialized='incremental'

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

SELECT
    event_id,
    event_timestamp,
    user_id,
    event_type
FROM {{ ref('base__analytics__events') }}

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

Le SQL généré varie selon votre incremental_strategy. Pour les stratégies merge, dbt crée une relation temporaire et génère une instruction MERGE INTO. Pour insert_overwrite, il identifie les partitions concernées et les remplace de façon atomique.

Options de configuration

unique_key

Définit le grain pour les décisions update/insert. Sans cette option, la plupart des stratégies adoptent par défaut un comportement append-only.

-- Colonne unique
{{ config(unique_key='order_id') }}

-- Clé composite
{{ config(unique_key=['user_id', 'session_number']) }}

Les colonnes dans unique_key ne doivent jamais contenir de valeurs NULL. Les valeurs NULL provoquent des échecs de correspondance lors des opérations merge, créant des doublons au lieu de mises à jour.

on_schema_change

Contrôle le comportement lorsque les colonnes de votre modèle changent :

Aucune de ces options ne remplit les valeurs historiques pour les colonnes nouvellement ajoutées. Si vous ajoutez une colonne, les lignes existantes auront des valeurs NULL.

incremental_predicates

Limite les scans de la table destination lors des opérations merge (critique pour les grandes tables) :

{{ config(
    materialized='incremental',
    unique_key='id',
    incremental_strategy='merge',
    incremental_predicates=[
        "DBT_INTERNAL_DEST.created_at > dateadd(day, -7, current_date)"
    ]
) }}

Cela ajoute des filtres à l’instruction MERGE, permettant le partition pruning sur la table destination. Sans predicates, le merge scanne l’intégralité de la destination. Avec predicates, le warehouse élague les partitions et ne scanne que les données récentes.

Disponibilité des stratégies par warehouse

À noter : l’insert_overwrite de BigQuery opère sur les partitions, tandis que celui de Snowflake remplace la table entière. Cette incohérence de nommage surprend beaucoup de monde.

Les stratégies en détail

Merge

Utilise des instructions SQL MERGE qui font correspondre sur unique_key, mettant à jour les lignes existantes et insérant les nouvelles.

-- SQL généré (simplifié)
MERGE INTO target AS DBT_INTERNAL_DEST
USING tmp AS DBT_INTERNAL_SOURCE
ON DBT_INTERNAL_DEST.id = DBT_INTERNAL_SOURCE.id
WHEN MATCHED THEN UPDATE SET col1 = DBT_INTERNAL_SOURCE.col1, ...
WHEN NOT MATCHED THEN INSERT (col1, col2) VALUES (...)

Le merge fonctionne bien pour les tables de taille petite à moyenne avec des mises à jour au niveau des lignes et les patterns CDC où des enregistrements individuels changent.

Le problème : sans incremental_predicates, l’instruction MERGE scanne chaque ligne de votre table destination pour vérifier les correspondances, même si vous n’insérez que 1 000 nouveaux enregistrements dans une table de 500 millions de lignes. Ce scan complet se produit à chaque exécution, ce qui signifie que votre modèle “incrémental” lit quand même l’intégralité de la table à chaque fois. Le résultat : des temps d’exécution plus longs et des coûts plus élevés qui augmentent avec la taille de la table plutôt qu’avec la quantité de nouvelles données. Au-delà de 100 millions de lignes, cela devient péniblement lent et coûteux. Ajoutez incremental_predicates pour limiter le scan aux partitions récentes (voir la section Configuration ci-dessus).

Delete+Insert

Exécute deux opérations : DELETE des enregistrements correspondants, puis INSERT de tous les nouveaux enregistrements.

-- Flux généré
1. Créer une table temporaire avec les résultats de la requête
2. DELETE FROM target USING tmp WHERE tmp.unique_key = target.unique_key
3. INSERT INTO target SELECT * FROM tmp

Cette stratégie brille sur les grandes tables Snowflake (3,4x plus rapide que merge à 500M+ lignes) et pour le traitement par lots où des fenêtres temporelles entières doivent être remplacées.

Le compromis concerne l’atomicité. Parce que delete+insert exécute deux opérations séparées (DELETE puis INSERT), un échec entre les deux laisse votre table dans un état incohérent : les enregistrements sont supprimés mais les nouveaux ne sont pas encore insérés. Contrairement à MERGE, qui est tout-ou-rien, delete+insert peut entraîner une perte de données si le job plante, timeout ou est annulé en cours d’exécution. Il faudrait alors lancer un full refresh pour récupérer. Pour les tables critiques, évaluez si ce risque est acceptable, ou encapsulez les opérations dans une transaction si votre warehouse le supporte. BigQuery ne supporte pas du tout cette stratégie ; utilisez merge ou insert_overwrite à la place.

Insert_Overwrite

Remplace des partitions entières plutôt que des lignes individuelles.

{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={
        'field': 'event_date',
        'data_type': 'date'
    }
) }}

Sur BigQuery, cela utilise la découverte de partitions pour déterminer quelles partitions remplacer :

-- SQL généré (BigQuery)
DECLARE dbt_partitions_for_replacement ARRAY<DATE>;
SET (dbt_partitions_for_replacement) = (
    SELECT AS STRUCT ARRAY_AGG(DISTINCT DATE(partition_col))
    FROM tmp_table
);

MERGE INTO target USING tmp ON FALSE
WHEN NOT MATCHED BY SOURCE
  AND partition_col IN UNNEST(dbt_partitions_for_replacement)
THEN DELETE
WHEN NOT MATCHED THEN INSERT ...

Cette stratégie est idéale pour les tables de faits partitionnées par date, les workloads append-heavy et l’optimisation des coûts BigQuery.

Une mise en garde importante : le nom insert_overwrite signifie des choses différentes selon les warehouses. Sur BigQuery, il remplace intelligemment uniquement les partitions contenant de nouvelles données, exactement ce à quoi on s’attend. Sur Snowflake en revanche, il remplace la table entière par ce que votre requête retourne. Si votre requête incrémentale ne sélectionne que les 3 derniers jours de données, vous perdrez tout ce qui est plus ancien. Cette incohérence de nommage a piégé beaucoup de monde, parfois avec des pertes de données catastrophiques. Sur Snowflake, utilisez plutôt delete+insert avec un unique_key basé sur la date pour obtenir un comportement similaire aux partitions en toute sécurité.

Microbatch (dbt 1.9+)

Traite les données par lots basés sur le temps sans nécessiter de logique is_incremental().

{{ config(
    materialized='incremental',
    incremental_strategy='microbatch',
    event_time='session_start',
    begin='2020-01-01',
    batch_size='day',
    lookback=3
) }}

-- Pas besoin de bloc is_incremental()
SELECT
    session_id,
    session_start,
    user_id,
    session_duration
FROM {{ ref('base__analytics__sessions') }}

Différences clés par rapport à l’incrémental traditionnel :

Commandes de backfill :

# Backfill ciblé
dbt run --event-time-start "2024-09-01" --event-time-end "2024-09-04"

# Réessayer uniquement les lots échoués
dbt retry

Microbatch excelle pour les gros backfills historiques, les scénarios de récupération après échec, et les équipes voulant une logique incrémentale plus simple.

Trois limitations à considérer avant de l’adopter :

1. Hypothèse timezone UTC : Microbatch utilise UTC pour tous les calculs temporels. Si votre colonne event_time est dans un fuseau horaire local (comme US/Eastern), les lots seront découpés à minuit UTC, pas à minuit heure locale. Cela peut faire atterrir des enregistrements dans des lots inattendus ou créer des erreurs de décalage d’un jour dans vos données. Convertissez votre event_time en UTC avant d’utiliser microbatch, ou acceptez que les limites des lots ne s’aligneront pas avec votre jour ouvré.

2. Pas de granularité sub-horaire : Le plus petit batch_size est hour. Si vous devez traiter des données par fenêtres de 15 ou 5 minutes (courant pour les dashboards temps réel ou les alertes), microbatch ne conviendra pas. Vous aurez besoin d’une logique incrémentale traditionnelle avec un filtrage temporel personnalisé.

3. Exécution séquentielle par défaut : Chaque lot s’exécute l’un après l’autre, pas en parallèle. Un backfill de 30 jours avec batch_size='day' signifie 30 exécutions de requêtes séparées. Bien que cela offre une meilleure isolation des échecs (ne réessayer que les lots échoués), c’est plus lent qu’une seule requête traitant les 30 jours d’un coup. Pour les très gros backfills, évaluez si les bénéfices du retry valent le compromis sur la vitesse.

Comportements spécifiques aux warehouses

BigQuery

Merge :

• Requiert unique_key (sans quoi le merge échoue)
• Ne supporte pas le partition pruning dynamique ; les sous-requêtes ne déclenchent pas le pruning
• Scans complets sur les grandes tables sans clustering

Insert_overwrite :

• Requiert la configuration partition_by
• Les partitions statiques (recommandées) sont les plus rapides et économiques
• Les partitions dynamiques ajoutent de l’overhead via les requêtes de découverte de partitions
• _dbt_max_partition est une variable de scripting BigQuery, pas du Jinja

Vous ne pouvez pas utiliser require_partition_filter=true avec la stratégie merge sauf si vous définissez aussi incremental_predicates.

Pattern d’optimisation des coûts :

{% set partitions_to_replace = [
    'CURRENT_DATE()',
    'DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)',
    'DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)'
] %}

{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={'field': 'event_date', 'data_type': 'date'},
    partitions=partitions_to_replace
) }}

SELECT
    event_id,
    event_date,
    user_id,
    event_type
FROM {{ ref('base__analytics__events') }}
{% if is_incremental() %}
WHERE event_date IN ({{ partitions_to_replace | join(',') }})
{% endif %}

Snowflake

Merge :

• Stratégie par défaut avec syntaxe MERGE INTO standard
• Échoue avec “nondeterministic merge” si unique_key n’est pas vraiment unique
• Performance qui se dégrade au-delà de 100M lignes

Delete+insert :

• Requiert tmp_relation_type: table quand unique_key est défini
• 3,4x plus rapide que merge à 500M+ lignes avec des mises à jour alignées sur les partitions

Insert_overwrite ne supporte PAS les overwrites basés sur les partitions dans Snowflake. Il remplace la table entière. Utilisez delete+insert avec des predicates de partition à la place.

{{ config(
    materialized='incremental',
    incremental_strategy='delete+insert',
    unique_key='date_day'
) }}

SELECT
    event_id,
    date_day,
    user_id,
    event_type
FROM {{ ref('base__analytics__events') }}
{% if is_incremental() %}
WHERE date_day >= DATEADD(DAY, -3, CURRENT_DATE)
{% endif %}

Databricks

Merge sur Delta Lake :

• MERGE INTO conforme ACID
• Supporte l’évolution de schéma via merge_with_schema_evolution=true
• Options avancées : matched_condition, not_matched_by_source_action

Insert_overwrite :

• Utilise la syntaxe INSERT INTO ... REPLACE ON sur les SQL Warehouses
• Dynamic partition overwrite via partitionOverwriteMode='dynamic'
• Régression v1.8.0 causait un remplacement complet de table ; corrigé en définissant spark.sql.sources.partitionOverwriteMode=DYNAMIC

Replace_where (exclusif à Databricks) :

{{ config(
    materialized='incremental',
    incremental_strategy='replace_where',
    incremental_predicates=["event_date >= '2024-01-01'"]
) }}

Utilise INSERT INTO table REPLACE WHERE predicate pour un remplacement atomique des lignes correspondant aux predicates.

Gestion des données tardives

Les données tardives surviennent quand des enregistrements arrivent après d’autres données de la même période. Sans traitement adapté, les modèles incrémentaux dérivent progressivement de la réalité source.

Le pattern lookback window

Soustrayez une période fixe de votre seuil :

{% set lookback_days = var('lookback_days', 3) %}

{% if is_incremental() %}
WHERE created_at >= (
    SELECT DATEADD(DAY, -{{ lookback_days }}, MAX(created_at))
    FROM {{ this }}
)
{% endif %}

Override pour des backfills personnalisés : dbt run --select model --vars '{"lookback_days": 10}'

Compromis selon la taille de la fenêtre :

Les lookback windows ne capturent pas les enregistrements arrivant après la fermeture de la fenêtre. Planifiez des full refreshes périodiques (hebdomadaires ou mensuels) pour réinitialiser la dérive accumulée.

Idempotence et déduplication

Les modèles idempotents produisent des résultats identiques quel que soit le nombre d’exécutions. Pré-dédupliquez dans votre SELECT :

SELECT
    event_id,
    event_time,
    user_id,
    event_type,
    event_properties
FROM {{ ref('base__analytics__events') }}
{% if is_incremental() %}
WHERE event_time >= (SELECT MAX(event_time) - INTERVAL 3 DAY FROM {{ this }})
{% endif %}
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY event_id
    ORDER BY event_time DESC
) = 1

La première exécution n’applique pas la déduplication du merge, donc des doublons peuvent apparaître au chargement initial. Incluez toujours la déduplication dans votre SELECT plutôt que de vous fier uniquement à unique_key.

Cadre de décision

Quand utiliser les modèles incrémentaux

Utilisez l’incrémental quand :

• Les données source comptent des millions ou milliards de lignes
• Les transformations sont coûteuses en calcul
• Les exécutions dbt deviennent trop lentes ou trop coûteuses

Seuils issus de la pratique :

• Les tables sous 10M lignes bénéficient rarement de la complexité incrémentale
• La performance du merge se dégrade notablement au-dessus de 100M lignes
• À 500M+ lignes, le merge peut être 3x plus lent que les alternatives optimisées

Quelle stratégie choisir

Anti-patterns courants

1. Ne pas définir unique_key avec merge : Résulte en comportement append-only avec doublons

2. NULLs dans les colonnes unique_key : Cause des échecs de correspondance et des doublons silencieux

3. Pas de lookback window : Les données tardives sont ratées ; les modèles dérivent de la source

4. Utiliser l’incrémental pour des petites tables : Ajoute de la complexité sans bénéfice mesurable

5. Pas de gestion des changements de schéma : Ajouter des colonnes sans configuration on_schema_change cause des échecs

6. Pas de full refresh périodique : La dérive des données s’accumule indéfiniment

7. Utiliser merge pour de très grandes tables sans predicates : Les scans complets deviennent coûteux

8. Se fier uniquement à unique_key pour la déduplication : La première exécution ne déduplique pas ; incluez QUALIFY ou ROW_NUMBER dans SELECT

Checklist des bonnes pratiques

• Commencer avec la matérialisation table ; passer à incrémental quand les exécutions ralentissent
• Toujours définir unique_key pour la stratégie merge ; s’assurer qu’il n’y a pas de NULLs dans les colonnes clés
• Implémenter des lookback windows de 3 jours par défaut pour les données tardives
• Utiliser incremental_predicates sur les tables >100M lignes
• Partitionner par date pour les tables d’événements ; clusterer par colonnes fréquemment filtrées
• Planifier des full refreshes périodiques (hebdomadaires/mensuels) pour réinitialiser la dérive
• Tester les modes incrémental et full-refresh avant déploiement
• Définir full_refresh: false pour les très grandes tables afin d’éviter les rebuilds accidentels
• Pré-dédupliquer dans SELECT plutôt que de se fier uniquement à unique_key
• Surveiller régulièrement la santé des partitions (BigQuery) et la profondeur du clustering (Snowflake)