MergeTree 엔진

ClickHouse는 모든 데이터를 MergeTree 엔진 변종 중 하나가 뒷받침하는 테이블에 저장한다. Snowflake에서 오는 사람에게는 대응되는 개념이 없다 — Snowflake는 모든 스토리지 결정을 내부에서 처리한다. ClickHouse에서는 올바른 엔진을 고르는 것이 당신의 책임이고, 잘못 고르면 조용히 틀린 결과가 나온다.

이 가이드는 NYC Taxi 랩에서 사용할 엔진들과, 모든 Snowflake 마이그레이터가 걸려 넘어지는 함정을 다룬다.

MergeTree란 무엇인가

MergeTree는 ClickHouse의 기본 스토리지 엔진이다. 데이터는 파트라고 불리는 불변 컬럼형 파일에 기록된다. ClickHouse는 백그라운드에서 주기적으로 파트를 머지한다 — 엔진의 규칙에 따라 정렬하고, 압축하고, 필요하면 변환한다.

핵심적인 결과는 이것이다. 머지가 일어나기 전까지 읽기는 한 행의 여러 버전을 볼 수 있다. 대부분의 엔진은 이를 투명하게 처리하지만, 일부(특히 ReplacingMergeTree)는 올바른 쿼리를 작성하려면 머지 생애주기를 이해해야 한다.

MergeTree 테이블을 만들 때는 ORDER BY를 반드시 지정해야 한다. 이것이 결정하는 것은 다음과 같다.

1. 각 파트 내부 데이터의 물리적 정렬 순서
2. primary index (스파스, 블록 단위, 메모리에 저장)
3. 중복 제거를 하는 엔진에서는 어떤 컬럼이 중복 제거의 "키"를 정의하는지

primary key, 클러스터드 인덱스, 분산 키 같은 별도 개념은 없다. ORDER BY가 이 모두를 한 번에 담당한다.

MergeTree

사용 시점: 테이블이 삽입 전용이거나 업데이트를 외부에서 처리하는 경우. 중복 제거가 필요 없다.

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

특성:

• 삽입은 데이터를 새 파트로 덧붙인다
• 중복 제거 없음 — 중복 행이 그대로 보존된다
• 머지는 스토리지와 압축을 최적화하지만 논리적 내용은 바꾸지 않는다
• 쿼리는 ORDER BY 프리픽스 범위에 해당하는 모든 파트를 읽는다

잘못되는 경우: 같은 행을 두 번 삽입하면(예: 네트워크 실패 후 재시도) 두 행 모두 쿼리 결과에 나타난다. 중복이 발생할 수 없는 진짜 삽입 전용 파이프라인이라면 이것이 맞다. CDC 업데이트나 재시도 가능한 로드를 받는 테이블이라면 ReplacingMergeTree를 쓰라.

ReplacingMergeTree

사용 시점: 행이 업데이트될 수 있고(예: 요금 정정, 상태 변경), 쿼리 결과에서 키당 한 행만 원하는 경우.

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

특성:

• 백그라운드 머지 중 같은 ORDER BY 키를 가진 행들이 중복 제거된다. 버전 컬럼 값이 가장 큰 행만 남는다
• 버전 컬럼(여기서는 updated_at)이 어느 행이 이기는지 결정한다 — 값이 클수록 최신이고, 남는다
• 중복 제거는 비동기다 — 머지가 실행되기 전까지 이전 버전과 새 버전이 공존한다

결정적 함정: 중복 제거 지연

머지 사이에는 FINAL 없는 쿼리가 한 행의 모든 버전을 본다.

-- 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은 읽기 시점에 중복 제거를 강제한다. ClickHouse가 중복 키를 찾기 위해 모든 파트를 확인해야 하므로 FINAL 없이 읽는 것보다 느리다. NYC Taxi 랩에서는 fact_trips를 대상으로 하는 모든 쿼리가 FINAL을 쓴다.

잘못되는 경우:

• 포인트 조회에서 FINAL을 빼면 → 조용히 중복 행이 반환되고, 집계가 과다 계산된다
• 잘못된 버전 컬럼을 쓰면(업데이트 시 증가하지 않는 컬럼) → 오래된 값이 이긴다
• 변경되는 테이블에 RMT 대신 MergeTree를 쓰면 → 모든 버전이 누적되고, 행 수가 무한정 늘어난다
• 동기적 중복 제거를 기대하면 → ETL 잡이 삽입 직후에 읽어서 중복을 보게 된다

RMT와 dbt: delete_insert 증분 전략은 들어오는 배치의 키 범위에 해당하는 행을 삽입 전에 삭제하므로, 애초에 테이블에 중복이 생기지 않는다. FINAL은 여전히 안전을 위해 권장되지만, dbt 전략이 올바르다면 덜 중요하다.

AggregatingMergeTree

사용 시점: 테이블이 부분 집계 상태를 저장하고, 그 상태를 백그라운드 머지 중에 병합하고 쿼리 시점에 결합해야 하는 경우.

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

특성:

• 같은 ORDER BY 키를 가진 행들이 집계 함수의 결합 로직으로 병합된다
• 쿼리 시점에는 -Merge 접미사 결합자를 쓴다: sumMerge(fare_sum), countMerge(trip_count)
• 보통 원시 삽입을 부분 상태로 변환하는 Materialized View가 데이터를 공급한다

