Gamebot

Art by Dabu Doodles (Erik Reichenbach)

What is a Gamebot in the Game of Survivor?

Survivor Term Glossary (search for Gamebot)

What is a Gamebot in Survivor? Thread

What is a Gamebot Outside of the Game? This Repository!:

Gamebot is a production-ready Survivor analytics stack that implements a complete medallion lakehouse architecture using Apache Airflow + dbt + PostgreSQL. It primarily ingests the comprehensive survivoR dataset, with plans to integrate Survivor data (e.g. collecting confessional text, pre-season interview text, edgic data, etc), transforming everything through bronze → silver → gold layers and delivering ML-ready features for winner prediction research.

Getting Started

The architecture follows a medallion lakehouse pattern optimized for ML feature engineering:

• Bronze Layer (21 tables): Raw survivoR dataset tables with comprehensive ingestion metadata and data lineage
• Silver Layer (8 tables): ML-focused feature engineering organized by strategic gameplay categories (advantage strategy, season context, voting dynamics, edit features, jury analysis, castaway profile, social positioning, challenge performance) - these curated features don't exist in the original survivoR dataset
• Gold Layer (2 tables): Two production ML-ready feature matrices for different modeling approaches (gameplay-only vs hybrid gameplay+edit features) - completely new analytical constructs built on top of survivoR

What makes this special: The entire pipeline runs seamlessly in containerized Apache Airflow with automated dependency management, comprehensive data validation, and zero-configuration setup. Perfect for data scientists who want to focus on analysis rather than infrastructure.

For a detailed reference of the mirrored upstream schema, see the official survivoR documentation.

Huge thanks to Daniel Oehm and the survivoR community; if you haven't already, please check survivoR out! This repository could not exist without their hard work and consistent effort!

What you can explore

• Check out these Survivor analyses with the survivoR dataset as examples of the types of analyses you can now more easily accomplish in python and SQL with Gamebot.

Choose Your Adventure

Looking for the fastest path to Survivor data analysis? Pick your persona:

Try It in 2 Minutes - Gamebot Lite (Analysts)

Perfect for: Exploratory analysis, prototyping, Jupyter notebooks, academic research

Installation: Choose your preferred analytics approach:

# Recommended: pandas for data analysis
pip install gamebot-lite

# Alternative: with DuckDB for SQL-style analytics
pip install gamebot-lite[duckdb]

from gamebot_lite import load_table, duckdb_query

# Load any table for pandas analysis
vote_history = load_table("vote_history_curated")
jury_votes = load_table("jury_votes")

# Or query with DuckDB for complex SQL analytics (requires duckdb extra)
# Get some stats on first boot legends
results = duckdb_query("""
    SELECT
        sub.castaway_name,
        sub.castaway_id_details,
        sub.version_season_details,
        sub.personality_type,
        sub.occupation,
        sub.pet_peeves,
        sub.first_ep_confessional_count,
        sub.first_ep_confessional_time,
        bo.boot_order_position AS order_voted_out,
        'ABSOLUTELY' AS is_legendary_first_boot
    FROM bronze.boot_order AS bo
    INNER JOIN (
        SELECT
            COALESCE(
                castaway_details.full_name,
                castaway_details.full_name_detailed,
                TRIM(concat_ws(' ', castaway_details.castaway, castaway_details.last_name))
            ) AS castaway_name,
            castaway_details.castaway_id AS castaway_id_details,
            castaway_details.version_season AS version_season_details,
            castaway_details.personality_type,
            castaway_details.occupation,
            castaway_details.pet_peeves,
            confessionals.confessional_count AS first_ep_confessional_count,
            confessionals.confessional_time AS first_ep_confessional_time
        FROM bronze.castaway_details
        INNER JOIN bronze.confessionals
            ON castaway_details.castaway_id = confessionals.castaway_id
            AND castaway_details.version_season = confessionals.version_season
        WHERE confessionals.episode = 1
    ) AS sub
        ON bo.castaway_id = sub.castaway_id_details
        AND bo.version_season = sub.version_season_details
    WHERE (
        sub.castaway_name LIKE '%Zane%' OR
        sub.castaway_name LIKE '%Jelinsky%' OR
        sub.castaway_name LIKE '%Francesca%' OR
        sub.castaway_name LIKE '%Reem%'
    )
    AND bo.boot_order_position = 1
    ORDER BY sub.castaway_name
""")

Available data: Bronze (21 raw tables), Silver (8 feature engineering tables), Gold (2 ML-ready matrices) - complete table guide

Gamebot Warehouse - Production Deployment

