Gamebot

Gamebot is a data warehouse for the TV show Survivor, and the warehouse under the Survivor analyses on this site. It takes the community-maintained survivoR dataset, 77 seasons of it, through a scheduled and versioned pipeline on Airflow, dbt, and PostgreSQL. The pipeline ends in 8 feature tables and 2 matrices with one row per castaway and season, which is what a model trains on. The exported warehouse contains 250,936 rows across 32 tables. gamebot-lite is the read-only slice for analysts, published on PyPI, and the demo runs SQL over every table in your browser.

In a businessTeams need reliable tables before they can trust a dashboard or train a model. This project builds that foundation with scheduled ingestion, SQL transformations, data tests, and documented lineage, then ships the data as a Python package analysts can use.

Technical skills: SQL · Python / pandas · Airflow · dbt · PostgreSQL · DuckDB / Parquet · Docker

My contribution

I designed and built the pipeline: Airflow orchestration, dbt models for the silver and gold layers, the PostgreSQL schema with ingestion metadata, the Docker Compose deployment, the gamebot-lite package, and the browser demo over Parquet exports.

Result and limit

22 bronze tables, 8 silver feature tables and 2 gold matrices (100 and 127 columns) over 250,936 rows; the Who Goes Home and Pearl Islands analyses on this site read from it. The demo serves 4.5 MB of Parquet and runs SQL in the browser; typed questions go to a hosted model to generate the SQL.

pip install gamebot-lite

from gamebot_lite import load_table
votes = load_table("vote_dynamics")     # any bronze, silver or gold table as a pandas DataFrame

Why version this dataset?

The survivoR package is maintained and re-released; a season’s records change after air, and a feature computed from an old release is a feature nobody can reproduce. The bronze layer keeps each release with ingestion runs and dataset versions, so every downstream number has a provenance row. The silver layer does the feature engineering once, in dbt models that are tested and documented, rather than in each analysis notebook: Who Goes Home and Pearl Islands both read from it. The gold layer fixes the unit of analysis, castaway by season, and the two matrices differ only in whether the edit (confessional counts against expectation) is included, so a model can be fit with and without what the broadcast reveals.

The three layers

Bronze, 22 tables. survivoR as ingested: castaways, vote_history, challenge_results, jury_votes, confessionals, advantage_movement, tribe_mapping and the rest, with ingestion and dataset-version records. The total includes 3 metadata tables.

Silver, 8 tables. One per strategic category, each a dbt model over bronze. vote_dynamics has every vote cast, with whether it landed, whether the voter was alone, and how it aligned with the majority. challenge_performance has each castaway’s result in each challenge, by challenge type. advantage_strategy has the advantages found and played, for whom, and with what outcome. social_positioning describes the tribe around each castaway, episode by episode. jury_analysis sets each jury vote against the juror’s and the finalist’s original tribes. edit_features measures confessionals against the episode’s expectation. castaway_profile and season_context carry the demographics and the season’s format.

Gold, 2 tables. ml_features_non_edit has 100 columns from gameplay alone. ml_features_hybrid has 127 columns, gameplay plus the edit. Both are keyed by castaway and season, and the outcome columns (winner, finalist, jury, placement) sit beside the features.

The demo

Query the warehouse (opens in a new tab) serves every table of the packaged slice as Parquet (4.5 MB in all) and runs DuckDB in the browser, so a query reads only the files it names and executes locally. Typed questions go to a hosted model, which generates SQL for the browser to run. The lineage map is drawn from the dbt dependency graph; clicking a table shows its columns and the SQL that builds it. Four ready queries open the data. They show challenge and voting records by finish, jury votes to original tribemates by season, votes cast alone by season, and the edit by finish. The castaway card shows one row of the gold matrix, grouped by the silver table each feature came from, with the castaway’s percentile in that season’s cast.

Asking in words

The demo’s “Ask in words” box sends a question to a hosted model, DeepSeek-Flash or GLM-5.3-Flash at the visitor’s choice. The prompt is generated from the warehouse itself. It holds every table’s columns and types, the values of each low-cardinality column, the dbt definitions of the derived columns, and four worked examples. It also names the data’s traps, such as a case-sensitive LIKE, 84 boolean columns DuckDB will not sum, and season numbers that repeat across the five versions of the show. The model answers with one query, and with a one-sentence assumption when the question left a choice open. The query lands in the editor, and the visitor’s browser runs it. The model never runs anything. A query that DuckDB rejects gets one repair with the error before the editor is handed back.

The trivia page

Survivor trivia, from the records (opens in a new tab) is the same tables for a different reader. It asks fourteen questions a fan would ask. Among them are the most individual immunity wins in one season, the winners nobody ever voted against, the closest final tribal councils, and the castaways voted out first more than once. Each is answered in a sentence, with the table under it and the query one click away. A US-only switch keeps the answers to the seasons most fans know, and “Ask your own” uses the same model-written queries as the warehouse page. A castaway lookup gives a career card, with survivoR’s own season scores credited to Daniel Oehm, and two names side by side make a head-to-head.

Pages for fans

Two more pages read the same warehouse. The season, episode by episode (opens in a new tab) replays any US season one episode at a time. Each episode gets a card of facts computed from the records and set against every earlier season: who left and by what vote, idol plays and the votes they cancelled, an immunity run only a few castaways had matched before. For the season being written, a hosted model adds a two-sentence lede from those facts alone, and a script throws the lede away if it names a person or a number the facts do not. The page also shows boot odds from the Who Goes Home (opens in a new tab) model, refitted for each season on the seasons before it, with a scorecard of how often its top pick went home. It tracks each castaway’s share of confessionals against the band where eventual winners sat at the same episode. It draws a matrix of who votes together, and it names the three past players whose games looked most like each castaway’s at that point. The daily five (opens in a new tab) asks five questions a day, the same five for everyone. Each answer is what a SQL query returns, the query opens in the warehouse page, and a question stays out of the bank when its query could return a tie or its source row is known to be wrong. Every answer also has to agree with a second table. That check caught a bug in the first bank: counting a winner’s rows in the jury table gave the size of the jury, not the votes. One question a day is written by a language model, and it is kept only when its query and a second query reach the same answer and a second model’s review passes it. The morning refresh rebuilds both pages’ data beside the warehouse data and swaps it in with the rest.

How the question box is evaluated

Forty questions with reference queries were written and verified before the prompt existed. Ten are lookups, ten are joins, and ten are aggregates over the engineered tables. The last ten are underdetermined questions such as “who was robbed?” and “which season was the most unpredictable?”, where the reference is one defensible reading and the model is also scored on whether its assumption names the choice. The score is execution accuracy, the same rows back, order-insensitive unless the question asks for a ranking. It is taken over three runs per model, on the first draft and after one repair, with an ablation that removes the data notes. A run on the current prompt is still to come, so this page does not yet state the box’s accuracy.

Stack and credits

Apache Airflow 2.9, dbt 1.10, PostgreSQL 15, Redis, Docker Compose; Python and pandas for ingestion; pyarrow for the site export; DuckDB-WASM in the page; a Cloudflare Worker route for the question box. Data from the survivoR project by Daniel Oehm and contributors (MIT), whose license is served beside the exported tables.