PSL Data Warehouse — ETL Pipeline

This project builds an analytical data warehouse for Pakistan Super League (PSL) ball-by-ball data spanning the 2016–2024 seasons.

The pipeline extracts raw CSV files, standardizes and validates the data, and loads it into a Kimball-style star schema designed for analytical querying. Beyond moving data from source to destination, the project focuses on the engineering concerns behind reliable ETL pipelines—modularity, transaction safety, and data quality.

The warehouse contains 66,112 deliveries across 280 matches, where each row in the fact table represents a single delivery.

---

Architecture

Warehouse Grain

One row represents one delivery bowled in a PSL match.

---

ETL Pipeline

flowchart LR

A([Raw CSV])
B[Extract]
C[Transform]
D{Validation}
E[Load Dimensions]
F[Load Fact Table]
G[(Data Warehouse)]
H([Rollback])

A --> B
B --> C
C --> D

D -->|Pass| E
D -->|Pass| F

E --> G
F --> G

D -->|Fail| H

Loading

The pipeline follows a traditional ETL workflow while ensuring the warehouse never enters an inconsistent state. Validation is performed before loading begins, and any failure prevents the transaction from being committed.

---

Data Warehouse Design

The warehouse follows Kimball dimensional modeling, using a star schema optimized for reporting and aggregation.

Fact Table

• fact_deliveries

Dimension Tables

• dim_season
• dim_umpire
• dim_venue
• dim_match_type
• dim_player
• dim_team
• dim_match
• dim_dismissal_kind
• dim_extra_type

Separating descriptive attributes from measurable events keeps analytical queries simple while reducing redundancy across the warehouse.

---

Data Validation

Before each load, the pipeline validates the incoming data against business rules to ensure warehouse integrity.

Validation includes:

• Total runs consistency (batter_runs + extras = total_runs)
• Duplicate delivery detection (match_id, inning, over, ball)
• Wicket and dismissal consistency (is_wicket flag vs dismissal_kind presence)
• Winner and victory method validation (win_by vs winner presence)

If any validation fails, the transaction is rolled back and no data is written to the warehouse.

---

Testing

pytest tests/ -v

Test Coverage:

• test_transform.py: Player name regex cleaning, venue standardization, null winner handling
• test_pipeline.py: Runs consistency, duplicate detection, wicket logic (both directions), winner logic

Result: 5 tests, all passing.

---

Repository Structure

psl-data-warehouse/
│
├── pipeline/
│   ├── config.py          # Paths, constants, settings
│   ├── extract.py         # CSV ingestion
│   ├── transform.py       # Data cleaning (regex, null handling)
│   ├── validate.py        # Business rule validation
│   ├── load.py            # SQL insert functions (no commits)
│   └── orchestrate.py     # Transaction control + logging
│
├── analysis/
│   └── reports.py         # Analytical queries (Pandas + SQL)
│
├── tests/
│   ├── test_transform.py  # Unit tests for cleaning functions
│   └── test_pipeline.py   # Validation rule tests
│
├── docs/
│   └── PSL-OLAP-Star-Schema.png
│
├── main.py                # Entry point: python main.py
├── requirements.txt
└── README.md

---

Example Analysis

SQL — Top 5 Run Scorers

SELECT
    p.player_name,
    SUM(f.batter_run) AS total_runs
FROM fact_deliveries f
JOIN dim_player p
    ON f.batter_player_key = p.player_key
GROUP BY p.player_name
ORDER BY total_runs DESC
LIMIT 5;

Player	Runs
Babar Azam	3504
Fakhar Zaman	2530
Mohammad Rizwan	2403
Shoaib Malik	2336
Rilee Rossouw	2020

Equivalent Analysis in Pandas

batsman_scorers = pd.merge(
    fact_table,
    player_table,
    how="inner",
    left_on="batter_player_key",
    right_on="player_key",
)

top_scorers = (
    batsman_scorers
    .groupby("player_name")
    .agg(total_runs=("batter_run", "sum"))
    .reset_index()
    .sort_values("total_runs", ascending=False)
    .head(5)
)

---

Engineering Decisions

Warehouse Grain

The warehouse is modeled at the delivery level, allowing every ball to become an analyzable event. This enables flexible reporting at higher levels of aggregation, including overs, innings, matches, seasons, teams, and players.

Role-Playing Player Dimension

Instead of maintaining separate dimensions for batters, bowlers, fielders, dismissed batters, and Players of the Match, a single dim_player table is reused through multiple foreign keys. This keeps the model compact while preserving analytical flexibility.

Transaction-Oriented Loading

Each seasonal load executes within a single database transaction. Validation occurs before the transaction is committed, ensuring that failed loads never leave the warehouse in a partially updated state.

Modular Pipeline

The ETL process is divided into extraction, transformation, validation, and loading modules. Separating responsibilities makes the codebase easier to test, debug, and extend as new business rules or data sources are introduced.

SQLite as the Storage Layer

SQLite provides a lightweight environment for local development without requiring additional infrastructure. Because the schema and ETL logic remain database-agnostic, migrating to PostgreSQL requires minimal structural changes.

---

Development Process

Rather than designing the warehouse first, development began with exploring the raw PSL dataset to understand its structure and identify inconsistencies. This revealed issues such as inconsistent player names (e.g., "Muhammad Nawaz (3)"), venue strings containing city information, duplicate delivery records, and incomplete match metadata (missing player_of_match for two matches, null winners for rain-affected matches).

Once these issues were identified, the transformation layer was implemented to standardize the data before modeling the warehouse. The star schema was then designed around the analytical grain of a single delivery, followed by the ETL pipeline and validation layer to ensure only trusted data is loaded into the warehouse.

---

What I Learned

• Transaction safety is non-negotiable: Early versions committed after each dimension load, risking partial warehouse states. Moving to one commit per season eliminated this entirely.
• Validation belongs before load, not after: Post-load checks catch corruption too late. Pre-load validation with ValueError stops bad data before it enters the warehouse.
• Role-playing dimensions reduce complexity: Using one dim_player for batters, bowlers, fielders, and dismissed players keeps the schema simple without sacrificing analytical flexibility.
• Idempotency enables safe re-runs: INSERT OR IGNORE with proper unique constraints means the pipeline can retry without duplicating data.
• Tests prove correctness, not hope: Writing tests for validation rules caught logical errors in my own thinking—specifically, I initially tested for "valid data exists" instead of "invalid data does not exist."

---

Running Locally

Prerequisites

• Python 3.10+
• pip

Setup

# Clone the repository
git clone https://github.com/m-hammad-qureshi/psl-data-warehouse.git
cd psl-data-warehouse

# Create a virtual environment
python -m venv venv

Windows

venv\Scripts\activate

Linux / macOS

source venv/bin/activate

Install dependencies.

pip install -r requirements.txt

Run the ETL pipeline.

python main.py

Execute the test suite.

pytest tests/ -v

Data Source

PSL Complete Dataset 2016-2024 — Kaggle