Worksheet 5: dbt model design

Time estimate: 15–20 minutes Reference: dbt on ClickHouse

Concept

Worksheets 1–4 produced the what: which engine, which ORDER BY, which ClickHouse types. This worksheet produces the how: how those decisions are expressed as dbt configuration before writing any SQL.

A dbt-clickhouse model has three layers of configuration that Snowflake developers don't need to think about:

1. Materialization — which physical object does dbt create (view, table, incremental, ephemeral)?
2. Engine config — for tables and incremental models, which ClickHouse engine and version column?
3. Incremental strategy — for incremental models, how does dbt handle new/updated rows?

There is also a correctness concern unique to ReplacingMergeTree:

4. FINAL placement — where in the dbt DAG does FINAL go to guarantee deduplicated reads?

Use this decision tree for materialization:

• Insert-only read from a source, with no writes to the model itself? → view
• Pure join logic, not queried directly? → ephemeral
• Full rebuild on every dbt run, no partial updates? → table
• Only new/changed rows processed per run? → incremental

Staging models are always views — the same rule from Worksheet 1. stg_trips and stg_taxi_zones are passthrough reads with no writes of their own, so they stay views regardless of how their source data behaves.

Exercise 1: Materialization Selection

For each model, pick the correct dbt materialization, then answer why in the question below the table.

Exercise 2: Engine Configuration

Only models with table or incremental materialization need a ClickHouse engine — views and ephemeral models have none. Reference your Worksheet 1 answers: the dbt engine config is the implementation of the engine decisions you already made there.

Exercise 3: Incremental Strategy Design

For each incremental model, design the full incremental configuration: unique_key, incremental_strategy, and the SQL filter that goes inside the is_incremental() guard block:

{% if is_incremental() %}
  WHERE <your filter here>
{% endif %}

Exercise 4: FINAL Placement

ReplacingMergeTree deduplication is asynchronous. FINAL forces it at read time — but placing FINAL in the wrong model has correctness or performance consequences. For each model below, answer whether FINAL belongs in that model's own FROM clause.

Loading worksheet...

Transfer to migration-plan.md

Once you have completed this worksheet, fill in Section 10 of migration-plan.md with your answers and check off:

- [ ] dbt model design: completed