Mecanismos MergeTree

O ClickHouse armazena todos os dados em tabelas baseadas em uma das variantes do mecanismo MergeTree. Para quem vem do Snowflake, não existe um conceito equivalente — o Snowflake toma internamente todas as decisões de armazenamento. No ClickHouse, escolher o mecanismo correto é responsabilidade sua, e uma escolha errada gera resultados silenciosamente incorretos.

Este guia aborda os mecanismos usados no laboratório NYC Taxi e as armadilhas que surpreendem quem migra do Snowflake.

O que é o MergeTree?

MergeTree é o principal mecanismo de armazenamento do ClickHouse. Os dados são gravados em arquivos colunares imutáveis chamados partes. O ClickHouse mescla periodicamente essas partes em segundo plano — ordenando, compactando e, opcionalmente, transformando-as de acordo com as regras do mecanismo.

A principal consequência é: uma leitura pode enxergar várias versões de uma linha até que ocorra uma mesclagem. A maioria dos mecanismos trata isso de forma transparente, mas alguns — em especial o ReplacingMergeTree — exigem que você entenda o ciclo de vida das mesclagens para escrever consultas corretas.

Ao criar uma tabela MergeTree, é obrigatório especificar ORDER BY. Essa expressão determina:

1. A ordem física dos dados dentro de cada parte
2. O índice primário (esparso, no nível de blocos e armazenado na memória)
3. Para os mecanismos que desduplicam, quais colunas definem a “chave” de desduplicação

Não há conceitos separados de chave primária, índice clusterizado ou chave de distribuição. ORDER BY cumpre todas essas funções ao mesmo tempo.

MergeTree

Use quando: a tabela recebe apenas inserções ou as atualizações são tratadas externamente. Não é necessária nenhuma desduplicação.

CREATE TABLE default.some_events (
    event_id      String,
    occurred_at   DateTime64(3, 'UTC'),
    payload       String
)
ENGINE = MergeTree()
ORDER BY (occurred_at, event_id);

Características:

• As inserções acrescentam dados como novas partes
• Não há desduplicação — linhas duplicadas são preservadas
• As mesclagens otimizam o armazenamento e a compactação, mas não alteram o conteúdo lógico
• As consultas leem todas as partes que correspondem ao intervalo do prefixo de ORDER BY

Quando dá errado: se você inserir a mesma linha duas vezes (por exemplo, ao repetir uma operação após uma falha de rede), as duas aparecerão nos resultados das consultas. Isso está correto em pipelines que realmente recebem apenas inserções, nos quais não podem ocorrer duplicatas. Para qualquer tabela que receba atualizações de CDC ou cargas que possam ser repetidas, use ReplacingMergeTree.

ReplacingMergeTree

Use quando: as linhas podem ser atualizadas (por exemplo, correções de tarifa ou mudanças de status) e você quer uma linha por chave nos resultados das consultas.

