Puck Analytics

NHL data pipeline & visualization platform

Data EngineeringUI/UX DesignDevelopment

This project was commisoned to narrow the scope of modern hockey analytics and analyze the impact of non-conventional metrics on roster construction.

Preview
Role

Designer & Developer

Timeline

Jan – Apr 2024

Type

Personal Project

Status

In Progress

Problem

The client believed that modern analytics were not helpful and wanted to analyze the impact of several non-conventional metrics on roster construction.

Solution

A PostgreSQL database ingests and normalizes data from the NHL Stats API, while a Next.js dashboard surfaces interactive visualizations — shot charts, player trends, and team performance breakdowns — without requiring SQL knowledge.

Preview

Steps

Database

Designed a normalized PostgreSQL schema and Python ETL pipeline to ingest play-by-play event data from the NHL Stats API.

PythonPostgreSQLNHL Stats APINeon

UI/UX Design

Wireframed and prototyped dashboard layouts in Figma with progressive disclosure for casual fans and power users alike.

Figma

Development

Built an interactive Next.js dashboard with Chart.js visualizations, backed by a Node.js API querying the Postgres database.

Next.jsNode.jsChart.js

Database Architecture

Purpose

Why create a database instead of fetching data from the existing API?

The NHL API was missing several key associated data points, and had some other inconsistencies:

  • Player ameture league data was not associated with any player related endpoints.

    The NHL API does not provide direct ameture league data for players, which is important for analyzing player development and performance. Because this was the top ask from the client, it was necessary to create a database that could fill in this missing data point.

  • Player draft position and year were not associated with any player related endpoints.

    The NHL API does not provide direct draft position and year data for players, which is important for analyzing player development and performance.

  • Inconsistent roster data existed for players who have been traded mid-season.

    Inconsistent roster data resulted in gaps in inferred line combinations.

  • The existing API can still be leveraged for certain data points

    By preserving team and player IDs from the NHL API, the existing API can still be leveraged for certain data points, such as player statistics and team information.

Methods

Creating and hosting the database

Python was utilized to fetch existing data from the NHL API and to fill in the missing data points. The data was then normalized and stored in a PostgreSQL database.

  • Player ameture league was deduced from the last played league prior to the NHL from the player statistics endpoint.

  • Player draft position and year was added utilizing the NHL API draft endpoint.

  • Player line combinations were created from the NHL shift charts API

  • The database was hosted on Neon, a serverless Postgres platform.
Structure

Tables

  • seasons

    One row per NHL season (2005-06 onward). Stores the season ID (e.g. 20052006), start/end years, rule flags (wild card, ties, OT loss point), and key dates (regular season end, playoff end).

  • teams

    One row per NHL franchise entry. Stores the team's numeric ID, full name, three-letter abbreviation, and franchise ID.

  • team_seasons

    Junction table linking teams to seasons. Each row represents a team participating in a given season and stores end-of-season standings data: wins, losses, OT losses, points, and a reference to the division. Itsprimary key (id / team_season_id) is the FK used throughout the rest of the schema.

  • players

    One row per NHL player. Stores the player's numeric ID, first/last name, birthdate, birth country, shoots/catches handedness, and amateur league (the last non-NHL/AHL league the player appeared in before turning pro).

  • rosters

    Links players to a specific team-season. Stores jersey number, position code, height (inches), and weight (pounds). A player can appear on multiple team_seasons rows (trades, multiple seasons).

  • conferences

    One row per conference per season (e.g. Eastern, Western). Stores the conference name and the season it belongs to. Created on-the-fly when standings are inserted.

  • divisions

    One row per division per season (e.g. Atlantic, Metropolitan). Stores the division name, its parent conference, and the season it belongs to. Created on-the-fly when standings are inserted.

  • player_stats

    Regular-season skater stats (game_type_id = 2) per player per team-season. Stores goals, assists, points, plus/minus, average time on ice, penalty minutes, and games played. Goalies are excluded.

  • player_stats_playoffs

    Playoff skater stats (any game_type_id other than 2) per player per team-season. Same columns as player_stats. Goalies are excluded.

  • playoffs

    One row per season that had a playoff. Acts as a parent record for playoff_series rows, keyed by season_id.

  • playoff_series

    One row per playoff series (e.g. first-round matchup). Stores the round number, series letter (A–O), the home and away team_season_ids, and each team's game wins within the series.

  • playoff_games

    One row per individual playoff game. Stores the parent playoff_series_id, game number within the series, home/away team_season_ids, and final scores for each team.

Relations

  • seasons
    • conferences
      • divisons
        • team_seasons
          • rosters
            • players
          • player_stats
          • player_stats_playoffs
          • playoff_series
          • playoff_games
    • teams
      • team_seasons (see above)
    • playoffs
      • playoff_series