Setting up the project and building the bronze layer

The requirements

Every data project starts with understanding what you need to build. This project had two goals.

Planning the work

I broke the project into epics, each with analysing, coding, validating and documenting steps.

Naming conventions

Without agreed rules, names quickly drift: one developer writes dimension_players, another DimensionClubs, another dim_Competitions. Everything becomes harder to query. So I set rules up front.

• snake_case — lowercase words separated by underscores.
• English for all names.
• No SQL reserved words as object names.

Repository, database and schemas

I created a Git repository with a folder for each layer's scripts, then created the FootballDataWarehouse database in pgAdmin 4. One schema per layer keeps everything separate:

-- Create schemas for the medallion architecture
CREATE SCHEMA IF NOT EXISTS bronze;
CREATE SCHEMA IF NOT EXISTS silver;
CREATE SCHEMA IF NOT EXISTS gold;

Building the bronze layer

The bronze layer stores the raw data exactly as it arrives. There are four steps: analyse the source, write the ingestion code, validate it, and document it.

1. Analyse the source systems

In a real organisation, you'd interview the people who own the source systems. These are the questions worth asking:

2. Create the tables

The table definitions (DDL — data definition language) mirror the source files exactly. Here's the players table:

CREATE TABLE IF NOT EXISTS bronze.players_details (
    player_id INTEGER,
    first_name TEXT,
    last_name TEXT,
    "name" TEXT,
    last_season INTEGER,
    current_club_id INTEGER,
    country_of_birth TEXT,
    country_of_citizenship TEXT,
    date_of_birth TEXT,
    "position" TEXT,
    sub_position TEXT,
    foot TEXT,
    height_in_cm INTEGER,
    contract_expiration_date TEXT,
    agent_name TEXT,
    international_caps INTEGER,
    international_goals INTEGER,
    market_value_in_eur NUMERIC,
    highest_market_value_in_eur NUMERIC
    -- ...plus a few more columns from the source file
);

3. Load the data

PostgreSQL can't load straight from a folder of files on its own, so I wrote a small Bash script. It empties each bronze table and reloads it from the matching CSV — a full load using truncate and insert.

DB_NAME="FootballDataWarehouse"
DB_USER="postgres"
DATA_PATH="./data"

echo "Loading bronze layer..."

psql -U $DB_USER -d $DB_NAME <<EOF
TRUNCATE TABLE bronze.players_details;
\copy bronze.players_details FROM '$DATA_PATH/players/players.csv' WITH (FORMAT CSV, HEADER true, DELIMITER ',');

TRUNCATE TABLE bronze.players_transfers;
\copy bronze.players_transfers FROM '$DATA_PATH/players/transfers.csv' WITH (FORMAT CSV, HEADER true, DELIMITER ',');

TRUNCATE TABLE bronze.games_details;
\copy bronze.games_details FROM '$DATA_PATH/games/games.csv' WITH (FORMAT CSV, HEADER true, DELIMITER ',');

-- ...and the same for the other bronze tables
EOF

4. Validate and document

To check completeness, I compared the number of rows in each table with the CSV files — simple SELECT queries were enough to confirm nothing was missing. Then I drew the data flow and committed the code.

In the next part, the real work begins: exploring and cleaning the data in the silver layer.