This is a submission for Weekend Challenge: Passion Edition
What I Built
Rivalry Engine — pick two national football teams, and Snowflake reads 150 years of their matches, scores how heated the rivalry is, calls a one-word verdict on its shape, predicts the next result, and lets Cortex narrate the one story inside it. All of it — the data, the analytics, the AI, and the app — runs inside Snowflake. Nothing ever leaves the warehouse.
A scoreboard tells you who won. It never tells you what the rivalry means. Argentina and Brazil have met over a hundred times across a century — a razor-thin ledger that has never let either side feel safe. Germany and England meet rarely, but every meeting carries a tournament's weight. Most national teams have never played each other at all. My goal was a product that could feel the difference between those three shapes — not another stats table, because a table is a report and I wanted an argument.
The passion is football. But the real engineering question underneath it was the one I actually cared about: can an AI tell the story of a rivalry without lying about the facts? So I gave myself one rule before writing a line of code:
The AI interprets the shape of a rivalry. It never invents the facts. Every count, date, score and streak on screen is computed in SQL from real matches. Cortex is handed only those computed facts, and told explicitly to never produce a number.
And its honest corollary: two nations that never met get a "first chapter unwritten" card — and Cortex is never called. A product that can't return nothing will invent something. This one returns nothing.
Demo
It runs entirely in Streamlit in Snowflake (Snowsight) — no public host, no API keys — so here's a walkthrough:
The 90-second tour:
- Argentina vs Brazil → the heat gauge pins to 🔥 Blood rivalry, recent-form chips light up, and the SQL detector's verdict reads Blood feud.
- ✨ Generate the story → Cortex writes a narrative that cites the real biggest thrashing and first-ever meeting — every number in it came from SQL.
- If they met tomorrow → a Snowpark Elo model gives a live Win / Draw / Loss forecast with each side's world rank.
- Ask the data: "Who has Brazil beaten most at World Cups?" → Cortex Analyst writes the SQL and answers.
- Two teams that never met → an honest grey "first chapter unwritten" card, and Cortex is skipped entirely. That empty state is the feature that makes me trust the other four.
Code
⚔️ Rivalry Engine
A scoreboard tells you who won. It never tells you what the rivalry means.
Pick two national teams. Snowflake reads 150 years of their matches with plain SQL, classifies the shape of the rivalry, predicts the next result, and lets Cortex narrate the one story inside it — the heat, the grudge, the moments. The data, the analytics, the AI, and the app all run inside Snowflake. Nothing leaves the warehouse.
Theme: Build Something Inspired by Passion · Target: Best Use of Snowflake · Design: mockup.html
The two rules that hold it together. Cortex reads the arc of a rivalry; it never invents the facts — every count, date, score and streak on screen is computed in SQL from real matches, and Cortex is handed only those computed moments. And two nations that never met get an honest "first chapter unwritten" card — Cortex is never…
The whole thing is ~six SQL scripts + one Streamlit file. The parts worth reading:
-
sql/05_detector.sql— the SQL detector: heat score, storyline verdict, and key moments as views. -
sql/06_elo.sql— the Snowpark Python stored procedure that computes Elo ratings in the warehouse. -
cortex_analyst/rivalry_semantic_model.yaml— the Cortex Analyst semantic model. -
streamlit_app.py— the Streamlit-in-Snowflake app (a thin renderer over views + Cortex calls). -
SETUP.md— full step-by-step deploy guide.
How I Built It
The theme of the build is one sentence: the warehouse is the historian, not a store the app reads from. Every time I was tempted to pull rows into Python and compute there, I pushed the logic down into SQL instead — and the app got thinner, the results got deterministic, and the "grounded AI" story got easier to defend.
1. The data lands in Snowflake and one view does the shaping. ~49,000 men's internationals since 1872 (the martj42 dataset) load into a MATCHES table. A MEETINGS view computes a winner column so "who has Brazil beaten" is one WHERE clause — no app-side logic, no home/away ambiguity.
CREATE OR REPLACE VIEW MEETINGS AS
SELECT match_date, home_team, away_team, home_score, away_score, tournament,
CASE WHEN home_score > away_score THEN home_team
WHEN away_score > home_score THEN away_team
ELSE NULL END AS winner -- NULL = draw
FROM MATCHES
WHERE home_score IS NOT NULL AND away_score IS NOT NULL;
2. A SQL detector scores the "heat" and calls the verdict — zero AI. A stack of plain views (PAIR_MEETINGS → RIVALRY_STATS) turns every pair into a deterministic 0–100 temperature from five real signals:
heat = 0.30·volume + 0.22·balance + 0.15·recency + 0.18·stakes + 0.15·drama
Then a priority-ordered CASE labels the rivalry with one word, every threshold pulled from a single DETECTOR_CONFIG view so tuning "what is a blood feud" is a one-line edit:
CASE
WHEN meetings < 5 THEN 'none' -- not a rivalry
WHEN years_since > 15 THEN 'dormant'
WHEN meetings >= 20 AND windiff_share <= 0.25
AND years_since <= 8 THEN 'blood_feud' -- lots, even, live
WHEN top_win_share >= 0.65 THEN 'domination' -- one-sided
WHEN highscore_share >= 0.40 THEN 'goal_fest'
ELSE 'simmering'
END AS storyline
3. Snowpark predicts the next match — in the warehouse. A Python stored procedure replays all 49k matches in order, updating an Elo rating for every team with a home-advantage bump and a margin-of-victory multiplier (a 5–0 moves ratings more than a 1–0). No external ML service; it writes a TEAM_RANKINGS view the app reads to render Win/Draw/Loss odds.
def run(session):
R = collections.defaultdict(lambda: 1500.0)
for m in session.sql("SELECT ... FROM MATCHES ORDER BY match_date").collect():
exp_h = 1.0 / (1.0 + 10 ** ((R[a] - (R[h] + adv)) / 400.0))
g = abs(hs - as_) # goal margin
mult = 1.0 if g <= 1 else (1.5 if g == 2 else 1.75 + (g - 3) / 8.0)
delta = K * mult * (sc_h - exp_h)
R[h] += delta; R[a] -= delta
4. Cortex writes the prose — and only the prose. The story is one SNOWFLAKE.CORTEX.COMPLETE call, handed the SQL-computed totals, the storyline verdict, and extracted moments (first meeting, biggest margin, highest-scoring game, longest streaks, current run), with the instruction to weave at least two in with their real dates and scores and never invent a number. When two teams have no history, the app stops before this call: no evidence, no story, no spend.
5. Cortex Analyst answers free-text questions. A semantic model over a per-team view, with synonyms and verified queries so "World Cup" resolves to the finals (FIFA World Cup) and not the qualifiers. Type a question → real SQL → a real answer (capped to a clean 10 rows).
6. Streamlit in Snowflake hosts the UI. Storage, analytics, AI, and front-end are one platform. No web host, no keys, no data leaving the account.
The one lesson I'd hand my past self: separate "facts" from "flourish" at the boundary. Numbers come from SQL, prose comes from Cortex, and they never cross. That single line is the difference between a demo and something you'd actually believe.
Prize Categories
Best Use of Snowflake. This project isn't a Snowflake-backed app — it's a Snowflake app. The platform does the thinking, not just the storing:
- Snowflake SQL computes the entire head-to-head record, the 0–100 heat score, and the storyline verdict as deterministic views — no analysis happens in the app.
- Snowpark (Python stored procedure) runs the Elo model in-warehouse to predict the next result.
-
Cortex
COMPLETEnarrates the rivalry, grounded strictly in SQL-computed facts, andSENTIMENTpowers a fan-mood meter. - Cortex Analyst turns plain-English questions into governed SQL over a semantic model.
- Streamlit in Snowflake hosts the whole experience natively — one platform, end to end.
Built solo for the Build Something Inspired by Passion weekend challenge.
Top comments (0)