05 Benchmark and cutover

Starting point

Module 04 complete: the analytics layer is populated and tested. analytics.fact_trips holds roughly 50 million rows; analytics.dim_taxi_zones, analytics.dim_payment_type, analytics.dim_vendor, and analytics.dim_date are fully loaded; dbt test passes end to end; and analytics.taxi_zones_dict is live and returning boroughs through dictGet(). analytics.agg_hourly_zone_trips is still empty — by design, not by defect — and stays that way until this module's cutover. The Snowflake producer is still running, and the gap between Snowflake and ClickHouse is still open. Budget about 45 minutes.

Why

Every module up to this point has been preparation. Module 03 proved ClickHouse can hold 50 million rows; module 04 proved the dbt pipeline runs against it. Neither one, on its own, is something a partner would sign off on as "migrated" — that needs two more things: a number, and a cutover.

The number is the benchmark in Step 2: the same seven queries from your migration plan, run back-to-back against Snowflake and against ClickHouse, medianed over three runs each. That is what turns "ClickHouse should be faster" into a specific, defensible speedup a partner can put in front of their own stakeholders.

The cutover in Step 3 is the other half. Every module so far has kept the two systems running side by side, Snowflake as the system of record and ClickHouse catching up behind it. A migration that never actually moves the write path is a copy, not a migration. Step 3 stops the Snowflake producer, closes the gap its continuous writes have kept open since module 01, and starts writing new trips to ClickHouse instead — the moment ClickHouse becomes the system of record.

This is also the last learner module before the written assessment in module 06, which is open-book against whatever you produce here. The dashboards, the benchmark CSV, and the parity check all need to be real before you tear anything down in Step 5.

Concepts — under the hood

The BI layer, mechanically. bash superset/add_clickhouse_connection.sh calls the Superset REST API directly in Step 1 — there is no manual click-through in the Superset UI. It registers the ClickHouse connection, then imports the committed dashboard export, adding four ClickHouse dashboards alongside the three Snowflake dashboards module 01 built (seven total):

What the seven benchmark queries exercise. Step 2 runs the same seven queries from your migration plan against both engines and compares wall-clock time. Each one targets a specific dialect gap or engine feature from module 02's plan:

The cutover gap. The Snowflake producer has been writing roughly 60 trips a minute since module 01, and it has never stopped. Module 03's migration script captured TRIPS_RAW as it stood when that script ran, and module 04 built the dbt pipeline on top of that snapshot. Every trip written to Snowflake since the migration script's last batch exists only in Snowflake — ClickHouse's tail is missing. Step 3's --resume catch-up pass closes exactly that gap: it reads max(pickup_at) already in ClickHouse and pulls only the rows written after it, so a pass that closes a gap open since module 01 takes seconds to minutes, not the 40-50 minutes the original bulk migration took. Skip it and cut over anyway, and ClickHouse permanently drops however many trips landed in the gap — a silent parity failure that Step 4 is built to catch, but only if Step 3 ran in order.

agg_hourly_zone_trips fills here, and only here. It has been empty since module 03 by design: its incremental filter is WHERE pickup_at >= now() - INTERVAL 2 HOUR, which only matches rows written by a live producer, and until this module the only producer writing was Snowflake's. Once Step 3 starts the ClickHouse producer, new rows finally land inside that two-hour window, and the table — and every dashboard chart backed by it — stops being blank for the first time in the lab.

Step 1 — Add the ClickHouse dashboards

For the full manual build — creating each of the 7 datasets, 18 charts, and 4 dashboards step by step in the Superset UI — follow Superset on ClickHouse. To skip the manual steps and import everything in one shot instead:

cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/03-migrate-to-clickhouse"
source .env && source .clickhouse_state
bash superset/add_clickhouse_connection.sh

The committed superset/dashboards/dashboard_export_*.zip has its ClickHouse host redacted to your-instance.clickhouse.cloud. The shortcut above patches the URI from .env before importing, so this is transparent — you will not notice the redaction. But if you import the ZIP manually through the Superset UI instead, the database connection it creates will not connect — you have to edit that connection afterward to point at your real CLICKHOUSE_HOST and credentials. See Superset on ClickHouse for exactly how.

Verify:

Open http://localhost:8088 (admin / admin). Under Dashboards, you should see 7 total — 3 Snowflake dashboards and 4 prefixed CH —.

Step 2 — Run the benchmark

