Overview

 Python Generator
 ─────────────────
  Arcade Score Events          Snowpipe Streaming SDK
  (unlimited rows/sec) ──────► StreamingIngestClient
  20 games · 45 cities          └─ Channel 0      ──► ARCADE_SCORES
  500 players                                          (Interactive Table)
                                                       CLUSTER BY (GAME_ENDED_AT)
                                                            │
                                                            ▼
                                                     SUMMIT_INT_WH
                                                  (Interactive Warehouse, XS)
                                                  Always-on · sub-second latency queries

Prerequisites

What You'll Learn

What You'll Need

What You'll Build

Open the Lab in a GitHub Codespace

Step 1 — Open the Codespace

aside positive The Codespace automatically installs the snowpipe-streaming Python package and all other dependencies from requirements.txt — no manual pip install needed.

Step 2 — Explore the repository structure

Summit26-InteractiveLab/
├── sql/
│   ├── 01_setup.sql          Snowflake object provisioning
│   ├── 02_service_auth.sh    RSA key generation + ALTER USER SQL
│   ├── 03_lab_queries.sql    All 11 exercises + bonus queries
│   ├── 04_generate_pat.sql   Optional: PAT for Cortex CLI dashboard deployment
│   └── 05_cleanup.sql        Teardown script
├── python/
│   ├── arcade_streamer.py    Snowpipe Streaming SDK ingest
│   ├── generator.py          Synthetic arcade data generator
│   └── config.py             All tuneable parameters
├── jmeter/                   Concurrency test plan (optional)
├── profile.json.example      Snowflake connection template
└── requirements.txt

Provision Snowflake Objects

What gets provisioned

Step 1 — Open Snowsight

Step 2 — Run the setup script

aside negative Important: Run the script with a Standard warehouse session (e.g. SUMMIT_TRAD_WH once created, or any existing standard warehouse). CREATE INTERACTIVE TABLE is not supported from an Interactive Warehouse session.

Setup complete – start the Python streamer, then wait ~2 min for cache warm-up.

What makes an Interactive Table different?

Register the RSA Key Pair

Step 1 — Run the keygen script

bash sql/02_service_auth.sh

-- Run as ACCOUNTADMIN in Snowsight
ALTER USER ARCADE_STREAMING_USER SET RSA_PUBLIC_KEY='MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ...';

Step 2 — Register the key in Snowsight

aside positive rsa_key.p8 is listed in .gitignore and will never be committed to the repository.

Configure the Connection Profile

Step 1 — Create profile.json

cp profile.json.example profile.json

Step 2 — Fill in your values

{
    "user":             "ARCADE_STREAMING_USER",
    "account":          "YOUR_ORG-YOUR_ACCOUNT",
    "url":              "https://YOUR_ORG-YOUR_ACCOUNT.snowflakecomputing.com:443",
    "private_key_file": "/workspaces/Summit26-InteractiveLab/rsa_key.p8",
    "role":             "ARCADE_STREAMING_ROLE"
}

aside positive Finding your account identifier

In Snowsight, click your username in the bottom-left corner. Your account identifier appears under the account name in ORG-ACCOUNT format (e.g. myorg-myaccount).

aside negative private_key_file must be an absolute path. The 02_service_auth.sh script prints the exact path for your Codespace environment.

How the Snowpipe Streaming SDK Works

1 — Create the client

# arcade_streamer.py  (main)
from snowflake.ingest.streaming import StreamingIngestClient

with StreamingIngestClient(
    client_name  = f"ARCADE_CLIENT_{uuid.uuid4().hex[:8].upper()}",
    db_name      = config.SNOWFLAKE_DATABASE,   # "ARCADE_DB"
    schema_name  = config.SNOWFLAKE_SCHEMA,     # "PUBLIC"
    pipe_name    = config.SNOWFLAKE_PIPE,        # "ARCADE_SCORES-STREAMING"
    profile_json = args.profile,                 # path to profile.json
) as client:
    ...

2 — Open a channel

# arcade_streamer.py  (channel_worker)
channel_name = f"ARCADE_CHANNEL_{channel_id}_{uuid.uuid4().hex[:8].upper()}"

