01 Source environment

Starting point

Module 00 complete: the toolchain is installed, both cloud trial accounts are live, the repo is cloned, and the dbt-snowflake virtualenv is built. This module takes about 45 minutes and spends roughly 2-4 Snowflake credits.

Why

You cannot plan a migration against a toy source. A single flat table with a handful of rows would let you skip every decision that makes a real migration hard. This module builds the shape of an actual customer deployment instead: a VARIANT column holding semi-structured JSON, a CDC stream, scheduled tasks, an incremental MERGE pipeline, and a BI layer reading on top of all of it. Every one of those becomes a specific migration decision in module 02 — this module exists so you have the real thing to point at when that decision comes up, instead of an abstraction.

Concepts — under the hood

Infrastructure (Terraform). Running setup.sh provisions:

• Warehouses — TRANSFORM_WH (SMALL, for ELT) and ANALYTICS_WH (MEDIUM, for BI), plus a resource monitor (ANALYTICS_WH_MONITOR) capped at 50 credits/month.
• Database — NYC_TAXI_DB, with three schemas: RAW, STAGING, ANALYTICS.
• Roles — TRANSFORMER_ROLE, ANALYST_ROLE, DBT_ROLE, LOADER_ROLE.

The Medallion shape. Data moves through three layers inside NYC_TAXI_DB:

• RAW — TRIPS_RAW (50M synthetic trip rows, including a TRIP_METADATA VARIANT column simulating app telemetry — this is the JSON migration challenge) plus dimension tables (DIM_TAXI_ZONES, DIM_PAYMENT_TYPE, DIM_VENDOR).
• STAGING — dbt views that clean types and flatten the VARIANT column.
• ANALYTICS — dbt tables and incremental models: fact_trips (50M rows, MERGE strategy), four dimension tables, and agg_hourly_zone_trips (an incremental aggregate).

Two Snowflake objects keep this pipeline moving on their own, independent of dbt:

• TRIPS_CDC_STREAM — a Change Data Capture stream on TRIPS_RAW.
• CDC_CONSUME_TASK — reads that stream every 5 minutes (runs in RAW, resumed during setup) — and HOURLY_AGG_TASK, which refreshes the hourly aggregate every hour (runs in STAGING, resumed after dbt builds).

Superset. All three dashboards read from the ANALYTICS schema through ANALYTICS_WH — none of them touch RAW or STAGING directly. That read path is what you're reproducing on the ClickHouse side later in the workshop.

Step 1 — Configure credentials

cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/01-setup-snowflake"

cp .env.example .env
# Edit .env with your Snowflake credentials

cp dbt/nyc_taxi_dbt/profiles.yml.example ~/.dbt/profiles.yml
# Edit ~/.dbt/profiles.yml with your account details

Both .env and ~/.dbt/profiles.yml are gitignored — they hold your Snowflake account, user, and password. Never commit either file.

Step 2 — Run setup

cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/01-setup-snowflake"
source .env && ./setup.sh

Expect this to take 5-10 minutes, most of it spent generating 50M rows of synthetic trip data with TABLE(GENERATOR). setup.sh provisions the Terraform infrastructure, seeds TRIPS_RAW, runs the dbt build, and brings up Docker Compose (the trip producer and Superset) in one pass.

Step 3 — Start the producer and Superset

setup.sh brings up Docker Compose with the correct environment, registers the Snowflake connection in Superset, and imports all three dashboards automatically.

The committed dashboard ZIPs in superset/dashboards/ have their sqlalchemy_uri redacted to placeholders (LAB_USER, MYORG-MYACCOUNT). The auto-import re-stamps the URI from your .env, so this is transparent when setup.sh runs. If you import a ZIP manually through the Superset UI instead, the connection it creates will use those placeholders and won't connect — edit the connection afterward to point at your real Snowflake account.

If you need to restart Superset manually:

cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/01-setup-snowflake/superset"
docker-compose --env-file ../.env up -d