Perfect for: Teams wanting a production-ready Survivor database with automated refreshes, accessed via any SQL client. Configurable for both development and production environments.

Architecture: Follows official Apache Airflow Docker patterns with Gamebot-specific medallion data pipeline.

What you get: Complete Airflow + PostgreSQL stack with scheduled data refreshes, no code repository required.

Quick Deployment

Prerequisites: Docker Engine/Desktop, basic .env configuration

# 1. Create project directory
mkdir survivor-warehouse && cd survivor-warehouse

# 2. Download docker-compose.yml, .env template, and init script
curl -O https://raw.githubusercontent.com/mgrody1/Gamebot/main/deploy/docker-compose.yml
curl -O https://raw.githubusercontent.com/mgrody1/Gamebot/main/deploy/.env.example
curl -O https://raw.githubusercontent.com/mgrody1/Gamebot/main/deploy/init-deployment.sh

# 3. Configure environment
cp .env.example .env
# Edit .env with your database credentials

# 4. Launch production stack
docker compose up -d

# 5. Access Airflow UI and trigger pipeline
# http://localhost:8080 (admin/admin)

Database Access: Connect any SQL client to localhost:5433 with credentials from your .env file.

What runs:

• Bronze: 21 raw tables (193k+ records)
• Silver: 8 feature engineering tables
• Gold: 2 ML-ready matrices (1,441 observations each)
• Schedule: Automatic weekly updates (configurable)

Documentation: Deployment Guide | Architecture Overview

Gamebot Studio - Development Environment

Perfect for: Developers customizing pipelines, contributors, researchers doing extensive EDA, or anyone wanting full control. Supports multiple development and production deployment patterns.

What you get: Complete source repository with multiple development workflows, VS Code integration, and notebook environment.

Choose Your Development Style

Recommended: VS Code Dev Container + Full Stack

Perfect for: New contributors, consistent development environment

# 1. Clone repository
git clone https://github.com/mgrody1/Gamebot.git
cd Gamebot

# 2. Configure environment
cp .env.example .env
# Edit .env with your settings

# 3. Open in VS Code with Dev Containers extension
# Command Palette → "Dev Containers: Reopen in Container"

# 4. Start complete stack (from host terminal)
make fresh

# 5. Access services
# - Airflow UI: http://localhost:8080
# - Database: localhost:5433
# - Jupyter: Select "gamebot" kernel in VS Code notebooks

Quick Local Development

Perfect for: Experienced developers who prefer local tools

# 1. Clone and setup
git clone https://github.com/mgrody1/Gamebot.git
cd Gamebot
uv sync

# 2. Configure environment
cp .env.example .env
# Edit .env with your settings

# 3. Start stack
make fresh

# 4. Optional: Manual pipeline execution
uv run python -m Database.load_survivor_data  # Bronze
uv run --env-file .env dbt build --project-dir dbt --profiles-dir dbt --select silver  # Silver
uv run --env-file .env dbt build --project-dir dbt --profiles-dir dbt --select gold    # Gold

Lightweight Pipeline (No Airflow)

Perfect for: Refreshing the data and the gamebot-lite snapshot on one machine

Needs uv and one Postgres. make lite-db starts Postgres in a single container; any Postgres that matches .env works.

make lite-setup      # uv sync, creates .env if missing
make lite-db         # optional: Postgres in one container
make lite-run        # bronze load, dbt build + tests, SQLite export, smoke test, pytest
make lite-package    # build the gamebot-lite wheel into dist/

The same steps without make:

uv sync
uv run python scripts/run_lite.py                      # all steps
uv run python scripts/run_lite.py --steps dbt export   # a subset
uv run python scripts/run_lite.py --force-refresh      # re-download the survivoR files
uv build

The bronze step drops and recreates the bronze, silver, and gold schemas.

Full Manual Control

Perfect for: Custom database setups, specific deployment requirements

# 1. Clone repository
git clone https://github.com/mgrody1/Gamebot.git
cd Gamebot

# 2. Setup Python environment
uv sync

# 3. Configure for external database
cp .env.example .env
# Edit .env with your PostgreSQL credentials (not warehouse-db)

# 4. Run pipeline manually
uv run python -m Database.load_survivor_data
uv run --env-file .env dbt deps --project-dir dbt --profiles-dir dbt
uv run --env-file .env dbt build --project-dir dbt --profiles-dir dbt

Notebook Development

For EDA and analysis within the repository:

If using VS Code Dev Container: Jupyter kernel is already configured - just select "gamebot" kernel in VS Code notebooks.

If using local Python environment:

