03 Provision and migrate

Starting point

Module 02 complete: migration-plan.md is filled in with every checkbox in its Completion Checklist checked, and the Snowflake producer is still running. This module's setup.sh checks for that file and warns if it is missing or incomplete, but it never blocks — nothing here stops you from continuing without it, only your own understanding of the next two modules does. Budget about 60 minutes total, of which roughly 40-50 minutes is an unattended data transfer you can leave running in the background. This is also where ClickHouse Cloud trial spend starts: provisioning the service and working through this module costs roughly $1-2 of trial credit (the whole lab runs about $2-4 in total).

Why

This module is where the plan becomes real. Every decision you wrote into migration-plan.md in module 02 — which MergeTree engine per table, the ORDER BY key derived from the actual query workload, how Snowflake-only constructs translate — gets typed directly into table DDL here, not re-derived from scratch. ClickHouse has no index you can bolt on after the fact: if an ORDER BY key turns out to be wrong once 50 million rows are sitting in a table, the fix is a full reload, not a quick ALTER.

That is also why the soft gate matters even though it cannot stop you. If you run this module without a completed plan, you will still succeed mechanically — dbt run will still create fact_trips as a ReplacingMergeTree, the migration script will still move 50 million rows — but you will not know why that engine and not plain MergeTree, why the sort key is shaped the way it is, or how to justify the ~6-9x benchmark speedups module 04 shows you later. The decision-alignment table below maps every choice this module implements back to the worksheet answer it comes from, so you can check your own plan against it before you provision anything.

Concepts — under the hood

Target architecture. Snowflake keeps writing new trips through the trip producer while a one-time Python script backfills 50 million existing rows into ClickHouse — the two systems run side by side for the length of the migration, not a cutover.

On the ClickHouse side, trips_raw is the landing table the script writes into. dbt then builds the staging views and the rest of the analytics layer on top of it — the schema this module creates, but does not yet populate beyond trips_raw:

Diagram color legend:

• Green — data sources (the trip producer, pre- and post-cutover)
• Blue — Snowflake tables
• Orange — dbt models and pipeline
• Red — ClickHouse tables and materialized views
• Cyan — Apache Superset dashboards
• Dashed arrows — post-cutover flows

Why a Python script rather than a native connector. Several methods exist for moving data from Snowflake to ClickHouse. This lab uses a Python batch script — here is why, compared with the alternatives:

Why the Python script is the right call for this lab:

• No AWS account required. S3-based approaches require bucket creation, IAM policies, and a Snowflake external stage — three setup steps that have nothing to do with ClickHouse.
• Self-contained. The two packages (snowflake-connector-python, clickhouse-connect) are installed into the same venv as dbt. No new services, no new credentials.
• Resumable. --resume makes the script safe to interrupt and restart. ReplacingMergeTree(_synced_at) ensures duplicate inserts on retry are automatically deduplicated.
• Transparent. Partners can read the script, understand the column mapping, and adapt it for their own schema — which is more educational than clicking through a UI wizard.

Handling the migration gap. The Snowflake producer keeps running while the migration script runs (~40-50 min). Any trips written to Snowflake during that window are not in ClickHouse. This lab closes that gap with a two-pass approach at cutover time, which module 05 walks through directly:

1. Stop the Snowflake producer to freeze the dataset.
2. Run python scripts/02_migrate_trips.py --resume — only the delta rows are transferred (seconds, not minutes).
3. Start the ClickHouse producer.

The same ReplacingMergeTree(_synced_at) dedup that handles migration retries also handles this: if any rows overlap between this module's run and the later --resume pass, the later _synced_at wins.

When you would choose S3 in production. If the dataset is > 500M rows, or if the Snowflake warehouse query cost of a full table scan is significant, the S3 export path is preferable: Snowflake exports compressed Parquet in parallel (much faster than a single cursor), and ClickHouse can load from S3 in parallel as well. The Python script approach here works well for lab scale.

Decision alignment. The table below is the same decision list from Worksheets 1 (engine selection), 2 (sort keys), and 3 (schema translation) in migration-plan.md, cross-checked against what this lab actually builds — compare it against your own plan before you provision anything:

Step 1 — Provision the ClickHouse cluster

setup.sh does one thing: run terraform apply and write the connection details to .clickhouse_state. It also re-checks for migration-plan.md before it provisions anything — see Why above — but that check only warns, it never blocks.

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