with client.open_channel(channel_name)[0] as channel:
    print(f"  [channel-{channel_id}] opened: {channel.channel_name}")
    ...

3 — Append rows

# arcade_streamer.py  (channel_worker inner loop)
batch = generate_batch(rows_per_batch)   # list of row dicts from generator.py

for row in batch:
    channel.append_row(row, str(offset_token))
    offset_token += 1

4 — What a row looks like

# generator.py  (generate_score)
return {
    "score_id":         str(uuid.uuid4()),   # VARCHAR(36)
    "player_id":        player["player_id"], # VARCHAR(36)
    "player_name":      player["player_name"],
    "player_country":   player["player_country"],
    "player_city":      player["player_city"],
    "latitude":         player["latitude"],  # FLOAT
    "longitude":        player["longitude"],
    "game_name":        game_name,
    "game_mode":        game_mode,
    "platform":         platform,
    "score":            score,               # NUMBER(12,0)
    "level_reached":    level,
    "duration_seconds": duration_sec,
    "lives_remaining":  lives,
    "accuracy_pct":     accuracy,            # nullable FLOAT
    "achievement":      achievement,         # nullable VARCHAR(64)
    "game_ended_at":    game_ended_at,       # TIMESTAMP_NTZ (UTC datetime)
}

5 — Offset tokens

# offset_token increments with every row:
channel.append_row(row, str(offset_token))
offset_token += 1

6 — Latency monitor

# arcade_streamer.py  (latency_monitor)
statuses = client.get_channel_statuses(names)
for name, st in statuses.items():
    lag = st.server_avg_processing_latency   # datetime.timedelta | None
    if lag is not None:
        print(f"[latency] {name}: {lag.total_seconds() * 1000:.0f} ms avg")

aside positive Preview the data without connecting to Snowflake

Before starting the real streamer you can preview a few generated rows to confirm the generator is working:

cd python
python arcade_streamer.py --dry-run --rows 3

Start the Arcade Streamer

cd python
python arcade_streamer.py

============================================================
 Summit 2026 – Arcade Scores Snowpipe Streamer
============================================================
  Account   : YOUR_ORG-YOUR_ACCOUNT
  Database  : ARCADE_DB.PUBLIC
  Pipe      : ARCADE_SCORES-STREAMING
  Channels  : 1
  Target    : unlimited rows/sec
============================================================

  [14:22:05]  rows:      512  |  512.0 rows/sec  |  errors: 0  |  elapsed:    1s
  [14:22:05]  [latency] ARCADE_CHANNEL_0_A3F2B1C4: 540 ms avg
  [14:22:10]  rows:    1,024  |  512.0 rows/sec  |  errors: 0  |  elapsed:    6s
  [14:22:10]  [latency] ARCADE_CHANNEL_0_A3F2B1C4: 512 ms avg

Streamer options

# Throttle to 50 rows/sec (useful to observe fresh data trickle in)
python arcade_streamer.py --rate 50

# Scale to 4 parallel channels for higher throughput
python arcade_streamer.py --channels 4

# Insert exactly 10,000 rows then stop
python arcade_streamer.py --rows 10000

# Preview generated data without connecting to Snowflake
python arcade_streamer.py --dry-run --rows 5

Wait for cache warm-up

aside positive After the streamer starts, wait 2–3 minutes before running lab queries. The SUMMIT_INT_WH Interactive Warehouse needs this time to populate its local SSD cache. Queries run before warm-up will still succeed but will be slower.

Exercise 1 — Pipeline Throughput

1a — Total row count

SELECT COUNT(*) AS TOTAL_SCORES FROM ARCADE_SCORES;

1b — Rows per second (last 60 seconds)

SELECT
    COUNT(*)                   AS ROWS_LAST_60_SEC,
    ROUND(COUNT(*) / 60.0, 1) AS ROWS_PER_SECOND,
    COUNT(DISTINCT PLAYER_ID)  AS ACTIVE_PLAYERS
FROM ARCADE_SCORES
WHERE GAME_ENDED_AT >= DATEADD('second', -60, CURRENT_TIMESTAMP());

1c — Throughput by 10-second bucket (last 3 minutes)