The --env-file ../.env flag loads the environment variables from the parent directory.

The three dashboards are Operations Command Center, Executive Weekly Report, and Driver & Quality Analytics (deliberately slow — it's the ClickHouse benchmark target later in the workshop). For the full dashboard build — data sources, charts, filters — see Superset on Snowflake.

Step 4 — Keep dbt current

The trip producer continuously inserts about 60 trips/minute into TRIPS_RAW. To keep fact_trips and agg_hourly_zone_trips up to date while you work, run the dbt refresh loop in a separate terminal:

cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/01-setup-snowflake"

# Default: refresh every 5 minutes (auto-sources .env)
./scripts/run_dbt.sh

# Custom interval
./scripts/run_dbt.sh --interval 15m

# Run once and exit
./scripts/run_dbt.sh --once

# Include dbt tests after each run
./scripts/run_dbt.sh --test

The script always runs incrementally — it never does a --full-refresh, so producer-inserted rows are preserved. Press Ctrl-C at any time to stop it; leave it running in its own terminal for the rest of the lab — module 03 still relies on it.

Step 5 — Explore the query library

cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/01-setup-snowflake"

The query directory (workshop_public/snowflake_migration_lab/01-setup-snowflake/queries/) holds seven annotated SQL files. Each one runs against the Snowflake environment you just built, and each one carries a deliberate migration challenge that module 02 will translate to ClickHouse:

Open each file and run it against your Snowflake environment before moving on. The comment block in each query already sketches the ClickHouse equivalent — module 02 is where you write and run that side for real.

How to verify you are done

cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/01-setup-snowflake"
source .env && ./scripts/verify_environment.sh

This checks:

1. Database & schemas — NYC_TAXI_DB exists with RAW, STAGING, ANALYTICS.
2. Tables & data — TRIPS_RAW has ~50M rows, FACT_TRIPS is populated, dimensions exist.
3. CDC stream — TRIPS_CDC_STREAM exists on TRIPS_RAW.
4. Scheduled tasks — CDC_CONSUME_TASK and HOURLY_AGG_TASK are in started state.
5. CDC activity — the tasks have executed recently.
6. Producer feed — the trip producer is inserting data continuously.
7. Superset — the BI dashboards are reachable at http://localhost:8088.

If you'd rather check by hand, SHOW TASKS LIKE '%TASK' IN DATABASE NYC_TAXI_DB; against the ACCOUNTADMIN role (tasks are owned by that role) confirms both tasks are running.

Wrap-up

You'll come back to this environment repeatedly over the next few modules, and a full ./setup.sh run costs 5-10 minutes you don't want to pay every time you tweak a Terraform file or a dbt model. setup.sh takes flags for exactly that:

Flags combine. Two common combinations:

# Re-run after a Terraform or SQL change — skip the ~10 min data load
./setup.sh --skip-seed

# Iterate on dbt models only — skip everything else
./setup.sh --skip-seed --skip-superset

Cost note. Data seeding runs about 12 minutes for 2 credits ($6), and a full dbt build runs about 8 minutes for 1.5 credits ($5). An 8-hour partner lab session adds roughly 12 more credits (~$36) — warehouses auto-suspend when idle, so cost stops accruing between sessions. Total per partner per day is roughly 16 credits, about $47.

End state

Snowflake is live: NYC_TAXI_DB is fully built, the CDC stream and both scheduled tasks are running, the trip producer is writing about 60 trips/minute into TRIPS_RAW, and all three Superset dashboards are up at http://localhost:8088.

Leave the producer running. Do not stop the Docker Compose stack and do not run ./teardown.sh — modules 02 through 05 depend on this environment staying live, and the cutover step in module 05 measures the exact gap the producer creates between Snowflake and ClickHouse during migration. Tearing down now would make the rest of the workshop fail in a way that's hard to trace back to this step. Teardown is covered at the end of module 05, not here.