언제 쓰는가: AggregatingMergeTree는 부분 상태가 반드시 결합 가능해야 하는 사전 집계 데이터를 위한 것이다. NYC Taxi 랩에서 agg_hourly_zone_trips는 실행마다 dbt가 재구축한다 — 부분 상태 누적기가 아니라 전체 교체 테이블이다. 거기에는 ReplacingMergeTree를 쓰라.

잘못되는 경우: 쿼리 시점에 sumMerge(fare_sum) 대신 sum(fare_sum)을 쓰면 바이너리 집계 상태를 Float64로 취급해 쓰레기 숫자를 반환한다. 조용한 정확성 오류다.

CollapsingMergeTree

사용 시점: "sign 행"을 삽입해 행을 삭제하거나 업데이트해야 하는 경우(sign=1은 삽입, sign=-1은 취소). 덜 흔하지만 이벤트 기반 CDC 패턴에 유용하다.

ENGINE = CollapsingMergeTree(sign)

머지 중에 같은 키에 대해 sign=1과 sign=-1인 행 쌍이 서로를 상쇄한다. NYC Taxi 랩에서는 쓰지 않는다 — 이 워크로드의 삽입-재시도 패턴에는 버전 컬럼을 쓴 ReplacingMergeTree가 더 간단하다.

TTL이 있는 MergeTree

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

TTL은 백그라운드 머지 중에 작동한다. 만료된 행은 파트가 머지될 때 제거된다. 이 랩에서는 TTL을 설정하지 않는다 — 4년치 데이터를 모두 보관한다. 프로덕션에서는 스토리지 비용을 관리하는 데 TTL이 필수다.

엔진 선택: 결정 트리

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

NYC Taxi 랩의 경우:

ORDER BY 설계

ORDER BY는 ClickHouse 테이블에서 가장 중요한 성능 결정이다. 이것이 결정하는 것은 다음과 같다.

1. primary index 효율 — ORDER BY 프리픽스 컬럼으로 필터링하는 쿼리는 무관한 블록을 건너뛴다
2. 압축률 — 정렬된 데이터가 더 잘 압축된다(비슷한 값이 인접한다)
3. 중복 제거 키 (RMT/AMT의 경우) — 두 행은 ORDER BY 컬럼이 일치할 때만 중복이다

ORDER BY 설계 규칙:

1. 카디널리티가 낮은 컬럼을 앞에 두라(예: borough, payment_type). 더 많은 행이 한 값을 공유하므로 인덱스가 더 많은 블록을 건너뛴다
2. 카디널리티가 높은 컬럼을 뒤에 두라(예: trip_id, UUID). 범위를 좁혀 주지만 앞쪽에 두면 압축 효율이 떨어진다
3. 소스 스키마가 아니라 실제 쿼리 필터에서 컬럼을 도출하라
4. RMT 테이블에서는 마지막 컬럼이 고유 행 식별자여야 한다(비즈니스 키당 한 행을 보장한다)

안티패턴: 소스의 primary key를 ORDER BY로 복사하기. Snowflake의 TRIPS_RAW에 명시적 정렬이 없는데 Snowflake 스키마 순서를 그대로 복사하면(trip_id가 첫 컬럼) ClickHouse는 무작위 ORDER BY를 갖게 되고, 어떤 분석 쿼리에서도 블록 건너뛰기가 일어나지 않는다.

fact_trips에 대한 도출 예시:

Q1–Q7은 모두 어떤 형태로든 pickup_at으로 필터링한다.

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

따라서 pickup_at은 ORDER BY에 들어가야 하고 앞쪽에 있어야 한다. toStartOfMonth(pickup_at)을 첫 컬럼으로 쓰면 더 굵은 단위의 프리픽스가 생겨, PARTITION BY 절이 없어도 파티션 수준의 프루닝이 가능해진다. trip_id는 RMT 고유성을 위해 마지막에 둔다.

결과: ORDER BY (toStartOfMonth(pickup_at), pickup_at, trip_id)

PARTITION BY

PARTITION BY는 선택 사항이고 ORDER BY와 별개다. 물리적 디렉터리 파티션을 만들며, 각 파티션은 독립적인 파트 집합이다.

PARTITION BY toYYYYMM(pickup_at)

PARTITION BY를 쓸 때:

• 특정 시간 범위 전체를 효율적으로 DROP해야 할 때 (ALTER TABLE DROP PARTITION '202401')
• TTL이 행 단위가 아니라 월 단위로 동작하기를 원할 때
• 테이블이 매우 크고(>1TB) 파티션별 메타데이터가 쿼리 계획에 도움이 될 때

PARTITION BY로 ORDER BY를 대체하지 마라. 흔한 실수는 toYYYYMM(date)를 PARTITION BY에 넣고 ORDER BY에서는 빼는 것이다 — 이러면 파티션 내부의 블록 단위 건너뛰기가 막힌다.

NYC Taxi 랩에서는 PARTITION BY가 필요 없다 — 데이터셋이 5천만 행(압축 후 약 8GB)이므로 단일 파티션 성능 범위에 충분히 들어온다.

핵심 함정 요약