# Setup Jupyter kernel for local development
uv sync ipykernel
uv run python -m ipykernel install --user --name=gamebot

# Create analysis notebooks
uv run python scripts/create_notebook.py adhoc    # Quick analysis
uv run python scripts/create_notebook.py model    # ML modeling

# Use "gamebot" kernel in Jupyter/VS Code

Studio Documentation:

• Developer Guide
• CLI Commands & Workflows
• Environment Configuration
• Architecture Overview

Architecture & Technical Details

Medallion Data Architecture

Core Technologies

• Orchestration: Apache Airflow 2.9.1 with Celery executor
• Transformation: Python with psycopg2 and dbt with custom macros
• Storage: PostgreSQL 15 with automated schema management
• Containerization: Docker Compose with context-aware networking
• Data Quality: Comprehensive validation and testing at each layer

Pipeline Execution

Automated Schedule: Weekly Monday 4AM UTC (configurable via GAMEBOT_DAG_SCHEDULE)

Manual Execution:

• Airflow UI: http://localhost:8080 → survivor_medallion_pipeline → Trigger
• CLI: docker compose exec airflow-scheduler airflow dags trigger survivor_medallion_pipeline

Execution Time: ~2 minutes end-to-end for complete medallion refresh

Documentation & Resources

Core Guides

Schema & Data References

Site demo: query the warehouse in the browser

preferencespace.com/survivor/gamebot runs DuckDB-WASM over a Parquet export of every table in the packaged gamebot-lite slice (4.3 MB), with a lineage map, ready queries and a per-castaway view of the gold matrix. scripts/export_site.py writes the export from gamebot_lite/data/gamebot.sqlite (uv run --with pyarrow --with pandas python scripts/export_site.py); rerun it after a new gamebot-lite export, then rebuild the site. The plan, decisions and the data findings the demo surfaced are in ../preferencespace/GAMEBOT_PLAN.md.

Advanced Topics

Use Cases & Examples

Data Analysis Examples

• Winner Prediction Models: Use gold layer ML features for predictive modeling
• Strategic Analysis: Leverage silver layer features for gameplay pattern analysis
• Historical Trends: Query bronze layer for comprehensive season-by-season analysis

Integration Patterns

• Business Intelligence: Connect Tableau/PowerBI to PostgreSQL warehouse
• Notebook Analysis: Use Gamebot Lite for rapid prototyping and exploration
• Custom Pipelines: Extend Gamebot Studio for specialized research workflows

Research Applications

• Academic Research: Comprehensive dataset for game theory and social dynamics studies
• Data Science Education: Production-ready pipeline for teaching modern data engineering
• Competition Analysis: ML feature engineering examples for prediction competitions

Configuration & Database Access

Single Configuration File: Gamebot uses a unified .env file with context-aware overrides for different execution environments:

# .env (production-ready defaults)
DB_HOST=localhost              # Automatically overridden in containers
DB_NAME=survivor_dw_dev
DB_USER=survivor_dev
DB_PASSWORD=your_secure_password
DB_PORT=5433                   # Application database connection port
AIRFLOW_PORT=8080              # Airflow web interface
GAMEBOT_TARGET_LAYER=gold      # Pipeline depth control

Database Connection: Connect to the warehouse database for analysis:

Container Networking: Docker Compose automatically handles database connectivity with container-to-container networking (warehouse-db:5432) while maintaining external access via localhost:5433.

Operations & Orchestration

Gamebot runs with automated Airflow orchestration on a configurable schedule (GAMEBOT_DAG_SCHEDULE, default Monday 4AM UTC). The complete medallion pipeline includes data freshness detection, incremental loading, and comprehensive validation.

Pipeline Management

# Start complete stack (Airflow + PostgreSQL + Redis)
make fresh

# Monitor pipeline execution
make logs

# Check service status
make ps

# Clean restart (removes all data)
make clean && make fresh

Airflow DAG: survivor_medallion_pipeline

The DAG automatically orchestrates:

1. Data Freshness Check: Detects upstream survivoR dataset changes
2. Bronze Loading: Python-based ingestion with validation
3. Silver Transformation: dbt models for ML feature engineering
4. Gold Aggregation: Production ML-ready feature matrices
5. Metadata Persistence: Dataset versioning and lineage tracking

Manual Triggering:

