Premier League Data Pipeline

Overview

Note

This repository contains a personal project designed to enhance my skills in Data Engineering. It focuses on developing data pipelines that extract, transform, and load data from various sources into diverse databases. Additionally, it involves creating a dashboard with visualizations using Streamlit.

Important

Many architectural choices and decisions in this project may not make the most efficent sense on purpose for the sake of practicing and learning.

Important Links

• Documentation - currently under construction 🔨
• Streamlit App
• Version History

Infrastructure

Tools & Services

Databases

Code Quality

Security Linter	Code Formatting	Type Checking	Code Linting
bandit	ruff-format	mypy	ruff

---

Data and CI/CD Pipelines

Data Pipelines

Data Pipeline 1

1. Data from the Financial Modeling Prep API is extracted with Python using the /quote endpoint.
2. The data is loaded directly into a PostgreSQL database hosted on Cloud SQL with no transformations.
3. The prior steps are orchestrated with Prefect.
4. Once the data is loaded into PostgreSQL, Datastream replicates the data into BigQuery. Datastream checks for staleness every 15 minutes.
5. dbt is used to transform the data in BigQuery and create a view with transformed data.

Data Pipeline 2

1. Data is extracted from multiple API sources with Python:
   • Data from the Football Data API is extracted with Python using the /standings, /teams, and top_scorers endpoints.
   • Data from the NewsAPI is extracted with Python using the /everything endpoint with parameters set to search for the Premier League.
   • Data from the Go & Gin API is extracted with Python using the /stadiums endpoint.
2. Python performs any necessary transformations and loads the data into BigQuery.
3. The prior steps are orchestrated with Prefect.

Data Pipeline 3

1. Data from the Football Data API is extracted with Python using the /fixtures endpoint.
2. Python creates dictionaries from the data and loads the data into Firestore
3. The prior steps are orchestrated with Cloud Scheduler as a Docker container hosted on Cloud Run as a Job.

Pipeline Diagram

CI/CD Pipeline

The CI/CD pipeline is focused on building the Streamlit app into a Docker container that is then pushed to Artifact Registry and deployed to Cloud Run as a Service. Different architecutres are buit for different machine types and pushed to Docker Hub.

1. The repository code is checked out and a Docker image containing the updated streamlit_app.py file will build.
2. The newly built Docker image will be pushed to Artifact Registry.
3. The Docker image is then deployed to Cloud Run as a Service.

Pipeline Diagram

---

Security

• Syft and Grype work together to scan the Streamlit Docker image. Syft creates an SBOM and Grype scans the SBOM for vulnerabilities. The results are sent to the repository's Security tab.
• Snyk is also used to scan the repository for vulnerabilities in the Python packages.