CREATE TABLE analytics.fact_trips (
    trip_id       String,
    pickup_at     DateTime64(3, 'UTC'),
    fare_amount   Float64,
    updated_at    DateTime64(3, 'UTC'),
    -- ...
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY (toStartOfMonth(pickup_at), pickup_at, trip_id);

Características:

• Durante as mesclagens em segundo plano, linhas com a mesma chave ORDER BY são desduplicadas: somente a linha com o maior valor na coluna de versão é mantida
• A coluna de versão (aqui, updated_at) determina qual linha prevalece — valor maior = mais recente = mantida
• A desduplicação é assíncrona — até ocorrer uma mesclagem, as versões antiga e nova coexistem

A principal armadilha: o atraso da desduplicação

Entre as mesclagens, uma consulta sem FINAL enxerga todas as versões de uma linha:

-- This may return multiple rows for the same trip_id
-- if the row has been updated since the last merge
SELECT * FROM analytics.fact_trips WHERE trip_id = 'abc123';

-- This returns exactly one row per trip_id, applying deduplication at query time
SELECT * FROM analytics.fact_trips FINAL WHERE trip_id = 'abc123';

FINAL força a desduplicação no momento da leitura. Ela é mais lenta que uma leitura sem FINAL, pois o ClickHouse precisa procurar chaves duplicadas em todas as partes. No laboratório NYC Taxi, todas as consultas a fact_trips usam FINAL.

Quando dá errado:

• Omitir FINAL em uma busca pontual → retorna silenciosamente linhas duplicadas; as agregações fazem contagem excessiva
• Usar a coluna de versão errada (uma que não aumenta nas atualizações) → os valores antigos prevalecem
• Usar MergeTree em vez de RMT para uma tabela mutável → todas as versões se acumulam; a contagem de linhas cresce sem limite
• Esperar uma desduplicação síncrona → uma tarefa de ETL lê logo depois da inserção e encontra duplicatas

RMT com dbt: a estratégia incremental delete_insert exclui as linhas no intervalo de chaves do lote recebido antes de inserir, de modo que a tabela nem chega a conter duplicatas. Ainda se recomenda FINAL por segurança, mas ela é menos importante quando a estratégia do dbt está correta.

AggregatingMergeTree

Use quando: a tabela armazena estados parciais de agregação que devem ser mesclados durante as mesclagens em segundo plano e combinados no momento da consulta.

CREATE TABLE analytics.agg_hourly_revenue (
    hour_bucket   DateTime,
    borough       String,
    fare_sum      AggregateFunction(sum, Float64),
    trip_count    AggregateFunction(count, UInt64)
)
ENGINE = AggregatingMergeTree()
ORDER BY (hour_bucket, borough);

Características:

• As linhas com a mesma chave ORDER BY são mescladas usando a lógica combinadora da função de agregação
• A consulta usa combinadores com o sufixo -Merge: sumMerge(fare_sum), countMerge(trip_count)
• Normalmente é alimentada por uma view materializada que converte inserções brutas em estados parciais

Quando usar: AggregatingMergeTree serve para dados pré-agregados cujos estados parciais precisam ser combináveis. No laboratório NYC Taxi, agg_hourly_zone_trips é recriada pelo dbt em cada execução — ela é uma tabela de substituição completa, não um acumulador de estados parciais. Use ReplacingMergeTree nesse caso.

Quando dá errado: usar sum(fare_sum) em vez de sumMerge(fare_sum) no momento da consulta trata o estado binário da agregação como um Float64 e retorna números sem sentido. Esse é um erro silencioso de exatidão.

CollapsingMergeTree

Use quando: você precisa excluir ou atualizar linhas inserindo uma “linha de sinal” (sign=1 para inserir, sign=-1 para cancelar). É menos comum, mas útil para padrões de CDC baseados em eventos.

ENGINE = CollapsingMergeTree(sign)

Durante as mesclagens, pares de linhas com sign=1 e sign=-1 para a mesma chave se anulam. Esse mecanismo não é usado no laboratório NYC Taxi — ReplacingMergeTree com uma coluna de versão é mais simples para o padrão de repetição de inserções desta carga de trabalho.

MergeTree com TTL

Adicione a expiração de dados baseada no tempo a qualquer variante do MergeTree:

CREATE TABLE default.trips_raw (
    trip_id    String,
    pickup_at  DateTime64(3, 'UTC'),
    _synced_at DateTime DEFAULT now(),
    -- ...
)
ENGINE = ReplacingMergeTree(_synced_at)
ORDER BY (pickup_at, trip_id)
TTL toDate(pickup_at) + INTERVAL 2 YEAR;

A TTL é acionada durante as mesclagens em segundo plano. Linhas expiradas são removidas das partes à medida que elas são mescladas. O laboratório não configura uma TTL — os quatro anos de dados são mantidos. Em produção, a TTL é essencial para gerenciar os custos de armazenamento.

Escolha do mecanismo: árvore de decisão

Does the table receive UPDATE or DELETE operations?
├── No (insert-only, e.g., event log, append-only stream)
│   └── MergeTree()
└── Yes
    ├── Do rows have a version/timestamp column that increases on update?
    │   ├── Yes → ReplacingMergeTree(version_col)
    │   └── No (full reload, e.g., dim tables rebuilt by dbt)
    │       └── MergeTree() — dbt atomic table swap (full rebuild) handles "upsert"
    └── Is the table a pre-aggregated accumulator with combinable states?
        └── AggregatingMergeTree()

Para o laboratório NYC Taxi:

Projeto de ORDER BY

ORDER BY é a decisão de desempenho mais importante em uma tabela do ClickHouse. Ela determina:

1. Eficiência do índice primário — consultas que filtram pelas colunas do prefixo de ORDER BY ignoram blocos irrelevantes
2. Taxa de compactação — dados ordenados são mais bem compactados (valores semelhantes ficam adjacentes)
3. Chave de desduplicação (para RMT/AMT) — duas linhas só são duplicatas quando suas colunas de ORDER BY coincidem

Regras para projetar ORDER BY:

1. Coloque primeiro as colunas de baixa cardinalidade (por exemplo, borough, payment_type): mais linhas compartilham um valor, portanto o índice ignora mais blocos
2. Coloque por último as colunas de alta cardinalidade (por exemplo, trip_id, UUID): elas estreitam o intervalo, mas não permitem tanta compactação no início
3. Derive as colunas dos filtros reais das consultas, não do esquema de origem
4. Para tabelas RMT, a última coluna deve ser o identificador exclusivo da linha (garante uma linha por chave de negócio)

Antipadrão: copiar a chave primária da origem como ORDER BY. Se TRIPS_RAW no Snowflake não possui uma ordenação explícita, copiar a ordem do esquema do Snowflake (trip_id primeiro) fornece ao ClickHouse uma ORDER BY aleatória — não haverá eliminação de blocos em nenhuma consulta analítica.

Exemplo de derivação para fact_trips:

As consultas de Q1 a Q7 filtram por pickup_at de alguma forma:

• Q1: WHERE pickup_at >= ...
• Q2: ORDER BY week, pickup_location_id
• Q3: WHERE pickup_at >= CURRENT_DATE - 7
• Q4: GROUP BY DATE_TRUNC('day', pickup_at)

Portanto, pickup_at precisa fazer parte de ORDER BY e ficar próximo ao início. Usar toStartOfMonth(pickup_at) como primeira coluna cria um prefixo de granularidade mais ampla, que permite eliminar partições mesmo sem uma cláusula PARTITION BY. trip_id fica por último para garantir a exclusividade no RMT.

Resultado: ORDER BY (toStartOfMonth(pickup_at), pickup_at, trip_id)

PARTITION BY

PARTITION BY é opcional e independente de ORDER BY. Ela cria partições físicas de diretório — cada partição é um conjunto independente de partes.

PARTITION BY toYYYYMM(pickup_at)

Use PARTITION BY quando:

• Você precisa eliminar de forma eficiente um intervalo de tempo inteiro (ALTER TABLE DROP PARTITION '202401')
• Você quer que a TTL opere por mês, em vez de por linha
• A tabela é muito grande (>1 TB) e os metadados por partição ajudariam no planejamento das consultas

NÃO use PARTITION BY para substituir ORDER BY. Um erro comum é colocar toYYYYMM(date) em PARTITION BY e omiti-la de ORDER BY — isso impede a eliminação por bloco dentro de uma partição.

No laboratório NYC Taxi, PARTITION BY não é necessária — o conjunto de dados tem 50 milhões de linhas (cerca de 8 GB compactados), muito abaixo do limite em que uma única partição afeta o desempenho.

Resumo das principais armadilhas