The gold layer: modelling transfers as a star schema

The gold layer is where data becomes a product that people use: a data scientist building a model, an analyst creating a dashboard, or an engineer building an API.

Data modelling

Data modelling means taking raw data and giving it structure: clear entities, readable columns and relationships between them. There are three levels of detail:

Star schema or snowflake schema?

Analytics needs a model that's easy to understand, flexible and fast to query. There are two classic options:

I chose a star schema. Storage isn't an issue here, it's great for reporting, and it makes it easy to build “one big table” for machine learning later.

Facts and dimensions

Choosing a use case: transfers

The silver tables are well organised, but querying them directly for analytics would need complex joins across many tables — slowing down dashboards and complicating feature engineering for machine learning. Instead, I built a data product for a use case I really care about: transfers.

Sun modelling

A fact table describes an event (a verb), not a thing (a noun). So instead of “a player performance table”, you track “a player appearance” event. I used the 3 Ds:

1. Discover an event — who does what?
2. Describe it with the 7 Ws
3. Document it as a sun model

For a transfer, the 7 Ws looked like this:

The logical model

The sun model converts almost directly into a star schema. The only addition is the foreign keys in the fact table that connect it to each dimension.

My 10-step method for gold tables

1. Start from the master table for each entity, and use LEFT JOINs rather than inner joins, so you don't silently lose rows that are missing from other tables.
2. Check for duplicates after every join. One-to-many relationships multiply rows.
3. Remove redundant columns, such as a player name that appears in two tables.
4. Resolve conflicts and nulls by agreeing which source is the master, then falling back in order:

   CASE
       WHEN master_source    IS NOT NULL THEN master_source
       WHEN secondary_source IS NOT NULL THEN secondary_source
       ELSE 'n/a'
   END AS final_value

5. Rename columns to friendly names, e.g. name becomes player_name.
6. Group columns logically — identity, then physical attributes, then career stats, then contract details.
7. Give every dimension a primary key, using a surrogate key when there's no natural one.
8. Keep only relevant data. Transfer fees have risen so much that very old transfers would skew analysis and models.
9. Build the fact table: keys to each dimension, numeric measures, and calculated measures guarded against dividing by zero.
10. Validate the integration: every key in the fact table should match a row in its dimension.

Building the dimensions

dim_player

I only kept current players: 2025 is the latest season in the data, and old transfer prices would hurt the accuracy of future models.

CREATE OR REPLACE VIEW gold.dim_player AS
SELECT
    player_id,
    name AS player_name,
    date_of_birth,
    age,
    country_of_birth,
    country_of_citizenship,
    position,
    sub_position,
    foot,
    height_in_cm,
    international_caps,
    international_goals,
    market_value_in_eur,
    highest_market_value_in_eur,
    contract_expiration_date,
    agent_name
FROM silver.players_details
WHERE last_season = 2025;

dim_club

CREATE OR REPLACE VIEW gold.dim_club AS
SELECT
    club_id,
    name AS club_name,
    domestic_competition_id,
    country,
    squad_size,
    foreigners_number,
    average_age,
    net_transfer_record,
    national_team_players,
    coach_name
FROM silver.competitions_clubs;

dim_date

The date dimension isn't sourced from silver — it's generated, one row per day, so it has to be a table rather than a view. I added football-specific attributes, like the season and the stage of the season.

CREATE TABLE gold.dim_date AS
WITH date_spine AS (
    SELECT d::date AS full_date
    FROM generate_series('2000-01-01'::date, '2030-12-31'::date, '1 day') d
)
SELECT
    TO_CHAR(full_date, 'YYYYMMDD')::int  AS date_id,
    full_date,
    EXTRACT(DAY FROM full_date)::int     AS day_of_month,
    TO_CHAR(full_date, 'Day')            AS day_name,
    EXTRACT(MONTH FROM full_date)::int   AS month_number,
    TO_CHAR(full_date, 'Month')          AS month_name,
    -- The season runs August to July
    CASE WHEN EXTRACT(MONTH FROM full_date) >= 8
         THEN EXTRACT(YEAR FROM full_date)::int
         ELSE (EXTRACT(YEAR FROM full_date) - 1)::int
    END                                  AS season_start_year,
    EXTRACT(ISODOW FROM full_date) IN (6, 7) AS is_weekend,
    CASE
        WHEN EXTRACT(MONTH FROM full_date) IN (7, 8)          THEN 'Pre-season'
        WHEN EXTRACT(MONTH FROM full_date) IN (9, 10, 11, 12) THEN 'First half'
        WHEN EXTRACT(MONTH FROM full_date) = 1                THEN 'Winter window'
        WHEN EXTRACT(MONTH FROM full_date) IN (2, 3, 4, 5)    THEN 'Second half'
        WHEN EXTRACT(MONTH FROM full_date) = 6                THEN 'Summer window'
    END                                  AS season_stage
