dbt en ClickHouse

Esta guía explica los patrones específicos de dbt-clickhouse que usarás en la Parte 3. Léela después de completar las hojas de trabajo 1–4 y antes de la Hoja 5 (Diseño de modelos dbt).

Si vienes de dbt-snowflake, casi todos los conceptos de dbt son idénticos: sources, refs, pruebas, macros y el patrón de capas staging/intermedia/analítica. Lo que cambia es la capa de configuración específica de ClickHouse: motor, order_by, estrategia incremental y semántica de FINAL.

1. Tipos de materialización

dbt-clickhouse admite cinco materializaciones. Elige según el patrón de actualización, no por preferencia.

Diferencia clave respecto a Snowflake: dbt-snowflake gestiona internamente los detalles de almacenamiento. En dbt-clickhouse, los modelos table e incremental exigen una configuración +engine explícita; dbt la usa para generar el DDL CREATE TABLE ... ENGINE = ....

Las vistas no tienen motor. Si añades por error +engine a una materialización view, dbt-clickhouse lo ignora. Solo table e incremental crean almacenamiento persistente que necesita un motor.

Vistas materializadas actualizables. La materialización materialized_view de dbt-clickhouse acepta un bloque de configuración refreshable: un interval y, opcionalmente, randomize. Con él emite la cláusula REFRESH directamente en la sentencia CREATE MATERIALIZED VIEW que genera. El modelo mv_live_trip_feed del laboratorio no define refreshable, por eso la MV creada carece de programación.

2. Expresar configuraciones de ClickHouse en dbt

Los ajustes específicos de ClickHouse se expresan como configuraciones de modelos dbt, ya sea en dbt_project.yml (valores predeterminados del proyecto) o en el bloque config() de un modelo (excepciones específicas).

En 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)"

En el bloque config() de un modelo

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

Ambos enfoques son equivalentes. Se prefiere dbt_project.yml para patrones generales del proyecto y los bloques config() para excepciones de un modelo o cuando quieres mantener la configuración junto al SQL.

Parámetros de configuración principales

Reglas de alcance: los ajustes de dbt_project.yml se propagan de padres a hijos. Un bloque config() a nivel de modelo siempre prevalece sobre la configuración del proyecto. Define el motor más común como valor predeterminado y sobrescríbelo en los modelos que sean distintos.

3. Mecánica de delete_insert

delete_insert es la estrategia incremental habitual de la comunidad dbt-clickhouse. Es lo más parecido a MERGE INTO de Snowflake, aunque su mecánica es diferente.

Requisito de versión: delete_insert usa eliminaciones ligeras de ClickHouse, introducidas como experimentales en 22.8 y listas para producción desde 23.3. ClickHouse Cloud cumple el requisito. Para activarlas, añade use_lw_deletes: true al destino de ClickHouse en ~/.dbt/profiles.yml, o define allow_experimental_lightweight_delete=1 en query_settings.

Qué hace

En cada ejecución incremental:

1. Hace DELETE de las filas de la tabla de destino cuyo unique_key coincide con alguna fila del lote entrante
2. Hace INSERT de todas las filas del lote entrante

-- 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;

Diferencias respecto a MERGE INTO de Snowflake

La estrategia merge de Snowflake genera un WHEN MATCHED THEN UPDATE / WHEN NOT MATCHED THEN INSERT por fila. ClickHouse no tiene sentencia MERGE INTO. delete_insert consigue el mismo resultado final (una fila por clave única) mediante una eliminación por lotes seguida de una inserción completa.

Interacción con ReplacingMergeTree

delete_insert es la vía principal de corrección. ReplacingMergeTree es la red de seguridad.

Si una ejecución de delete_insert termina con normalidad, la tabla queda limpia (una fila por trip_id), sin duplicados.

Si se interrumpe a mitad de camino (fallo después de DELETE y antes de INSERT), es probable que los datos queden en un estado no válido: algunas filas eliminadas no se habrán reinsertado. La siguiente ejecución correcta restaurará el estado, pero no consultes la tabla entre el DELETE fallido y su repetición.

Si una ejecución genera duplicados por cualquier motivo, la fusión en segundo plano de ReplacingMergeTree acabará deduplicándolos y conservará la fila con el valor más alto de la columna de versión.

