Worksheet 4: Migration wave plan

Time estimate: 15–20 minutes Reference: Module 03 — Provision and migrate → Module 04 — Rebuild the dbt pipeline → Module 05 — Benchmark and cutover for execution order

Concept

Database objects have dependencies. A view that queries fact_trips cannot be created before fact_trips exists. A materialized view that reads from trips_raw cannot be populated before trips_raw has data. Migrating in the wrong order causes create-table failures, empty results, or incomplete data that is hard to diagnose.

The solution: wave planning. Organize every object into a numbered wave where each wave contains only objects whose dependencies are satisfied by prior waves.

Complexity grading helps prioritize migration effort. The line between B and C is whether a statement has to be restructured or only re-typed:

• Grade A — trivial: standard table or passthrough view, no special logic, straightforward type mapping
• Grade B — medium: one ClickHouse-specific construct to learn and test, but the translation itself is a substitution — a ClickHouse engine plus incremental config, a CREATE DICTIONARY, a refreshable MV's REFRESH EVERY, or a JSONExtract* path standing in for a VARIANT read
• Grade C — complex: a statement has to be rebuilt, not just re-typed (QUALIFY wrapped in a subquery, MERGE INTO replaced by an incremental strategy), and that rewrite has to stay consistent with the engine choice and with FINAL placement in everything downstream; test carefully
• Grade D — requires redesign: Snowflake-specific feature with no direct equivalent (Streams → producer cutover to ClickHouse, Tasks → refreshable MVs or scheduled dbt runs)

Exercise 3 below asks you to grade on a compressed three-tier scale instead of the four letters above — Low (Grade A), Medium (Grade B), High (Grade C or D) — since the distinction that matters most for sequencing effort is trivial / needs testing / needs redesign, not the four-way split. Each answer's explanation names which of A–D applies, so you can still map back to the letter grade.

Exercise 1: Dependency DAG

For each object below, pick what it depends on. Three of the ten are static reference data with no dependency at all — that is a real answer, not a gap to fill in later.

Exercise 2: Migration Wave Assignment

Waves 0 and 1 are filled in as context. For waves 2 through 4, pick which objects belong in that wave, then answer what actually happens in it.

Exercise 3: Complexity Grading

Grade each object, then answer its primary-challenge question below the table.

Reflection questions

The last two questions cover the three entries in the answer key's risk register — verifying fact_trips (Grade C) and agg_hourly_zone_trips after they land, and verifying the producer cutover that replaces TRIPS_CDC_STREAM and CDC_CONSUME_TASK (Grade D). The register is not simply the Grade C/D set: agg_hourly_zone_trips grades Medium, but its rolling recalculation window is the easiest thing in this pipeline to get subtly wrong.

Loading worksheet...

Transfer to migration-plan.md

Copy your wave plan and complexity grades to Section 6 of migration-plan.md and check off:

- [ ] Migration wave plan: completed