FROM date_spine;

ALTER TABLE gold.dim_date ADD PRIMARY KEY (date_id);

dim_transfer_type: a junk dimension

Whether a transfer is permanent or free are just yes/no flags that don't belong to any real entity. Putting them straight on the fact table would repeat the same combinations across thousands of rows, and you couldn't easily group by “transfer type” as a concept. So I made a tiny junk dimension with every valid combination, and the fact table carries one key.

CREATE TABLE gold.dim_transfer_type (
    transfer_type    VARCHAR PRIMARY KEY,
    is_permanent     BOOLEAN NOT NULL,
    is_free_transfer BOOLEAN NOT NULL
);

INSERT INTO gold.dim_transfer_type VALUES
    ('Paid Transfer',   TRUE,  FALSE),
    ('Free Transfer',   TRUE,  TRUE),
    ('Unknown',         FALSE, FALSE),
    ('Contract Expiry', FALSE, TRUE);

Building the fact table

The fact table has the classic shape: lots of keys, a date and numeric measures. The fee_value_ratio compares what a club paid with the player's market value — above 1 means they paid more than the player was valued at. It's guarded so it never divides by zero.

CREATE OR REPLACE VIEW gold.fact_transfers AS
SELECT
    ROW_NUMBER() OVER (ORDER BY t.transfer_date, t.player_id)::bigint AS transfer_id,
    t.player_id,
    t.from_club_id,
    t.to_club_id,
    TO_CHAR(t.transfer_date::date, 'YYYYMMDD')::int AS date_id,
    CASE
        WHEN t.to_club_name = 'Without Club' OR t.from_club_name = 'Without Club' THEN 'Contract Expiry'
        WHEN t.transfer_fee > 0 THEN 'Paid Transfer'
        WHEN t.transfer_fee = 0 THEN 'Free Transfer'
        ELSE 'Unknown'
    END AS transfer_type,
    t.transfer_fee,
    t.market_value_in_eur AS market_value,
    CASE
        WHEN t.market_value_in_eur > 0 THEN ROUND(t.transfer_fee / t.market_value_in_eur, 4)
        ELSE NULL  -- avoid dividing by zero
    END AS fee_value_ratio
FROM silver.players_transfers t
WHERE t.transfer_date IS NOT NULL
  AND t.to_club_name   NOT IN ('Retired', 'Career break')
  AND t.from_club_name NOT IN ('Retired', 'Career break')
  AND t.player_id    IN (SELECT player_id FROM gold.dim_player)
  AND t.from_club_id IN (SELECT club_id   FROM gold.dim_club)
  AND t.to_club_id   IN (SELECT club_id   FROM gold.dim_club);

Validating the result

Table	Rows
dim_player	18,146
dim_club	796
dim_date	11,323
dim_transfer_type	4
fact_transfers	35,962

The silver transfers table had about 157,000 rows, so the fact table keeps roughly 36,000 — a 77% reduction. That makes sense: it's scoped to current players and to clubs in the competitions covered. A final sanity check confirmed the transfer types were distributed sensibly and the measures weren't empty:

SELECT
    transfer_type,
    COUNT(*)               AS transfers,
    COUNT(transfer_fee)    AS has_fee,
    COUNT(market_value)    AS has_market_value,
    COUNT(fee_value_ratio) AS has_ratio
FROM gold.fact_transfers
GROUP BY transfer_type
ORDER BY transfers DESC;

Documenting the data product

Every data product needs a data catalogue: a description of each table and column, with an example, so people don't have to keep asking what the data means or how to join it. Here's the fact table:

What it enables

With the star schema in place, questions that used to need complex joins become simple queries: which clubs consistently pay above market value, how fees change through the season, or which transfer windows are busiest. It's also the foundation for machine learning — like predicting transfer fees, or the player similarity experiments I build on top of football data.