spotify_etl

The spotify_etl project processes Spotify data from both the GDPR data releases and the developer API. This is being created for personal use and as an excercise in good practice ETL process design. It adheres to the medallion data architecture and seeks to perform the whole ETL task in a structured, maintainable and idiomatic way.

It is designed to reduce storage and especially compute load on the paired Neon cloud database, so it can remain on the free-tier.

Note

This project is a work-in-progress.

ETL Architecture

Here is a simplified mapping of the ETL workflow:

flowchart LR

%% Sources
subgraph SOURCES
    DataGDPR["GDPR Download\n(JSON)"]
    DataAPI["Spotify Dev API"]
end

%% Neon Database
subgraph NEON["NEON DATABASE"]
    SchemaBronze[("Bronze Schema")]
    SchemaSilver[("Silver Schema")]
    SchemaGold[("Gold Schema")]
end

%% Python
subgraph LOCAL["PYTHON"]

    subgraph MODELS["Pydantic Modelling"]
        IngestModels["Source-Agnostic Models<br/><div align='left'>
        • Validation<br/>
        • Normalisation<br/>
        • PK/FK generation<br/>
        </div>"]

        Adapters["Source Adapters<br/><div align='left'>
        • GDPR mapping<br/>
        • API mapping<br/>
        </div>"]

        Adapters --> IngestModels
    end

    subgraph ETL["ETL Processes"]
        ETLBronze["Bronze ETL"]
        ETLSilver["Silver ETL"]
        ETLGold["Gold ETL"]
    end
end

%% Main Flow
DataGDPR --> Adapters
DataAPI --> Adapters

IngestModels --> ETLBronze

ETLBronze --> SchemaBronze
SchemaBronze --> ETLSilver

ETLSilver <--> SchemaSilver
SchemaSilver --> ETLGold

ETLGold --> SchemaGold

Loading

Quick links

• spotify_etl/utils/api.py : Typed interface for managing Spotify API user tokens for use in the tekore library. Includes patching workflow for outdated Pydantic models.
• spotify_etl\utils\json_records.py : Streamlined discovery and collection of json files across directories of arbitrary depth.
• spotify_etl\_generate_models.py : Automatic generation of draft Pydantic models derived from raw JSON files.
• spotify_etl\_generate_sql.py : Automatic generation of CREATE TABLE statements to perfectly match provided Pydantic models.
• spotify_etl/models : Detailed Pydantic models with UUID generation, PK/FK distribution through nested source structures.
• spotify_etl\bronze.py : ETL for the Bronze layer.
• spotify_etl\silver.py : ETL for the Silver layer.
• run.py : Main runner for the entire ETL process across layers.

Todo

Updated 2026-06-11:

• Modelling and ingestion is done for the GDPR data. Do the same for the Spotify API data.
• MDM modelling and transformations in the Silver layer.
• ETL for the Gold layer.