Nunca dependas solo de RMT sin delete_insert: la estrategia delete_insert evita depender de fusiones en segundo plano, que son asíncronas y pueden tardar minutos u horas en tablas grandes.

Cuándo usar append

append inserta filas nuevas sin tocar las existentes. Es correcto para tablas que solo reciben inserciones y cuyas filas nunca se actualizan, como un registro inmutable de eventos o una tabla de ingesta sin procesar con identificadores únicos garantizados y sin correcciones. append no exige una versión concreta ni conlleva riesgo de mutación.

Para fact_trips, append es incorrecto: un viaje puede corregirse a posteriori (ajuste de tarifa, cambio de estado), por lo que el mismo trip_id vuelve a llegar con valores nuevos. Con append, ambas versiones se acumulan permanentemente y las agregaciones (SUM de tarifas, COUNT de viajes) cuentan de más hasta la siguiente fusión de RMT. Usa delete_insert siempre que las filas puedan actualizarse.

¿Por qué no usar la estrategia merge?

La estrategia merge (valor predeterminado antiguo antes de delete_insert) crea una tabla temporal, la llena con las filas existentes sin modificar más el lote nuevo y después sustituye atómicamente la tabla original. A diferencia de delete_insert, no usa eliminaciones ligeras: vuelve a escribir la tabla completa en cada ejecución incremental. Para una fact_trips de 50 millones de filas sería carísimo. delete_insert solo procesa las filas del lote actual; merge toca todas las filas de la tabla. Usa delete_insert.

4. Estrategia de ubicación de FINAL

La deduplicación de ReplacingMergeTree se produce en segundo plano: ClickHouse fusiona las partes de forma asíncrona. Entre fusiones coexisten filas duplicadas. FINAL fuerza la deduplicación síncrona durante la lectura.

Dónde debe ir FINAL en una canalización dbt

En la capa que lee de un origen ReplacingMergeTree y genera datos analíticos limpios con FINAL.

Para la carga 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 es el único punto que impone la deduplicación de trips_raw. Todos los modelos posteriores que leen stg_trips reciben automáticamente datos de origen limpios y deduplicados. No necesitas FINAL en int_trips_enriched ni fact_trips porque leen de stg_trips (una vista, no una tabla RMT).

Las consultas de paneles y las pruebas dbt que leen directamente de fact_trips usan FINAL externamente. El propio modelo no incorpora FINAL porque se aplicaría a todos los recorridos de su consulta, incluida la subconsulta is_incremental() que lee max(updated_at) de {{ this }}.

Impacto de FINAL en el rendimiento

FINAL añade una latencia proporcional al número de filas duplicadas. En una tabla RMT bien mantenida (con fusiones frecuentes en segundo plano), la sobrecarga es mínima porque quedan pocos duplicados por resolver. En una tabla recién cargada y con muchas partes sin fusionar, puede ser considerablemente más lento.

En pruebas dbt y consultas de verificación, usa siempre FINAL en tablas RMT. En las consultas del benchmark, donde se compara la latencia con Snowflake, las consultas de ClickHouse ya usan FINAL, por lo que la comparación es justa.

5. Macro generate_schema_name

De forma predeterminada, dbt antepone a los esquemas de modelos el nombre del esquema de destino del perfil. Si tu perfil dbt apunta al esquema nyc_taxi_ch, un modelo con +schema: analytics acaba en nyc_taxi_ch_analytics, no en analytics.

En Snowflake resulta inocuo (los esquemas son espacios de nombres dentro de una base), pero crea nombres incómodos en ClickHouse, donde los esquemas son bases de datos. nyc_taxi_ch_analytics es válido, pero menos elegante que analytics y no coincide con los nombres de bases de la arquitectura de ClickHouse de la Parte 3.

La solución es sobrescribir 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 %}

Esta macro:

• Devuelve custom_schema_name tal cual (en minúsculas) si el modelo especifica +schema: analytics
• Devuelve el esquema de destino del perfil (en minúsculas) si el modelo no tiene un esquema personalizado

El filtro | lower también garantiza nombres de esquema siempre en minúsculas, de acuerdo con las reglas de identificadores sensibles a mayúsculas de ClickHouse (en la Parte 1 de Snowflake se usó | upper).

Ubicación: macros/generate_schema_name.sql, en el directorio superior macros/; dbt_project.yml define macro-paths: ["macros"].

Resumen de la configuración dbt para 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 %}