Merge est la stratégie incrémentale par défaut dans dbt. C’est aussi celle qui risque le plus de faire exploser votre facture une fois que vos tables dépassent 100 millions de lignes.

Souvent, merge fait plus de travail que nécessaire. Quand vous remplacez des partitions entières de données d’événements, la comparaison ligne par ligne est un gaspillage. Quand votre clé unique garantit l’absence de doublons, un MERGE complet est excessif.

Choisir la bonne stratégie peut réduire considérablement les temps d’exécution et diminuer les coûts de scan de plusieurs ordres de grandeur sur les grandes tables.

Ce guide détaille le fonctionnement de chaque stratégie, les cas d’usage appropriés, et les points d’attention spécifiques à BigQuery, Snowflake et Databricks.

Disponibilité des stratégies par warehouse

Toutes les stratégies ne sont pas disponibles sur tous les warehouses. Voici ce que vous pouvez réellement utiliser :

La limitation de Snowflake sur insert_overwrite est celle qui surprend le plus. Contrairement à BigQuery et Databricks, insert_overwrite sur Snowflake remplace la table entière, pas des partitions spécifiques. Pour un remplacement au niveau partition sur Snowflake, utilisez plutôt delete+insert.

Fonctionnement de chaque stratégie

Merge

Merge compare chaque ligne entrante avec la table de destination en utilisant votre unique_key. Les lignes correspondantes sont mises à jour. Les lignes sans correspondance sont insérées.

MERGE INTO target AS DBT_INTERNAL_DEST
USING staging 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 SQL généré scanne la table de destination entière à moins d’ajouter des incremental_predicates pour limiter la fenêtre de comparaison.

Points forts : Gère proprement les mélanges d’insertions et de mises à jour. Opération atomique. Modèle mental le plus simple.

Points faibles : Les scans de table complète deviennent vite coûteux. Les performances se dégradent significativement au-delà de 100M de lignes.

Delete+Insert

Delete+insert exécute deux opérations distinctes : d’abord supprimer tous les enregistrements correspondants, puis insérer le nouveau lot.

-- Étape 1 : Supprimer les enregistrements existants
DELETE FROM target
USING tmp
WHERE tmp.unique_key = target.unique_key

-- Étape 2 : Insérer les nouvelles données
INSERT INTO target SELECT * FROM tmp

Ce fonctionnement en deux étapes crée un état intermédiaire bref. Si le processus échoue entre le delete et l’insert, vous aurez des données manquantes jusqu’à la prochaine exécution réussie.

Points forts : Plus rapide que merge à grande échelle (3,4x plus rapide à 500M de lignes sur Snowflake). Fonctionne bien pour les remplacements par lots.

Points faibles : Non atomique. Nécessite une unique_key. Crée des états intermédiaires en cas d’échec.

Insert_Overwrite

Insert_overwrite remplace des partitions entières plutôt que de comparer des lignes individuelles. Sur BigQuery, il identifie les partitions contenant de nouvelles données, puis les remplace de manière atomique.

-- BigQuery détermine les partitions concernées
DECLARE dbt_partitions_for_replacement ARRAY<DATE>;
SET (dbt_partitions_for_replacement) = (
    SELECT AS STRUCT ARRAY_AGG(DISTINCT DATE(partition_col))
    FROM source_table
);

-- Remplacement atomique des partitions
MERGE INTO target USING staging ON FALSE
WHEN NOT MATCHED BY SOURCE
  AND partition_col IN UNNEST(dbt_partitions_for_replacement)
THEN DELETE
WHEN NOT MATCHED THEN INSERT ...

Points forts : Extrêmement efficace pour les données partitionnées par temps. Pas de surcoût de comparaison ligne par ligne. Gère naturellement les données arrivant en retard dans les partitions écrasées.

Points faibles : Nécessite un partitionnement. Ne permet pas de mettre à jour des lignes spécifiques dans une partition. Le comportement diffère significativement selon les warehouses.

Replace_Where (Databricks uniquement)

Replace_where est l’alternative Databricks au remplacement de partition, basée sur des prédicats. Au lieu de remplacer des partitions, il remplace toutes les lignes correspondant à une condition.

