Spotify Ingestion

One of four sources feeding api_warehouse’s personal data platform. Spotify’s entire integration is a YAML file plus a thin pipeline class for the parts that genuinely need code — OAuth token handling and an incremental watermark. Everything else is handled by the warehouse’s generic, config-driven ingestion engine.

Endpoints

Five endpoints, executed in order, each writing to its own spotify.* table:

1. recently_played — me/player/recently-played, 50 at a time.
2. track — tracks?ids=..., batched from the distinct track_ids already sitting in spotify.recently_played.
3. album — albums?ids=..., batched from the distinct album_ids on those tracks, with refetch_if_null: images so an album stored before its artwork was captured gets picked up again.
4. artist — artists?ids=..., same pattern as album.
5. saved_tracks — me/tracks, currently disabled (is_active: false).

The chaining (source_table/source_column/is_distinct) is what makes this “only pull what’s missing”: each downstream endpoint derives its id list from what an earlier endpoint has already written, not from a separate query against Spotify.

Incremental fetching

recently_played uses a watermark, not the id-chaining trick — Spotify’s endpoint takes an after (Unix ms) cursor. The pipeline keeps a recently_played_watermark in the API’s persisted config, injects it as after on every run, and after a successful run recomputes it from MAX(played_at) in the stored table. Track/album/artist enrichment then only has to catch up on whatever ids that new slice of history introduced.

Auth

Standard OAuth 2.0 refresh-token flow: a TokenManager does a Basic-auth’d refresh against accounts.spotify.com/api/token and caches the access token until just before it expires. If Spotify rotates the refresh token during that exchange, the new one is written back into the API’s config row immediately — the database, not .env, is the source of truth after the very first run. That first run instead falls back to an interactive authorize-code flow.

Schema evolution

The spotify.* schema has been reshaped a few times as real usage exposed better designs:

• top_artists/top_tracks — periodic snapshot tables from Spotify’s top-items endpoints — were dropped entirely once that endpoint was disabled.
• images (JSONB) was added to both album and artist once artwork became worth capturing.
• Most recently, recently_played had its denormalized track_name/track_duration_ms/track_explicit/track_popularity columns dropped in favor of a join to track — a normalization cleanup that cascaded into rebuilding the staging view and the int_track_enriched intermediate model.

dbt modelling

• Staging — recently_played, track, album, artist: thin renames/casts over the raw tables.
• Intermediate — int_track_enriched joins track + artist + album, alongside a play-count CTE that rolls recently_played up into times_played, plays_last_7_days, plays_last_30_days, plays_last_365_days, and most_recent_play per track.
• Marts — dim_tracks, dim_albums, dim_artists for dimension lookups; fct_play_history for the raw event stream; fct_track_stats for per-track listening stats; fct_artist_stats, which rolls fct_track_stats up to artist level and converts total listening time to hours.

Engineering notes

The interesting part isn’t Spotify-specific at all — it’s that id-chaining plus refetch_if_null gets you both “don’t re-fetch what you have” and “do re-fetch what’s incomplete” without any bespoke diffing logic per source.