dbt sur ClickHouse

Ce guide présente les modèles propres à dbt-clickhouse que vous utiliserez dans la troisième partie. Lisez-le après avoir terminé les fiches 1 à 4 et avant la fiche 5, consacrée à la conception des modèles dbt.

Si vous venez de dbt-snowflake, la plupart des concepts dbt sont identiques : sources, refs, tests, macros et organisation des couches staging, intermédiaire et analytics. Seule la couche de configuration propre à ClickHouse change : moteur, order_by, stratégie incrémentielle et sémantique de FINAL.

1. Types de matérialisation

dbt-clickhouse prend en charge cinq matérialisations. Choisissez selon le mode de mise à jour, et non selon vos préférences.

Différence essentielle avec Snowflake : dbt-snowflake gère les détails de stockage en interne. Dans dbt-clickhouse, les modèles table et incremental exigent une configuration +engine explicite, que dbt utilise pour générer le DDL CREATE TABLE ... ENGINE = ....

Les vues n’ont pas de moteur. Si vous ajoutez par erreur +engine à une matérialisation view, dbt-clickhouse l’ignore. Seules les matérialisations table et incremental créent un stockage persistant qui nécessite un moteur.

Vues matérialisées actualisables. La matérialisation materialized_view de dbt-clickhouse accepte un bloc de configuration refreshable, contenant un interval et, éventuellement, randomize. Elle écrit directement la clause REFRESH dans l’instruction CREATE MATERIALIZED VIEW générée. Dans ce laboratoire, le modèle mv_live_trip_feed ne définit pas refreshable ; la vue matérialisée qu’il construit n’a donc aucun calendrier d’actualisation.

2. Exprimer les configurations ClickHouse dans dbt

Les paramètres propres à ClickHouse s’expriment sous forme de configurations de modèle dbt, soit dans dbt_project.yml pour les valeurs par défaut de tout le projet, soit dans le bloc config() d’un modèle pour des remplacements spécifiques.

Dans dbt_project.yml

models:
  your_project:
    analytics:
      +schema: analytics
      +materialized: table
      +engine: "MergeTree()"          # default for all analytics tables

      fact_trips:
        +materialized: incremental
        +engine: "ReplacingMergeTree(updated_at)"   # overrides the default
        +incremental_strategy: delete_insert
        +unique_key: trip_id
        +order_by: "(toStartOfMonth(pickup_at), pickup_at, trip_id)"

Dans le bloc config() d’un modèle

{{ config(
    materialized         = 'incremental',
    engine               = 'ReplacingMergeTree(updated_at)',
    incremental_strategy = 'delete_insert',
    unique_key           = 'trip_id',
    order_by             = '(toStartOfMonth(pickup_at), pickup_at, trip_id)'
) }}

Les deux approches sont équivalentes. Préférez dbt_project.yml pour les conventions à l’échelle du projet et les blocs config() pour les remplacements propres à un modèle ou lorsque vous souhaitez conserver la configuration près du SQL.

Principaux paramètres de configuration

Règles de portée : les paramètres de dbt_project.yml se propagent du parent vers les enfants. Un bloc config() au niveau du modèle l’emporte toujours sur la configuration du projet. Définissez le moteur le plus courant comme valeur par défaut du projet, puis remplacez-le pour les modèles qui diffèrent.

3. Fonctionnement de delete_insert

delete_insert est la stratégie incrémentielle standard de la communauté dbt-clickhouse. C’est l’équivalent le plus proche de MERGE INTO dans Snowflake, mais son fonctionnement diffère.

Version requise : delete_insert emploie les suppressions légères de ClickHouse, apparues à titre expérimental dans la version 22.8 et adaptées à la production depuis la version 23.3. ClickHouse Cloud satisfait cette exigence. Pour les activer, ajoutez use_lw_deletes: true à la cible ClickHouse de votre ~/.dbt/profiles.yml, ou définissez allow_experimental_lightweight_delete=1 dans query_settings.

Fonctionnement

À chaque exécution incrémentielle :

1. DELETE supprime de la table cible les lignes dont la unique_key correspond à une ligne du lot entrant
2. INSERT insère toutes les lignes du lot entrant

