What is a data warehouse?
A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data that supports decision-making. In plain English:
Subject-oriented
Organised around a business area, like players, transfers or matches.
Integrated
Brings data from multiple sources together in one place.
Time-variant
Keeps historical data, not just the latest snapshot.
Non-volatile
Once data is in, it isn't deleted or changed on the fly.
Why do we need one?
Imagine three analysts each building reports from three different sources. Each one collects and transforms the data in their own way. One report covers the last 40 days, another the last 10, another the last 5. Nobody can say exactly how each number was produced.
That causes real problems:
- Decisions get harder, because the numbers don't agree.
- Reports are slow — sometimes weeks or months each.
- Big data breaks things. Analysts often aren't set up to handle large volumes, so the work can fail completely.
- Integrated reports are almost impossible, because every source, transformation and number is different.
With a data warehouse, a data engineer builds an ETL pipeline that automatically extracts, transforms and loads data from every source into one place. The data is integrated and historic, and analysts work from a single source of truth — so they can spend their time answering questions instead of wrangling data.
The kitchen analogy: a good warehouse is like a well-organised kitchen. Ingredients arrive, get prepared and are stored neatly, ready to use. The chef simply takes what they need to make each dish.
What is ETL?
ETL stands for extract, transform, load. Each step has a few options.
Extract
Method
- Pull — you fetch the data
- Push — the source sends it
Type
- Full — everything, every time
- Incremental — only what's new or changed
Technique
- Database queries
- File parsing
- API calls
- Event streaming
- Change data capture
- Web scraping
Transform
- Data enrichment — add useful values to the data.
- Data integration — combine multiple sources into one model.
- Derived columns — create new columns from existing ones.
- Normalisation and standardisation — put values in a consistent format.
- Business rules — build columns from business logic.
- Aggregations — summarise data to a different level of detail.
- Data cleaning — remove duplicates, filter, handle missing and invalid values, trim spaces, fix data types and detect outliers.
Load
Processing
- Batch — load on a schedule
- Stream — load continuously
Full loads
- Truncate and insert — empty the table, reload it
- Upsert — update existing rows, insert new ones
- Drop, create, insert — rebuild the table from scratch
Incremental loads
- Upsert
- Append — add new rows only
- Merge — update, insert and delete
Slowly changing dimensions
- SCD0 — never update
- SCD1 — overwrite (history is lost)
- SCD2 — add a new row for each change and keep the old one
Choosing an architecture
There are four common ways to manage analytical data:
- Data warehouse — structured, cleaned data ready for reporting. This project's focus.
- Data lake — stores anything in raw form. Flexible, but it can easily become a “data swamp”.
- Data lakehouse — a mix of the two.
- Data mesh — no central platform; each domain builds and shares its own data products.
How to build a data warehouse
| Approach | How it works | Trade-offs |
|---|---|---|
| Inmon | Raw data lands in staging, is modelled into an enterprise data warehouse (in third normal form), then subsets become data marts for reporting. | One integrated, enterprise-wide model — but slower and more work up front. |
| Kimball | Skips the enterprise warehouse and builds data marts straight from staging. | Quicker — but without the bigger picture, you can end up repeating the same transformations in different marts. |
| Data vault | Splits the warehouse into a raw vault (original data) and a business vault (business logic), then builds data marts. | More standards and rules, and adapts well to change — but more complex. |
| Medallion | Three layers: bronze (raw), silver (cleaned) and gold (business-ready). | Simple, easy to understand and flexible for many uses — not just reporting. |
The medallion architecture
I chose the medallion architecture. Its strength is a strict separation of concerns: each layer has one job.
Bronze
Ingest only. Raw data, exactly as it arrives. Useful for tracing and debugging issues back to the source.
Silver
Clean only. Cleansing and standardising — but no business rules yet. Data never jumps straight from source to silver.
Gold
Business logic only. No cleaning here — just data products for reporting, analysis and machine learning.
In the next part, I'll set up the project and load the raw data into the bronze layer.