SELECT
    DATEADD('second',
        FLOOR(DATEDIFF('second', '2000-01-01'::TIMESTAMP_NTZ, GAME_ENDED_AT) / 10) * 10,
        '2000-01-01'::TIMESTAMP_NTZ)   AS TIME_BUCKET,
    COUNT(*)                           AS SCORES
FROM ARCADE_SCORES
WHERE GAME_ENDED_AT >= DATEADD('minute', -3, CURRENT_TIMESTAMP())
GROUP BY TIME_BUCKET
ORDER BY TIME_BUCKET DESC
LIMIT 18;

Exercise 2 — Data Freshness

SELECT
    MAX(GAME_ENDED_AT)                                                    AS LATEST_GAME_ENDED,
    DATEDIFF(
        'millisecond',
        MAX(GAME_ENDED_AT),
        CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP())::TIMESTAMP_NTZ
    ) / 1000.0                                                            AS FRESHNESS_SEC
FROM ARCADE_SCORES
WHERE GAME_ENDED_AT >= DATEADD('minute', -5,
          CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP())::TIMESTAMP_NTZ);

aside positive CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP())::TIMESTAMP_NTZ normalises the session-local CURRENT_TIMESTAMP() to UTC so it compares correctly against the UTC TIMESTAMP_NTZ values in GAME_ENDED_AT.

Exercise 3 — Global Leaderboard

SELECT
    RANK() OVER (ORDER BY SCORE DESC)  AS RANK,
    PLAYER_NAME,
    PLAYER_COUNTRY,
    PLAYER_CITY,
    GAME_NAME,
    TO_CHAR(SCORE, '999,999,999')      AS SCORE,
    LEVEL_REACHED,
    PLATFORM,
    COALESCE(ACHIEVEMENT, '—')         AS ACHIEVEMENT,
    GAME_ENDED_AT
FROM ARCADE_SCORES
WHERE GAME_ENDED_AT >= DATEADD('hour', -24, CURRENT_TIMESTAMP())
ORDER BY SCORE DESC
LIMIT 20;

aside positive Try it without the WHERE clause. Remove the GAME_ENDED_AT filter and re-run. Snowsight will show the query hitting the 5-second Interactive Warehouse timeout — which is the expected, by-design behaviour that enforces efficient query patterns.

Exercise 4 — Per-Game Top 5

SELECT
    GAME_NAME,
    ROW_NUMBER() OVER (
        PARTITION BY GAME_NAME ORDER BY SCORE DESC
    )                                  AS POSITION,
    PLAYER_NAME,
    PLAYER_COUNTRY,
    TO_CHAR(SCORE, '999,999,999')      AS SCORE,
    LEVEL_REACHED,
    GAME_MODE
FROM ARCADE_SCORES
WHERE GAME_ENDED_AT >= DATEADD('hour', -1, CURRENT_TIMESTAMP())
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY GAME_NAME ORDER BY SCORE DESC
) <= 5
ORDER BY GAME_NAME, POSITION;

Exercises 5–9 — Deeper Analysis

Exercise 5 — Country Heat Map

SELECT
    PLAYER_COUNTRY,
    COUNT(*)                   AS GAMES_PLAYED,
    ROUND(AVG(SCORE))          AS AVG_SCORE,
    MAX(SCORE)                 AS HIGH_SCORE,
    COUNT(DISTINCT PLAYER_ID)  AS UNIQUE_PLAYERS
FROM ARCADE_SCORES
WHERE GAME_ENDED_AT >= DATEADD('hour', -1, CURRENT_TIMESTAMP())
GROUP BY PLAYER_COUNTRY
ORDER BY GAMES_PLAYED DESC
LIMIT 20;

Exercise 6 — Game Popularity

SELECT
    GAME_NAME,
    COUNT(*)                                        AS SESSIONS,
    COUNT(DISTINCT PLAYER_ID)                       AS UNIQUE_PLAYERS,
    TO_CHAR(ROUND(AVG(SCORE)), '999,999,999')       AS AVG_SCORE,
    TO_CHAR(MAX(SCORE),        '999,999,999')       AS HIGH_SCORE,
    ROUND(AVG(LEVEL_REACHED), 1)                    AS AVG_LEVEL,
    ROUND(AVG(DURATION_SECONDS) / 60.0, 1)          AS AVG_GAME_MIN
