Engine MergeTree

ClickHouse menyimpan semua data dalam tabel yang ditopang salah satu varian engine MergeTree. Jika Anda datang dari Snowflake, tidak ada konsep yang setara — Snowflake menangani semua keputusan storage secara internal. Di ClickHouse, memilih engine yang tepat adalah tanggung jawab Anda, dan salah memilih menghasilkan hasil yang diam-diam tidak benar.

Panduan ini mencakup engine yang akan Anda pakai di lab NYC Taxi serta gotcha yang menjegal setiap migrator Snowflake.

Apa itu MergeTree?

MergeTree adalah engine storage utama ClickHouse. Data ditulis ke file kolumnar yang tidak dapat diubah, disebut parts. ClickHouse secara periodik me-merge parts di latar belakang — menyortir, mengompresi, dan secara opsional mentransformasinya sesuai aturan engine-nya.

Konsekuensi utamanya: sebuah pembacaan bisa melihat beberapa versi dari satu baris sampai merge terjadi. Sebagian besar engine menangani ini secara transparan, tetapi ada yang tidak (terutama ReplacingMergeTree) dan menuntut Anda memahami siklus hidup merge agar bisa menulis query yang benar.

Saat membuat tabel MergeTree, Anda wajib menentukan ORDER BY. Ini menentukan:

1. Urutan sortir fisik data di dalam setiap part
2. Primary index (sparse, tingkat blok, disimpan di memori)
3. Untuk engine yang melakukan deduplikasi, kolom mana yang mendefinisikan "kunci" deduplikasi

Tidak ada konsep terpisah berupa primary key, clustered index, atau distribution key. ORDER BY adalah semua itu sekaligus.

MergeTree

Pakai ketika: Tabel bersifat insert-only atau update ditangani di luar. Tidak perlu deduplikasi.

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

Karakteristik:

• INSERT menambahkan data sebagai parts baru
• Tanpa deduplikasi — baris duplikat dipertahankan
• Merge mengoptimalkan storage dan kompresi tetapi tidak mengubah isi logisnya
• Query membaca semua parts yang cocok dengan rentang prefiks ORDER BY

Kapan ini jadi masalah: Jika Anda menyisipkan baris yang sama dua kali (misalnya percobaan ulang setelah kegagalan jaringan), kedua baris muncul di hasil query. Untuk pipeline yang benar-benar insert-only di mana duplikat tidak mungkin terjadi, ini benar. Untuk tabel apa pun yang menerima update CDC atau muatan yang bisa diulang, pakai ReplacingMergeTree.

ReplacingMergeTree

Pakai ketika: Baris bisa diperbarui (misalnya koreksi tarif, perubahan status). Anda ingin satu baris per kunci di hasil query.

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

Karakteristik:

• Selama merge latar belakang, baris dengan kunci ORDER BY yang sama dideduplikasi: hanya baris dengan nilai kolom versi tertinggi yang dipertahankan
• Kolom versi (di sini updated_at) menentukan baris mana yang menang — nilai lebih tinggi = lebih baru = dipertahankan
• Deduplikasi bersifat asinkron — sampai sebuah merge berjalan, versi lama dan baru sama-sama ada

Gotcha kritisnya: keterlambatan deduplikasi

Di antara merge, query tanpa FINAL melihat semua versi dari sebuah baris:

-- 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 memaksa deduplikasi saat pembacaan. Ia lebih lambat daripada membaca tanpa FINAL karena ClickHouse harus memeriksa semua parts untuk mencari kunci duplikat. Untuk lab NYC Taxi, semua query terhadap fact_trips memakai FINAL.

Kapan ini jadi masalah:

• Melewatkan FINAL pada point lookup → diam-diam mengembalikan baris duplikat; agregat menghitung berlebih
• Memakai kolom versi yang salah (yang tidak bertambah saat update) → nilai lama yang menang
• Memakai MergeTree bukan RMT untuk tabel yang mutable → semua versi menumpuk; jumlah baris tumbuh tanpa batas
• Mengharapkan deduplikasi sinkron → job ETL membaca segera setelah INSERT, melihat duplikat

RMT dengan dbt: Strategi inkremental delete_insert menghapus baris dalam rentang kunci batch masuk sebelum menyisipkan, sehingga tabel tidak pernah punya duplikat sejak awal. FINAL masih disarankan demi keamanan tetapi kurang krusial ketika strategi dbt-nya sudah benar.

AggregatingMergeTree

Pakai ketika: Tabel menyimpan state agregasi parsial yang harus di-merge selama merge latar belakang dan digabungkan saat query.

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

Karakteristik:

• Baris dengan kunci ORDER BY yang sama di-merge memakai logika combiner dari fungsi agregatnya
• Saat query pakai combiner bersufiks -Merge: sumMerge(fare_sum), countMerge(trip_count)
• Biasanya diisi oleh Materialized View yang mengubah INSERT mentah menjadi state parsial