# Configure credentials
cp .env.example .env
vim .env
# Fill in: CLICKHOUSE_ORG_ID, CLICKHOUSE_TOKEN_KEY, CLICKHOUSE_TOKEN_SECRET, CLICKHOUSE_PASSWORD

# Provision
source .env && ./setup.sh

.env is gitignored — never commit it.

Expected output: Terraform creates 2 resources (service + IP access list) in approximately 2-3 minutes:

Apply complete! Resources: 2 added, 0 changed, 0 destroyed.

Outputs:
clickhouse_host = "abc123xyz.us-east-1.aws.clickhouse.cloud"
clickhouse_port = 8443

The host and port are saved to .clickhouse_state. Source it in any terminal to pick up the connection:

source .clickhouse_state

Verify:

curl "https://${CLICKHOUSE_HOST}:${CLICKHOUSE_PORT}/?query=SELECT+1" \
  --user "default:${CLICKHOUSE_PASSWORD}"
# Expected: 1

Step 2 — Create the empty tables

First, manually create trips_raw with the correct engine. The migration script loads data into this table in Step 3 — it must already exist with ReplacingMergeTree so the version column is in place before any rows arrive.

-- Run in the ClickHouse SQL console (cloud.clickhouse.com -> SQL console)
CREATE TABLE IF NOT EXISTS default.trips_raw (
    trip_id               String,
    vendor_id             UInt8,
    pickup_at             DateTime64(3, 'UTC'),
    dropoff_at            DateTime64(3, 'UTC'),
    passenger_count       UInt8,
    trip_distance_miles   Float32,
    pickup_location_id    UInt16,
    dropoff_location_id   UInt16,
    payment_type_id       UInt8,
    rate_code_id          UInt8,
    store_fwd_flag        String,
    fare_amount_usd       Float32,
    extra_amount_usd      Float32,
    mta_tax_usd           Float32,
    tip_amount_usd        Float32,
    tolls_amount_usd      Float32,
    total_amount_usd      Float32,
    ingested_at           DateTime64(3, 'UTC'),
    trip_metadata         String,
    _synced_at            DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree(_synced_at)
ORDER BY (pickup_at, trip_id);

_synced_at is set automatically on every INSERT. If the migration script is interrupted and re-run with --resume, duplicate rows for the same trip_id may briefly exist — RMT keeps the later row (higher _synced_at). stg_trips queries trips_raw FINAL to force deduplication before any downstream model sees the data.

Next, seed the zone reference data. This is static data (265 NYC TLC zones) that dbt's stg_taxi_zones reads as a source.

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

clickhouse-client --host "${CLICKHOUSE_HOST}" --port 9440 --secure \
  --user default --password "${CLICKHOUSE_PASSWORD}" \
  --multiquery < scripts/00_seed_zones.sql

(Or paste the contents of scripts/00_seed_zones.sql directly into the ClickHouse SQL console instead.)

Configure the dbt profile. This project's dbt_project.yml declares profile: 'nyc_taxi_ch'. Without a matching profile in ~/.dbt/profiles.yml, dbt run fails immediately with Could not find profile named 'nyc_taxi_ch'. The template is at workshop_public/snowflake_migration_lab/03-migrate-to-clickhouse/dbt/nyc_taxi_dbt_ch/profiles.yml.example.

Module 01 already wrote ~/.dbt/profiles.yml with a nyc_taxi: profile for Snowflake, and its Step 4 refresh loop keeps querying against that profile for as long as the Snowflake producer runs. Do not replace that file with the ClickHouse template — overwriting it with profiles.yml.example would delete the nyc_taxi: profile and break module 01's refresh loop. Instead, open the template and merge its nyc_taxi_ch: block into your existing ~/.dbt/profiles.yml as a second top-level profile, alongside nyc_taxi::

nyc_taxi:        # from module 01 — leave this one alone
  target: dev
  outputs:
    dev:
      type: snowflake
      # ...

nyc_taxi_ch:      # add this block
  target: dev
  outputs:
    dev:
      type: clickhouse
      schema: nyc_taxi_ch
      host: "{{ env_var('CLICKHOUSE_HOST') }}"
      port: 8443
      user: "{{ env_var('CLICKHOUSE_USER', 'default') }}"
      password: "{{ env_var('CLICKHOUSE_PASSWORD') }}"
      secure: true

nyc_taxi_ch: reads CLICKHOUSE_HOST, CLICKHOUSE_USER, and CLICKHOUSE_PASSWORD from the environment via env_var(), so .env and .clickhouse_state must be sourced before any dbt command in this module — the dbt run below already does this. Like the Snowflake profile, ~/.dbt/profiles.yml holds credentials and is gitignored; never commit it, and this merge adds a second set of credentials to a file that already held one.

Verify:

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

dbt debug
# Expected: "All checks passed!" — confirms dbt found the nyc_taxi_ch profile and
# connected to ClickHouse

Then run dbt run to create the analytics tables and staging views:

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

dbt deps   # install packages (first run only)
dbt run    # creates analytics tables and staging views; all empty at this point

Expected: ~8 models created in under 2 minutes (all tables empty).

Verify:

# Check analytics tables were created
curl "https://${CLICKHOUSE_HOST}:${CLICKHOUSE_PORT}/?query=SHOW+TABLES+IN+analytics" \
  --user "default:${CLICKHOUSE_PASSWORD}"
# Expected: agg_hourly_zone_trips, dim_date, dim_payment_type, dim_vendor, dim_taxi_zones, fact_trips

# Check staging views were created
curl "https://${CLICKHOUSE_HOST}:${CLICKHOUSE_PORT}/?query=SHOW+TABLES+IN+staging" \
  --user "default:${CLICKHOUSE_PASSWORD}"
# Expected: stg_trips, stg_taxi_zones

# Check trips_raw exists with the correct engine
curl "https://${CLICKHOUSE_HOST}:${CLICKHOUSE_PORT}/?query=SELECT+engine+FROM+system.tables+WHERE+database%3D%27default%27+AND+name%3D%27trips_raw%27" \
  --user "default:${CLICKHOUSE_PASSWORD}"
# Expected: ReplacingMergeTree

All six analytics tables and both staging views now exist, but every one of them is still empty — dbt run only created their schemas. The only table with data after this step is trips_raw, and it has none yet either; that comes next.

Step 3 — Migrate the data

Load all rows from Snowflake NYC_TAXI_DB.RAW.TRIPS_RAW into ClickHouse default.trips_raw using the Python batch migration script.

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

Expected output (approximately 40-50 minutes for 50M rows):

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  NYC Taxi Migration: Snowflake -> ClickHouse
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  Rows to migrate: 50,000,000
  Batch size:      100,000

  Rows inserted    Elapsed      ETA                    Rate
  -------------------- ------------ ---------------------- ---------------
  100,000              0m 07s       56m 14s remaining      13,945 rows/s
  200,000              0m 14s       55m 28s remaining      14,021 rows/s
  ...

The Snowflake producer keeps writing to TRIPS_RAW the entire time this script runs, so ClickHouse falls behind by roughly the length of this transfer — that gap is expected and is addressed in module 05, not here.

If the script is interrupted, re-run it with --resume to continue from the last checkpoint:

python scripts/02_migrate_trips.py --resume

--resume reads max(pickup_at) from ClickHouse and skips already-loaded rows, so it is always safe to interrupt this script and restart it — you will never end up with a partial, unrecoverable load.

How to verify you are done

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

# Row count in trips_raw
curl "https://${CLICKHOUSE_HOST}:${CLICKHOUSE_PORT}/?query=SELECT+count()+FROM+default.trips_raw" \
  --user "default:${CLICKHOUSE_PASSWORD}"
# Expected: approximately 50000000

# .clickhouse_state was written by setup.sh
ls -la .clickhouse_state

# Service is reachable
curl "https://${CLICKHOUSE_HOST}:${CLICKHOUSE_PORT}/?query=SELECT+1" \
  --user "default:${CLICKHOUSE_PASSWORD}"
# Expected: 1

End state

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 — seven objects in the analytics schema in total, built by this module's dbt run. Every analytics table is still empty except mv_live_trip_feed, which already holds the one snapshot row that dbt run produced when it built the view. Only default.trips_raw otherwise has data: roughly 50 million rows, moved by the Python migration script in Step 3.

The Snowflake producer is still running. It was never stopped in this module and does not stop here. Every trip written to Snowflake's TRIPS_RAW after the migration script's last batch is a row ClickHouse does not have, so ClickHouse is now behind Snowflake by roughly the length of the migration window (~40-50 minutes, plus whatever this module's setup took). That gap is real and keeps growing for as long as the producer keeps running. Do not close it in this module. Module 05's cutover closes it deliberately, in a controlled two-pass step that measures the size of the gap before eliminating it — stopping the producer or re-running the migration script now would remove the exact thing module 05 is built to demonstrate.