Run all seven queries back-to-back against both Snowflake and ClickHouse and compare wall-clock time:

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

Expected output:

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  NYC Taxi Lab — Query Benchmark: Snowflake vs ClickHouse
  (median of 3 runs each)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Query                                   Snowflake     ClickHouse    Speedup
────────────────────────────────────────────────────────────────────────
Q1  Hourly revenue by borough           5.0s          0.7s          6x
Q2  Rolling 7-day avg distance          5.5s          0.8s          6x
Q3  Top 10 trips (QUALIFY→subquery)     5.0s          0.7s          6x
Q4  Driver ratings (JSON flatten)       5.4s          0.8s          6x
Q5  Surge pricing (VARIANT)             5.1s          0.7s          6x
Q6  Hourly aggregation (MERGE→RMT)      5.9s          0.8s          7x
Q7  CDC/live data freshness             7.9s          0.8s          9x
────────────────────────────────────────────────────────────────────────
Total                                   40.1s         5.6s          7x avg
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

These figures are one representative run, not a guarantee — your own numbers will vary with warehouse size, ClickHouse Cloud tier, and whatever else is running against either service at the time.

The script writes every run to workshop_public/snowflake_migration_lab/03-migrate-to-clickhouse/scripts/benchmark_results_<timestamp>.csv. Keep it on disk — it is one of the two files module 06's assessment needs, so do not delete it during Step 5's teardown.

Step 3 — Cut over to ClickHouse

Migration gap. The Snowflake producer has been running throughout this lab, writing roughly 60 trips a minute. Module 03's migration script captured TRIPS_RAW as it stood when that script ran — rows written since then exist only in Snowflake. Close that gap before cutting over the write path.

Run all four steps below, in order — the order is what keeps the two systems consistent:

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

# Step 1: Stop the Snowflake producer (freeze the dataset)
docker stop nyc_taxi_producer

# Step 2: Catch up the delta — only migrates rows with pickup_at newer than
# what's already in ClickHouse. Runs in seconds to minutes, not the original
# 40-50 minutes, because only the gap rows move.
python scripts/02_migrate_trips.py --resume

# Step 3: Refresh the analytics tables with the newly migrated rows
cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/03-migrate-to-clickhouse/dbt/nyc_taxi_dbt_ch"
dbt run
cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/03-migrate-to-clickhouse"

# Step 4: Start the ClickHouse producer
source .env && source .clickhouse_state
./scripts/03_cutover.sh

Do not skip Step 2. --resume reads max(pickup_at) already in ClickHouse and adds a WHERE PICKUP_DATETIME > <watermark> filter to the Snowflake query, so it transfers only the rows written during and after module 03's original migration run — the ones ClickHouse has never seen. Cut over without it and ClickHouse permanently drops however many trips landed in that window. Step 4's parity check is built to catch exactly that, but only if this step ran first.

./scripts/03_cutover.sh prompts Type "cutover" to confirm, then repeats steps 1-3 as its own safety net: it stops the Snowflake producer again (a no-op if you already did), runs one more dbt run, then builds and starts the ClickHouse producer (nyc_taxi_ch_producer). Thirty seconds after the producer starts, it confirms new rows are landing in default.trips_raw and runs dbt once more — the run that finally gives agg_hourly_zone_trips its first rows, closing the gap module 04 left open by design.

Verify:

-- Most recent trip should be within the last 60 seconds
SELECT max(pickup_at) AS most_recent_trip FROM default.trips_raw;

-- Row count should be increasing — wait 60 seconds and run again
SELECT count() FROM default.trips_raw;

-- agg_hourly_zone_trips should now have rows for the first time in the lab
SELECT count() FROM analytics.agg_hourly_zone_trips;

docker ps | grep nyc_taxi_ch_producer   # should show running

Keeping the analytics layer fresh. fact_trips and agg_hourly_zone_trips are dbt incremental models — they do not auto-refresh. 03_cutover.sh runs dbt run once after confirming the producer is live, but dashboards drift stale as new trips accumulate; re-run dbt run from dbt/nyc_taxi_dbt_ch whenever you want current numbers (in production you would schedule this — cron, Airflow, dbt Cloud — but on-demand is fine for the lab). analytics.mv_live_trip_feed, by contrast, is a refreshable materialized view — module 04's dbt run already built it with engine = 'ReplacingMergeTree(refreshed_at)' — but the lab never switches its refresh interval on: the MODIFY REFRESH EVERY 30 SECOND statement that would make it re-execute on its own exists only as a comment in the model file. Enabling it is a one-statement ALTER TABLE you would run yourself; without it, mv_live_trip_feed only updates the one time dbt built it.