INSERT INTO table REPLACE WHERE date_col >= '2024-01-01'

Points forts : Plus flexible que le remplacement par partition. Fonctionne sur les SQL Warehouses. Atomique.

Points faibles : Databricks uniquement. Nécessite Delta Lake.

Comportements spécifiques par warehouse

BigQuery

Particularités de merge :

• Nécessite une unique_key. Sans elle, merge échoue (pas de comportement append).
• Ne supporte pas le dynamic partition pruning. Les sous-requêtes dans les prédicats ne déclenchent pas le pruning.
• Les scans de table complète sont la norme, sauf si vous ajoutez des incremental_predicates ou clusterez votre table.

Avantages d’insert_overwrite :

• Fonctionne avec les tables partitionnées (configuration requise : partition_by)
• Les partitions statiques (liste explicite) sont plus rapides que la découverte dynamique de partitions
• La variable _dbt_max_partition est du scripting BigQuery, pas du Jinja (elle s’exécute au moment de la requête)

Piège BigQuery : Vous ne pouvez pas utiliser require_partition_filter=true avec merge sans définir également des incremental_predicates. L’instruction merge ne sait pas quelles partitions traiter.

Impact sur les coûts : Les équipes passant de merge à insert_overwrite avec partitions statiques rapportent des réductions de coûts de 100 à 200x sur les grandes tables.

Snowflake

Comportement de merge :

• Stratégie par défaut, syntaxe MERGE INTO standard
• Échoue avec “nondeterministic merge” si votre unique_key n’est pas vraiment unique
• Les performances se dégradent notablement au-delà de 100M de lignes

Avantages de delete+insert :

• 3,4x plus rapide que merge à 500M+ lignes dans les benchmarks
• Nécessite tmp_relation_type: table quand unique_key est définie

Limitation critique : Insert_overwrite sur Snowflake remplace la table entière, pas les partitions. Cela le rend inutile pour le traitement incrémental. Si vous avez besoin d’un remplacement au niveau partition sur Snowflake, utilisez delete+insert avec des prédicats de date dans votre clause WHERE.

Databricks

Merge sur Delta Lake :

• Utilise un MERGE INTO conforme ACID
• Supporte l’évolution de schéma via merge_with_schema_evolution=true
• Options avancées comme matched_condition et not_matched_by_source_action='delete' pour les patterns CDC

Spécificités d’insert_overwrite :

• Utilise le dynamic partition overwrite par défaut
• Une régression en v1.8.0 causait un remplacement de table complète au lieu du remplacement de partition. Corrigez en définissant spark.sql.sources.partitionOverwriteMode=DYNAMIC

Replace_where : Souvent le meilleur choix pour les mises à jour bornées par date sur les SQL Warehouses où le comportement des partitions est moins prévisible.

Données de performance

Benchmarks réels issus de systèmes en production :

Merge fonctionne bien à petite échelle mais devient un goulot d’étranglement une fois que les tables grossissent.

Exemples de configuration

Merge avec prédicats (grandes tables)

{{ config(
    materialized='incremental',
    unique_key='event_id',
    incremental_strategy='merge',
    incremental_predicates=[
        "DBT_INTERNAL_DEST.event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)"
    ]
) }}

SELECT
    event_id,
    event_date,
    event_name,
    user_id,
    event_properties
FROM {{ ref('base__segment__events') }}
{% if is_incremental() %}
WHERE event_date >= (SELECT MAX(event_date) - INTERVAL 3 DAY FROM {{ this }})
{% endif %}

Les incremental_predicates filtrent la table de destination pendant le merge. Cela active le partition pruning et évite les scans de table complète.

Delete+Insert sur Snowflake

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

SELECT
    order_id,
    customer_id,
    order_date,
    updated_at,
    total_amount
FROM {{ ref('base__shopify__orders') }}
{% if is_incremental() %}
WHERE updated_at >= (SELECT MAX(updated_at) - INTERVAL 3 DAY FROM {{ this }})
{% endif %}

Insert_Overwrite avec partitions statiques (BigQuery)

