✉ Snowflake Summit 2026 — Say Hello from Summit!

Interactive booth activation where attendees write a physical postcard, enter the destination zip code into a terminal CLI, and watch Snowflake compute the journey distance via geospatial SQL — then chat with Cortex AI about the live event dataset.

⚠️ Requires a Standard/Enterprise Snowflake account — trial accounts will not work. The Cortex AI Q&A step calls SNOWFLAKE.CORTEX.COMPLETE, which is blocked on trial accounts (399258 (0A000): AI function COMPLETE is not available for trial accounts). The CLI auto-detects this and skips the Q&A so the postcard flow still runs, but you lose the headline AI demo.

---

Project Structure

snowflake-postcard/
├── init_db.sql           ← Run once in Snowflake to set up DB, table, views, POSTCARD_USER
├── cli_app.py            ← CLI app run on each booth laptop (this is the attendee experience)
├── tv_map.py             ← Streamlit real-time map for the TV display
├── requirements.txt
├── setup-laptop.sh       ← Per-laptop booth setup (one-time)
├── start-postcard.sh     ← Per-attendee launcher (what the `postcard` alias runs)
├── postcard_rsa.p8       ← Booth private key (NOT in repo — copy from 1Password before setup)
├── postcard_rsa.pub      ← Booth public key (also NOT in repo — body lives in init_db.sql)
├── .env.example          ← Template; setup-laptop.sh creates .env for you
└── README.md

---

Quick Start

1. One-time per Snowflake account (facilitator, before the event)

Install the ZIP-code Marketplace dataset and run the init script.

a) Install the ZIP-code dataset. In Snowsight: Data Products → Marketplace → search "U.S. ZIP Code Metadata with Geometry" by SFR Analytics → Get → override the database name to exactly FREE_ZIPCODES_DB. Grant access to SYSADMIN and POSTCARD_ROLE (the role created in step b).

b) Run init_db.sql as ACCOUNTADMIN. Creates the warehouse, database, table, views, the POSTCARD_USER booth user, and registers the booth's RSA public key on it.

snow sql -f init_db.sql

2. Per workshop laptop (one-time per machine)

git clone https://github.com/MLH/snowflake-postcard && cd snowflake-postcard

# Copy the booth private key into the repo root. It is NOT committed —
# pull it from the "Snowflake Postcard Booth Key" item in 1Password and
# save it as ./postcard_rsa.p8 before running the setup script.
./setup-laptop.sh

setup-laptop.sh does five things:

1. Adds a postcard connection to ~/.snowflake/config.toml (keypair auth pointing at POSTCARD_USER)
2. Creates .env with the absolute path to the booth private key
3. Creates a Python venv and installs requirements.txt
4. Adds a postcard shell alias to ~/.zshrc and ~/.bashrc so volunteers can just type postcard
5. Smoke-tests the connection

Open a new terminal (so the alias loads) and you're done.

3. Per attendee session

postcard          # aliased to start-postcard.sh — activates venv + runs cli_app.py

The attendee enters their destination zip, sees the journey distance, asks Cortex an AI question about the live data, and the next attendee picks up where they left off.

Big-screen TV display (one per booth, not per laptop)

source .venv/bin/activate
streamlit run tv_map.py

Auto-refreshes every 4 seconds. Shows KPI metrics, a great-circle arc map from SF to all destinations, state leaderboard, and recent entries.

---

Auth model

Every booth laptop authenticates as the same POSTCARD_USER via the same RSA private key. No password (no rotation drift, no policy rejections), no browser flow (no per-attendee OAuth popups), no MFA prompts.

Rotating the booth keypair

The private key is never committed. It is stored in 1Password (item: "Snowflake Postcard Booth Key") and copied into each laptop's checkout out-of-band. Only the public key body lives in the repo, embedded in init_db.sql. After each event, rotate:

# 1. Generate a fresh keypair (these files are git-ignored)
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out postcard_rsa.p8 -nocrypt
openssl rsa -in postcard_rsa.p8 -pubout -out postcard_rsa.pub

# 2. Copy the new public key body (between BEGIN/END PUBLIC KEY, no newlines)
#    into init_db.sql's ALTER USER ... SET RSA_PUBLIC_KEY = '...'

# 3. Push the new key to Snowflake (zero-downtime — Snowflake supports two
#    active public keys per user via RSA_PUBLIC_KEY + RSA_PUBLIC_KEY_2)
snow sql -f init_db.sql

# 4. Replace the postcard_rsa.p8 attachment in the "Snowflake Postcard Booth Key"
#    1Password item with the new file. Booth laptops re-pull from there.

# 5. Commit + push ONLY init_db.sql. The .p8 / .pub files are git-ignored.
git add init_db.sql && git commit -m "Rotate booth keypair"

If the old private key has been exposed (committed, screenshared, leaked), revoke it on Snowflake before laptops are migrated:

ALTER USER POSTCARD_USER UNSET RSA_PUBLIC_KEY;
ALTER USER POSTCARD_USER UNSET RSA_PUBLIC_KEY_2;

Then re-run init_db.sql to install the new key.

---

Snowflake Features Highlighted

Feature	Where Used
ST_MAKEPOINT	Build point geometry from lat/lon
ST_DISTANCE	Great-circle distance in metres → miles
ST_MAKELINE	Build LineString flight path
GEOGRAPHY column type	Stores the arc path in postcard_entries
SNOWFLAKE.CORTEX.COMPLETE	AI Q&A grounded in live data
Marketplace data	FREE_ZIPCODES_DB.PUBLIC.ZIP_CODE_META_SHARE (SFR Analytics)
MVCC	Safe concurrent INSERTs from all booth laptops

Cortex Model

The CLI uses mistral-large2 by default (widely available across regions). To change, edit cli_app.py → ask_cortex().

---

Troubleshooting

Database 'FREE_ZIPCODES_DB' does not exist or not authorized → Either the Marketplace listing isn't installed yet (step 1a), or it was installed under a different database name. Re-install and override the database name to exactly FREE_ZIPCODES_DB, and grant access to POSTCARD_ROLE.

JWT token is invalid when cli_app.py connects → The booth public key isn't registered on POSTCARD_USER. Re-run init_db.sql as ACCOUNTADMIN; check DESC USER POSTCARD_USER and confirm RSA_PUBLIC_KEY is set.

AI function COMPLETE is not available for trial accounts → Expected on trial accounts. The CLI auto-detects this and skips the Q&A step. To use Cortex, upgrade the Snowflake account.

Setup script fails with 'snow' CLI not found → Install with brew install snowflake-cli or pipx install snowflake-cli.

postcard command not found after running setup-laptop.sh → The alias was added to your rc file but the current shell didn't pick it up. Open a new terminal, or run source ~/.zshrc.