The problem
Football data is everywhere, but it's messy. The Transfermarkt dataset covers players, games, clubs, competitions, transfers and valuations — spread across a dozen files, with inconsistent names, German labels, impossible formations and duplicated rows.
Querying it directly means complex joins and repeated cleaning every time. I wanted a single source of truth: clean, integrated data that's fast to query for reporting and ready to feed machine learning models.
The architecture
I used a medallion architecture in PostgreSQL, where each layer has one job.
Bronze
12 raw tables, loaded as-is from the CSV files with a Bash script.
Silver
Cleaned and standardised tables, loaded by a stored procedure.
Gold
A transfers star schema: one fact table and four dimensions.
The result: a transfers star schema
The gold layer models every transfer as an event, connected to the player, both clubs, the date and the type of transfer. A fee_value_ratio measure shows whether a club paid above or below the player's market value.
Read the series
I've written up the whole build, step by step, so you can follow along and build your own.
What is a data warehouse — and why build one?
What a data warehouse is, the problem it solves, how ETL works, and the architecture I chose.
Read part 1 →Setting up the project and building the bronze layer
Requirements, naming conventions, the database, and loading raw Transfermarkt data into PostgreSQL exactly as it arrives.
Read part 2 →The silver layer: exploring and cleaning football data
Finding how the tables connect, checking data quality, and cleaning messy football data — from German confederation names to impossible formations.
Read part 3 →The gold layer: modelling transfers as a star schema
Turning clean data into a data product. Sun modelling, facts and dimensions, and a transfers star schema ready for analysis and machine learning.
Read part 4 →Tools
I built this project while following the Building a Modern Data Warehouse – Data Engineering Bootcamp course, applying its methods to football data.