Level 1 Case StudyFull-Stack Systems / Jul 2026

Spotify Personal Analytics Platform - Full-Stack Data Analytics App

I built a personal analytics platform that processes a 71K+ event Spotify streaming-history export through a 4-stage Python ETL pipeline and serves insights via a FastAPI backend and React/MUI dashboard.

71K+ Spotify streaming events processed13K+ tracks enriched with genre metadata13.8K tracks processed via two-stage lyrics ingestion16 SQL RPC functions over a materialized-view analytics schema

TL;DR

I built a personal analytics platform that processes a 71K+ event Spotify streaming-history export through a 4-stage Python ETL pipeline and serves insights via a FastAPI backend and React/MUI dashboard.
Best published result: 71K+ Spotify streaming events processed

Problem

Turn a raw Spotify GDPR streaming-history export into queryable, enriched personal listening analytics Personal use - anyone wanting deeper insight into their own Spotify listening history than the built-in Spotify Wrapped/stats provide. Raw GDPR exports are unenriched JSON with no genre/lyrics context and no queryable interface.

My Role

personal build with end-to-end ownership
Problem framing: Turn a raw Spotify GDPR streaming-history export into queryable, enriched personal listening analytics
Architecture: 4-stage Python ETL pipeline (dedup/extraction -> metadata + genre enrichment -> two-stage lyrics ingestion -> validation) + FastAPI backend
Implementation: PostgreSQL (Supabase) analytics schema with materialized views, composite/partial indexes, 16 SQL RPC functions
Evaluation: 71K+ Spotify streaming events processed; 13K+ tracks enriched with genre metadata; 13.8K tracks processed via two-stage lyrics ingestion; 16 SQL RPC functions over a materialized-view analytics schema
Before: Raw GDPR exports are unenriched JSON with no genre/lyrics context and no queryable interface
Personally designed: Built a 4-stage ETL pipeline with custom rate limiting and exponential-backoff retry handling because Spotify Web API enrichment for 71K+ events needed resilient handling of rate limits without dropping data; Built a genre-enrichment pipeline by joining artist-level metadata because Spotify's API doesn't expose track-level genre directly, so joining via artist metadata was needed to enrich 13K+ tracks; Two-stage lyrics ingestion (batch + retry) with checkpointing and multi-source API fallback because Processing 13.8K tracks against lyrics APIs needed resilience to per-source failures and the ability to resume from checkpoints, with automatic failure-based source disabling; Designed a PostgreSQL analytics schema with materialized views and 16 SQL RPC functions because Enables low-latency pre-aggregated queries over streaming history instead of computing aggregates at request time
Others owned: No separate collaborator-owned subsystem is published in the source data.

Constraints

Built Jul 2026 (personal). Spotify API rate limits; multi-source lyrics API reliability.

Architecture

Input: Spotify GDPR streaming-history JSON export
Backend: 4-stage Python ETL pipeline (dedup/extraction -> metadata + genre enrichment -> two-stage lyrics ingestion -> validation) + FastAPI backend
Data & storage: PostgreSQL (Supabase) analytics schema with materialized views, composite/partial indexes, 16 SQL RPC functions
External APIs: Spotify Web API (spotipy), Genius API (lyricsgenius), musixmatch-api
Output: React/MUI dashboard with charts (MUI X Charts) over pre-aggregated analytics queries

FlowSpotify Analytics system flow

4-stage Python ETL pipeline (dedup/extraction -> metadata + genre enrichment -> two-stage lyrics ingestion -> validation) + FastAPI backend; PostgreSQL (Supabase) analytics schema with materialized views, composite/partial indexes, 16 SQL RPC functions; React/MUI dashboard with charts (MUI X Charts) over pre-aggregated analytics queries