• UI: Navigate to Airflow (http://localhost:8080) → Unpause and trigger DAG
• CLI: docker compose exec airflow-scheduler airflow dags trigger survivor_medallion_pipeline

Pipeline Results

Successful execution produces:

• Bronze: 21 tables with 193,000+ raw records
• Silver: 8 curated tables with strategic gameplay features
• Gold: 2 ML-ready matrices (1,441 castaway-season observations each)
• Testing: 13 dbt tests ensuring data quality

Releases

• Data releases: Triggered when upstream survivoR data changes: data-YYYYMMDD
• Code releases: When integrating new code changes: code-vX.Y.Z
• CI/CD: GitHub Actions automate testing and release workflows

Troubleshooting

Common Issues

• Port conflicts: Set AIRFLOW_PORT in .env
• Missing DAG changes: Stop stack, rerun make up (DAGs are bind-mounted)
• Fresh start needed: make clean removes volumes and images

Useful Commands

make logs   # Follow scheduler logs
make ps     # Service status
make show-last-run ARGS="--tail --category validation"  # Latest run artifact

Data Quality Reports

Each pipeline run generates Excel validation reports with comprehensive data quality analysis:

# Find latest validation report
docker compose exec airflow-worker bash -c "
  find /opt/airflow -name 'data_quality_*.xlsx' -type f | head -5
"

# Copy latest report to host
LATEST_REPORT=$(docker compose exec airflow-worker bash -c "
  find /opt/airflow -name 'data_quality_*.xlsx' -type f -printf '%T@ %p\n' | sort -n | tail -1 | cut -d' ' -f2
" | tr -d '\r')

docker compose cp airflow-worker:$LATEST_REPORT ./data_quality_report.xlsx

Report contents: Row counts, column types, PK/FK validations, duplicate analysis, schema drift detection, and detailed remediation notes.

Contributing

Want to help? Read the Contributing Guide for:

• Trunk-based workflow and git commands
• Environment setup for contributors
• Release checklist and collaboration ideas
• PR requirements (include zipped run logs)

Repository Structure

Root Configuration
├── .env                                   # Single configuration file
├── .env.example                           # Configuration template
├── Makefile                               # Simplified commands
├── pyproject.toml                         # Python package configuration
├── pyproject.toml / uv.lock               # Package definition and locked dev environment
├── params.py                              # Global pipeline parameters
└── README.md                              # This documentation

Core Pipeline
├── airflow/
│   ├── dags/survivor_medallion_dag.py    # Complete orchestration pipeline
│   ├── docker-compose.yaml               # Production-ready stack definition
│   ├── Dockerfile                        # Custom Airflow image
│   ├── entrypoint-wrapper.sh             # Branch protection and initialization
│   └── requirements.txt                  # Airflow Python dependencies
├── dbt/
│   ├── models/silver/                     # ML feature engineering (8 models)
│   ├── models/gold/                       # Production ML features (2 models)
│   ├── tests/                             # Data quality validation (13 tests)
│   ├── macros/                            # Custom dbt macros
│   ├── dbt_project.yml                    # dbt configuration
│   └── profiles.yml                       # Database connection config
├── Database/
│   ├── load_survivor_data.py              # Bronze layer ingestion
│   ├── create_tables.sql                  # DDL for warehouse schema
│   └── sql/                               # Legacy SQL scripts
└── gamebot_core/
    ├── db_utils.py                        # Schema validation and utilities
    ├── data_freshness.py                  # Change detection and metadata
    ├── validation.py                      # Data quality validation
    ├── env.py                             # Environment configuration
    ├── github_data_loader.py              # survivoR dataset downloader
    ├── log_utils.py                       # Logging utilities
    ├── notifications.py                   # Alert system
    └── source_metadata.py                 # Dataset versioning

Analysis & Distribution
├── gamebot_lite/                          # PyPI package for analysts
│   ├── __init__.py / __main__.py          # Package entry points
│   ├── client.py                          # Data loading interface
│   ├── catalog.py                         # Table metadata
│   └── data/                              # SQLite database (gitignored)
├── examples/
│   ├── example_analysis.py                # 2-minute demo
│   └── streamlit_app.py                   # Interactive data viewer
└── notebooks/                             # Analysis examples and EDA

Deployment & Operations
├── deploy/                                # Standalone warehouse deployment
│   ├── docker-compose.yml                # Production deployment stack
│   ├── .env.example                       # Environment configuration
│   └── init-deployment.sh                # Deployment initialization script
├── .devcontainer/                         # VS Code dev container config
├── .github/workflows/                     # CI/CD pipelines
├── docs/                                  # Comprehensive guides
├── scripts/                               # Automation and utilities
├── tests/                                 # Unit and integration tests
├── run_logs/                              # Validation artifacts (gitignored)
└── templates/                             # templates for generating notebooks
└── data_cache/                            # survivoR dataset cache (gitignored)