Lab · Snowflake (Codebase, optional)

Module 3 stretch — Snowflake CLI + Snowpark from local terminal

title: “Lab · Snowflake (Codebase, optional)” subtitle: “Module 3 stretch — Snowflake CLI + Snowpark from local terminal” —

ImportantCodebase paths vs main lab

The required lab is Exercise: Snowflake: A.1 (Snowsight SQL) and B.1 (Workspaces Notebook) — both run entirely inside Snowsight.

This optional page covers the codebase alternatives that run from a local terminal (Codespaces or your machine). These paths are common in production CI/CD environments.

Module 3 does not require this page. Try it if you have extra time after completing the main lab.

Module: 3 — Snowflake Pipeline · Required lab: Exercise: Snowflake · Setup: Snowflake

When to use each path

A.1 / B.1 — Snowsight ✅ A.2 / B.2 — Codebase
Where code runs Inside Snowflake (browser) Your machine / Codespaces
How you connect Git workspace + Snowflake session .env credentials
Setup needed Git sync only .env + pip install (or snow CLI pre-installed)
Best for Interactive development, workshop labs CI/CD pipelines, automation, terminal-first workflows
When in practice Module 3 labs, debugging, ad-hoc queries Production deployments, GitHub Actions, scheduled jobs
NoteWhy two compute models?
  • A.1 (Snowsight SQL) runs on a warehouse — same SQL engine as A.2.
  • B.1 (Workspaces Notebook) runs on Container Runtime (SPCS) via a compute pool — the notebook kernel executes inside Snowflake.
  • A.2 (Snowflake CLI) sends SQL to a warehouse via the snow CLI from your terminal.
  • B.2 (Snowpark Local) uses the Snowpark client library, which translates DataFrame operations to SQL and sends them to a warehouse. No compute pool needed.

Key insight: Snowsight paths (A.1, B.1) are managed by Snowflake — no credentials on your machine. Codebase paths (A.2, B.2) require .env with your Snowflake credentials — more setup, but full control for automation.

Prerequisites

  • Completed the main Snowflake lab (Bronze, Silver, Gold tables exist)
  • Role DE_WORKSHOP_ROLE, warehouse DE_WORKSHOP_WH Started

Configure .env

Both codebase paths need a local .env. Snowsight paths (A.1, B.1) do not — credentials are managed by Snowflake.

  1. Create .env from the template:

    cp .env.template .env
    copy .env.template .env
    cp .env.template .env

  2. Edit .env — in Codespaces: click .env in the Explorer sidebar (left) to open it in the built-in editor — fill in these values:

    Variable Value Where to find it
    ATTENDEE_ID e.g. de_01_alice Assigned by trainer — see My Workshop
    SNOWFLAKE_ACCOUNT e.g. el30551.west-europe.azure Snowsight URL: …/west-europe.azure/el30551/… → copy the two segments (no https://, no .snowflakecomputing.com)
    SNOWFLAKE_USER your Snowflake username Trial signup email
    SNOWFLAKE_PASSWORD your Snowflake password Trial signup

    Leave defaults for: SNOWFLAKE_WAREHOUSE=DE_WORKSHOP_WH, SNOWFLAKE_DATABASE=DE_MASTERCLASS, SNOWFLAKE_ROLE=DE_WORKSHOP_ROLE.

  3. Load into your terminal:

    sync-env     # regenerate ~/.snowflake/config.toml from .env (needed for the `snow` CLI in A.2)
verify-env   # confirm Snowflake connectivity before running A.2 / B.2
source .env  # also export the vars into the current shell (for Snowpark in B.2)
    source .env
    Get-Content .env | ForEach-Object { if ($_ -match '^([^#][^=]+)=(.*)$') { [System.Environment]::SetEnvironmentVariable($matches[1], $matches[2]) } }

You can skip Databricks variables (DATABRICKS_HOST, DATABRICKS_TOKEN, DATABRICKS_HTTP_PATH) — they are only needed for Module 4 (dbt).

TipVerify before you run (Codespaces)

After editing .env, run sync-env then verify-env — a healthy run ends with 🎉 Environment verified, confirming the snow CLI (A.2) and Snowpark (B.2) will connect. If Snowflake shows ❌, fix SNOWFLAKE_* in .env, then sync-env → verify-env again.

NoteHow .env powers each codebase path
  • A.2 (Snowflake CLI): setup-environment.sh reads .env and generates ~/.snowflake/config.toml — the CLI picks up connection details automatically.
  • B.2 (Snowpark): _snowpark_bootstrap.py reads .env directly to create a Snowpark session.

A.2 — Snowflake CLI

The devcontainer has the Snowflake CLI (snow) pre-installed. After configuring .env, run each .sql file from the terminal:

snow sql -f snowflake/sql/bronze/01_ingest_trips.sql -c default
snow sql -f snowflake/sql/silver/01_create_cleaned.sql -c default
snow sql -f snowflake/sql/silver/02_create_enriched.sql -c default
snow sql -f snowflake/sql/gold/01_create_kpis.sql -c default

Set attendee_id in the SQL file’s SET statement before running (replace de_XX_yourname with your actual ID), or pass it as a CLI variable:

snow sql -f snowflake/sql/bronze/01_ingest_trips.sql -c default -D "attendee_id=$ATTENDEE_ID"

B.2 — Snowpark (Local Terminal)

The Python process runs locally; DataFrame operations are pushed down to the Snowflake warehouse as SQL. Connection details come from snowflake/snowpark/_snowpark_bootstrap.py via .env environment variables (setup).

All packages are pre-installed from pyproject.toml. Skip to running the scripts:

source .env
python snowflake/snowpark/00_snowpark_setup.py   # optional — verify connectivity
python snowflake/snowpark/01_bronze_ingestion.py
python snowflake/snowpark/02_silver_cleaning.py
python snowflake/snowpark/03_gold_kpis.py

Install packages first, then run:

uv pip install --system .
# Load .env (see step 3 above)
python snowflake/snowpark/00_snowpark_setup.py   # optional — verify connectivity
python snowflake/snowpark/01_bronze_ingestion.py
python snowflake/snowpark/02_silver_cleaning.py
python snowflake/snowpark/03_gold_kpis.py
uv pip install --system .
source .env
python snowflake/snowpark/00_snowpark_setup.py   # optional — verify connectivity
python snowflake/snowpark/01_bronze_ingestion.py
python snowflake/snowpark/02_silver_cleaning.py
python snowflake/snowpark/03_gold_kpis.py
  1. Run 01_bronze_ingestion.py — look for [PASS] on all Bronze verification lines
  2. Run 02_silver_cleaning.py
  3. Run 03_gold_kpis.py — confirm kpi_top_pickup_zones shows 20 rows and all 12 kpi_* tables exist

Expected Results

Same as the main lab — Bronze 3,646,319 rows, Silver 3,146,710 rows, Gold KPIs identical.

Cleanup

At the end of the workshop, run the central cleanup script to drop all your schemas and suspend the warehouse in one step:

  • Workspaces SQL file: run snowflake/sql/setup/99_cleanup.sql
  • Workspaces notebook: open 00_setup/99_cleanup and click Run all

No compute pool cleanup needed — codebase paths (A.2, B.2) use the warehouse only, not SPCS.

Return to module