02 Plan and design

Starting point

Module 01 complete: Snowflake is fully built, the CDC stream and both scheduled tasks are running, and — critically — the trip producer is still writing about 60 trips/minute into TRIPS_RAW. Leave it running; this module only reads from Snowflake. Budget about 90 minutes and roughly 0.5 Snowflake credits.

Why

The most common reason ClickHouse migrations underperform is not a tuning problem — it is an architecture problem. Teams move data first and think about design later. By the time they realize the wrong MergeTree engine silently produces incorrect results, or that a sort key copied from the source schema ignores actual query patterns, the migration is already "done." This module forces the opposite order: profile what you actually have, then make every engine, sort-key, type-mapping, and sequencing decision explicit — in writing — before module 03 executes any of it.

This is also the module partners are most tempted to skip. Module 03's setup.sh checks for migration-plan.md and warns if it is missing or incomplete, but it never blocks — you can charge ahead without it. If you do, you will run module 03 executing decisions you never made: you'll see fact_trips come up as a ReplacingMergeTree without knowing why that engine and not plain MergeTree, see an ORDER BY key without knowing how it was derived from the query workload, see delete_insert and FINAL in the dbt configs without knowing how to derive them for a different workload, and see benchmark speedups in module 04 you cannot explain or reproduce for a customer. The 90 minutes here is what turns the rest of the workshop from copying commands into understanding a migration.

Concepts — under the hood

Every decision this module produces falls into one of five categories, each with a worksheet:

• Engine family — which MergeTree variant fits each table's write pattern: plain MergeTree for append-only, ReplacingMergeTree for tables that receive updates via CDC, AggregatingMergeTree for pre-aggregated rollups. See MergeTree engines.
• ORDER BY key — ClickHouse has no indexes to add after the fact; the sort key is chosen once, from the actual query workload, not from the source table's primary key.
• Type mapping and dialect gaps — Snowflake's VARIANT, LATERAL FLATTEN, and MERGE INTO have no direct ClickHouse equivalent and need a translated form. QUALIFY is the exception: ClickHouse has had a native QUALIFY clause since v24.5, but this lab still teaches the subquery rewrite because it is portable to ClickHouse versions and SQL engines that predate or lack QUALIFY. See Snowflake vs ClickHouse.
• dbt model design — materialization, engine config, incremental strategy, and FINAL placement per model. See dbt on ClickHouse.
• Wave ordering — which objects can move first because nothing downstream depends on them yet, and which must wait.

Step 1 — Profile the Snowflake environment

cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/02-plan-and-design"
source ../01-setup-snowflake/.env
./scripts/01_profile_snowflake.sh

This runs against your live module 01 Snowflake instance and writes profile_report.md with four sections: an object inventory (every table, view, stream, and task with row counts and a complexity grade), the top 10 queries by total elapsed time over the last 7 days, table statistics (row counts, date ranges, null rates, VARIANT usage), and auto-detected schema-compatibility gaps.

profile_report.md is gitignored — it's generated fresh from your own Snowflake account each run, so it's machine-specific and never committed. Don't expect to find it in a fresh clone, and don't try to commit it yourself.

If ACCOUNT_USAGE isn't available yet (it needs a 1-3 hour propagation delay or the ACCOUNTADMIN role), the script falls back to INFORMATION_SCHEMA and notes what it couldn't measure. You can also run scripts/02_query_history.sql by hand in the Snowflake UI.

Step 2 — Work the five worksheets

Work through the five worksheets in order. Each teaches a concept, then has multiple-choice exercises for the actual NYC Taxi workload. Every answer is checked as soon as you choose it, and each worksheet has a "Copy as markdown" button that gives you the filled-in table to paste into your migration plan.

• Worksheet 1: MergeTree engine selection — engine family and engine choice per table
• Worksheet 2: Sort key design — ORDER BY derived from the query workload
• Worksheet 3: Schema translation — type mapping and function translation
• Worksheet 4: Migration wave plan — dependency ordering and wave assignment
• Worksheet 5: dbt model design — materialization, engine, incremental strategy and FINAL placement

Your answers are saved in your browser's local storage, not in the repo — they do not follow you to another machine and do not survive clearing site data. If you switch laptops partway through the workshop, you will need to redo the worksheets there.

Step 3 — Fill in the migration plan

Open workshop_public/snowflake_migration_lab/02-plan-and-design/migration-plan.md and fill in each section using your worksheet answers. The document has ten sections and a Completion Checklist with five checkboxes at the top:

- [ ] Engine selection: completed
- [ ] Sort key design: completed
- [ ] Schema translation: completed
- [ ] Migration wave plan: completed
- [ ] dbt model design: completed

Module 03's setup.sh checks this checklist and warns if it is incomplete, but it does not block you from proceeding. Finishing it anyway is what makes module 03's decisions make sense instead of feeling arbitrary.

How to verify you are done

You're done when all of the following hold:

• All five worksheets show full marks — each one's score line at the bottom reads N/N correct.
• workshop_public/snowflake_migration_lab/02-plan-and-design/migration-plan.md has every checkbox in its Completion Checklist checked.
• profile_report.md exists on disk from Step 1 (gitignored, so it won't show up in git status).

Once your own plan is written, compare it against Worked example: a completed plan — a fully filled-in plan for this same workload. Use it to sanity-check your reasoning and to understand any place you chose differently, not as a template to fill in before you've thought it through yourself.

End state

A filled-in migration-plan.md on disk, every checkbox checked, backed by five completed worksheets. The Snowflake producer is still running — module 03 migrates data out of a live, moving source, and module 05's cutover measures the exact gap the producer creates between Snowflake and ClickHouse during migration. Do not stop it now.