04 Rebuild the dbt pipeline

Starting point

Module 03 complete: the ClickHouse Cloud service is live and reachable, and .clickhouse_state has been written to disk by setup.sh with CLICKHOUSE_HOST and CLICKHOUSE_PORT. All target tables and staging views exist — default.trips_raw, the two staging views (stg_trips, stg_taxi_zones), the six analytics tables (fact_trips, agg_hourly_zone_trips, dim_taxi_zones, dim_payment_type, dim_vendor, dim_date), and the refreshable materialized view analytics.mv_live_trip_feed — module 03's dbt run already built all seven. Every analytics table is still empty except mv_live_trip_feed, which already holds the one snapshot row from that build. Only default.trips_raw has data otherwise: roughly 50 million rows. The Snowflake producer is still running, so ClickHouse is behind Snowflake by roughly the length of the migration window. Budget about 30 minutes.

Why

Module 03 proved ClickHouse can hold 50 million rows. It did not prove the pipeline can run on ClickHouse — the staging views, the incremental fact table, the dimension reloads, the tests that catch a broken model before a partner ever sees it. That is what this module rebuilds: the same Medallion models from module 01, expressed with dbt-clickhouse instead of dbt-snowflake, running against the tables module 03 already created.

Nothing about the model logic changes — stg_trips still type-casts and extracts JSON, int_trips_enriched still joins the dimensions, fact_trips still ends up as one row per trip. What changes is the materialization layer underneath: no MERGE INTO, no Snowflake Task, no cluster_by. This is the module that shows the migration is not a one-off data dump — the pipeline a partner's team runs every day, on the same schedule, gated by the same dbt test run, keeps working once the warehouse underneath it changes.

Concepts — under the hood

The model set differs from the Snowflake pipeline in four ways. The full configuration reference is dbt on ClickHouse — this is the short version you need before running dbt run in Step 1. For the source pipeline these models replace, see dbt on Snowflake.

1. delete_insert replaces MERGE. ClickHouse has no MERGE INTO statement. Where the Snowflake pipeline used incremental_strategy: merge to upsert fact_trips and agg_hourly_zone_trips, the ClickHouse models use incremental_strategy: delete_insert: dbt deletes the rows matching unique_key for the incoming batch, then inserts the batch. For fact_trips, unique_key is trip_id, and the incremental filter watermarks on updated_at rather than pickup_at — a fare correction re-inserts the same trip_id with the same pickup_at but a newer updated_at, so watermarking on pickup_at would silently miss it.

2. ReplacingMergeTree is the safety net under delete_insert, not a substitute for it. Both incremental models are declared ReplacingMergeTree(updated_at). If a delete_insert run completes normally, the table already has one row per key and the engine has nothing to clean up. If a run is interrupted mid-way — crash after the delete, before the insert — background merges eventually deduplicate any leftover rows, keeping the one with the highest updated_at. Never rely on ReplacingMergeTree alone to do the deduplication work delete_insert is meant to do: background merges are asynchronous and can lag minutes to hours on a table this size.

3. Refreshable materialized views replace the scheduled task. The Snowflake pipeline used a scheduled Task running a stored procedure to keep a rolling aggregate current. ClickHouse's dbt project instead declares mv_live_trip_feed with materialized = 'materialized_view' and engine = 'ReplacingMergeTree(refreshed_at)' — built by dbt run as a refreshable materialized view, versus 30-plus lines of Snowflake CREATE TASK DDL for the same effect. This module does not switch a refresh interval on; doing so is a manual ALTER TABLE analytics.mv_live_trip_feed MODIFY REFRESH EVERY ... statement the lab does not script (see module 05 for why).

4. mv_live_trip_feed has no Snowflake counterpart at all. It is not a translation of an existing model — it is new capability the migration adds. A standard ClickHouse materialized view fires once per INSERT and only ever sees the rows in that batch, so it cannot correctly compute a lifetime aggregate like total trips or average fare. A REFRESHABLE materialized view instead re-runs its whole query — here, SELECT ... FROM {{ ref('fact_trips') }} — on a schedule, so every refresh sees the full table. The Snowflake side of this workshop never had this option.

The models dbt actually builds, and when each one gets data:

Step 1 — Populate the analytics layer

Activate the dbt-clickhouse venv you built in module 00, then run dbt run a second time. Module 03 already ran it once against empty tables to create the schemas; this run has real data behind it — trips_raw now holds 50 million rows.

cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/03-migrate-to-clickhouse"
source .venv/bin/activate
source .env && source .clickhouse_state

cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/03-migrate-to-clickhouse/dbt/nyc_taxi_dbt_ch"
dbt run