Reverse cutover, if you need to undo this step and go back to the Snowflake producer:

docker stop nyc_taxi_ch_producer
cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/01-setup-snowflake/superset"
docker-compose --env-file ../.env up -d producer

Step 4 — Verify parity

Now that the --resume catch-up pass has run and the ClickHouse producer is active, both systems should be at parity. Confirm it:

cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/03-migrate-to-clickhouse"
source .env && source .clickhouse_state
bash scripts/01_verify_migration.sh

Expected output:

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  Migration Parity Check
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  ✓ ClickHouse default.trips_raw: 50,008,250 rows

  ✓ Snowflake NYC_TAXI_DB.RAW.TRIPS_RAW: 50,008,250 rows
  ✓ Row count parity: PASS  (difference: 0 rows = 0.0000%)

  ✓ trip_metadata populated: 50,008,250 non-empty rows
  pickup_at range: 2022-03-30   2026-03-31

  ✓ ClickHouse has 50,008,250 rows — migration looks complete
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Your own row count will differ; what matters is the parity line. The Snowflake producer is stopped by this point, so no new rows are landing there — counts should match exactly, or within a handful of rows if a batch was still in flight during the --resume pass, well inside the 0.01% threshold the script checks against.

If the parity check fails (difference greater than 0.01%), the gap did not fully close — run the catch-up pass again and re-check:

cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/03-migrate-to-clickhouse"
source .env && source .clickhouse_state
python scripts/02_migrate_trips.py --resume
bash scripts/01_verify_migration.sh

Step 5 — Tear down

Before tearing anything down, confirm the migration is in a correct final state:

cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/03-migrate-to-clickhouse"
source .env && source .clickhouse_state
bash scripts/01_verify_migration.sh

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

Once all four pass, two files are everything module 06 needs, and both survive teardown: workshop_public/snowflake_migration_lab/02-plan-and-design/migration-plan.md and workshop_public/snowflake_migration_lab/03-migrate-to-clickhouse/scripts/benchmark_results_<timestamp>.csv. Module 06 is an open-book paper assessment, roughly 60 minutes, and needs nothing else — there is no reason to keep a paid ClickHouse Cloud service running for the length of a written exam. Copy or note the contents of both files somewhere you can reach them, then tear everything down:

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

This destroys the ClickHouse Cloud service (via terraform destroy) and the ClickHouse trip producer container, if cutover was performed.

Part 1's Snowflake resources are not torn down by this script. Tear down the Snowflake side separately:

cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/01-setup-snowflake"
source .env && ./teardown.sh

How to verify you are done

By this point in the module you should have confirmed, in order:

• Parity check passing — Step 4's 01_verify_migration.sh reported PASS with the row-count difference under 0.01%.
• Benchmark CSV on disk — Step 2 wrote benchmark_results_<timestamp>.csv with all 7 queries carrying a speedup value, and you kept it before Step 5's teardown.
• 7 dashboards present — Step 1's Superset check showed 3 Snowflake dashboards and 4 CH — dashboards side by side.
• ClickHouse producer writing — Step 3's verify block showed default.trips_raw gaining rows and nyc_taxi_ch_producer running, before Step 5 stopped it for teardown.

If any of those did not hold at the time, go back to the corresponding step rather than re-running these checks now — Step 5 has already destroyed the ClickHouse Cloud service and, if cutover happened, the producer container along with it.

End state

The migration is complete and measured: 50 million rows moved from Snowflake to ClickHouse and verified at parity, seven queries benchmarked head-to-head with ClickHouse faster on every one, the BI layer rebuilt with 4 ClickHouse dashboards alongside the original 3 Snowflake dashboards, and the write path cut over from Snowflake to ClickHouse for good. Both cloud environments are torn down — no ClickHouse Cloud service, no ClickHouse producer container, and, once Part 1's teardown has also run, no Snowflake warehouse either.

Two files survive teardown and are everything module 06 needs: workshop_public/snowflake_migration_lab/02-plan-and-design/migration-plan.md and workshop_public/snowflake_migration_lab/03-migrate-to-clickhouse/scripts/benchmark_results_<timestamp>.csv. Module 06 is an open-book written assessment — bring those two files and nothing else.