-- Step 1: dbt generates this DELETE
ALTER TABLE analytics.fact_trips
DELETE WHERE trip_id IN (SELECT trip_id FROM incoming_batch);

-- Step 2: dbt generates this INSERT
INSERT INTO analytics.fact_trips
SELECT * FROM incoming_batch;

Différence avec MERGE INTO dans Snowflake

La stratégie merge de Snowflake génère, ligne par ligne, WHEN MATCHED THEN UPDATE / WHEN NOT MATCHED THEN INSERT. ClickHouse n’a aucune instruction MERGE INTO. delete_insert produit le même résultat final, une ligne par clé unique, en supprimant un lot puis en le réinsérant intégralement.

Interaction avec ReplacingMergeTree

delete_insert constitue le mécanisme principal d’exactitude. ReplacingMergeTree sert de filet de sécurité.

Lorsqu’une exécution delete_insert se termine normalement, la table est propre, avec une ligne par trip_id, sans doublon.

Si une exécution delete_insert est interrompue en cours de route, par exemple après DELETE mais avant INSERT, les données risquent de se trouver dans un état incorrect : les lignes supprimées peuvent ne pas avoir été réinsérées. La prochaine exécution réussie rétablira l’état correct, mais n’interrogez pas la table entre l’échec de DELETE et cette nouvelle exécution.

Si une exécution produit des doublons pour une raison quelconque, la fusion en arrière-plan de ReplacingMergeTree finira par les dédupliquer et conservera la ligne dont la valeur de la colonne de version est la plus élevée.

Ne vous reposez jamais sur RMT seul sans delete_insert : les fusions en arrière-plan sont asynchrones et peuvent prendre de quelques minutes à plusieurs heures sur de grandes tables.

Quand utiliser append

append insère de nouvelles lignes sans modifier celles qui existent déjà. Cette stratégie convient aux tables alimentées exclusivement par insertion, dont les lignes ne sont jamais mises à jour, par exemple un journal d’événements immuable ou une table d’ingestion brute avec des identifiants garantis uniques et sans correction. append n’impose aucune version minimale et ne présente aucun risque de mutation.

Pour fact_trips, append est inadapté : un trajet peut être corrigé après coup, par exemple pour ajuster un tarif ou changer son statut. Le même trip_id arrive alors avec de nouvelles valeurs. Avec append, les deux versions s’accumulent indéfiniment et les agrégats, comme la somme des tarifs ou le nombre de trajets, sont surestimés jusqu’à la prochaine fusion RMT en arrière-plan. Utilisez delete_insert dès que des lignes peuvent être mises à jour.

Pourquoi ne pas utiliser la stratégie merge ?

La stratégie merge, utilisée par défaut avant delete_insert, crée une table temporaire, la remplit avec les lignes existantes inchangées et le nouveau lot, puis remplace atomiquement la table d’origine. Contrairement à delete_insert, elle n’emploie pas les suppressions légères : elle réécrit toute la table à chaque exécution incrémentielle. Sur une table fact_trips de 50 millions de lignes, ce serait extrêmement coûteux. delete_insert ne traite que les lignes du lot en cours, tandis que merge touche chaque ligne de la table. Utilisez delete_insert.

4. Stratégie de placement de FINAL

La déduplication de ReplacingMergeTree s’effectue en arrière-plan : ClickHouse fusionne les parties de manière asynchrone. Entre deux fusions, les lignes en double coexistent. FINAL force une déduplication synchrone lors de la lecture.

Où placer FINAL dans un pipeline dbt

Dans la couche qui lit une source ReplacingMergeTree et produit des données analytiques propres.

Pour la charge de travail NYC Taxi :

trips_raw (RMT)
    ↓
stg_trips (view): SELECT ... FROM trips_raw FINAL   ← FINAL goes here
    ↓
int_trips_enriched (ephemeral CTE)
    ↓
fact_trips (incremental, RMT)                        ← NO FINAL in model
    ↓
Dashboard queries: SELECT ... FROM fact_trips FINAL  ← FINAL goes here (externally)

