dbt no ClickHouse

Este guia aborda os padrões específicos do dbt-clickhouse usados na Parte 3. Leia-o depois de concluir as Planilhas de 1 a 4 e antes da Planilha 5 (Projeto de modelos dbt).

Para quem vem do dbt-snowflake, a maioria dos conceitos do dbt é idêntica — sources, refs, testes, macros e o padrão de camadas de preparação/intermediária/analytics. O que muda é a camada de configuração específica do ClickHouse: mecanismo, order_by, estratégia incremental e semântica de FINAL.

1. Tipos de materialização

O dbt-clickhouse aceita cinco materializações. Escolha com base no padrão de atualização, não em uma preferência pessoal.

Principal diferença em relação ao Snowflake: o dbt-snowflake trata internamente os detalhes de armazenamento. No dbt-clickhouse, os modelos table e incremental exigem uma configuração +engine explícita — o dbt a usa para gerar o DDL CREATE TABLE ... ENGINE = ....

Views não possuem mecanismo. Se você adicionar por engano +engine a uma materialização view, o dbt-clickhouse a ignorará. Somente as materializações table e incremental criam armazenamento persistente que precisa de um mecanismo.

Views materializadas atualizáveis. A materialização materialized_view do dbt-clickhouse aceita um bloco de configuração refreshable — um interval (e, opcionalmente, randomize) — que emite a cláusula REFRESH diretamente na instrução CREATE MATERIALIZED VIEW gerada. O modelo mv_live_trip_feed deste laboratório não define refreshable, razão pela qual a MV criada por ele não tem cronograma de atualização.

2. Como expressar configurações do ClickHouse no dbt

As configurações específicas do ClickHouse são expressas como configurações de modelos do dbt, seja em dbt_project.yml (para padrões de todo o projeto) ou no bloco config() de um modelo (para substituições específicas).

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

No bloco config() de um 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)'
) }}

As duas abordagens são equivalentes. dbt_project.yml é preferível para padrões válidos em todo o projeto; blocos config() são preferíveis para substituições específicas de um modelo ou quando você quer manter a configuração ao lado do SQL.

Principais parâmetros de configuração

Regras de escopo: as configurações em dbt_project.yml se propagam do pai para o filho. Um bloco config() no nível do modelo sempre prevalece sobre a configuração do projeto. Defina o mecanismo mais comum como padrão do projeto e, em seguida, substitua-o nos modelos diferentes.

3. Funcionamento de delete_insert

delete_insert é a estratégia incremental padrão da comunidade dbt-clickhouse. É o equivalente mais próximo de MERGE INTO do Snowflake, mas seu funcionamento é diferente.

Requisito de versão: delete_insert usa exclusões leves do ClickHouse, introduzidas na versão 22.8 (experimentais) e prontas para produção desde a 23.3. O ClickHouse Cloud atende a esse requisito. Para ativá-las, adicione use_lw_deletes: true ao destino do ClickHouse em ~/.dbt/profiles.yml ou defina allow_experimental_lightweight_delete=1 em query_settings.

O que ela faz

Em cada execução incremental:

1. EXCLUI da tabela de destino as linhas cuja unique_key corresponde a uma linha do lote recebido
2. INSERE todas as linhas do lote recebido

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

Diferenças em relação a MERGE INTO do Snowflake

A estratégia merge do Snowflake gera, linha a linha, WHEN MATCHED THEN UPDATE / WHEN NOT MATCHED THEN INSERT. O ClickHouse não possui uma instrução MERGE INTO. delete_insert alcança o mesmo resultado final — uma linha por chave exclusiva — por meio de uma exclusão em lote seguida de uma inserção completa.

Interação com o ReplacingMergeTree

delete_insert é o principal caminho para a exatidão. ReplacingMergeTree é a rede de proteção.

Quando uma execução de delete_insert termina normalmente, a tabela fica limpa (uma linha por trip_id), sem duplicatas.

Se uma execução de delete_insert for interrompida no meio (falha depois de DELETE e antes de INSERT), é provável que os dados fiquem em um estado inválido — talvez as linhas excluídas não tenham sido reinseridas. A próxima execução bem-sucedida restaurará o estado correto, mas não consulte a tabela entre um DELETE com falha e sua nova execução.

Se uma execução produzir duplicatas por qualquer motivo, a mesclagem em segundo plano do ReplacingMergeTree acabará por desduplicá-las, mantendo a linha com o maior valor na coluna de versão.

Nunca dependa apenas do RMT sem delete_insert — as mesclagens em segundo plano são assíncronas e podem levar de minutos a horas em tabelas grandes.

