♔ Chess Analytics Pipeline

An automated ELT (Extract, Load, Transform) pipeline that ingests thousands of chess games from the Lichess API, structures the data into a relational warehouse, and powers an Opening Recommendation Engine to help you find the best openings to play against any opponent.

Architecture

Lichess API  →  Python Extraction  →  PostgreSQL (Raw)  →  dbt Transforms  →  Next.js Dashboard
                     ↑                                           ↓
               Apache Airflow                           analytics.dim_players
              (Daily Schedule)                          analytics.dim_openings
                                                        analytics.fct_player_openings
                                                        analytics.recommendation_engine

Tech Stack

Layer	Tool
Orchestration	Apache Airflow 2.8
Extraction	Python + python-chess + Lichess API
Data Warehouse	PostgreSQL 16
Transformation	dbt (Data Build Tool)
Dashboard	Next.js + React + Recharts
Infrastructure	Docker Compose

Quick Start

Prerequisites

• Docker & Docker Compose
• Node.js 18+
• Python 3.11+

1. Start Infrastructure

docker compose up -d

This starts PostgreSQL (port 5432) and Airflow (port 8080).

2. Initialize Airflow

docker compose run airflow-init

Login to Airflow at http://localhost:8080 (admin / admin).

3. Run dbt

cd dbt_chess
pip install dbt-postgres
dbt run --profiles-dir .
dbt test --profiles-dir .

4. Start Dashboard

cd dashboard
npm install
npm run dev

Open http://localhost:3000 and search for any Lichess username.

How It Works

1. Search: Enter a Lichess username in the dashboard
2. Ingest: The app fetches their recent games from the Lichess API and loads them into PostgreSQL
3. Transform: dbt models clean the data and calculate opening performance metrics
4. Recommend: The recommendation engine identifies openings where the opponent performs worst
5. Visualize: Results are displayed with interactive charts, W/D/L bars, and performance trends

Data Model

raw.games (source)
  ↓
analytics.stg_games (staging - clean & type)
  ↓
analytics.dim_players (player profiles & stats)
analytics.dim_openings (opening catalog)
analytics.fct_player_openings (player × opening performance)
  ↓
analytics.recommendation_engine (ranked opening suggestions)

Project Structure

Chess/
├── docker-compose.yml           # Airflow + PostgreSQL
├── .env                         # Environment config
├── requirements.txt             # Python deps
├── db/init/                     # SQL schema init scripts
├── extraction/                  # Python EL scripts
│   ├── lichess_client.py        # Lichess API client
│   ├── pgn_parser.py            # PGN flattening & analysis
│   └── loader.py                # PostgreSQL batch loader
├── airflow/dags/                # Airflow DAGs
│   └── lichess_etl_dag.py       # Daily extraction DAG
├── dbt_chess/                   # dbt project
│   ├── models/staging/          # Staging models
│   └── models/marts/            # Dimension, fact, & recommendation models
└── dashboard/                   # Next.js web app
    ├── app/                     # App router pages & API routes
    ├── app/components/          # React components
    └── lib/                     # Database utilities