name: PLAYLISTS_SEMANTIC_VIEW description: This semantic view gives access to artist/track specific playlist data. tables: - name: ISRC_BRAND_MAPPING description: Maps ISRCs to company brands (Sony Music, AWAL, The Orchard, etc.) via the product/project/vendor chain. base_table: database: LORELAI_AGENT schema: DEV table: ISRC_BRAND_MAPPING dimensions: - name: COMPANY_BRAND description: 'The company brand the track belongs to. Values include: Sony Music (SME), The Orchard, AWAL, Altafonte, DRM, Foundation Media, Human Re:Sources, Above Board, and others.' expr: COMPANY_BRAND data_type: VARCHAR(16777216) is_enum: true sample_values: - Above Board - Magic Star Kids - Sony Music - Foundation Media - DRM - Altafonte - The Orchard - Mass Appeal - AWAL - Human Re:Sources - Kollective Neighbouring Rights primary_key: columns: - ISRC - name: DIM_STORE description: The table contains records of DSP/digital service provider names and and related ids. Those are platforms that serve music- like Spotify, Deezer etc. base_table: database: FACTS schema: PROD table: DIM_STORE dimensions: - name: STOREID description: Unique identifier for each store in the dimension table. expr: STOREID data_type: NUMBER(32,0) sample_values: - '1592' - '1883' - '1661' - name: STORENAME description: The name of the DSP/digital streaming platform expr: STORENAME data_type: VARCHAR(255) is_enum: true sample_values: - iTunes/Apple - Napster - Amazon Music - Beatport Digital Download Network - Spotify - Deezer - YouTube - Google Play - Pandora - LINE Japan - SoundCloud - AWA - Kuwo - Kugou - QQ primary_key: columns: - STOREID - name: GLOBAL_SOUND_RECORDING description: |- The table contains records of sound recordings(songs) from various music artists. Each record represents an individual recording and includes metadata such as release name and unique identifiers. base_table: database: FACTS schema: PROD table: GLOBAL_SOUND_RECORDING dimensions: - name: NAME synonyms: - song_name - track_name description: The name or title of the sound recording(song). expr: NAME data_type: VARCHAR(16777216) sample_values: - All I Want (Edit) - Hot Steppa - Original Edit - Inexcusable primary_key: columns: - ISRC - name: V_PLAYLISTS_PLACEMENTS_BY_PARTICIPANT_ISRC_PLAYLIST_PUBLIC description: |- Unioned view of all playlist tiers (priority, hourly, non-priority). Contains music track placements on streaming service playlists with position metrics, follower counts, genre classification, and curator info. This is the stable external interface — never query tier tables directly. base_table: database: FACTS schema: PROD table: V_PLAYLISTS_PLACEMENTS_BY_PARTICIPANT_ISRC_PLAYLIST_PUBLIC dimensions: - name: CURATOR_COUNTRY description: The country where the playlist curator is located using two-letter country codes. expr: CURATOR_COUNTRY data_type: VARCHAR(16777216) sample_values: - IN - CA - US - GB - name: CURATOR_NAME description: The name of the curator who created or manages the playlist. expr: CURATOR_NAME data_type: VARCHAR(16777216) sample_values: - Christian Fecteau - Kokolo - maffamaffa - Spotify - Apple - Amazon - Deezer - youtube - name: GLOBAL_PARTICIPANT_ID synonyms: - artist id description: Unique identifier for a music artist across the system. expr: GLOBAL_PARTICIPANT_ID data_type: VARCHAR(16777216) sample_values: - 2279a41f-5439-41cb-881b-24926e5a7be4 - 2279aeae-2cbe-4873-ac9f-4113fbb2bb81 - 2279a533-6e62-4053-b86f-1997433a175f - name: ISRC description: International Standard Recording Code uniquely identifying sound recordings and music video recordings. expr: ISRC data_type: VARCHAR(16777216) sample_values: - QZJ842000368 - USUM72009633 - USUM71904608 - name: PLAYLIST_GENRES description: List of music genres associated with the playlist. expr: PLAYLIST_GENRES data_type: ARRAY sample_values: - |- [ "deep indian pop", "desi hip hop", "filmi" ] - |- [ "deep disco", "disco", "post-disco" ] - name: PLAYLIST_NAME description: 'Names of music playlists from various genres and regions. Playlist names often contain emojis and special characters. IMPORTANT: Always use ILIKE with wildcards for playlist name filtering (e.g. ILIKE ''%search term%'') instead of exact = matching, because users may not include emojis or special characters that are part of the actual playlist name.' expr: PLAYLIST_NAME data_type: VARCHAR(16777216) sample_values: - Chill Rap - Hard Rap 🤬 - New in Hip-Hop - name: PLAYLIST_TYPE description: The type of playlist indicating its source or creation method. expr: PLAYLIST_TYPE data_type: VARCHAR(16777216) is_enum: true sample_values: - USER_GENERATED - EDITORIAL - PERSONALIZED - NEW_MUSIC_FRIDAY - CHART - CURATED - RADIO - name: STORE_ID synonyms: - dsp - music platform - platform description: unique identifier for the DSP/digital service provider. expr: STORE_ID data_type: NUMBER(38,0) sample_values: - '348' - '286' - '1' - name: STORE_PLAYLIST_ID description: Unique identifier for a playlist in the system. expr: STORE_PLAYLIST_ID data_type: VARCHAR(16777216) sample_values: - 6EYFeLAgq3DSlB6x4RYoMF - pl.32a7205ba49f4543b37e53477015dfed - pl.434e71c6da7f4dbf8c4e8d130177c809 - name: STOREFRONT description: The country or region marketplace where the playlist is available, using ISO 2-letter country codes (e.g. US, GB, JP) or GLOBAL. This differs from CURATOR_COUNTRY — STOREFRONT is where the playlist appears, CURATOR_COUNTRY is where the curator is located. expr: STOREFRONT data_type: VARCHAR(2) sample_values: - PH - AR - IE - GLOBAL - GB - US time_dimensions: - name: LAST_ADDED_ON_DATE description: The latest date and time when playlist curator added specific track by artist to this playlist. expr: LAST_ADDED_ON_DATE data_type: TIMESTAMP_NTZ(9) sample_values: - 2023-04-07T23:17:22.913+0000 - 2019-12-15T08:37:51.000+0000 - 2023-04-13T17:35:06.929+0000 facts: - name: CURRENT_POSITION description: The position of a track or item within the playlist at the current time. expr: CURRENT_POSITION data_type: NUMBER(38,0) access_modifier: public_access sample_values: - '229' - '668' - '423' - name: PLAYLIST_FOLLOWER_COUNT description: The number of followers for a playlist. expr: PLAYLIST_FOLLOWER_COUNT data_type: NUMBER(38,0) access_modifier: public_access sample_values: - '1836' - '213' - '1796' - name: PLAYLIST_TRACK_COUNT description: The number of tracks contained in the playlist. expr: PLAYLIST_TRACK_COUNT data_type: NUMBER(38,0) access_modifier: public_access sample_values: - '196' - '79' - '298' primary_key: columns: - ISRC - GLOBAL_PARTICIPANT_ID - PLAYLIST_URI - CURATOR_ID relationships: - name: V_PLAYLISTS_PLACEMENTS_BY_PARTICIPANT_ISRC_PLAYLIST_PUBLIC_TO_ISRC_BRAND_MAPPING left_table: V_PLAYLISTS_PLACEMENTS_BY_PARTICIPANT_ISRC_PLAYLIST_PUBLIC right_table: ISRC_BRAND_MAPPING relationship_columns: - left_column: ISRC right_column: ISRC - name: V_PLAYLISTS_PLACEMENTS_BY_PARTICIPANT_ISRC_PLAYLIST_PUBLIC_TO_DIM_STORE left_table: V_PLAYLISTS_PLACEMENTS_BY_PARTICIPANT_ISRC_PLAYLIST_PUBLIC right_table: DIM_STORE relationship_columns: - left_column: STORE_ID right_column: STOREID - name: V_PLAYLISTS_PLACEMENTS_BY_PARTICIPANT_ISRC_PLAYLIST_PUBLIC_TO_GLOBAL_SOUND_RECORDING left_table: V_PLAYLISTS_PLACEMENTS_BY_PARTICIPANT_ISRC_PLAYLIST_PUBLIC right_table: GLOBAL_SOUND_RECORDING relationship_columns: - left_column: ISRC right_column: ISRC verified_queries: - name: '"For artist with id bfbe7cba-93f1-4c0b-96a2-819397540c04 and track with id USRC12403763. What are the characteristics of playlist called Pop Up from Spotify? "' sql: |- SELECT p.playlist_name, p.curator_name, p.curator_country, p.playlist_type, p.playlist_genres, p.playlist_follower_count, p.playlist_track_count, p.current_position, p.last_added_on_date FROM v_playlists_placements_by_participant_isrc_playlist_public p inner join dim_store s on p.store_id=s.storeid WHERE p.global_participant_id = 'bfbe7cba-93f1-4c0b-96a2-819397540c04' AND p.isrc = 'USRC12403763' AND p.playlist_name ILIKE '%Pop Up%' AND s.storename='Spotify' ORDER BY p.last_added_on_date DESC NULLS LAST LIMIT 15 /* Generated by Cortex Analyst (request_id: 704fed9b-b4ec-42ee-9069-87f96cce94ef) */ question: 'For artist with id bfbe7cba-93f1-4c0b-96a2-819397540c04 and track with id USRC12403763. What are the characteristics of playlist called Pop Up from Spotify? ' verified_at: 1772540873 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"For artist bfbe7cba-93f1-4c0b-96a2-819397540c04, what tracks are on Amazon Music playlists in australia and japan, tracks that were added in 2026."' sql: |- SELECT p.isrc, p.playlist_name, p.curator_name, p.curator_country, p.playlist_type, p.playlist_genres, p.playlist_follower_count, p.playlist_track_count, p.current_position, p.last_added_on_date FROM v_playlists_placements_by_participant_isrc_playlist_public p inner join dim_store s on p.store_id=s.storeid WHERE p.global_participant_id = 'bfbe7cba-93f1-4c0b-96a2-819397540c04' AND p.curator_country IN ('AU', 'JP') AND DATE_PART('YEAR', p.last_added_on_date) = 2026 and s.storename='Amazon Music' ORDER BY p.last_added_on_date DESC NULLS LAST /* Generated by Cortex Analyst (request_id: 71936043-9f88-4424-a334-e734f39e004d) */ question: For artist bfbe7cba-93f1-4c0b-96a2-819397540c04, what tracks are on Amazon Music playlists in australia and japan, tracks that were added in 2026. verified_at: 1772540919 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"Are there any playlists from Youtube that have artist''s bfbe7cba-93f1-4c0b-96a2-819397540c04 tracks added in February?"' sql: |- SELECT p.playlist_name, p.isrc, p.curator_name, p.curator_country, p.playlist_type, p.playlist_genres, p.playlist_follower_count, p.playlist_track_count, p.current_position, p.last_added_on_date FROM v_playlists_placements_by_participant_isrc_playlist_public AS p INNER JOIN dim_store AS s ON p.store_id = s.storeid WHERE p.global_participant_id = 'bfbe7cba-93f1-4c0b-96a2-819397540c04' AND s.storename = 'YouTube' AND DATE_PART('YEAR', p.last_added_on_date) = 2026 AND DATE_PART('MONTH', p.last_added_on_date) = 2 ORDER BY p.last_added_on_date DESC NULLS LAST LIMIT 15 /* Generated by Cortex Analyst (request_id: b8b32e04-9f69-4076-b973-da491a0b56b3) */ question: Are there any playlists from Youtube that have artist's bfbe7cba-93f1-4c0b-96a2-819397540c04 tracks added in February? verified_at: 1772541001 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"What editorial playlists on Spotify have tracks from The Orchard artists?"' sql: |- SELECT p.playlist_name, p.isrc, gsr.name AS track_name, p.curator_name, p.playlist_follower_count, p.current_position, p.last_added_on_date FROM v_playlists_placements_by_participant_isrc_playlist_public p INNER JOIN dim_store s ON p.store_id = s.storeid INNER JOIN isrc_brand_mapping b ON p.isrc = b.isrc INNER JOIN global_sound_recording gsr ON p.isrc = gsr.isrc WHERE s.storename = 'Spotify' AND b.company_brand = 'The Orchard' AND p.playlist_type = 'EDITORIAL' AND p.last_added_on_date >= DATEADD(DAY, -14, CURRENT_DATE()) ORDER BY p.playlist_follower_count DESC NULLS LAST LIMIT 15 question: What editorial playlists on Spotify have tracks from The Orchard artists? verified_at: 1776936041 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"For artist bfbe7cba-93f1-4c0b-96a2-819397540c04, show all editorial playlists their tracks are on, with track names."' sql: |- SELECT gsr.name AS track_name, p.isrc, p.playlist_name, p.playlist_type, s.storename, p.playlist_follower_count, p.current_position, p.last_added_on_date FROM v_playlists_placements_by_participant_isrc_playlist_public p INNER JOIN dim_store s ON p.store_id = s.storeid INNER JOIN global_sound_recording gsr ON p.isrc = gsr.isrc WHERE p.global_participant_id = 'bfbe7cba-93f1-4c0b-96a2-819397540c04' AND p.playlist_type = 'EDITORIAL' ORDER BY p.playlist_follower_count DESC NULLS LAST LIMIT 15 question: For artist bfbe7cba-93f1-4c0b-96a2-819397540c04, show all editorial playlists their tracks are on, with track names. verified_at: 1776936086 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"For artist bfbe7cba-93f1-4c0b-96a2-819397540c04, what Apple Music playlists are their tracks on?"' sql: |- SELECT p.playlist_name, p.isrc, p.curator_name, p.playlist_type, p.playlist_follower_count, p.current_position, p.last_added_on_date FROM v_playlists_placements_by_participant_isrc_playlist_public p INNER JOIN dim_store s ON p.store_id = s.storeid WHERE p.global_participant_id = 'bfbe7cba-93f1-4c0b-96a2-819397540c04' AND s.storename = 'iTunes/Apple' ORDER BY p.playlist_follower_count DESC NULLS LAST LIMIT 15 question: For artist bfbe7cba-93f1-4c0b-96a2-819397540c04, what Apple Music playlists are their tracks on? verified_at: 1776936117 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"For artist bfbe7cba-93f1-4c0b-96a2-819397540c04 and track USRC12403763, what are the biggest playlists this track is on by follower count?"' sql: |- SELECT p.playlist_name, s.storename, p.playlist_type, p.playlist_follower_count, p.playlist_track_count, p.current_position, p.last_added_on_date FROM v_playlists_placements_by_participant_isrc_playlist_public p INNER JOIN dim_store s ON p.store_id = s.storeid WHERE p.global_participant_id = 'bfbe7cba-93f1-4c0b-96a2-819397540c04' AND p.isrc = 'USRC12403763' ORDER BY p.playlist_follower_count DESC NULLS LAST LIMIT 15 question: For artist bfbe7cba-93f1-4c0b-96a2-819397540c04 and track USRC12403763, what are the biggest playlists this track is on by follower count? verified_at: 1776936139 verified_by: Rain Bomberg use_as_onboarding_question: false module_custom_instructions: sql_generation: |- - MANDATORY: Always filter by GLOBAL_PARTICIPANT_ID and/or ISRC. This table has 2.5B+ rows — unfiltered queries will time out. - If user hasn't specified a date range, ORDER BY LAST_ADDED_ON_DATE DESC LIMIT 15. - When users say "Apple Music", filter DIM_STORE.STORENAME = 'iTunes/Apple'. The STORENAME value is 'iTunes/Apple', NOT 'Apple Music'. - When users ask about playlists for a specific brand (SME, Orchard, AWAL, etc.), JOIN to ISRC_BRAND_MAPPING on ISRC and filter by COMPANY_BRAND. - Brand name mappings: "SME" = "Sony Music", "Orchard" = "The Orchard", "AWAL" = "AWAL". - Not all playlist ISRCs have a brand mapping. Only tracks distributed through Orchard/SME/AWAL ecosystem will match. - Always use ILIKE with wildcards for PLAYLIST_NAME filtering (e.g. ILIKE '%name%') — playlist names contain emojis and special characters. - PLAYLIST_TYPE values: EDITORIAL, USER_GENERATED, CHART, CURATED, NEW_MUSIC_FRIDAY, PERSONALIZED, RADIO. Use these exact values for filtering. - CURATOR_COUNTRY is the country of the person/entity that curates the playlist. STOREFRONT is the country/region marketplace where the playlist appears. - Do not editorialize about playlist importance, prestige, or ranking. Use PLAYLIST_FOLLOWER_COUNT and CURRENT_POSITION as objective measures.