---
topic: dbt-analytics playlist models
last_updated: 2026-03-16
---

# dbt-analytics Playlist Models

Repo: `/Users/cbeesley/code/dbt-analytics`
Package root: `playlists/`
Package config: `playlists/dbt_project.yml`

## Tier Split Architecture (IN-16920, merged ~2026-02-xx)

The playlists dbt package was **fully split into three tiers** plus a unioned layer. This is the most important structural change to understand.

### Three Tiers

| Tier | dbt Tag | Source Filter Logic |
|------|---------|---------------------|
| `priority` | `priority_playlists` | Joins `CLEAN_DIM_PLAYLIST_DBT` to `priority_playlists` seed (OR `playlist_filter_override`) |
| `hourly` | `hourly_playlists` | Joins to `hourly_playlists` seed; excludes `playlist_filter_override` overrides |
| `non_priority` | `non_priority_playlists` | Everything in CLEAN_DIM_PLAYLIST_DBT NOT in hourly, NOT in priority, NOT in filter_override |

Key files for the filter logic:

- `playlists/models/priority/filtered_sources/PRIORITY_DIM_PLAYLIST_DBT.sql` — WHERE EXISTS in priority_playlists OR playlist_filter_override
- `playlists/models/hourly/filtered_sources/HOURLY_DIM_PLAYLIST_DBT.sql` — WHERE EXISTS in hourly_playlists AND NOT in playlist_filter_override
- `playlists/models/non_priority/filtered_sources/NON_PRIORITY_DIM_PLAYLIST_DBT.sql` — NOT in hourly, NOT in priority, NOT in filter_override

### Unioned Views Layer

All external consumers (ows-playlist, graphql-knowledge, graphql-analytics) now reference `V_` prefixed views that UNION ALL three tiers:

| View | Location | Purpose |
|------|----------|---------|
| `V_PLAYLISTS_BY_PLAYLIST` | `unioned/by_playlist/` | Core playlist metadata (no percent breakdowns, no OWNER) — used for placement metadata in ows-playlist |
| `V_PLAYLIST_METADATA` | `unioned/by_playlist/` | **Primary header data source** — full metadata incl. FRONTLINE_PERCENT, SMG_PERCENT, LOCAL_PERCENT, OWNER. Powers `PlaylistPageHeader` and `PlaylistPageHeaderMetrics` via graphql-knowledge's `playlistMetadataById` DataLoader. |
| `V_PLAYLISTS_BY_PLAYLIST_CURRENT_TRACKLIST` | `unioned/by_playlist/` | Current tracklist with INFERRED_COMPILATION, FIRST_ADDED_ON_DATE |
| `V_HISTORICAL_PLAYLIST_TRACKLISTS_AGGREGATED` | `unioned/by_playlist/` | Historical tracklists aggregated |
| `V_HISTORICAL_PLAYLIST_TRACKLISTS_BY_MARKET` | `unioned/by_playlist/` | Historical tracklists by market |
| `V_PLAYLISTS_FOLLOWERS_BY_PLAYLIST_DATE_SPOTIFY` | `unioned/followers/` | Spotify followers timeseries |
| `V_PLAYLISTS_PLACEMENT_EVENTS_BY_ISRC_PLAYLIST` | `unioned/placement_events_by_isrc_playlist/` | Placement add/remove/change events |
| `V_PLAYLISTS_PLACEMENTS_BY_ISRC_PLAYLIST_PUBLIC` | `unioned/placements_by_isrc_playlist/` | Public placements by ISRC |
| `V_PLAYLISTS_PLACEMENTS_BY_ISRC_PLAYLIST_PRIVATE` | `unioned/placements_by_isrc_playlist/` | Private placements (with label access) |
| `V_PLAYLISTS_PLACEMENTS_BY_ISRC_PLAYLIST_PRIVATE_CLEAN` | `unioned/placements_by_isrc_playlist/` | Private placements cleaned |
| `V_PLAYLISTS_PLACEMENTS_BY_ISRC_PLAYLIST_COUNTRY_PRIVATE` | `unioned/placements_by_isrc_playlist/` | Private with country |
| `V_PLAYLISTS_PLACEMENTS_BY_PARTICIPANT_ISRC_PLAYLIST_*` | `unioned/placements_by_participant_isrc_playlist/` | Participant-scoped variants |
| `V_PLAYLISTS_PLACEMENTS_BY_TRACK_PLAYLIST_DISTRIBUTOR_PUBLIC` | `unioned/placements_by_track_playlist_distributor/` | Recent placements |
| `V_PLAYLISTS_PLACEMENTS_BY_TRACK_PLAYLIST_DISTRIBUTOR_PUBLIC_ONLY_FRESHEST` | `unioned/placements_by_track_playlist_distributor/` | Freshest only |
| `V_DIM_EDITORIAL_PLAYLIST_TOP_NON_EDITORIAL_PLAYLIST` | `unioned/editorial_playlists/` | Editorial + top non-editorial |
| `V_GSR_AND_CHARTMETRIC_METADATA` | `unioned/global_sound_recording_isrc/` | GSR + Chartmetric metadata |
| `V_PLAYLISTS_PLACEMENTS_BY_ISRC_PLAYLIST_PUBLIC` | `unioned/` | Public placements (ISRC) |

