ServicesÀ proposNotesContact Me contacter →
EN FR
Note

Les incremental_predicates pour le merge dbt

Comment les incremental_predicates limitent les scans de la table de destination lors des opérations de merge dbt, transformant des scans complets en lectures avec élagage de partitions.

Planté
dbtbigquerysnowflakeincremental processingcost optimization

Le principal problème de performance de la stratégie merge n’est pas le merge lui-même — c’est le scan de la table de destination. Sans incremental_predicates, une instruction MERGE compare chaque ligne entrante à chaque ligne de la table de destination. Insérer 1 000 enregistrements dans une table de 500 millions de lignes, et le warehouse lit quand même les 500 millions de lignes côté destination pour vérifier les correspondances. Ce scan complet se produit à chaque exécution, ce qui signifie que votre modèle « incrémental » lit encore toute la table à chaque fois.

incremental_predicates corrige cela en ajoutant des filtres WHERE côté destination de l’instruction MERGE. Le warehouse utilise ces filtres pour l’élagage de partitions, ne scannant que les partitions qui pourraient potentiellement contenir des lignes correspondantes.

Comment ça fonctionne

Vous configurez incremental_predicates comme une liste d’expressions SQL dans le bloc config de votre modèle. Ces expressions sont injectées dans l’instruction MERGE comme filtres sur la table de destination (aliasée DBT_INTERNAL_DEST) :

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


SELECT
    id,
    created_at,
    user_id,
    event_type
FROM {{ ref('base__analytics__events') }}
{% if is_incremental() %}
WHERE created_at > dateadd(day, -7, current_date)
{% endif %}

L’instruction MERGE générée ressemble à quelque chose comme :

MERGE INTO target AS DBT_INTERNAL_DEST
USING tmp AS DBT_INTERNAL_SOURCE
ON DBT_INTERNAL_DEST.created_at > dateadd(day, -7, current_date)
   AND DBT_INTERNAL_DEST.id = DBT_INTERNAL_SOURCE.id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...

Remarquer que le prédicat atterrit dans la clause ON aux côtés de la correspondance unique_key. Cela permet au warehouse d’élaguer les partitions avant d’effectuer la comparaison ligne par ligne.

Les deux filtres ont des rôles différents

Une source de confusion courante : la clause WHERE is_incremental() et incremental_predicates semblent faire la même chose, mais ils opèrent sur des tables différentes.

  • La clause WHERE is_incremental() filtre la requête source. Elle contrôle quelles lignes entrent dans la table de staging temporaire.
  • incremental_predicates filtre la table de destination. Ils contrôlent quelles lignes le warehouse scanne pendant la correspondance MERGE.

Vous avez besoin des deux. Sans le filtre source, vous lisez trop de données sources. Sans le prédicat de destination, vous scannez toute la table cible pendant le merge.

-- Filtre source (limite ce qui entre dans la table temporaire)
{% if is_incremental() %}
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
{% endif %}


-- Filtre de destination (limite ce que le MERGE scanne)
{{ config(
    incremental_predicates=[
        "DBT_INTERNAL_DEST.event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)"
    ]
) }}

La fenêtre du prédicat doit être au moins aussi large que votre filtre source. Si votre filtre source couvre 7 jours, votre prédicat doit couvrir 7 jours ou plus. Un prédicat plus étroit pourrait faire manquer au MERGE des correspondances légitimes côté destination.

Quand ajouter des prédicats

La règle empirique des praticiens : ajouter incremental_predicates lorsque votre table de destination dépasse 100 M de lignes. En dessous de ce seuil, le scan complet de la table lors du merge est généralement assez rapide pour que la configuration supplémentaire n’en vaille pas la complexité.

Au-delà de 100 M de lignes, l’impact est spectaculaire. Des benchmarks en production montrent des améliorations de 2 à 4 fois du temps d’exécution du merge simplement en ajoutant des prédicats. Sur BigQuery, où vous payez par octet scanné, la réduction des coûts peut être encore plus significative — une réduction de 88 %+ des octets scannés côté destination.

Considérations BigQuery

Sur BigQuery, incremental_predicates interagissent avec l’option de table require_partition_filter. Si vous avez défini require_partition_filter=true sur une table et que vous utilisez la stratégie merge, le MERGE échouera sauf si vous fournissez également des incremental_predicates incluant un filtre de partition. BigQuery impose l’exigence de filtre de partition sur le scan de destination, et sans prédicats, il n’y a aucun filtre à satisfaire.

BigQuery ne peut pas non plus effectuer d’élagage de partitions à partir de sous-requêtes. Cela signifie qu’un prédicat comme DBT_INTERNAL_DEST.event_date = (SELECT MAX(event_date) FROM other_table) ne déclenchera pas réellement l’élagage. Utiliser des expressions littérales ou de l’arithmétique de dates sur CURRENT_DATE() à la place.

Considérations Snowflake

Sur Snowflake, les prédicats aident à l’élagage des micro-partitions. Snowflake n’a pas de partitions explicites comme BigQuery, mais organise les données en micro-partitions et maintient des métadonnées sur les plages de valeurs dans chacune. Un prédicat bien ciblé permet à Snowflake de passer les micro-partitions dont les plages ne chevauchent pas la fenêtre du prédicat.

Pour les tables Snowflake dépassant 500 M de lignes, envisager si delete+insert serait un meilleur choix architectural. À cette échelle, même avec des prédicats, la comparaison ligne par ligne du merge est plus lente que les opérations en masse de delete+insert.

Alignement avec les fenêtres de lookback

Si vous utilisez un pattern de fenêtre de lookback pour les données en retard, vos incremental predicates doivent correspondre ou dépasser la fenêtre de lookback. Si votre lookback retraite 3 jours de données sources, le prédicat de destination doit couvrir au moins 3 jours — sinon le merge ne peut pas trouver les lignes de destination qui doivent être mises à jour.

{% set lookback_days = 3 %}


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


SELECT *
FROM {{ ref('base__events') }}
{% if is_incremental() %}
WHERE event_date >= (
    SELECT DATE_SUB(MAX(event_date), INTERVAL {{ lookback_days }} DAY)
    FROM {{ this }}
)
{% endif %}

Cela maintient les deux filtres synchronisés. Si vous changez la fenêtre de lookback, le filtre source et le prédicat de destination se mettent à jour ensemble.

L’alternative : changer de stratégie

incremental_predicates est un correctif sur la limitation fondamentale du merge. Si vous vous retrouvez à ajouter des prédicats parce que votre table est trop grande pour un merge confortable, envisager si une stratégie différente — insert_overwrite sur BigQuery, delete+insert sur Snowflake — serait un meilleur choix architectural. Ces stratégies n’ont pas besoin de prédicats car elles n’effectuent pas de comparaisons ligne par ligne sur toute la table de destination.

Les prédicats ont le plus de sens lorsque vous avez réellement besoin du comportement merge (mises à jour au niveau des lignes sur des enregistrements qui changent individuellement) mais que votre table a suffisamment grandi pour que le scan complet par défaut soit pénible.