--- topic: Playlist Pages data model and Snowflake tables last_updated: 2026-03-16 --- # Playlist Pages Data Model ## Snowflake Environment - Production database: `FACTS`, schema: `PROD` - QA database: `FACTS`, schema: `QA` - All dbt objects use `_DBT` suffix; `upgrade_view` macro promotes to production-named views - `V_*` views in unioned layer are dbt view-materialized models ## Key Snowflake Tables / Views (FACTS.PROD) ### Chartmetric Ingestion — How Playlists Get Tracked There are three mechanisms by which playlists enter Chartmetric and ultimately flow into the playlist data pipeline: | Mechanism | Table | Chartmetric cadence | dbt tier | |-----------|-------|---------------------|----------| | Explicit priority register | `PRIORITY_PLAYLISTS` | **Every 5 minutes** via `L_SPOTIFY_PLAYLIST_SONY` (dedicated Chartmetric shared table, negotiated specifically for this use case) | `priority/` | | Explicit hourly register | `HOURLY_PLAYLISTS` | Hourly | `hourly/` | | Sony curator ownership | `sony_playlist_owners` | Chartmetric's standard rules (monthly for <150 followers) | `non_priority/` (via CLEAN_DIM_PLAYLIST_DBT) | | Sony-owned playlist register | `SONY_OWNED_PLAYLISTS` | — (static register, populated from temp table) | TBD (IN-16951) | **`sony_playlist_owners`** (`FACTS.{env}`) — A list of Spotify curator `user_identifier` values (e.g. `sonymusic`, `filtr`, `theorchardus`, ~100 entries, all `store_id=286`). Chartmetric automatically watches and scrapes ALL playlists owned by these accounts following its standard rules, including a once-a-month cadence for playlists with fewer than 150 followers. When Chartmetric picks up a new playlist from one of these curators, it appears in `CLEAN_DIM_PLAYLIST_DBT` and flows into the `non_priority/` dbt tier. No manual registration in `HOURLY_PLAYLISTS` or `PRIORITY_PLAYLISTS` is needed for these playlists to be tracked — they are covered automatically. DDL: `database/snowflake/FACTS/build/changelog/ddl/MAINT-apollo-playlist-authors_CREATE_sony_playlist_owners.sql` **Data flow summary**: - `priority_playlists` → `L_SPOTIFY_PLAYLIST_SONY` replicates every **5 min** → Snowflake stream → priority task DAG (~1 min processing) → **~6 min end-to-end from Spotify publish** - `hourly_playlists` → Chartmetric scrapes **hourly** → dbt `hourly/` tier → **~1-2 hours end-to-end** - `sony_playlist_owners` curator accounts → Chartmetric scrapes on **standard cadence** (monthly for <150 followers) → `CLEAN_DIM_PLAYLIST_DBT` → dbt `non_priority/` tier **Note on `L_SPOTIFY_PLAYLIST_SONY`**: This is a dedicated Chartmetric shared table negotiated specifically for priority playlists. It replicates every 5 minutes — far faster than Chartmetric's standard ~1-2 hour cadence. The "Playlist Freshness Improvements" design doc describes 1-2 hour latency as the *problem* that motivated this work, not a property of the current system. ### Playlist Register Tables (seeds/managed) | Table | Owner | Purpose | | ------- | ------- | --------- | | `PRIORITY_PLAYLISTS` | Snowflake task graph | High-priority playlists — near-real-time via L_SPOTIFY_PLAYLIST_SONY | | `HOURLY_PLAYLISTS` | Snowflake tasks (gsheets sync) | Hourly-scraped playlists (Spotify + Apple Music) | | `sony_playlist_owners` | Static/manual | Sony curator accounts — Chartmetric auto-tracks all their playlists | | `playlist_filter_override` | dbt seed | Manual overrides to move playlists between tiers | ### HOURLY_PLAYLISTS Schema Columns: `chartmetric_playlist_id`, `store_id`, `store_playlist_id`, `playlist_name`, `code2` - Spotify playlists: `store_id = 286` - Apple Music playlists: `store_id = 1` - Populated by Snowflake tasks from Fivetran-synced Google Sheet (IN-16924) ### External Interface Views (stable — referenced by all consumers) | View | Notes | |------|-------| | `V_PLAYLIST_METADATA` | **Primary header data source** — full metadata incl. PLAYLIST_FOLLOWER_COUNT, PLAYLIST_TRACK_COUNT, SMG_PERCENT, FRONTLINE_PERCENT, LOCAL_PERCENT, PRIMARY_PLAYLIST_TYPE, OWNER. Powers both PlaylistPageHeader and PlaylistPageHeaderMetrics via graphql-knowledge. | | `V_PLAYLISTS_BY_PLAYLIST` | Core playlist metadata subset (no percent breakdowns). Not the header metrics source — see V_PLAYLIST_METADATA. | | `V_PLAYLISTS_BY_PLAYLIST_CURRENT_TRACKLIST` | Current tracklist; includes INFERRED_COMPILATION, FIRST_ADDED_ON_DATE | | `V_HISTORICAL_PLAYLIST_TRACKLISTS_AGGREGATED` | Aggregated historical tracklists | | `V_HISTORICAL_PLAYLIST_TRACKLISTS_BY_MARKET` | Historical tracklists by market/storefront | | `V_PLAYLISTS_FOLLOWERS_BY_PLAYLIST_DATE_SPOTIFY` | Spotify followers timeseries (all tiers unioned) | | `V_PLAYLISTS_PLACEMENT_EVENTS_BY_ISRC_PLAYLIST` | Placement add/remove/change events | | `V_PLAYLISTS_PLACEMENTS_BY_ISRC_PLAYLIST_PUBLIC` | Placements (public, ISRC-scoped) | | `V_PLAYLISTS_PLACEMENTS_BY_ISRC_PLAYLIST_PRIVATE` | Placements (private, ISRC-scoped) | | `V_PLAYLISTS_PLACEMENTS_BY_ISRC_PLAYLIST_PRIVATE_CLEAN` | Cleaned private placements | | `V_PLAYLISTS_PLACEMENTS_BY_ISRC_PLAYLIST_COUNTRY_PRIVATE` | Private with country dimension | | `V_PLAYLISTS_PLACEMENTS_BY_PARTICIPANT_ISRC_PLAYLIST_*` | Participant-scoped variants (PUBLIC/PRIVATE/COUNTRY_PRIVATE) | | `V_PLAYLISTS_PLACEMENTS_BY_TRACK_PLAYLIST_DISTRIBUTOR_PUBLIC` | Recent placements (all distributors) | | `V_PLAYLISTS_PLACEMENTS_BY_TRACK_PLAYLIST_DISTRIBUTOR_PUBLIC_ONLY_FRESHEST` | Most recent per track-playlist pair | | `V_STREAMS_BY_PLAYLIST_COUNTRY_FEED_DISTRIBUTOR_DAILY` | Streams timeseries (PoT chart) | | `V_DEMOGRAPHICS_BY_PLAYLIST_COUNTRY` | Spotify demographics by country | | `V_DIM_EDITORIAL_PLAYLIST_TOP_NON_EDITORIAL_PLAYLIST` | Editorial + top non-editorial rankings | ### Priority-only Sources (managed by Snowflake task graph, NOT dbt) These are declared as dbt sources in `unioned/sources.yml`: - `PLAYLISTS_PRIORITY_BY_PLAYLIST_CURRENT_TRACKLIST` - `PLAYLISTS_PRIORITY_PLACEMENTS_BY_ISRC_PLAYLIST_PUBLIC` - `PLAYLISTS_PRIORITY_PLACEMENTS_BY_PARTICIPANT_ISRC_PLAYLIST_PUBLIC` - `PLAYLISTS_PRIORITY_PLACEMENTS_BY_PLAYLIST_PUBLIC` - `PLAYLISTS_PRIORITY_PLACEMENTS_BY_TRACK_PLAYLIST_DISTRIBUTOR_PUBLIC` - `PLAYLISTS_PRIORITY_PLACEMENTS_BY_TRACK_PLAYLIST_DISTRIBUTOR_PUBLIC_ONLY_FRESHEST` - `RECENT_PLAYLISTS_PRIORITY_PLACEMENTS_BY_TRACK_PLAYLIST_DISTRIBUTOR` - `RECENT_PLAYLISTS_PRIORITY_PLACEMENTS_BY_TRACK_PLAYLIST_DISTRIBUTOR_ONLY_FRESHEST` - `PRIORITY_PLAYLIST_METADATA` ### Analytics/Streams Tables | Table/View | Notes | |-----------|-------| | `V_STREAMS_BY_PLAYLIST_COUNTRY_FEED_DISTRIBUTOR_DAILY` | PoT streams timeseries | | `V_PLAYLISTS_FOLLOWERS_BY_PLAYLIST_DATE_SPOTIFY` | Followers (Spotify only, store_id=286) | | `V_DEMOGRAPHICS_BY_PLAYLIST_COUNTRY` | Spotify demographics | | `STREAMS_BY_PLAYLIST_ROLLUP` | Aggregate streams by playlist (global) | | `STREAMS_BY_PLAYLIST_COUNTRY_ROLLUP` | Streams by playlist + country | | `STREAMS_BY_PLAYLIST_FEED_ROLLUP` | Streams by playlist + feed | | `STREAMS_BY_PLAYLIST_COUNTRY_FEED_ROLLUP` | Streams by playlist + country + feed | ## V_PLAYLIST_METADATA Column Reference **UI role**: This view is the source for both `PlaylistPageHeader` (identity) and `PlaylistPageHeaderMetrics` (metrics) in the Insights frontend. It is queried via graphql-knowledge's `playlistMetadataById` DataLoader (Redis TTL: 1 hour). All 20 columns (confirmed 2026-04-22): ``` STORE_PLAYLIST_ID -- platform-native ID (PK with STORE_ID) STORE_ID -- 286=Spotify, 1=Apple Music PLAYLIST_NAME PLAYLIST_FOLLOWER_COUNT -- header metric PLAYLIST_TRACK_COUNT -- header metric PLAYLIST_ARTWORK_URL PLAYLIST_URI PLAYLIST_DESCRIPTION -- can be NULL PLAYLIST_TYPE -- e.g. CURATED, EDITORIAL PRIMARY_PLAYLIST_TYPE -- e.g. New Music Friday; NULL if uncategorized PLAYLIST_GENRES -- VARIANT, can be NULL CURATOR_NAME CURATOR_ID CURATOR_COUNTRY CREATED_AT LAST_UPDATED FRONTLINE_PERCENT -- VARIANT; circular header metric #1 — share of frontline tracks SMG_PERCENT -- VARIANT; circular header metric #2 — share of SMG tracks LOCAL_PERCENT -- VARIANT; circular header metric #3 — share of tracks whose ISRC country prefix matches the user's context market (e.g. USXXX = local to USA) OWNER -- Sony | Universal | Spotify | Spotify Radio | Brand | Tastemaker | Other Label | User ``` **The three circular header metrics** are `FRONTLINE_PERCENT`, `SMG_PERCENT`, and `LOCAL_PERCENT`. These are the only fields driving the donut rings in `PlaylistPageHeaderMetrics`. `PRIMARY_PLAYLIST_TYPE` is **not** a header metric. It is a sub-categorization field used solely to flag "Hot Hits" designation. `PLAYLIST_TYPE` (e.g. CURATED, EDITORIAL) is the canonical playlist type field displayed in the header. **NULL header metrics diagnosis**: If `FRONTLINE_PERCENT`, `SMG_PERCENT`, or `LOCAL_PERCENT` are NULL, the corresponding circular metric ring will be blank in the UI. Root cause: the dbt track-categorization models have not resolved values — most often because the playlist contains no Orchard-distributed (DDEX-registered) tracks that can be classified. ## V_PLAYLISTS_BY_PLAYLIST_CURRENT_TRACKLIST Column Reference Key columns: ``` ISRC, STORE_ID, STORE_PLAYLIST_ID, STOREFRONT, CURRENT_POSITION, PEAK_POSITION, PREVIOUS_POSITION, POSITION_CHANGE, LAST_ADDED_ON_DATE, FIRST_ADDED_ON_DATE, REMOVED_ON, DAYS_ON_PLAYLIST, STOREFRONT_COUNT, STREAMS_COUNTRY, FIRST_PLAY, STREAMS_ALL_TIME, STREAMS_LAST_365_DAYS, STREAMS_LAST_183_DAYS, STREAMS_LAST_28_DAYS, STREAMS_LAST_7_DAYS, STREAMS_LAST_1_DAY, COMPLETIONS_* (multiple periods), PLAYLIST_TYPE, PLAYLIST_NAME, PLAYLIST_ARTWORK_URL, PLAYLIST_TRACK_COUNT, PLAYLIST_FOLLOWER_COUNT, PLAYLIST_URI, CURATOR_NAME, CURATOR_ID, CURATOR_COUNTRY, GSR_ID, CHARTMETRIC_TRACK_NAME, CHARTMETRIC_ARTIST_NAME, CHARTMETRIC_ARTWORK_URL, INFERRED_COMPILATION, LABEL_IDS, SUBACCOUNT_IDS, LABEL_PARTICIPANT_IDS ``` ## Snowflake Task Infrastructure (IN-16924) Two Snowflake tasks sync playlists from Fivetran-synced Google Sheet: **GSHEETS_TO_HOURLY_PLAYLISTS** - Schedule: prod=10 min, QA=hourly - Syncs `store_id=286` (Spotify) rows into `HOURLY_PLAYLISTS` - Source: `ORCHARD_APP_REPORTING_V2.{env}_INSIGHTS_PLAYLIST_PAGES_GSHEETS_SYNC.SPOTIFY_HOURLY_PLAYLISTS` - Excludes playlists already in `PRIORITY_PLAYLISTS` **GSHEETS_TO_AM_HOURLY_PLAYLISTS** - Schedule: prod=10 min, QA=hourly - Syncs `store_id=1` (Apple Music) rows into `HOURLY_PLAYLISTS` - Source: `ORCHARD_APP_REPORTING_V2.{env}_INSIGHTS_PLAYLIST_PAGES_GSHEETS_SYNC.APPLE_MUSIC_HOURLY_PLAYLISTS` Both tasks use dedicated role: `{env}_INSIGHTS_PLAYLIST_PAGES_GSHEETS_SYNC_TASK_ROLE` Role has: `ORCHARD_APP_REPORTING_V2.DB_{env}_INSIGHTS_PLAYLIST_PAGES_GSHEETS_SYNC_SCHEMA_READ` DDL location: `/Users/cbeesley/code/database/snowflake/FACTS/build/changelog/ddl/snowflake_tasks/insights_playlist_lists_sync/` ## Listeners Metric — Definition and Caveats **Formula**: `COUNT(DISTINCT storeuserid_varchar)` from `FACT_ANALYTICS` **Calculation chain**: 1. Track+playlist+country+feed level: `STREAMS_BY_TRACK_PLAYLIST_COUNTRY_FEED_DISTRIBUTOR_DAILY_RECENT_DBT.sql` line 47 2. Rolled up to playlist level (without track dimension): `SUM(listeners)` in `STREAMS_BY_PLAYLIST_COUNTRY_FEED_DISTRIBUTOR_DAILY_RECENT_DBT.sql` line 31 **Critical caveats**: - **Spotify only** — Apple Music sets `listeners = NULL` (line 96 of the track-level model, with a TODO comment). Never show listeners for Apple Music playlists. - **Spotify periodically resets `storeuserid_varchar`** — this makes `COUNT(DISTINCT storeuserid_varchar)` aggregations over longer time periods inaccurate. The code contains an explicit warning about this. Long-range listener totals should be treated as approximations, not exact counts. ## Insights vs Apollo Stream Count Discrepancy **Insights playlist streams will always be lower than Apollo for the same playlist and date period.** This is by design, not a bug. ### Root causes **1. Video streams excluded (primary cause — SME-heavy)** Spotify sends both audio and video streams in the raw feed. Apollo counts everything from `staging_raw_spotify_v2` directly. Insights joins through `fact_analytics` → `dim_track`, which only contains products registered via DDEX. We do not receive video content via DDEX. Video ISRCs/UPCs exist in GRPS (`MEDIA_TYPE_NAME = 'Video'`) but are absent from our DIM tables and are silently dropped during the FA join. Data flow: `staging_raw_spotify_v2` (all streams, matches Apollo) → `fact_analytics` via `swf-feed-ingestion/.../load_staging_fact.sql` (drops rows where product not in DIM tables) → dbt → Insights. **2. Initially missing products (minor)** If a product is created in our system after its streams arrive, the first days of streams are permanently lost in FA even after the product is eventually backfilled. The product joins to dim_track as NULL and is dropped. ### Confirmed via investigation (2026-02-06) Playlist `37i9dQZF1DXcBWIGoYBM5M` (Today's Top Hits), 2026-01-08 → 2026-02-04: - Apollo / staging_raw: **26,324,166** - Insights / fact_analytics: **26,235,080** - Gap: **~89,086** (~4.4k from late-created products; ~85k from video ISRCs not in dim_track) ### Diagnostic query ```sql WITH fa AS ( SELECT dt.isrc, fa.releaseid AS upc, SUM(fa.units) AS streams FROM fact_analytics fa INNER JOIN CLEAN_DIM_PLAYLIST_DBT dp ON fa.playlistid = dp.playlistid INNER JOIN dim_track_clean_mv dt ON dt.trackid = fa.trackid WHERE dp.storeplaylistid = '' AND download_activity_date BETWEEN '' AND '' GROUP BY 1, 2 ), raw AS ( SELECT isrc, upc, COUNT(*) AS streams FROM staging_raw_spotify_v2 WHERE download_date BETWEEN '' AND '' AND source_uri LIKE '%%' GROUP BY 1, 2 ) SELECT fa.isrc, fa.upc, fa.streams AS fa_streams, raw.isrc AS raw_isrc, raw.upc AS raw_upc, raw.streams AS raw_streams, COALESCE(fa.streams, 0) - COALESCE(raw.streams, 0) AS diff FROM fa FULL OUTER JOIN raw ON fa.isrc = raw.isrc AND fa.upc = raw.upc ORDER BY diff DESC; ``` Rows in `raw` but not `fa` (diff < 0 with NULL fa columns) = products missing from DIM. Check GRPS for `MEDIA_TYPE_NAME = 'Video'` to confirm video cause. ### Status Not a playlist pages bug — systemic across all Insights vs Apollo stream comparisons. Open question: whether to add a UI disclaimer in Insights that video streams are excluded. (Raised by Farouk/Haley 2026-02-06, no resolution yet.) ## Streams/Demographics/Followers Coverage Gap [Retained from prior memory — still accurate as of 2026-03-16] **Streams** (V_STREAMS_BY_PLAYLIST_COUNTRY_FEED_DISTRIBUTOR_DAILY): - Lineage: fact_analytics → streams aggregates → view - Coverage requires Orchard-distributed content to have been streamed from the playlist - Hourly/non-priority playlists may produce no rows if no Orchard tracks present **Demographics** (V_DEMOGRAPHICS_BY_PLAYLIST_COUNTRY): - Raw Spotify demographic feed — no allowlist filter - Coverage gap same root cause: Spotify only sends data when Orchard content streamed **Followers** (V_PLAYLISTS_FOLLOWERS_BY_PLAYLIST_DATE_SPOTIFY): - Comes from Chartmetric, which tracks all Spotify playlists regardless of Orchard content - ~82% coverage vs ~37% for streams/demographics ## OWNER Classification Categories Apple Curators | Amazon Curators | Spotify | Spotify Radio | Sony | Universal | Warner | Brand | Tastemaker | Other Label | User --- ## FAQ: Missing Cover Art (playlistArtworkUrl is NULL) **Ticket**: IN-16874 **Symptom**: Playlist shows no cover art in Insights but has cover art on Spotify. **Root cause**: Chartmetric stopped scraping the playlist and marked it `active = false`. The `PLAYLIST_ARTWORK_URL` stored in our Snowflake metadata reflects the last Chartmetric scrape — which may be an expired Spotify CDN URL, or NULL if no URL was ever captured. **How to confirm**: ```sql -- Check Chartmetric's raw data for the playlist SELECT * FROM chartmetric.raw_data.spotify_playlist WHERE playlist_id = ''; -- Look for active = false and an expired image URL -- Cross-check our metadata SELECT * FROM facts.prod.hourly_playlist_metadata WHERE store_playlist_id = ''; ``` **How to detect without Snowflake**: Check the playlist header in Insights — if Chartmetric stopped scraping, the `active` field in the header debug info will be false. **Fix**: Trigger a Chartmetric refetch: 1. Find the playlist in Chartmetric: `https://app.chartmetric.com/playlist/spotify//about` 2. Click the **Refetch** button on the playlist page 3. Wait a few hours — Chartmetric will re-scrape, re-activate the playlist, and the updated `PLAYLIST_ARTWORK_URL` will flow back through our pipeline into `V_PLAYLIST_METADATA` **Note**: This issue is particularly common for hourly playlists with few followers that Chartmetric de-prioritizes after a period of low activity. It is a Chartmetric data staleness issue, not a bug in our code.