**Important**: The `unioned/sources.yml` also declares several **priority tables managed by the Snowflake task graph (not dbt)**:

- `PLAYLISTS_PRIORITY_BY_PLAYLIST_CURRENT_TRACKLIST` (source, not dbt model)
- `PLAYLISTS_PRIORITY_PLACEMENTS_BY_*` tables
- `RECENT_PLAYLISTS_PRIORITY_PLACEMENTS_BY_*`
- `PRIORITY_PLAYLIST_METADATA`

This means priority tracklist/placements data is produced by Snowflake tasks, then referenced as dbt sources in the unioned views.

### State Models (Incremental)

Each tier has a `_STATE_DBT` incremental model that tracks `prev_modified_at` and `next_modified_at` for each source table (Amazon, Apple, Deezer, Spotify, YouTube). Used for incremental build logic via pre-hook macros.

- `PLAYLISTS_PRIORITY_STATE_DBT` → tagged `priority_playlists`
- `PLAYLISTS_NON_PRIORITY_STATE_DBT` → tagged `non_priority_playlists`
- `PLAYLISTS_HOURLY_STATE_DBT` → tagged `hourly_playlists`

State vars in dbt_project.yml:

- `playlists_priority_state`
- `playlists_non_priority_state`
- `playlists_hourly_state`

### Subdirectory Structure per Tier

Each of `priority/`, `non_priority/`, `hourly/` contains:

- `by_playlist/` — main placement counts, current tracklist, metadata, track company brand
- `editorial_playlists/` — editorial + top non-editorial ranking models
- `filtered_sources/` — per-store filtered dim tables (SPOTIFY, ITUNES, DEEZER, AMAZON, YOUTUBE + stat/history tables)
- `global_sound_recording_isrc/` — GSR + Chartmetric metadata staging
- `placement_events_by_isrc_playlist/` — add/remove/change events per store
- `placements_by_isrc_playlist/` — placements by ISRC, per store staging models
- `placements_by_participant_isrc_playlist/` — participant-scoped placements
- `playlist_merged_to_playlist/` — merged playlist tracking
- `followers/` — Spotify followers timeseries per tier
- `state/` — incremental state tracking model

`non_priority/` additionally has:

- `placements_by_track_playlist_distributor/` — recent placements table (non-priority only; priority version is managed by Snowflake tasks)

`hourly/` additionally has:

- `filtered_sources/HOURLY_DIM_ALGORITHMIC_PLAYLISTS_DBT.sql`
- `filtered_sources/HOURLY_STREAMS_BY_PLAYLIST_*` — streams rollup filtered sources

