## Adding New Models

When adding new dbt models to the playlists project, follow these steps to ensure the models are properly deployed and accessible:

### 1. Create the Model File
Create your model SQL file in the all the 3 tier directories under `models/{priority,non_priority,hourly}/` with the `_DBT` suffix.

### 2. Add a Unioned V_ View (if applicable)
If this model's data should be exposed externally, add a `V_` view in `models/unioned/` that unions the tier-specific `_DBT` tables. The V_ views are the only externally consumed interface.

## Test fixtures generated by:

### ISRCs

**Spotify + Apple (3-tier coverage):**
- USSM12103949 (The Kid LAROI, Justin Bieber - STAY) [priority]
- USSM19400325 (Mariah Carey - All I Want for Christmas Is You) [hourly]
- JPP301900716 (YOASOBI - 夜に駆ける) [hourly]
- SEYOL1797201 (ASMR Sleep Triggers - White Noise) [non-priority]
- USSM11905215 (The Weeknd - Heartless) [non-priority]

**Deezer / YouTube / Amazon (shared metadata only):**
- QMFME2004132 (Bad Bunny, Jhay Cortez - DÁKITI)
- USSM12100531 (Lil Nas X - MONTERO (Call Me By Your Name))

### Playlist IDs

**Spotify (3-tier):**
- 37i9dQZF1DXcBWIGoYBM5M (Today's Top Hits, Editorial) [priority]
- 37i9dQZF1DX0Yxoavh5qJV (Christmas Hits, Editorial) [hourly]
- 72u7iyyy7tQOAGnUwtJn6B (White Noise Sounds for Sleep, Curated) [non-priority]

**Apple Music (2-tier):**
- pl.043a2c9876114d95a4659988497567be (Top 100: Japan, Editorial) [hourly]
- pl.c28445da3d2a4ea194494aa2c940b096 (Morgan Wallen Essentials, Editorial) [non-priority]

**Algorithmic:**
- onRepeat (On Repeat)

**Amazon:**
- B0979NT5H8 (Hit Songs 2021 by Filtr, Public)
- B08NY1BSBF (Best of 2020: Latin, Public)
- B07QVB9TQG (Brand New Music, Public)
- B06XRHPMTW (Private)

**Deezer:**
- 53362031 (Les titres du moment, Public)
- 2098157264 (Hits of the Moment, Public)
- 1273315391 (Reggaeton Hits, Public)

**YouTube:**
- PL4fGSI1pDJn5bl_gsJFsUpYrkbsxFzSqD (Top 100 Songs Colombia, Public)
- PL4fGSI1pDJn5b5REdvzwia1MjmaIwa5MN (Top 100 Songs Portugal, Public)
- PLFgquLnL59alCl_2TQvOiD5Vgm1hCaGSI (Popular Music Videos, Public)
- PLx0sYbCqOb8TBPRdmBHs5Iftvv9TPboYG (Top 50 This Week & Top 100 Songs 2021, Public)

### Global Participant IDs

**For Spotify + Apple ISRCs:**
- deeaf06f-0e9c-4189-8b0c-6c09e3460c44 (Justin Bieber)
- f7115f2c-2ff6-4308-88cc-8533a81366ab (The Kid LAROI)
- a71d3107-e68d-462f-9712-94fbf0184efa (Mariah Carey)
- 634609f5-9f4d-4b0e-aa79-72c8acb7e199 (YOASOBI)
- faaf1e95-c3ea-4528-a53c-0b30e91cff06 (White Noise Baby Sleep)
- 19f12cff-0782-449d-b36d-a6ba24b77560 (White Noise for Babies)
- 5a8ba6fd-34d5-4883-9109-65ec20f04ec1 (Diplo)
- e7c6ba99-0d6d-4b92-bfad-79098f2dfc5c (Morgan Wallen)

**For Deezer / YouTube / Amazon ISRCs:**
- e49ea9f9-0a74-4623-b3c6-ff819c7c0a4d (Bad Bunny)
- 3f1d08b9-219d-4280-8e26-4cb8cdcfff4c (Jhay Cortez)
- c963dc6b-3ba8-4558-b061-a7a376df7b0a (Lil Nas X)

### Priority Tables (Snowflake Tasks)

The following 8 tables are managed by a Snowflake task graph (`theorchard/database/.../spotify_priority_playlists_full_graph.sql`), not by dbt. They are declared as `source()` in `models/unioned/sources.yml` and referenced in V_ views instead of `ref()`. Their test fixtures are plain CSVs seeded directly.

- PLAYLISTS_PRIORITY_PLACEMENTS_BY_ISRC_PLAYLIST_PUBLIC
- PLAYLISTS_PRIORITY_PLACEMENTS_BY_PLAYLIST_PUBLIC
- PLAYLISTS_PRIORITY_PLACEMENTS_BY_PARTICIPANT_ISRC_PLAYLIST_PUBLIC
- PLAYLISTS_PRIORITY_BY_PLAYLIST_CURRENT_TRACKLIST
- 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

### Queries

#### DIM_ALGORITHMIC_PLAYLISTS
```
select
    *
from
    facts.prod.dim_algorithmic_playlists
where
    store_playlist_id = 'onRepeat'
;
```

#### DIM_PLAYLIST
```
select
    *
from
    facts.prod.dim_playlist
where
    storeplaylistid in ('B06XRHPMTW', 'B077GGKCDD , B09BRH7Y6J')
;
```

#### DIM_RELEASE
```
select
    dim_release.*
from
    facts.prod.dim_release
inner join
    facts.prod.dim_track_clean_mv
        on dim_track_clean_mv.upc = dim_release.releaseid
where
    dim_track_clean_mv.isrc in ('QMFME2004132', 'USSM12100531', 'USSM12103949', 'USSM19400325', 'JPP301900716', 'SEYOL1797201', 'USSM11905215')
;
```

#### DIM_TRACK_CLEAN_MV
```
select
    *
from
    facts.prod.dim_track_clean_mv
where
    isrc in ('QMFME2004132', 'USSM12100531', 'USSM12103949', 'USSM19400325', 'JPP301900716', 'SEYOL1797201', 'USSM11905215')
;
```

#### GLOBAL_PARTICIPANT
```
select
    gp.*
from
    facts.prod.global_sound_recording as gsr
inner join
    facts.prod.global_sound_recording_represents_public_sound_recording as gsr_represents_psr
        on gsr_represents_psr.global_sound_recording_id = gsr.id
inner join
    facts.prod.public_participant_participated_in_public_sound_recording as pp_participated_in_psr
        on pp_participated_in_psr.public_sound_recording_id = gsr_represents_psr.public_sound_recording_id
inner join
    facts.prod.global_participant_represents_public_participant as gp_represents_pp
        on gp_represents_pp.public_participant_id = pp_participated_in_psr.public_participant_id
left join
    facts.prod.global_participant_merged_to_global_participant as gp_merged_to_gp
        on gp_merged_to_gp.global_participant_id_from = gp_represents_pp.global_participant_id
inner join
    facts.prod.global_participant as gp
        on coalesce(gp_merged_to_gp.global_participant_id_to, gp_represents_pp.global_participant_id) = gp.id
where
    gsr.isrc in ('QMFME2004132', 'USSM12100531', 'USSM12103949', 'USSM19400325', 'JPP301900716', 'SEYOL1797201', 'USSM11905215')
;
```

#### GLOBAL_PARTICIPANT_MERGED_TO_GLOBAL_PARTICIPANT
```
select
    gp_merged_to_gp.*
from
    facts.prod.global_sound_recording as gsr
inner join
    facts.prod.global_sound_recording_represents_public_sound_recording as gsr_represents_psr
        on gsr_represents_psr.global_sound_recording_id = gsr.id
inner join
    facts.prod.public_participant_participated_in_public_sound_recording as pp_participated_in_psr
        on pp_participated_in_psr.public_sound_recording_id = gsr_represents_psr.public_sound_recording_id
inner join
    facts.prod.global_participant_represents_public_participant as gp_represents_pp
        on gp_represents_pp.public_participant_id = pp_participated_in_psr.public_participant_id
inner join
    facts.prod.global_participant_merged_to_global_participant as gp_merged_to_gp
        on gp_merged_to_gp.global_participant_id_from = gp_represents_pp.global_participant_id
where
    gsr.isrc in ('QMFME2004132', 'USSM12100531', 'USSM12103949', 'USSM19400325', 'JPP301900716', 'SEYOL1797201', 'USSM11905215')
;
```

#### GLOBAL_PARTICIPANT_REPRESENTS_PUBLIC_PARTICIPANT
```
select
    gp_represents_pp.*
from
    facts.prod.global_sound_recording as gsr
inner join
    facts.prod.global_sound_recording_represents_public_sound_recording as gsr_represents_psr
        on gsr_represents_psr.global_sound_recording_id = gsr.id
inner join
    facts.prod.public_participant_participated_in_public_sound_recording as pp_participated_in_psr
        on pp_participated_in_psr.public_sound_recording_id = gsr_represents_psr.public_sound_recording_id
inner join
    facts.prod.global_participant_represents_public_participant as gp_represents_pp
        on gp_represents_pp.public_participant_id = pp_participated_in_psr.public_participant_id
where
    gsr.isrc in ('QMFME2004132', 'USSM12100531', 'USSM12103949', 'USSM19400325', 'JPP301900716', 'SEYOL1797201', 'USSM11905215')
;
```

#### GLOBAL_SOUND_RECORDING
```
select
    gsr.*
from
    facts.prod.global_sound_recording as gsr
where
    gsr.isrc in ('QMFME2004132', 'USSM12100531', 'USSM12103949', 'USSM19400325', 'JPP301900716', 'SEYOL1797201', 'USSM11905215')
;
```

#### GLOBAL_SOUND_RECORDING_REPRESENTS_PUBLIC_SOUND_RECORDING
```
select
    gsr_represents_psr.*
from
    facts.prod.global_sound_recording as gsr
inner join
    facts.prod.global_sound_recording_represents_public_sound_recording as gsr_represents_psr
        on gsr_represents_psr.global_sound_recording_id = gsr.id
where
    gsr.isrc in ('QMFME2004132', 'USSM12100531', 'USSM12103949', 'USSM19400325', 'JPP301900716', 'SEYOL1797201', 'USSM11905215')
;
```

#### LABEL_PARTICIPANT_PARTICIPATED_IN_ORCHARD_TRACK
```
select
    l_participated_in_t.*
from
    facts.prod.dim_track_clean_mv
inner join
    facts.prod.label_participant_participated_in_orchard_track as l_participated_in_t
        on l_participated_in_t.track_id = dim_track_clean_mv.track_unique_id
        and l_participated_in_t.participated_as in ('performer', 'remixer', 'featuring')
where
    dim_track_clean_mv.isrc in ('QMFME2004132', 'USSM12100531', 'USSM12103949', 'USSM19400325', 'JPP301900716', 'SEYOL1797201', 'USSM11905215')
;
```

#### PLAYLIST_CURATOR_MAPPINGS
```
select
    *
from
    facts.prod.playlist_curator_mappings
where
    curator_id in ('spotify', 'apple-music')
;
```

#### PLAYLIST_MAPPINGS
```
select
    *
from
    facts.prod.playlist_mappings
where
    (store_id = 286 and store_playlist_id in ('37i9dQZF1DXcBWIGoYBM5M', '37i9dQZF1DX0Yxoavh5qJV', '72u7iyyy7tQOAGnUwtJn6B'))
    or (store_id = 1 and store_playlist_id in ('pl.043a2c9876114d95a4659988497567be', 'pl.c28445da3d2a4ea194494aa2c940b096'))
;
```

#### PUBLIC_PARTICIPANT_PARTICIPATED_IN_PUBLIC_SOUND_RECORDING
```
select
    pp_participated_in_psr.*
from
    facts.prod.global_sound_recording as gsr
inner join
    facts.prod.global_sound_recording_represents_public_sound_recording as gsr_represents_psr
        on gsr_represents_psr.global_sound_recording_id = gsr.id
inner join
    facts.prod.public_participant_participated_in_public_sound_recording as pp_participated_in_psr
        on pp_participated_in_psr.public_sound_recording_id = gsr_represents_psr.public_sound_recording_id
where
    gsr.isrc in ('QMFME2004132', 'USSM12100531', 'USSM12103949', 'USSM19400325', 'JPP301900716', 'SEYOL1797201', 'USSM11905215')
;
```

#### STREAMS_BY_TRACK_PLAYLIST_COUNTRY_FEED_DISTRIBUTOR_ROLLUP
```
select
    *
from
    facts.prod.streams_by_track_playlist_country_feed_distributor_rollup
where
    isrc in ('QMFME2004132', 'USSM12100531', 'USSM12103949', 'USSM19400325', 'JPP301900716', 'SEYOL1797201', 'USSM11905215')
    and store_playlist_id in (
        'onRepeat',
        'B0979NT5H8',
        'B08NY1BSBF',
        'B06XRHPMTW',
        'B077GGKCDD , B09BRH7Y6J',
        '37i9dQZF1DXcBWIGoYBM5M',
        '37i9dQZF1DX0Yxoavh5qJV',
        '72u7iyyy7tQOAGnUwtJn6B',
        'pl.043a2c9876114d95a4659988497567be',
        'pl.c28445da3d2a4ea194494aa2c940b096'
    )
order by
    isrc,
    store_playlist_id,
    track_id,
    country_code
;
```

#### STREAMS_COMPLETIONS_BY_TRACK_PLAYLIST_COUNTRY_FEED_DISTRIBUTOR_ROLLUP
```
select
    *
from
    facts.prod.streams_completions_by_track_playlist_country_feed_distributor_rollup
where
    isrc in ('QMFME2004132', 'USSM12100531', 'USSM12103949', 'USSM19400325', 'JPP301900716', 'SEYOL1797201', 'USSM11905215')
    and store_playlist_id in (
        'onRepeat',
        'B0979NT5H8',
        'B08NY1BSBF',
        'B06XRHPMTW',
        'B077GGKCDD , B09BRH7Y6J',
        '37i9dQZF1DXcBWIGoYBM5M',
        '37i9dQZF1DX0Yxoavh5qJV',
        '72u7iyyy7tQOAGnUwtJn6B',
        'pl.043a2c9876114d95a4659988497567be',
        'pl.c28445da3d2a4ea194494aa2c940b096'
    )
order by
    isrc,
    store_playlist_id,
    track_id,
    country_code
;
```

#### CM_TAG
```
select
    *
from
    chartmetric.raw_data.cm_tag
where
    type = 'genre'
    and name in ('dance pop', 'pop', 'latin', 'reggaeton')
;
```

#### AMAZON
```
select
    *
from
    chartmetric.raw_data.amazon
where
    isrc in ('QMFME2004132', 'USSM12100531')
order by
    isrc asc nulls last
;
```

#### AMAZON_PLAYLIST
```
select
    *
from
    chartmetric.raw_data.amazon_playlist
where
    id in ('B0979NT5H8', 'B08NY1BSBF', 'B07QVB9TQG', 'B077GGKCDD , B09BRH7Y6J')
order by
    id asc nulls last
;
```

#### L_AMAZON_PLAYLIST
```
select
    l_amazon_playlist.*
from
    chartmetric.raw_data.l_amazon_playlist
inner join
    chartmetric.raw_data.amazon
        on amazon.id = l_amazon_playlist.amazon
inner join
    chartmetric.raw_data.amazon_playlist
        on amazon_playlist.id = l_amazon_playlist.amazon_playlist
where
    amazon.isrc in ('QMFME2004132', 'USSM12100531')
    and amazon_playlist.id in ('B0979NT5H8', 'B08NY1BSBF', 'B07QVB9TQG')
order by
    l_amazon_playlist.amazon_playlist asc nulls last,
    l_amazon_playlist.amazon asc nulls last
;
```

#### L_AMAZON_PLAYLIST_HISTORY
```
select
    l_amazon_playlist_history.*
from
    chartmetric.raw_data.l_amazon_playlist_history
inner join
    chartmetric.raw_data.amazon
        on amazon.id = l_amazon_playlist_history.amazon
inner join
    chartmetric.raw_data.amazon_playlist
        on amazon_playlist.id = l_amazon_playlist_history.amazon_playlist
where
    amazon.isrc in ('QMFME2004132', 'USSM12100531')
    and amazon_playlist.id in ('B0979NT5H8', 'B08NY1BSBF', 'B07QVB9TQG')
order by
    l_amazon_playlist_history.amazon_playlist asc nulls last,
    l_amazon_playlist_history.amazon asc nulls last
;
```

#### L_AMAZON_PLAYLIST_STAT
```
select
    l_amazon_playlist_stat.*
from
    chartmetric.raw_data.l_amazon_playlist_stat
inner join
    chartmetric.raw_data.amazon
        on amazon.id = l_amazon_playlist_stat.amazon
inner join
    chartmetric.raw_data.amazon_playlist
        on amazon_playlist.id = l_amazon_playlist_stat.amazon_playlist
where
    amazon.isrc in ('QMFME2004132', 'USSM12100531')
    and amazon_playlist.id in ('B0979NT5H8', 'B08NY1BSBF', 'B07QVB9TQG')
qualify row_number() over (
    partition by
        l_amazon_playlist_stat.amazon,
        l_amazon_playlist_stat.amazon_playlist
    order by
        l_amazon_playlist_stat.timestp desc nulls last
) = 1
order by
    l_amazon_playlist_stat.amazon_playlist asc nulls last,
    l_amazon_playlist_stat.amazon asc nulls last,
    l_amazon_playlist_stat.timestp desc nulls last
;
```

#### DEEZER
```
select
    *
from
    chartmetric.raw_data.deezer
where
    isrc in ('QMFME2004132', 'USSM12100531')
order by
    isrc asc nulls last
;
```

#### DEEZER_USER
```
select
    *
from
    chartmetric.raw_data.deezer_user
inner join
    chartmetric.raw_data.deezer_playlist
        on deezer_playlist.deezer_user = deezer_user.id
where
    deezer_playlist.id in (53362031, 2098157264, 1273315391)
;
```

#### DEEZER_PLAYLIST
```
select
    *
from
    chartmetric.raw_data.deezer_playlist
where
    id in (53362031, 2098157264, 1273315391)
order by
    id asc nulls last
;
```

#### DEEZER_PLAYLIST_STAT
```
select
    *
from
    chartmetric.raw_data.deezer_playlist_stat
where
    deezer_playlist in (53362031, 2098157264, 1273315391)
qualify row_number() over (
    partition by
        deezer_playlist
    order by
        timestp desc nulls last
) = 1
order by
    deezer_playlist asc nulls last
;
```

#### L_DEEZER_PLAYLIST
```
select
    l_deezer_playlist.*
from
    chartmetric.raw_data.l_deezer_playlist
inner join
    chartmetric.raw_data.deezer
        on deezer.id = l_deezer_playlist.deezer
inner join
    chartmetric.raw_data.deezer_playlist
        on deezer_playlist.id = l_deezer_playlist.deezer_playlist
where
    deezer.isrc in ('QMFME2004132', 'USSM12100531')
    and deezer_playlist.id in (53362031, 2098157264, 1273315391)
order by
    l_deezer_playlist.deezer_playlist asc nulls last,
    l_deezer_playlist.deezer asc nulls last
;
```

#### L_DEEZER_PLAYLIST_HISTORY
```
select
    l_deezer_playlist_history.*
from
    chartmetric.raw_data.l_deezer_playlist_history
inner join
    chartmetric.raw_data.deezer
        on deezer.id = l_deezer_playlist_history.deezer
inner join
    chartmetric.raw_data.deezer_playlist
        on deezer_playlist.id = l_deezer_playlist_history.deezer_playlist
where
    deezer.isrc in ('QMFME2004132', 'USSM12100531')
    and deezer_playlist.id in (53362031, 2098157264, 1273315391)
order by
    l_deezer_playlist_history.deezer_playlist asc nulls last,
    l_deezer_playlist_history.deezer asc nulls last
;
```

#### L_DEEZER_PLAYLIST_STAT
```
select
    l_deezer_playlist_stat.*
from
    chartmetric.raw_data.l_deezer_playlist_stat
inner join
    chartmetric.raw_data.deezer
        on deezer.id = l_deezer_playlist_stat.deezer
inner join
    chartmetric.raw_data.deezer_playlist
        on deezer_playlist.id = l_deezer_playlist_stat.deezer_playlist
where
    deezer.isrc in ('QMFME2004132', 'USSM12100531')
    and deezer_playlist.id in (53362031, 2098157264, 1273315391)
qualify row_number() over (
    partition by
        l_deezer_playlist_stat.deezer,
        l_deezer_playlist_stat.deezer_playlist
    order by
        l_deezer_playlist_stat.timestp desc nulls last
) = 1
order by
    l_deezer_playlist_stat.deezer_playlist asc nulls last,
    l_deezer_playlist_stat.deezer asc nulls last,
    l_deezer_playlist_stat.timestp desc nulls last
;
```

#### L_DEEZER_PLAYLIST_TAG
```
select
    l_deezer_playlist_tag.*
from
    chartmetric.raw_data.deezer_playlist
inner join
    chartmetric.raw_data.l_deezer_playlist_tag
        on l_deezer_playlist_tag.deezer_playlist = deezer_playlist.id
where
    deezer_playlist.id in (53362031, 2098157264, 1273315391)
;
```

#### ITUNES
```
select
    *
from
    chartmetric.raw_data.itunes
where
    isrc in ('JPP301900716', 'USSM11905215')
order by
    isrc asc nulls last
;
```

#### ITUNES_PLAYLIST
```
select
    *
from
    chartmetric.raw_data.itunes_playlist
where
    playlist_id in ('pl.043a2c9876114d95a4659988497567be', 'pl.c28445da3d2a4ea194494aa2c940b096')
;
```

#### ITUNES_PLAYLIST_OWNER
```
select
    itunes_playlist_owner.*
from
    chartmetric.raw_data.itunes_playlist
inner join
    chartmetric.raw_data.itunes_playlist_owner
        on itunes_playlist_owner.id = itunes_playlist.itunes_playlist_owner
where
    itunes_playlist.playlist_id in ('pl.043a2c9876114d95a4659988497567be', 'pl.c28445da3d2a4ea194494aa2c940b096')
;
```

#### L_ITUNES_PLAYLIST
```
select
    l_itunes_playlist.*
from
    chartmetric.raw_data.l_itunes_playlist
inner join
    chartmetric.raw_data.itunes
        on itunes.id = l_itunes_playlist.itunes
inner join
    chartmetric.raw_data.itunes_playlist
        on itunes_playlist.id = l_itunes_playlist.itunes_playlist
where
    itunes.isrc in ('JPP301900716', 'USSM11905215')
    and itunes_playlist.playlist_id in ('pl.043a2c9876114d95a4659988497567be', 'pl.c28445da3d2a4ea194494aa2c940b096')
order by
    l_itunes_playlist.itunes_playlist asc nulls last,
    l_itunes_playlist.itunes asc nulls last
;
```

#### L_ITUNES_PLAYLIST_HISTORY
```
select
    l_itunes_playlist_history.*
from
    chartmetric.raw_data.l_itunes_playlist_history
inner join
    chartmetric.raw_data.itunes
        on itunes.id = l_itunes_playlist_history.itunes
inner join
    chartmetric.raw_data.itunes_playlist
        on itunes_playlist.id = l_itunes_playlist_history.itunes_playlist
where
    itunes.isrc in ('JPP301900716', 'USSM11905215')
    and itunes_playlist.playlist_id in ('pl.043a2c9876114d95a4659988497567be', 'pl.c28445da3d2a4ea194494aa2c940b096')
order by
    l_itunes_playlist_history.itunes_playlist asc nulls last,
    l_itunes_playlist_history.itunes asc nulls last
;
```

#### L_ITUNES_PLAYLIST_STAT
```
select
    l_itunes_playlist_stat.*
from
    chartmetric.raw_data.l_itunes_playlist_stat
inner join
    chartmetric.raw_data.itunes
        on itunes.id = l_itunes_playlist_stat.itunes
inner join
    chartmetric.raw_data.itunes_playlist
        on itunes_playlist.id = l_itunes_playlist_stat.itunes_playlist
where
    itunes.isrc in ('JPP301900716', 'USSM11905215')
    and itunes_playlist.playlist_id in ('pl.043a2c9876114d95a4659988497567be', 'pl.c28445da3d2a4ea194494aa2c940b096')
qualify row_number() over (
    partition by
        l_itunes_playlist_stat.itunes,
        l_itunes_playlist_stat.itunes_playlist,
        l_itunes_playlist_stat.storefront
    order by
        l_itunes_playlist_stat.timestp desc nulls last
) = 1
order by
    l_itunes_playlist_stat.itunes_playlist asc nulls last,
    l_itunes_playlist_stat.itunes asc nulls last,
    l_itunes_playlist_stat.timestp desc nulls last
;
```

#### L_ITUNES_PLAYLIST_TAG
```
select
    l_itunes_playlist_tag.*
from
    chartmetric.raw_data.itunes_playlist
inner join
    chartmetric.raw_data.l_itunes_playlist_tag
        on l_itunes_playlist_tag.itunes_playlist = itunes_playlist.id
where
    itunes_playlist.playlist_id in ('pl.043a2c9876114d95a4659988497567be', 'pl.c28445da3d2a4ea194494aa2c940b096')
;
```

#### SPOTIFY
```
select
    *
from
    chartmetric.raw_data.spotify
where
    isrc in ('USSM12103949', 'USSM19400325', 'SEYOL1797201')
order by
    isrc asc nulls last
;
```

#### SPOTIFY_PLAYLIST
```
select
    *
from
    chartmetric.raw_data.spotify_playlist
where
    playlist_id in ('37i9dQZF1DXcBWIGoYBM5M', '37i9dQZF1DX0Yxoavh5qJV', '72u7iyyy7tQOAGnUwtJn6B')
order by
    playlist_id asc nulls last
;
```

#### SPOTIFY_PLAYLIST_OWNER
```
select
    spotify_playlist_owner.*
from
    chartmetric.raw_data.spotify_playlist
inner join
    chartmetric.raw_data.spotify_playlist_owner
        on spotify_playlist_owner.id = spotify_playlist.spotify_playlist_owner
where
    spotify_playlist.playlist_id in ('37i9dQZF1DXcBWIGoYBM5M', '37i9dQZF1DX0Yxoavh5qJV', '72u7iyyy7tQOAGnUwtJn6B')
group by
    1,2,3,4,5,6,7,8
;
```

#### L_SPOTIFY_PLAYLIST
```
select
    l_spotify_playlist.*
from
    chartmetric.raw_data.l_spotify_playlist
inner join
    chartmetric.raw_data.spotify
        on spotify.id = l_spotify_playlist.spotify
inner join
    chartmetric.raw_data.spotify_playlist
        on spotify_playlist.id = l_spotify_playlist.spotify_playlist
where
    spotify.isrc in ('USSM12103949', 'USSM19400325', 'SEYOL1797201')
    and spotify_playlist.playlist_id in ('37i9dQZF1DXcBWIGoYBM5M', '37i9dQZF1DX0Yxoavh5qJV', '72u7iyyy7tQOAGnUwtJn6B')
order by
    l_spotify_playlist.spotify_playlist asc nulls last,
    l_spotify_playlist.spotify asc nulls last
;
```

#### L_SPOTIFY_PLAYLIST_HISTORY
```
select
    l_spotify_playlist_history.*
from
    chartmetric.raw_data.l_spotify_playlist_history
inner join
    chartmetric.raw_data.spotify
        on spotify.id = l_spotify_playlist_history.spotify
inner join
    chartmetric.raw_data.spotify_playlist
        on spotify_playlist.id = l_spotify_playlist_history.spotify_playlist
where
    spotify.isrc in ('USSM12103949', 'USSM19400325', 'SEYOL1797201')
    and spotify_playlist.playlist_id in ('37i9dQZF1DXcBWIGoYBM5M', '37i9dQZF1DX0Yxoavh5qJV', '72u7iyyy7tQOAGnUwtJn6B')
order by
    l_spotify_playlist_history.spotify_playlist asc nulls last,
    l_spotify_playlist_history.spotify asc nulls last
;
```

#### L_SPOTIFY_PLAYLIST_STAT
```
select
    l_spotify_playlist_stat.*
from
    chartmetric.raw_data.l_spotify_playlist_stat
inner join
    chartmetric.raw_data.spotify
        on spotify.id = l_spotify_playlist_stat.spotify
inner join
    chartmetric.raw_data.spotify_playlist
        on spotify_playlist.id = l_spotify_playlist_stat.spotify_playlist
where
    spotify.isrc in ('USSM12103949', 'USSM19400325', 'SEYOL1797201')
    and spotify_playlist.playlist_id in ('37i9dQZF1DXcBWIGoYBM5M', '37i9dQZF1DX0Yxoavh5qJV', '72u7iyyy7tQOAGnUwtJn6B')
qualify row_number() over (
    partition by
        l_spotify_playlist_stat.spotify,
        l_spotify_playlist_stat.spotify_playlist
    order by
        l_spotify_playlist_stat.timestp desc nulls last
) = 1
order by
    l_spotify_playlist_stat.spotify_playlist asc nulls last,
    l_spotify_playlist_stat.spotify asc nulls last,
    l_spotify_playlist_stat.timestp desc nulls last
;
```

#### L_SPOTIFY_PLAYLIST_TAG
```
select
    l_spotify_playlist_tag.*
from
    chartmetric.raw_data.spotify_playlist
inner join
    chartmetric.raw_data.l_spotify_playlist_tag
        on l_spotify_playlist_tag.spotify_playlist = spotify_playlist.id
where
    spotify_playlist.playlist_id in ('37i9dQZF1DXcBWIGoYBM5M', '37i9dQZF1DX0Yxoavh5qJV', '72u7iyyy7tQOAGnUwtJn6B')
;
```

#### L_SPOTIFY_PLAYLIST_SONY
```
select
    *
from
    chartmetric.raw_data.l_spotify_playlist_sony
where
    spotify_playlist in (
        select id from chartmetric.raw_data.spotify_playlist
        where playlist_id in ('37i9dQZF1DXcBWIGoYBM5M', '37i9dQZF1DX0Yxoavh5qJV', '72u7iyyy7tQOAGnUwtJn6B')
    )
order by
    spotify_playlist asc nulls last,
    timestp desc nulls last
;
```

#### YOUTUBE
```
select
    *
from
    chartmetric.raw_data.youtube
where
    isrc in ('QMFME2004132', 'USSM12100531')
order by
    isrc asc nulls last
;
```

#### YOUTUBE_PLAYLIST
```
select
    *
from
    chartmetric.raw_data.youtube_playlist
where
    id in ('PL4fGSI1pDJn5bl_gsJFsUpYrkbsxFzSqD', 'PL4fGSI1pDJn5b5REdvzwia1MjmaIwa5MN', 'PLFgquLnL59alCl_2TQvOiD5Vgm1hCaGSI', 'PLx0sYbCqOb8TBPRdmBHs5Iftvv9TPboYG')
;
```

#### YOUTUBE_PLAYLIST_STAT
```
select
    *
from
    chartmetric.raw_data.youtube_playlist_stat
where
    youtube_playlist in ('PL4fGSI1pDJn5bl_gsJFsUpYrkbsxFzSqD', 'PL4fGSI1pDJn5b5REdvzwia1MjmaIwa5MN', 'PLFgquLnL59alCl_2TQvOiD5Vgm1hCaGSI', 'PLx0sYbCqOb8TBPRdmBHs5Iftvv9TPboYG')
qualify row_number() over (
    partition by
        youtube_playlist
    order by
        timestp desc nulls last
) = 1
order by
    youtube_playlist asc nulls last
;
```

#### L_YOUTUBE_PLAYLIST
```
select
    l_youtube_playlist.*
from
    chartmetric.raw_data.l_youtube_playlist
inner join
    chartmetric.raw_data.youtube
        on youtube.id = l_youtube_playlist.video_id
inner join
    chartmetric.raw_data.youtube_playlist
        on youtube_playlist.id = l_youtube_playlist.playlist_id
where
    youtube.isrc in ('QMFME2004132', 'USSM12100531')
    and youtube_playlist.id in ('PL4fGSI1pDJn5bl_gsJFsUpYrkbsxFzSqD', 'PL4fGSI1pDJn5b5REdvzwia1MjmaIwa5MN', 'PLFgquLnL59alCl_2TQvOiD5Vgm1hCaGSI', 'PLx0sYbCqOb8TBPRdmBHs5Iftvv9TPboYG')
order by
    l_youtube_playlist.playlist_id asc nulls last,
    l_youtube_playlist.video_id asc nulls last
;
```

#### L_YOUTUBE_PLAYLIST_HISTORY
```
select
    l_youtube_playlist_history.*
from
    chartmetric.raw_data.l_youtube_playlist_history
inner join
    chartmetric.raw_data.youtube
        on youtube.id = l_youtube_playlist_history.video_id
inner join
    chartmetric.raw_data.youtube_playlist
        on youtube_playlist.id = l_youtube_playlist_history.playlist_id
where
    youtube.isrc in ('QMFME2004132', 'USSM12100531')
    and youtube_playlist.id in ('PL4fGSI1pDJn5bl_gsJFsUpYrkbsxFzSqD', 'PL4fGSI1pDJn5b5REdvzwia1MjmaIwa5MN', 'PLFgquLnL59alCl_2TQvOiD5Vgm1hCaGSI', 'PLx0sYbCqOb8TBPRdmBHs5Iftvv9TPboYG')
qualify row_number() over (
    partition by
        l_youtube_playlist_history.video_id,
        l_youtube_playlist_history.playlist_id
    order by
        l_youtube_playlist_history.end_sys_period desc nulls last
) = 1
order by
    l_youtube_playlist_history.playlist_id asc nulls last,
    l_youtube_playlist_history.video_id asc nulls last
;
```

#### L_YOUTUBE_PLAYLIST_STAT
```
select
    l_youtube_playlist_stat.*
from
    chartmetric.raw_data.l_youtube_playlist_stat
inner join
    chartmetric.raw_data.youtube
        on youtube.id = l_youtube_playlist_stat.video_id
inner join
    chartmetric.raw_data.youtube_playlist
        on youtube_playlist.id = l_youtube_playlist_stat.playlist_id
where
    youtube.isrc in ('QMFME2004132', 'USSM12100531')
    and youtube_playlist.id in ('PL4fGSI1pDJn5bl_gsJFsUpYrkbsxFzSqD', 'PL4fGSI1pDJn5b5REdvzwia1MjmaIwa5MN', 'PLFgquLnL59alCl_2TQvOiD5Vgm1hCaGSI', 'PLx0sYbCqOb8TBPRdmBHs5Iftvv9TPboYG')
qualify row_number() over (
    partition by
        l_youtube_playlist_stat.video_id,
        l_youtube_playlist_stat.playlist_id
    order by
        l_youtube_playlist_stat.timestp desc nulls last
) = 1
order by
    l_youtube_playlist_stat.playlist_id asc nulls last,
    l_youtube_playlist_stat.video_id asc nulls last,
    l_youtube_playlist_stat.timestp desc nulls last
;
```

## Playlist Historic Snapshot Reconstruction Logic

### HISTORICAL_PLAYLIST_TRACKLISTS_BY_MARKET_DBT

This model reconstructs historical playlist snapshots using real-time snapshot data (when available) and temporal tables with position data for historical reconstruction.

#### History Depth by Tier

The tiers differ in how far back they retain historical snapshots:

| Tier | History depth |
|------|--------------|
| **priority** | Full history (no lookback limit) |
| **hourly** | Full history (no lookback limit) |
| **non_priority** | **3 months** rolling window (`DATEADD(month, -3, CURRENT_DATE())`) |

The non-priority tier is limited to a 3-month lookback to control compute and storage costs. This filter is applied in `playlist_date_combos` (initial run) and `playlist_date_combos_all` (incremental run), restricting which `l_spotify_playlist_stat` timestamps are considered. The filter is skipped in the test environment so that test fixtures with older dates continue to work.

#### Data Sources

- **Fast sync table - priority snapshots** (`L_SPOTIFY_PLAYLIST_SONY`): Authoritative complete snapshots for priority playlists on recent dates. In production this table only started capturing data ~ May '25, but test fixtures may use earlier (pre-2025) dates for Sony data for testing purposes only.
- **Chartmetric temporal tables** (`l_spotify_playlist` and `l_spotify_playlist_history`): Identify which tracks were members of the playlist on a given date. The _history table shows tracks that have been removed.
- **Chartmetric position snapshots** (`l_spotify_playlist_stat`): Historical position observations with timestamps - the primary source for reconstructing historical snapshots

#### Key Principles

A playlist snapshot on a given date consists of:

- **Fixed positions**: 1, 2, 3, ... N (the structure of the playlist)
- **Variable tracks**: Which track occupies each position on that date

The critical requirement: **one track per position per snapshot date**.

#### Position Assignment Logic

The `position_snapshots` CTE uses this deduplication logic:

```sql
ROW_NUMBER() OVER (
    PARTITION BY store_id, store_playlist_id, snapshot_date, stat.position + 1
    ORDER BY stat.timestp DESC, track_id DESC
) AS rn_position
```

**What this does:**
- Partitions by **position** to ensure one track per position
- Orders by **most recent timestamp** (`timestp DESC`) - most recently observed track wins
- Uses **track_id DESC** as a deterministic tiebreaker - when timestamps are identical, the track with the highest ID is chosen

This matches the authoritative ChartMetric snapshot reconstruction query, which partitions by position and orders by timestamp to get the most recently observed track at each position.

#### Data Prioritization & Flow

The model implements a prioritized data flow:

1. **Realtime snapshots take priority**: When realtime snapshot data exists for a playlist/date combination, it is used exclusively. Temporal table reconstruction is skipped via `WHERE scd.snapshot_date IS NULL` filter.

2. **Temporal reconstruction as fallback**: For playlist/date combinations without realtime data, the model reconstructs snapshots using:
   - ChartMetric temporal tables to identify which tracks were members
   - ChartMetric position snapshots to assign positions

3. **Final deduplication**: The `chartmetric_snapshots_all_dates` CTE performs a final deduplication pass over combined data (realtime + temporal) to enforce exactly one track per position:
   - Prefers current (non-removed) tracks over historical removed tracks
   - For realtime data, prefers the most recent snapshot timestamp (`snapshot_created_at`)
   - Uses deterministic ordering to ensure stable selection

This ensures that for any playlist/date where realtime snapshots are available, they fully override the temporal reconstruction, while temporal data fills gaps where realtime data is absent.