input 01Input
Spotify GDPR streaming-history JSON export
process 02Backend
input ->
4-stage Python ETL pipeline (dedup/extraction -> metadata + genre enrichment -> two-stage lyrics ingestion -> validation) + FastAPI backend
storage 03Data / storage
backend ->
PostgreSQL (Supabase) analytics schema with materialized views, composite/partial indexes, 16 SQL RPC functions
external 04External APIs
backend ->
Spotify Web API (spotipy), Genius API (lyricsgenius), musixmatch-api
output 05Output
storage ->external ->
React/MUI dashboard with charts (MUI X Charts) over pre-aggregated analytics queries

Routes

Input -> Backend
Backend -> Data / storage
Backend -> External APIs
Data / storage -> Output
External APIs -> Output

Key Technical Decisions

Built a 4-stage ETL pipeline with custom rate limiting and exponential-backoff retry handling
Built a genre-enrichment pipeline by joining artist-level metadata
Two-stage lyrics ingestion (batch + retry) with checkpointing and multi-source API fallback
Designed a PostgreSQL analytics schema with materialized views and 16 SQL RPC functions

Implementation

Input layer: Spotify GDPR streaming-history JSON export
Core system: 4-stage Python ETL pipeline (dedup/extraction -> metadata + genre enrichment -> two-stage lyrics ingestion -> validation) + FastAPI backend
Data layer: PostgreSQL (Supabase) analytics schema with materialized views, composite/partial indexes, 16 SQL RPC functions
External boundary: Spotify Web API (spotipy), Genius API (lyricsgenius), musixmatch-api
User output: React/MUI dashboard with charts (MUI X Charts) over pre-aggregated analytics queries

What Broke / What Didn't Work

Rejected: Naive sequential API calls without backoff. Chosen path: Built a 4-stage ETL pipeline with custom rate limiting and exponential-backoff retry handling.
Rejected: Skipping genre enrichment entirely. Chosen path: Built a genre-enrichment pipeline by joining artist-level metadata.
Rejected: Single-source lyrics fetch without fallback. Chosen path: Two-stage lyrics ingestion (batch + retry) with checkpointing and multi-source API fallback.
Rejected: Computing all aggregates on-demand in the API layer. Chosen path: Designed a PostgreSQL analytics schema with materialized views and 16 SQL RPC functions.
Materialized views add refresh/maintenance overhead but keep dashboard queries fast
Multi-source lyrics fallback increases pipeline complexity but improves coverage across 13.8K tracks

Results

71K+ Spotify streaming events processed - Scale of ETL pipeline input - Scale of ETL pipeline input - master-resume
13K+ tracks enriched with genre metadata - Coverage of genre-enrichment pipeline - Coverage of genre-enrichment pipeline - master-resume
13.8K tracks processed via two-stage lyrics ingestion - Coverage of lyrics ingestion pipeline - Coverage of lyrics ingestion pipeline - master-resume
16 SQL RPC functions over a materialized-view analytics schema - Query layer supporting low-latency pre-aggregated analytics - Query layer supporting low-latency pre-aggregated analytics - master-resume

What I'd Change Now

Add year-over-year comparison views
Support multi-user accounts
Add playlist-level analytics

Stack

React 19.1.1
TypeScript 5.9.3
Vite 7.1.7
Material UI 7.3.4
MUI X Charts 8.14.0
Zustand 5.0.8
React Router 7.9.4
Axios 1.12.2
Python
FastAPI
Uvicorn
Pydantic
pandas
scikit-learn
PostgreSQL (Supabase)
spotipy
lyricsgenius
musixmatch-api
Netlify
Render

Ask me about the trade-offs.

Why this architecture boundary exists: 4-stage Python ETL pipeline (dedup/extraction -> metadata + genre enrichment -> two-stage lyrics ingestion -> validation) + FastAPI backend
How I evaluated Scale of ETL pipeline input
The hardest tradeoff: Materialized views add refresh/maintenance overhead but keep dashboard queries fast
What I would change next: Add year-over-year comparison views

Contact me More projects