## OWNER Column (IN-16779, IN-16779 addl.)

The `CLASSIFY_PLAYLIST_OWNER` Snowflake UDF classifies curator ownership into categories:

- **Apple Curators**, **Amazon Curators**, **Spotify**, **Spotify Radio**
- **Sony**, **Universal**, **Warner**
- **Brand** (Nike, Disney, etc.)
- **Tastemaker** (Pitchfork, BBC, NPR, etc.)
- **Other Label**
- **User** (default fallback)

UDF definition: `/Users/cbeesley/code/dbt-analytics/utils/macros/playlists/classify_playlist_owner.sql`
The UDF normalizes curator_name and curator_id by stripping diacritics before matching.

`OWNER` appears in `V_PLAYLIST_METADATA` (via `PLAYLIST_HOURLY_METADATA_DBT`, `PLAYLIST_NON_PRIORITY_METADATA_DBT`, and `PRIORITY_PLAYLIST_METADATA` source).

## Key Recent Commits

- `d712596b` — `IN-16873: fix header metrics for personalized playlists` (personalized playlist handling fix)
- `000408fe` — `IN-16920 Split the playlists package into 3 tiers + unioned views`
- `28f6fa66` — `IN-16920 Upgrade playlist state models`
- `4112af83` — `Restore missing playlist_type and follower/streams filters to non_priority staging`
- `61faeaa7` — `MAINT Remove hourly logic from non_priority`
- `1cf270b1` — `IN-16850: Use track_company_brand table in hourly playlist metadata`
- `87141e99` — `IN-16779 additional owner classifications for pp`
- `3586d09a` — `IN-16847-2: priority playlists first_added_on_date no longer null`
- `9425dd9c` — `IN-16847: Add first_added_on_date column to hourly current tracklist`
- `4f0be98a` — `Update V_PLAYLISTS_PLACEMENTS_BY_ISRC_PLAYLIST_PUBLIC.sql`
- `4497e1fa` — `IN-16910: Remove DISTRIBUTOR column and optimize historical playlist tracklists`

## V_PLAYLIST_METADATA Consolidation (graphql-knowledge)

`graphql-knowledge` consolidated multiple playlist metadata tables into `V_PLAYLIST_METADATA` view (IN-1523). This view is the single source of truth for playlist metadata for the knowledge graph.

## Three Circular Header Metrics

The three donut-ring percentage metrics shown in `PlaylistPageHeaderMetrics` come from `V_PLAYLIST_METADATA` (VARIANT columns):

| Column | Meaning | NULL when |
|--------|---------|-----------|
| `FRONTLINE_PERCENT` | Share of tracklist tracks that are frontline releases | No Orchard-distributed tracks in tracklist |
| `SMG_PERCENT` | Share of tracklist tracks that are SMG | No Orchard-distributed tracks in tracklist |
| `LOCAL_PERCENT` | Share of tracks whose ISRC country prefix matches the user's current context market (e.g., `USXXX` ISRCs are "local" for a US user) | No Orchard-distributed tracks in tracklist |

`PRIMARY_PLAYLIST_TYPE` is **not** a header metric. It is a sub-categorization field used solely to flag "Hot Hits" designation (controlled by `insights_primary_playlist_type` flag). `PLAYLIST_TYPE` (e.g., CURATED, EDITORIAL) is the canonical playlist type field shown in the header.

## Jenkins Build Tags

- `priority_playlists` — runs hourly/frequently
- `hourly_playlists` — separate Jenkins job
- `non_priority_playlists` — separate Jenkins job
- Adjusted in: `MAINT Adjust hourly Jenkins job to build both priority and hourly playlists (#1644)`

## Macros

- `classify_playlist_owner.sql` — creates `CLASSIFY_PLAYLIST_OWNER` UDF
- `create_playlist_udfs.sql` — creates other playlist UDFs
- `placements_last_added_index.sql` — last-added indexing logic