Kapan memakainya: AggregatingMergeTree untuk data pra-agregasi di mana state parsial harus bisa digabungkan. Untuk lab NYC Taxi, agg_hourly_zone_trips dibangun ulang oleh dbt pada setiap eksekusi — ia tabel penggantian penuh, bukan akumulator state parsial. Pakai ReplacingMergeTree di sana.

Kapan ini jadi masalah: Memakai sum(fare_sum) bukan sumMerge(fare_sum) saat query memperlakukan state agregat biner sebagai Float64 dan mengembalikan angka sampah. Ini adalah kesalahan kebenaran yang senyap.

CollapsingMergeTree

Pakai ketika: Anda perlu menghapus atau memperbarui baris dengan menyisipkan "baris sign" (sign=1 untuk insert, sign=-1 untuk cancel). Lebih jarang dipakai tetapi berguna untuk pola CDC berbasis event.

ENGINE = CollapsingMergeTree(sign)

Selama merge, pasangan baris dengan sign=1 dan sign=-1 untuk kunci yang sama saling meniadakan. Tidak dipakai di lab NYC Taxi — ReplacingMergeTree dengan kolom versi lebih sederhana untuk pola insert-retry pada workload ini.

MergeTree dengan TTL

Tambahkan kedaluwarsa data berbasis waktu ke varian MergeTree mana pun:

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;

TTL terpicu selama merge latar belakang. Baris kedaluwarsa dibuang dari parts ketika parts itu di-merge. Untuk lab ini, TTL tidak dikonfigurasi — seluruh 4 tahun data dipertahankan. Di produksi, TTL sangat penting untuk mengelola biaya storage.

Memilih Engine: Pohon Keputusan

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()

Untuk lab NYC Taxi:

Desain ORDER BY

ORDER BY adalah keputusan performa terpenting dalam sebuah tabel ClickHouse. Ia menentukan:

1. Efisiensi primary index — query yang memfilter pada kolom prefiks ORDER BY melewati blok yang tidak relevan
2. Rasio kompresi — data tersortir terkompresi lebih baik (nilai yang mirip berdekatan)
3. Kunci deduplikasi (untuk RMT/AMT) — dua baris disebut duplikat hanya jika kolom ORDER BY mereka cocok

Aturan mendesain ORDER BY:

1. Letakkan kolom berkardinalitas rendah lebih dulu (misalnya borough, payment_type): lebih banyak baris berbagi satu nilai, jadi index melewati lebih banyak blok
2. Letakkan kolom berkardinalitas tinggi paling akhir (misalnya trip_id, UUID): kolom ini mempersempit rentang tetapi tidak terkompresi sebaik itu jika ditaruh di depan
3. Turunkan kolom dari filter query yang sesungguhnya, bukan dari skema sumber
4. Untuk tabel RMT, kolom terakhir sebaiknya berupa pengenal baris unik (memastikan satu baris per kunci bisnis)

Anti-pola: Menyalin primary key sumber sebagai ORDER BY. Jika TRIPS_RAW di Snowflake tidak punya sortir eksplisit, menyalin urutan skema Snowflake (trip_id lebih dulu) memberi ClickHouse ORDER BY yang acak — tidak ada block skipping untuk query analitik apa pun.

Contoh penurunan untuk fact_trips:

Query Q1–Q7 semuanya memfilter pada pickup_at dalam bentuk tertentu:

• 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)

Jadi pickup_at harus ada di ORDER BY dan sebaiknya dekat bagian depan. Memakai toStartOfMonth(pickup_at) sebagai kolom pertama menciptakan prefiks bergranularitas lebih kasar yang memungkinkan pruning tingkat partisi bahkan tanpa klausa PARTITION BY. trip_id diletakkan paling akhir untuk keunikan RMT.

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

PARTITION BY

PARTITION BY bersifat opsional dan terpisah dari ORDER BY. Ia menciptakan partisi direktori fisik — setiap partisi adalah kumpulan parts yang independen.

PARTITION BY toYYYYMM(pickup_at)

Pakai PARTITION BY ketika:

• Anda perlu men-DROP seluruh rentang waktu secara efisien (ALTER TABLE DROP PARTITION '202401')
• Anda ingin TTL bekerja per bulan, bukan per baris
• Tabelnya sangat besar (>1TB) dan metadata per partisi akan membantu perencanaan query

JANGAN pakai PARTITION BY untuk menggantikan ORDER BY. Kesalahan umum adalah menaruh toYYYYMM(date) di PARTITION BY dan menghilangkannya dari ORDER BY — ini mencegah block skipping di dalam sebuah partisi.

Untuk lab NYC Taxi, PARTITION BY tidak diperlukan — datasetnya 50 juta baris (~8GB terkompresi), masih nyaman dalam jangkauan performa satu partisi.

Ringkasan Gotcha Kunci