04 Rebuild the dbt pipeline

Facilitator companion to the learner lesson 04 Rebuild the dbt pipeline.

Timing

About 30 minutes. The only stretch with real dead air is the second dbt run in Step 1 (roughly 8-12 minutes to process 50 million rows through the incremental models) — short enough that it usually doesn't need a break of its own, but long enough to narrate rather than watch in silence. Step 2 (the zone dictionary) and the verification queries are quick and interactive.

Talk track

• Frame this module as proving the pipeline, not the data: module 03 proved ClickHouse can hold 50 million rows; this module proves the same Medallion pipeline from module 01 — staging views, incremental fact table, dimension reloads, tests — runs unchanged in shape on ClickHouse.
• The one dbt mechanism worth pausing on: delete_insert replaces MERGE INTO (ClickHouse has no MERGE statement), and ReplacingMergeTree is the safety net underneath it, not a substitute for it — if delete_insert completes normally there is nothing for RMT to clean up; it only matters if a run is interrupted mid-way.
• mv_live_trip_feed is worth calling out by name: it has no Snowflake counterpart at all. A standard materialized view only ever sees the rows in the batch that triggered it; a REFRESHABLE materialized view re-runs its whole query on a schedule, so it can maintain a lifetime aggregate. This is new capability the migration adds, not a straight port.
• Say this before anyone asks: agg_hourly_zone_trips will be empty after Step 1's dbt run, and that is correct, not a bug. Its incremental filter is WHERE pickup_at >= now() - INTERVAL 2 HOUR, which only matches rows written by a live producer; every row just migrated is historical. It stays empty until module 05 starts the ClickHouse producer at cutover. Partners reliably assume this is a broken pipeline — get ahead of the question.

Common failures

• agg_hourly_zone_trips returns 0 rows after Step 1, and a partner reports it as a bug. It is not — see the talk track above. Confirm dim_taxi_zones (265 rows) and fact_trips (roughly 50 million rows) are populated as expected before spending any time on agg_hourly_zone_trips; if those two are correct, the empty aggregate table is working exactly as designed. This is the module's headline failure — expect the question every time.
• dbt run in Step 1 fails or cannot connect. This module depends on the ClickHouse dbt profile module 03 should already have created (~/.dbt/profiles.yml, nyc_taxi_ch). If that profile is missing — see Step 2's "Configure the dbt profile" in 03 Provision and migrate — Step 1 fails here instead, one module later than the root cause.
• TODO: the site's Troubleshooting page's ## dbt on ClickHouse section has no dedicated entry for a failure that surfaces only at this step. Capture anything rehearsal-specific here.

Reset steps

• Re-run dbt run freely — it is incremental and safe to repeat; nothing here needs a teardown.
• After a dbt model or schema change, force a full rebuild of the incremental models: dbt run --full-refresh (from workshop_public/snowflake_migration_lab/03-migrate-to-clickhouse/dbt/nyc_taxi_dbt_ch).
• If the zone dictionary looks wrong or stale, just re-run scripts/04_create_dictionary.sql — it is CREATE OR REPLACE DICTIONARY, safe to run again without dropping anything first.
• No environment-level reset applies here — this module's teardown is the same workshop_public/snowflake_migration_lab/03-migrate-to-clickhouse/teardown.sh covered under module 03; do not run it mid-module.