Quando usar append

append insere novas linhas sem alterar as existentes. É a estratégia correta para tabelas que recebem exclusivamente inserções e cujas linhas nunca são atualizadas — por exemplo, um log de eventos imutável ou uma tabela de ingestão bruta com IDs garantidamente exclusivos e sem correções. append não impõe requisito de versão nem risco de mutação.

Para fact_trips, append está errado: uma corrida pode ser corrigida depois do fato (ajuste da tarifa ou mudança de status), portanto o mesmo trip_id chega novamente com novos valores. Com append, as duas versões se acumulam permanentemente, e as agregações (SUM das tarifas, COUNT das corridas) fazem contagem excessiva até a próxima mesclagem em segundo plano do RMT. Use delete_insert sempre que as linhas puderem ser atualizadas.

Por que não usar a estratégia merge?

A estratégia merge (o padrão legado anterior a delete_insert) cria uma tabela temporária, preenche-a com as linhas existentes que não foram alteradas mais o novo lote e, então, substitui atomicamente a tabela original. Ao contrário de delete_insert, ela não usa exclusões leves — reescreve a tabela inteira em cada execução incremental. Para uma tabela fact_trips com 50 milhões de linhas, isso seria extremamente caro. delete_insert processa apenas as linhas do lote atual; merge toca em todas as linhas da tabela. Use delete_insert.

4. Estratégia de posicionamento de FINAL

A desduplicação do ReplacingMergeTree ocorre em segundo plano — o ClickHouse mescla as partes de forma assíncrona. Entre as mesclagens, as linhas duplicadas coexistem. FINAL força a desduplicação síncrona durante a leitura.

Onde FINAL deve ficar em um pipeline dbt

Na camada que lê uma origem ReplacingMergeTree e produz dados analíticos limpos.

Para a carga de trabalho 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 é o único ponto em que se impõe a desduplicação de trips_raw. Todos os modelos posteriores que leem stg_trips recebem automaticamente dados de origem limpos e desduplicados. Você não precisa de FINAL em int_trips_enriched nem em fact_trips, pois eles leem stg_trips (uma view, não uma tabela RMT).

Consultas de dashboard e testes do dbt que leem diretamente de fact_trips usam FINAL externamente. O próprio modelo não incorpora FINAL, pois ela seria aplicada a todas as varreduras dentro da consulta do modelo — inclusive à subconsulta is_incremental() que lê max(updated_at) de {{ this }}.

Impacto de FINAL no desempenho

FINAL acrescenta uma latência proporcional ao número de linhas duplicadas. Em uma tabela RMT bem mantida (com mesclagens frequentes em segundo plano), FINAL tem uma sobrecarga mínima porque há poucas duplicatas para resolver. Em uma tabela recém-carregada com muitas partes ainda não mescladas, FINAL pode ser significativamente mais lenta.

Em testes do dbt e consultas de verificação, sempre use FINAL nas tabelas RMT. Nas consultas de benchmark, em que o objetivo é comparar a latência com o Snowflake, as consultas do ClickHouse já usam FINAL — portanto, a comparação é justa.

5. Macro generate_schema_name

Por padrão, o dbt adiciona aos esquemas dos modelos o nome do esquema de destino definido no perfil. Se o perfil dbt aponta para o esquema nyc_taxi_ch, um modelo com +schema: analytics vai parar em nyc_taxi_ch_analytics — não em analytics.

Isso é inofensivo no Snowflake (esquemas são namespaces dentro de um banco de dados), mas gera nomes incômodos no ClickHouse, onde esquemas são bancos de dados. nyc_taxi_ch_analytics é um nome de banco de dados válido no ClickHouse, mas é menos elegante que analytics e não corresponde aos nomes dos bancos de destino usados na arquitetura do ClickHouse da Parte 3.

A correção é substituir a 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 %}

Essa macro:

• Retorna custom_schema_name sem alteração (em minúsculas) quando um modelo especifica +schema: analytics
• Retorna o esquema de destino do perfil (em minúsculas) para modelos sem esquema personalizado

O filtro | lower também garante que os nomes de esquema estejam sempre em minúsculas, de acordo com as regras de diferenciação entre maiúsculas e minúsculas dos identificadores do ClickHouse (a Parte 1 no Snowflake usou | upper).

Onde ela fica: macros/generate_schema_name.sql — no diretório macros/ de nível superior; dbt_project.yml define macro-paths: ["macros"].

Visão integrada: resumo da configuração do 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 %}