FROM ARCADE_SCORES
WHERE GAME_ENDED_AT >= DATEADD('hour', -1, CURRENT_TIMESTAMP())
GROUP BY GAME_NAME
ORDER BY SESSIONS DESC;

Exercise 7 — Platform Breakdown

SELECT
    PLATFORM,
    COUNT(*)                   AS SESSIONS,
    ROUND(AVG(SCORE))          AS AVG_SCORE,
    ROUND(AVG(ACCURACY_PCT), 1) AS AVG_ACCURACY_PCT,
    COUNT(DISTINCT PLAYER_ID)  AS UNIQUE_PLAYERS
FROM ARCADE_SCORES
WHERE GAME_ENDED_AT >= DATEADD('hour', -1, CURRENT_TIMESTAMP())
GROUP BY PLATFORM
ORDER BY SESSIONS DESC;

Exercise 8 — Live Score Feed (re-run repeatedly!)

SELECT
    PLAYER_NAME,
    PLAYER_CITY,
    PLAYER_COUNTRY,
    GAME_NAME,
    TO_CHAR(SCORE, '999,999,999')  AS SCORE,
    LEVEL_REACHED,
    PLATFORM,
    COALESCE(ACHIEVEMENT, '—')     AS ACHIEVEMENT,
    GAME_ENDED_AT
FROM ARCADE_SCORES
WHERE GAME_ENDED_AT >= DATEADD('minute', -5, CURRENT_TIMESTAMP())
ORDER BY GAME_ENDED_AT DESC
LIMIT 30;

Exercise 9 — Achievement Rarity

SELECT
    ACHIEVEMENT,
    COUNT(*)                   AS TIMES_EARNED,
    COUNT(DISTINCT PLAYER_ID)  AS UNIQUE_EARNERS,
    ROUND(AVG(SCORE))          AS AVG_SCORE_WHEN_EARNED,
    ROUND(AVG(LEVEL_REACHED), 1) AS AVG_LEVEL
FROM ARCADE_SCORES
WHERE ACHIEVEMENT IS NOT NULL
  AND GAME_ENDED_AT >= DATEADD('hour', -1, CURRENT_TIMESTAMP())
GROUP BY ACHIEVEMENT
ORDER BY TIMES_EARNED ASC;

Exercise 10 — Interactive vs Traditional Warehouse

Step A — Interactive Warehouse

USE WAREHOUSE SUMMIT_INT_WH;

SELECT
    PLAYER_COUNTRY,
    COUNT(*)    AS SESSIONS,
    MAX(SCORE)  AS HIGH_SCORE
FROM ARCADE_SCORES
WHERE GAME_ENDED_AT >= DATEADD('hour', -1, CURRENT_TIMESTAMP())
GROUP BY PLAYER_COUNTRY
ORDER BY SESSIONS DESC;

Step B — Standard Warehouse (identical query)

USE WAREHOUSE SUMMIT_TRAD_WH;

SELECT
    PLAYER_COUNTRY,
    COUNT(*)    AS SESSIONS,
    MAX(SCORE)  AS HIGH_SCORE
FROM ARCADE_SCORES
WHERE GAME_ENDED_AT >= DATEADD('hour', -1, CURRENT_TIMESTAMP())
GROUP BY PLAYER_COUNTRY
ORDER BY SESSIONS DESC;

Compare in Query History

aside positive Switch back to the Interactive Warehouse before continuing:

USE WAREHOUSE SUMMIT_INT_WH;

Bonus Exercises

Bonus A — Time Travel on an Interactive Table

-- Row count 5 minutes ago
SELECT COUNT(*) AS ROWS_5_MIN_AGO
FROM ARCADE_SCORES AT(OFFSET => -300);

-- How many rows were generated in the last 5 minutes?
SELECT COUNT(*) AS NEW_ROWS_LAST_5_MIN
FROM ARCADE_SCORES
WHERE GAME_ENDED_AT >= DATEADD('minute', -5, CURRENT_TIMESTAMP());

Bonus B — Rolling 1-Minute City Hotspot

SELECT
    PLAYER_CITY,
    PLAYER_COUNTRY,
    COUNT(*) AS GAMES_LAST_MINUTE
