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, QUALIFY, LATERAL FLATTEN, and MERGE INTO have no direct ClickHouse equivalent and need a translated form. 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 fill-in exercises for the actual NYC Taxi workload. Answer keys sit in collapsible sections at the bottom of each — try the exercises first.

These worksheets live in the repo and you edit them in place — there is nothing to create:

• workshop_public/snowflake_migration_lab/02-plan-and-design/worksheets/01_mergetree_engine_selection.md — engine family, engine choice and reasoning per table
• workshop_public/snowflake_migration_lab/02-plan-and-design/worksheets/02_sort_key_design.md — ORDER BY from the query workload, a sort key per table
• workshop_public/snowflake_migration_lab/02-plan-and-design/worksheets/03_schema_translation.md — type mapping and function translation, a type for each column and a ClickHouse equivalent for each Snowflake expression
• workshop_public/snowflake_migration_lab/02-plan-and-design/worksheets/04_migration_wave_plan.md — dependency ordering, a wave table mapping object to wave to dependencies
• workshop_public/snowflake_migration_lab/02-plan-and-design/worksheets/05_dbt_model_design.md — dbt model configuration: materialization, engine, incremental strategy, and FINAL placement per model

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 reads this checklist. Every checkbox must be checked — that's the completion criterion the next module depends on, not a suggestion.

How to verify you are done

You're done when all of the following hold:

• All five worksheets under workshop_public/snowflake_migration_lab/02-plan-and-design/worksheets/ are complete.
• 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.