03 Provision and migrate

Facilitator companion to the learner lesson 03 Provision and migrate.

Timing

About 60 minutes total, and the shape matters more than the total: roughly 10-15 minutes of hands-on work (Step 1 provisions the ClickHouse Cloud service via Terraform, about 2-3 minutes; Step 2 creates trips_raw, seeds the zone reference data, and runs the first empty dbt run, a few more minutes) followed by 40-50 minutes of unattended data transfer (Step 3's migration script, moving 50 million rows at roughly 20K rows/s).

This is the single most important scheduling fact in the whole workshop: once Step 3 starts, there is nothing for the room to do for the better part of an hour. Start it, then take the break here, or use the wait for the module 02 talk-track material the room didn't have time for, or a live Q&A on partners' migration-plan.md decisions. Do not schedule a break anywhere else in this module — schedule it here, on purpose.

Talk track

• The module's own rationale for why this lab moves data with a Python script rather than a native connector: Snowflake is not a supported ClickPipes source (Kafka, S3, Kinesis, and Postgres/MySQL CDC are; Snowflake is not), and every alternative (S3 export, Snowflake -> Kafka -> ClickHouse) trades a lab-scale setup for infrastructure that has nothing to do with the migration itself — an S3 bucket, an IAM role, a Kafka cluster.
• The Python script's actual selling points, worth naming explicitly: no AWS account required, self-contained (both new packages live in the same venv as dbt), resumable via --resume against a max(pickup_at) watermark, and transparent enough that a partner can read the column mapping instead of clicking through a UI wizard.
• Name the production alternative honestly: past roughly 500M rows, or where a full-table-scan warehouse cost matters, S3 export is the better call — parallel export, parallel load. The Python script is right for lab scale, not a universal recommendation.
• Preview the migration gap now, even though module 05 closes it: the Snowflake producer keeps writing the whole time Step 3 runs, so ClickHouse falls behind Snowflake by roughly the length of the transfer. That gap is expected here, and it is exactly what module 05's cutover is built to close and measure — say this before someone asks whether the 40-50 minute wait is "wasting" data.

Common failures

• The first dbt run in Step 2 fails with Could not find profile named 'nyc_taxi_ch'. Nothing in the lab creates the ClickHouse nyc_taxi_ch dbt profile automatically, even though dbt_project.yml requires it. The learner lesson's 03 Provision and migrate covers this: Step 2's "Configure the dbt profile" walks partners through merging a nyc_taxi_ch: block into their existing ~/.dbt/profiles.yml before this dbt run runs. If a partner skipped or mis-copied that step, point them back at it rather than re-explaining the workaround here.
• Terraform auth fails with 401 Unauthorized. Verify CLICKHOUSE_TOKEN_KEY and CLICKHOUSE_TOKEN_SECRET — both live in the ClickHouse Cloud UI under Settings -> API keys and must carry Admin scope.
• The migration script fails mid-run. Re-run with --resume; it watermarks on max(pickup_at) already in ClickHouse and skips rows already loaded, so restarting never produces a partial, unrecoverable load.
• The migration script fails to connect at all. Confirm every Snowflake and ClickHouse environment variable is set (echo $SNOWFLAKE_ORG $SNOWFLAKE_ACCOUNT $SNOWFLAKE_USER $SNOWFLAKE_PASSWORD and echo $CLICKHOUSE_HOST $CLICKHOUSE_PASSWORD), then source .env && source .clickhouse_state and retry.
• dbt run fails with Connection refused or Unknown host. CLICKHOUSE_HOST is not set in the current shell — source .clickhouse_state from workshop_public/snowflake_migration_lab/03-migrate-to-clickhouse/ and retry.

Reset steps

• Interrupted migration script: python scripts/02_migrate_trips.py --resume continues from the watermark rather than restarting the full 50M-row transfer.
• ClickHouse Cloud service needs a clean rebuild: source .env && ./teardown.sh from workshop_public/snowflake_migration_lab/03-migrate-to-clickhouse/ (destroys the service and the ClickHouse producer container if cutover already happened), then ./setup.sh again. This does not touch module 01's Snowflake side — that has its own teardown.sh in workshop_public/snowflake_migration_lab/01-setup-snowflake/.
• Full reset is expensive here: a fresh migration re-pays the entire 40-50 minute transfer. Prefer --resume or a targeted fix over a full teardown whenever the ClickHouse service itself is still healthy.