dbt reads the ClickHouse connection from ~/.dbt/profiles.yml, based on dbt/nyc_taxi_dbt_ch/profiles.yml.example — the same profile module 03 used to create the empty schemas.

Expected: approximately 8-12 minutes (50 million rows processed by the incremental models).

agg_hourly_zone_trips will be empty after this run — that is expected, not a failure. Its incremental filter is WHERE pickup_at >= now() - INTERVAL 2 HOUR, which only matches rows written by the live producer. Every row you just migrated is historical, so none of it falls inside a 2-hour window measured from right now. This table stays empty until module 05's cutover starts the ClickHouse producer — do not spend time debugging this as a broken pipeline.

Then run the test suite:

dbt test

Expected: all tests pass.

Verify:

SELECT formatReadableQuantity(count()) FROM analytics.fact_trips FINAL;
-- Expected: ~50 million

SELECT count() FROM analytics.agg_hourly_zone_trips;
-- Expected: 0 (normal — populated after cutover in module 05)

SELECT count() FROM analytics.dim_taxi_zones;
-- Expected: 265

Step 2 — Create the zone dictionary

analytics.dim_taxi_zones is now populated with all 265 NYC TLC zones. Build analytics.taxi_zones_dict, an in-memory dictionary backed by that table, so downstream queries can look up a zone's borough with dictGet() instead of a JOIN.

What a dictionary buys over a join. A dictionary loads into memory once and stays hot; a lookup against it is effectively free on every subsequent query. A JOIN against dim_taxi_zones re-reads and re-matches the dimension table every time it runs. For a small, rarely-changing reference table like this one — 265 rows, fully reloaded by every dbt run — that trade is one-sided. Module 05's benchmark run queries taxi_zones_dict directly with dictGet, so this step is a hard dependency for that module, not an optional add-on.

Source the connection details, then load the dictionary DDL either through clickhouse-client or the HTTP API — pick whichever you have available:

cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/03-migrate-to-clickhouse"
source .clickhouse_state

# Via clickhouse-client
clickhouse-client \
  --host "${CLICKHOUSE_HOST}" \
  --port 9440 \
  --user default \
  --password "${CLICKHOUSE_PASSWORD}" \
  --secure \
  --multiquery \
  < scripts/04_create_dictionary.sql

# Or via HTTP API
curl "https://${CLICKHOUSE_HOST}:${CLICKHOUSE_PORT}/" \
  --user "default:${CLICKHOUSE_PASSWORD}" \
  --data-binary @scripts/04_create_dictionary.sql

Verify:

-- Should return 'Manhattan' for zone 42
SELECT dictGet('analytics.taxi_zones_dict', 'borough', toUInt16(42));

-- Should show status = LOADED, element_count = 265
SELECT name, status, element_count
FROM system.dictionaries
WHERE name = 'taxi_zones_dict';

How to verify you are done

SELECT formatReadableQuantity(count()) FROM analytics.fact_trips FINAL;
-- Expected: ~50 million

SELECT count() FROM analytics.agg_hourly_zone_trips;
-- Expected: 0

Zero is correct here, not a failure. agg_hourly_zone_trips's incremental filter (WHERE pickup_at >= now() - INTERVAL 2 HOUR) only matches rows written by the live producer, and every row in ClickHouse right now is historical data moved by module 03's migration script — none of it is less than 2 hours old relative to now(). The table fills in only after module 05's cutover starts the ClickHouse producer; until then, any dashboard chart backed by this table will show no data, and that is expected, not something to debug.

SELECT count() FROM analytics.dim_taxi_zones;
-- Expected: 265

cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/03-migrate-to-clickhouse/dbt/nyc_taxi_dbt_ch"
dbt test

Expected: all tests pass.

-- Should return a borough name, e.g. 'Manhattan'
SELECT dictGet('analytics.taxi_zones_dict', 'borough', toUInt16(42));

End state

The analytics layer is populated and tested: fact_trips holds roughly 50 million rows, dim_taxi_zones, dim_payment_type, dim_vendor, and dim_date are fully loaded, dbt test passes end to end, and analytics.taxi_zones_dict is live and returning boroughs through dictGet(). agg_hourly_zone_trips is still empty — by design, not by defect — and stays that way until module 05's cutover.

Dashboards, the ClickHouse-vs-Snowflake benchmark, and cutover are module 05, not this one.

The Snowflake producer is still running, and the gap between Snowflake and ClickHouse is still open. Nothing in this module touched the producer or the migration script — module 05 closes that gap deliberately, in the same controlled two-pass step module 03 previewed. Do not stop the producer now.