Worksheet 3: Schema translation

Time estimate: 20–25 minutes Reference: Snowflake vs ClickHouse — Section 2 (SQL Dialect Gaps)

Concept

Type mapping and function translation are the most mechanical part of migration, but also the most error-prone if done carelessly. Snowflake and ClickHouse have different type systems with different semantics, and using the wrong type can cause silent precision loss, excessive storage, or broken query logic.

Key principles:

1. Be explicit about precision. Snowflake's TIMESTAMP_NTZ(9) has nanosecond precision. ClickHouse's DateTime has only second precision — do not use it for version columns. Use DateTime64(3, 'UTC') for millisecond precision (matching most real-world requirements) or DateTime64(9, 'UTC') for nanoseconds. This matters for correctness: if a ReplacingMergeTree version column has only second precision, two updates arriving within the same second are non-deterministic — ClickHouse cannot determine which is newer.

2. Use the smallest correct integer type. Snowflake's INTEGER is NUMBER(38, 0) — 38-digit fixed precision stored as a 128-bit value. ClickHouse has fixed-width integers: Int8, Int16, Int32, Int64, UInt8, UInt16, UInt32, UInt64. Choosing UInt8 for vendor_id (values 1–3) saves 7 bytes per row vs Int64. At 50M rows, that is 350MB.

3. VARIANT → String. ClickHouse has a native JSON type (available in v25.3+ as production-stable), but it is designed for truly dynamic schemas where the field names and structure are unknown at table creation time. For trip_metadata in this lab, the structure is known (driver.rating, app.surge_multiplier, etc.) — the better approach is to pre-flatten into typed columns during migration, or store as String and use JSONExtract* at query time. Use the JSON type when you genuinely cannot predict the schema: e.g., ingesting arbitrary customer event payloads where every event has different fields. ("Pre-flatten into typed columns" means extracting fields into separate top-level columns during ETL — the way FACT_TRIPS.driver_rating is produced from trip_metadata — not wrapping the JSON blob itself in a Tuple. A Tuple column still commits to one fixed set of fields, so it breaks the moment a trip's metadata doesn't match that shape.)

4. Float precision. Snowflake's FLOAT maps to Float64 in ClickHouse. For monetary amounts where exact decimal arithmetic is required, use Decimal(18, 2) — but for this lab, Float64 is sufficient to match the source. (This is a default, not a rule that every FLOAT column takes Float64 regardless of range: a column like driver_rating, whose values run 1.0–5.0 at one decimal place, fits comfortably in Float32's ~7 significant digits — the choice there turns on nullability, not on the precision Rule 4 is protecting for money.)

5. LowCardinality() — ClickHouse-only optimization. Wrapping a type in LowCardinality(String) (or LowCardinality(UInt8), etc.) tells ClickHouse to use a dictionary encoding for that column — values are stored as integer references to a dictionary rather than repeated strings. This typically gives 2–5x compression improvement and faster GROUP BY on string columns with fewer than ~10,000 distinct values. Snowflake has no equivalent; it handles this automatically. Good candidates in this lab: pickup_borough (6 values), payment_type (6 values), vehicle_type, vendor_name.

Exercise: type mapping for TRIPS_RAW

Map each column from NYC_TAXI_DB.RAW.TRIPS_RAW to its ClickHouse type. TRIP_ID is filled in as an example: String is idiomatic when migrating from VARCHAR(36) — it requires no casting, supports every string function, and avoids UUID parsing overhead on insert, even though ClickHouse also has a native UUID type.

Exercise: type mapping for FACT_TRIPS

FACT_TRIPS adds computed/derived columns that were added by the dbt pipeline. Most columns repeat a TRIPS_RAW decision; DRIVER_RATING and UPDATED_AT are new.

DRIVER_RATING is frequently NULL (no rating given). In ClickHouse, Nullable(Float64) has a slight performance overhead compared to a non-nullable column — a separate bitmask is stored alongside the data to track which rows are null. The choice for this column is between Nullable(Float32) (explicit null semantics) and a bare Float32 with a sentinel value like -1.0 (faster, less conventional). This lab uses Nullable(Float32) for correctness.

Exercise: function translation

Translate each Snowflake expression to its ClickHouse equivalent. These come directly from Q1–Q7 in 01-setup-snowflake/queries/. Three of the eight — QUALIFY, MERGE INTO, and the CDC stream read — don't have a one-line expression as their answer, so they're worked as questions below the table instead.

Reflection questions

Once the tables above are filled in, work through these. Three come from Exercise 3's translations that needed more than one line to answer; three are the source worksheet's "Non-Obvious Translation Decisions" — the reasoning behind the TRIP_METADATA, FARE_AMOUNT, and PICKUP_LOCATION_ID type choices above.

Loading worksheet...

Transfer to migration-plan.md

Copy your type decisions and any non-obvious translation notes to Section 5 of migration-plan.md and check off:

- [ ] Schema translation: completed