# imfc12/shot-data-warehouse architecture

> Architecture diagram of the GitHub repository imfc12/shot-data-warehouse, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-07-05). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/imfc12/shot-data-warehouse
- Repository: https://github.com/imfc12/shot-data-warehouse

## Overview

##### Project type and purpose
- This is a Python-MySQL ETL/data-warehouse pipeline that extracts NBA shot data via the nba_api, stages it, and loads it into an OLAP-ready star schema in MySQL.

##### File structure
- Top-level script: main.py, which drives the full pipeline.
- ETL/ directory: contains four Python modules—
- reference_data.py (loads reference tables)
- staging_data.py (extracts & stages shot data)
- staging_utils.py (helpers: month/clutch mapping, DB connections)
- orchestrate_load.py (runs stored procedures to move data from staging to star schema)
- SQL/ directory: DDL/DML scripts to define and populate database objects—
- star_schema.sql (dimension & fact table definitions)
- staging_table.sql (staging table)
- reference_tables.sql (ref_teams, ref_players)
- retrieve_team_proc.sql (stored proc to get teams/players)
- load_procedures.sql (stored procs to move staging→star schema)
- Docs/: mapping between staging & star schema, and project journal

##### README for additional insights
- Confirms a star schema consisting of five dimension tables and one fact table
- Describes pipeline steps and sequencing in main.py
- Notes external dependency: nba_api
- Mentions environment setup (.env for DB creds, pip requirements)

## Diagram (Mermaid)

```mermaid
flowchart TD
    subgraph "External Service"
        NBA["NBA API"]:::external
    end

    subgraph "Configuration"
        ENV[".env / requirements.txt"]:::config
    end

    subgraph "ETL Application"
        Main["main.py\n(Orchestrator)"]:::python
        Ref["reference_data.py\n(Load Reference)"]:::python
        StgUtil["staging_utils.py\n(Utilities)"]:::python
        Stg["staging_data.py\n(Stage Shot Data)"]:::python
        OrLoad["orchestrate_load.py\n(Load to Star)"]:::python
    end

    subgraph "Database"
        subgraph "Reference Schema"
            RefTeams["ref_teams"]:::dbtable
            RefPlayers["ref_players"]:::dbtable
        end
        subgraph "Staging Schema"
            StgShots["stg_shots"]:::dbtable
        end
        subgraph "Star Schema"
            DimPlayers["dim_players"]:::dbtable
            DimTeams["dim_teams"]:::dbtable
            DimGames["dim_games"]:::dbtable
            DimTime["dim_time"]:::dbtable
            DimShots["dim_shots"]:::dbtable
            FactShots["fact_shots"]:::dbtable
        end
    end

    subgraph "SQL Scripts"
        StarSQL["star_schema.sql"]:::sqlscript
        StgSQL["staging_table.sql"]:::sqlscript
        RefSQL["reference_tables.sql"]:::sqlscript
        RetrieveProc["retrieve_team_proc.sql"]:::sqlscript
        LoadProcs["load_procedures.sql"]:::sqlscript
    end

    subgraph "Artifacts"
        Drawio["schema.drawio"]:::artifact
        MWB1["shot-data-model.mwb"]:::artifact
        MWB2["shot-eff-model.mwb.bak"]:::artifact
        ColMap["column_mapping.md"]:::artifact
        Journal["Journal.txt"]:::artifact
        EnvDef["environment.yml"]:::artifact
        ReqTxt["requirements.txt"]:::artifact
        Readme["README.md"]:::artifact
    end

    NBA -->|"Extract Ref"| Ref
    NBA -->|"Extract Shots"| Stg
    StgUtil --> Ref
    StgUtil --> Stg
    Main -->|"Run Ref ETL"| Ref
    Main -->|"Run Staging ETL"| Stg
    Main -->|"Run Load"| OrLoad
    Main --> ENV
    Main --> Drawio

    Ref --> RefSQL
    RefSQL --> RefTeams
    RefSQL --> RefPlayers

    Stg --> StgSQL
    StgSQL --> StgShots

    OrLoad --> LoadProcs
    LoadProcs --> DimPlayers
    LoadProcs --> DimTeams
    LoadProcs --> DimGames
    LoadProcs --> DimTime
    LoadProcs --> DimShots
    LoadProcs --> FactShots
    OrLoad --> RetrieveProc
    RetrieveProc --> RefTeams
    RetrieveProc --> RefPlayers

    click Main "https://github.com/imfc12/shot-data-warehouse/blob/main/main.py"
    click Ref "https://github.com/imfc12/shot-data-warehouse/blob/main/ETL/reference_data.py"
    click Stg "https://github.com/imfc12/shot-data-warehouse/blob/main/ETL/staging_data.py"
    click StgUtil "https://github.com/imfc12/shot-data-warehouse/blob/main/ETL/staging_utils.py"
    click OrLoad "https://github.com/imfc12/shot-data-warehouse/blob/main/ETL/orchestrate_load.py"
    click StarSQL "https://github.com/imfc12/shot-data-warehouse/blob/main/SQL/star_schema.sql"
    click StgSQL "https://github.com/imfc12/shot-data-warehouse/blob/main/SQL/staging_table.sql"
    click RefSQL "https://github.com/imfc12/shot-data-warehouse/blob/main/SQL/reference_tables.sql"
    click RetrieveProc "https://github.com/imfc12/shot-data-warehouse/blob/main/SQL/retrieve_team_proc.sql"
    click LoadProcs "https://github.com/imfc12/shot-data-warehouse/blob/main/SQL/load_procedures.sql"
    click Drawio "https://github.com/imfc12/shot-data-warehouse/blob/main/Data Models/schema.drawio"
    click MWB1 "https://github.com/imfc12/shot-data-warehouse/blob/main/Data Models/shot-data-model.mwb"
    click MWB2 "https://github.com/imfc12/shot-data-warehouse/blob/main/Data Models/shot-eff-model.mwb.bak"
    click ColMap "https://github.com/imfc12/shot-data-warehouse/blob/main/Docs/column_mapping.md"
    click Journal "https://github.com/imfc12/shot-data-warehouse/blob/main/Docs/Journal.txt"
    click EnvDef "https://github.com/imfc12/shot-data-warehouse/blob/main/environment.yml"
    click ReqTxt "https://github.com/imfc12/shot-data-warehouse/blob/main/requirements.txt"
    click Readme "https://github.com/imfc12/shot-data-warehouse/blob/main/README.md"

    classDef python fill:#ADD8E6,stroke:#333,stroke-width:1px
    classDef external fill:#FFA500,stroke:#333,stroke-width:1px
    classDef config fill:#D3D3D3,stroke:#333,stroke-width:1px
    classDef dbtable fill:#90EE90,stroke:#333,stroke-width:1px
    classDef sqlscript fill:#F5F5F5,stroke:#333,stroke-width:1px
    classDef artifact fill:#E6E6FA,stroke:#333,stroke-width:1px
```