FROM ARCADE_SCORES
WHERE GAME_ENDED_AT >= DATEADD('minute', -1, CURRENT_TIMESTAMP())
GROUP BY PLAYER_CITY, PLAYER_COUNTRY
ORDER BY GAMES_LAST_MINUTE DESC
LIMIT 15;

Bonus C — Inspect Interactive Table Metadata

USE WAREHOUSE SUMMIT_TRAD_WH;
USE ROLE ACCOUNTADMIN;

SHOW INTERACTIVE TABLES IN SCHEMA ARCADE_DB.PUBLIC;

-- Clustering depth (lower is better-clustered for time-range queries)
SELECT SYSTEM$CLUSTERING_INFORMATION('ARCADE_DB.PUBLIC.ARCADE_SCORES', '(GAME_ENDED_AT)');

Bonus D — Find the Ghost Player

SELECT PLAYER_NAME, PLAYER_CITY, PLAYER_COUNTRY,
       COUNT(*) AS PERFECT_GAMES,
       MIN(SCORE) AS MIN_SCORE,
       MAX(SCORE) AS MAX_SCORE,
       COUNT(DISTINCT GAME_NAME) AS GAMES_PLAYED
FROM ARCADE_SCORES
WHERE ACHIEVEMENT = 'Summit 2026'
GROUP BY PLAYER_NAME, PLAYER_CITY, PLAYER_COUNTRY
ORDER BY PERFECT_GAMES DESC
LIMIT 5;

Optional: Streamlit Dashboard

aside positive The compute pool ARCADE_REPORTING_POOL (CPU_X64_XS) was provisioned by sql/01_setup.sql and is ready to use. No additional Snowflake setup is required.

Step 1 — Install the Snowflake CLI

pip install snowflake-cli
snow --version

Step 2 — Install the Cortex CLI

curl -LsS https://ai.snowflake.com/static/cc-scripts/install.sh | sh
cortex --version

Step 3 — Generate a PAT for the Cortex CLI

snow connection test

Step 4 — Run Cortex

cortex --dangerously-allow-all-tool-calls

Step 5 — Send the deployment prompt

Use skill `developing-with-streamlit` to create a real-time arcade scores dashboard with the following requirements:

**Data Source:**
- Table: `ARCADE_DB.PUBLIC.ARCADE_SCORES`
- Warehouse: `SUMMIT_INT_WH` (Interactive Warehouse)
- Connection: Use default connection with programmatic token
- Columns: All columns and tables are uppercase in Snowflake

**Deployment:**
- Deploy as Streamlit in Snowflake named `ARCADE_SCORES_DASHBOARD` in `ARCADE_DB.PUBLIC`
- Use compute pool `ARCADE_REPORTING_POOL` (already provisioned)
- Add external access integration and network rule for PyPI access

**Dashboard Features:**
1. Global Leaderboard - Highest scores in last 24 hours
2. Top Players by Game - Top 5 players per game in last hour
3. Geographic Heatmap - Country-level plays over last hour
4. Game Popularity - Session and player counts by game (last hour)
5. Platform Breakdown - Session and player counts by platform (last hour)
6. Recent Activity - Last 30 scores
7. Achievements - All earned achievements (where ACHIEVEMENT IS NOT NULL) with counts
8. Active Cities - Most active cities in last minute
9. Pipeline Health - Operational metrics page with:
   - Total games played (all time)
   - Current data freshness in decimal seconds: DATEDIFF('millisecond', MAX(GAME_ENDED_AT), CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP())::TIMESTAMP_NTZ) / 1000.0
   - Freshness over last hour as a time-series line chart bucketed by DATE_TRUNC('minute', GAME_ENDED_AT)
   - Rows/sec over last hour: count per minute bucket divided by 60, as a time-series line chart

aside positive The dashboard queries ARCADE_SCORES through SUMMIT_INT_WH — the same Interactive Warehouse used in the lab exercises. All nine pages will refresh with live data as long as the Python streamer is running.

Cleanup

aside negative The Interactive Warehouse (SUMMIT_INT_WH) bills a minimum of 1 hour from the time it is created, then per-second thereafter. Run cleanup as soon as you are finished with the lab to avoid unnecessary charges.

Conclusion And Resources

What You Learned

Related Resources