Worksheet 1: MergeTree engine selection

Time estimate: 15–20 minutes Reference: MergeTree engines

Concept

In Snowflake, you create a table and Snowflake decides how to store it. In ClickHouse, you choose a storage engine — and this choice determines correctness, not just performance.

The three engines you need for this lab:

MergeTree — The base engine. Data is stored in sorted columnar files. No deduplication. Use this when the table is insert-only or when your pipeline manages updates externally (e.g., full reload on every dbt run).

ReplacingMergeTree(version_col) — Extends MergeTree with background deduplication. When rows with the same ORDER BY key exist in multiple parts, only the row with the highest version_col value is kept after a merge. Use this when rows can be updated and you have a column that increases monotonically on every update (e.g., updated_at timestamp).

Critical gotcha: Deduplication is asynchronous. Until ClickHouse runs a background merge, both the old and new version of a row coexist. Always use SELECT ... FINAL on ReplacingMergeTree tables to force deduplication at query time.

AggregatingMergeTree — Extends MergeTree with partial aggregate state merging. Use this when the table stores combinable aggregate states (e.g., HyperLogLog sketches, quantile digests) and you need background aggregation. Not needed for the NYC Taxi lab — the aggregation tables are rebuilt by dbt, not accumulated.

Decision Tree

Does the table receive UPDATE or DELETE operations?
│
├── No (insert-only)
│   └─► MergeTree()
│
└── Yes
    ├── Is there a timestamp/version column that increases on every update?
    │   ├── Yes → ReplacingMergeTree(version_col)
    │   └── No (e.g., full-reload dimension tables)
    │       └─► MergeTree()  — dbt handles upsert via atomic table swap (full rebuild)
    │
    └── Does the table store partial aggregate states (AggregateFunction types)?
        └── Yes → AggregatingMergeTree()

Staging Models Are Always Views

In dbt + ClickHouse, staging models should be materialized as views, not tables. Views have zero storage cost and are always fresh — they are just saved SQL, not physical objects.

The engine selection exercise below covers analytics-layer dbt models (fact tables, aggregates, dimensions) and the ClickHouse Materialized View. It does not include trips_raw or staging models:

• trips_raw is a base ClickHouse table created directly by the migration script (scripts/02_migrate_trips.py) — not a dbt model. It uses ReplacingMergeTree(_synced_at) because the migration script may retry a batch and re-insert the same trip_id. After cutover, the live producer can also retry on transient failures; _synced_at DateTime DEFAULT now() ensures the most recent write wins.
• stg_trips is a dbt view on top of trips_raw. It applies SELECT ... FROM trips_raw FINAL to resolve any unmerged duplicates before the data reaches downstream analytics models. Deduplication responsibility belongs here — not in a ReplacingMergeTree staging table.

Exercise: engine selection for NYC Taxi tables

For each table below, determine the update pattern, identify the version column (if any), and pick the engine that keeps the table correct. Then work the reasoning questions once every row is filled in.

Loading worksheet...

Transfer to migration-plan.md

Once you have filled in this worksheet, copy your engine decisions to Section 3 of migration-plan.md and check off:

- [ ] Engine selection: completed