stg_trips constitue l’unique point d’application de la déduplication de trips_raw. Tous les modèles en aval qui lisent stg_trips reçoivent automatiquement des données sources propres et dédupliquées. Vous n’avez pas besoin de FINAL dans int_trips_enriched ni dans fact_trips, car ils lisent depuis stg_trips, qui est une vue et non une table RMT.

Les requêtes de tableau de bord et les tests dbt qui lisent directement fact_trips emploient FINAL à l’extérieur. Le modèle lui-même n’intègre pas FINAL, car celui-ci s’appliquerait à chaque lecture dans la requête du modèle, y compris à la sous-requête is_incremental() qui lit max(updated_at) depuis {{ this }}.

Incidence de FINAL sur les performances

FINAL ajoute une latence proportionnelle au nombre de lignes en double. Sur une table RMT bien entretenue, avec des fusions fréquentes en arrière-plan, le surcoût de FINAL est faible car il reste peu de doublons à résoudre. Sur une table fraîchement chargée comportant de nombreuses parties non fusionnées, FINAL peut ralentir considérablement la requête.

Pour les tests dbt et les requêtes de vérification, utilisez toujours FINAL sur les tables RMT. Les requêtes ClickHouse des benchmarks de comparaison de latence avec Snowflake l’emploient déjà, ce qui rend la comparaison équitable.

5. Macro generate_schema_name

Par défaut, dbt préfixe les schémas des modèles avec le nom du schéma cible du profil. Si votre profil dbt cible le schéma nyc_taxi_ch, un modèle configuré avec +schema: analytics arrive dans nyc_taxi_ch_analytics, et non dans analytics.

Cela ne pose pas de problème dans Snowflake, où les schémas sont des espaces de noms au sein d’une base de données, mais produit des noms peu pratiques dans ClickHouse, où les schémas sont des bases de données. nyc_taxi_ch_analytics est un nom de base ClickHouse valide, mais il est moins élégant que analytics et ne correspond pas aux noms de bases cibles utilisés dans l’architecture ClickHouse de la troisième partie.

La solution consiste à remplacer la macro generate_schema_name :

-- macros/generate_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
  {%- if custom_schema_name is none -%}
    {{ target.schema | lower }}
  {%- else -%}
    {{ custom_schema_name | lower }}
  {%- endif -%}
{%- endmacro %}

Cette macro :

• renvoie custom_schema_name tel quel, en minuscules, lorsqu’un modèle indique +schema: analytics ;
• renvoie le schéma cible du profil, en minuscules, pour les modèles sans schéma personnalisé.

Le filtre | lower garantit également que les noms de schémas restent en minuscules, conformément aux règles de ClickHouse sur la casse des identifiants. La première partie sur Snowflake utilisait | upper.

Emplacement : macros/generate_schema_name.sql, dans le répertoire macros/ de premier niveau ; dbt_project.yml définit macro-paths: ["macros"].

Récapitulatif de la configuration dbt pour NYC Taxi

# dbt_project.yml (abbreviated)
models:
  nyc_taxi_dbt_ch:
    staging:
      +schema: staging
      +materialized: view           # no engine — views need none

    intermediate:
      +schema: staging
      +materialized: ephemeral      # inlined as CTE

    analytics:
      +schema: analytics
      +materialized: table
      +engine: "MergeTree()"        # default for dim_* tables

      fact_trips:
        +materialized: incremental
        +engine: "ReplacingMergeTree(updated_at)"
        +incremental_strategy: delete_insert
        +unique_key: trip_id

      agg_hourly_zone_trips:
        +materialized: incremental
        +engine: "ReplacingMergeTree(updated_at)"
        +incremental_strategy: delete_insert
        +unique_key: [hour_bucket, zone_id]

-- stg_trips.sql (staging view — the FINAL enforcement point)
SELECT ... FROM {{ source('raw', 'trips_raw') }} FINAL

-- fact_trips.sql (incremental — no FINAL in model body)
SELECT ... FROM {{ ref('int_trips_enriched') }}
{% if is_incremental() %}
WHERE updated_at > (SELECT max(updated_at) FROM {{ this }})
{% endif %}