NYC Taxi Snowflake Migration Lab
Migrate a production-shaped Snowflake workload to ClickHouse Cloud — 50 million rows, a dbt Medallion pipeline, seven analytical queries, live producers, and BI dashboards — then defend your decisions.
Welcome to the NYC Taxi Snowflake Migration Lab playbook. This lab is for ClickHouse Solutions Architects and partners learning to migrate a production-grade Snowflake workload to ClickHouse Cloud: a synthetic NYC taxi ride stream with 50 million rows, a full Medallion dbt pipeline, seven complex analytical queries, live data producers, and Superset dashboards, built to simulate the kind of workload you will encounter in real customer engagements.
Why this workshop
The point of this lab is not moving rows from one warehouse to another — a copy script
can do that. The point is making, and defending, the decisions a real migration forces
on you: which MergeTree engine family fits each table, how to design an ORDER BY key
that earns its keep, and which Snowflake SQL idioms have no direct ClickHouse
equivalent. By the end you will be able to profile a Snowflake workload, make correct
engine and schema decisions, execute a production-style migration with a resumable
script, rebuild a dbt pipeline on ClickHouse, quantify the business case with a
seven-query benchmark, and explain and defend every one of those decisions.
This playbook is three-track. The Learner track is the lesson you work through. The Instructor track is the facilitator's companion for the same modules: timing, talk track, common failures, and reset steps. Reference is standing material — engine, dbt, and dashboard guides — meant to be read alongside any module, not in order.
Modules
| # | Learner | Instructor | Outcome |
|---|---|---|---|
| 00 | Setup | notes | Toolchain installed, both cloud trial accounts created, repo cloned, both dbt virtualenvs built — everything the migration needs before you touch data |
| 01 | Source environment | notes | A Snowflake environment that mirrors a real customer deployment: 50M rows, a dbt Medallion pipeline, a live trip producer, and three Superset dashboards |
| 02 | Plan and design | notes | The Snowflake workload profiled and the architecture decisions the migration will execute made explicit: engine selection, sort keys, schema translation, deployment waves, and dbt model design |
| 03 | Provision and migrate | notes | ClickHouse Cloud provisioned with Terraform, target tables created from your plan, and 50 million rows moved with a resumable Python migration script |
| 04 | Rebuild the dbt pipeline | notes | The Medallion pipeline rebuilt on ClickHouse with dbt-clickhouse — delete_insert incremental models, ReplacingMergeTree, refreshable materialized views — and the zone dictionary created |
| 05 | Benchmark and cutover | notes | Dashboards rebuilt on ClickHouse, all seven queries benchmarked against both engines, the producer cut over, parity verified, and both clouds torn down |
| 06 | Evaluation | notes | The 20-question multiple-choice and 4-question open assessment completed, open-book, to earn the ClickHouse Migration Proficiency Badge |
What this lab does not cover
This lab is scoped to the core migration pattern. The following topics are intentionally out of scope:
- Real-time streaming ingestion — the lab uses a Docker-based trip producer for live writes post-cutover, not Kafka, Kinesis, or ClickPipes streaming sources.
- Multi-node ClickHouse clusters — all work is on a ClickHouse Cloud single-service deployment; self-hosted distributed deployments (sharding, replication topology) are not covered.
- Data governance and access control — role-based access, row-level security, and masking policies are not implemented.
- Incremental schema evolution — the lab uses a fixed schema throughout; handling live schema changes during migration is not covered.
- Non-Snowflake sources — the migration pattern is Snowflake-specific. PostgreSQL, MySQL, BigQuery, and other sources have different SQL dialects and CDC approaches.
- Production SLA and monitoring — observability, alerting, and SLA management in production ClickHouse Cloud are not covered.
Time and cost
| Module | Wall-clock time | Snowflake credits | ClickHouse Cloud |
|---|---|---|---|
| 00 — Setup | ~30 min | — | — |
| 01 — Source environment | ~45 min | ~2–4 credits | — |
| 02 — Plan and design | ~90 min | ~0.5 credits | — |
| 03 — Provision and migrate | ~60 min | ~1–2 credits | ~$1–2 (trial) |
| 04 — Rebuild the dbt pipeline | ~30 min | ~0.5–1 credits | ~$0.5–1 (trial) |
| 05 — Benchmark and cutover | ~45 min | ~1–2 credits | ~$0.5–1 (trial) |
| 06 — Evaluation | ~60 min | — | — |
| Total | ~6 hours | ~6–10 credits | ~$2–4 |
Snowflake credit estimates assume a standard X-Small warehouse. ClickHouse Cloud cost assumes a Development tier service torn down within a few hours.