{% 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,
    event_name,
    user_id,
    event_properties
FROM {{ ref('base__segment__events') }}
{% if is_incremental() %}
WHERE event_date IN ({{ partitions_to_replace | join(',') }})
{% endif %}

Les partitions statiques sont plus rapides que les partitions dynamiques car BigQuery n’a pas besoin d’interroger la table source pour déterminer quelles partitions remplacer.

Replace_Where sur Databricks

{{ config(
    materialized='incremental',
    incremental_strategy='replace_where',
    incremental_predicates=["event_date >= CURRENT_DATE - INTERVAL 3 DAY"]
) }}

SELECT
    event_id,
    event_date,
    event_name,
    user_id,
    event_properties
FROM {{ ref('base__segment__events') }}
WHERE event_date >= CURRENT_DATE - INTERVAL 3 DAY

Cadre de décision

Utilisez merge quand :

• Les tables font moins de 100M de lignes
• Vous devez mettre à jour des lignes spécifiques en fonction de données source changeantes
• La simplicité compte plus que l’optimisation
• Vous gérez du CDC avec un mélange d’insertions, mises à jour et suppressions

Utilisez delete+insert quand :

• Les tables dépassent 100M de lignes sur Snowflake
• Vos mises à jour sont orientées par lots (remplacement de tous les enregistrements d’une fenêtre temporelle)
• Vous pouvez tolérer de brefs états intermédiaires pendant l’opération en deux étapes

Utilisez insert_overwrite quand :

• Les tables sont partitionnées par temps (BigQuery, Databricks)
• Vous traitez des données d’événements ou de faits où des journées/partitions entières sont retraitées
• Les données arrivant en retard tombent dans votre fenêtre de réécriture
• L’optimisation des coûts est une priorité

Utilisez replace_where quand :

• Vous êtes sur Databricks avec Delta Lake
• Vous avez besoin d’un remplacement basé sur des prédicats sans dépendances aux partitions
• Le comportement d’insert_overwrite sur SQL Warehouse est imprévisible

Utilisez append quand :

• Les données sont vraiment en append-only (logs, événements sans mises à jour)
• Vous gérez la déduplication en aval
• Snowflake ou Databricks (BigQuery ne le supporte pas)

Recommandations par warehouse

BigQuery : Privilégiez insert_overwrite pour les tables de faits avec partitions temporelles. Utilisez des listes de partitions statiques quand c’est possible. Repliez-vous sur merge avec incremental_predicates pour les tables de dimensions nécessitant des mises à jour au niveau ligne.

Snowflake : Commencez par merge pour les tables de moins de 100M de lignes. Passez à delete+insert pour les tables plus grandes ou les mises à jour orientées par lots. Évitez complètement insert_overwrite ; il ne fait pas ce à quoi on s’attend.

Databricks : Merge fonctionne bien grâce aux optimisations de Delta Lake. Envisagez replace_where pour des mises à jour plus propres basées sur des prédicats. Utilisez insert_overwrite pour les workloads alignés sur les partitions, mais vérifiez que partitionOverwriteMode est correctement configuré.

Points de vigilance

Merge à grande échelle : Surveillez les coûts de requête et les temps d’exécution. Si un modèle qui prenait 5 minutes en prend maintenant 30, votre table a probablement dépassé les capacités de la stratégie merge.

Intégrité de la clé unique : Les valeurs nulles dans les colonnes de la unique_key causent des échecs de correspondance et des doublons. Les clés composites nécessitent que toutes les colonnes soient non nulles.

Changements de schéma : Ajouter des colonnes avec on_schema_change='append_new_columns' fonctionne pour les nouvelles données mais ne rétroremplit pas les enregistrements historiques. Planifiez en conséquence.

Données arrivant en retard : Insert_overwrite gère les données tardives dans les partitions réécrites. Il ne capture pas les enregistrements arrivant après la fermeture de la fenêtre de partition. Des rafraîchissements complets périodiques restent nécessaires.

La bonne stratégie dépend de vos patterns de données, de la taille de vos tables et de votre warehouse. Commencez simple avec merge, mesurez vos coûts et temps d’exécution, puis optimisez quand les chiffres justifient la complexité ajoutée.