# NMF Status Check Monitor New Music Friday playlist ingestion across the full pipeline — from Spotify through to Insights. ## Prerequisites: Spotify Credentials This skill calls the Spotify Web API using the client credentials flow (no user auth required). It needs `SPOTIFY_CLIENT_ID` and `SPOTIFY_CLIENT_SECRET`. On first run (or if credentials are not found), the skill will prompt you to either: - Provide a path to an existing `.env` file containing those variables, or - Enter the credentials directly — the skill will save them to `~/.claude/skills/nmf-status-check/.env` for future runs. If you need to obtain credentials, create an app at https://developer.spotify.com/dashboard. Client credentials flow requires no special scopes. ## Overview The skill runs 5 checkpoints for a target NMF Friday date, plus an automatic timing analysis and an optional tracklist check: 1. **Spotify updated?** — Spotify API: are this week's tracks present? (`added_at ≥ target Friday − 1 day` in UTC, to account for early-timezone markets such as AU that update Thursday ~14:00 UTC) 2. **Pipeline health?** — Snowflake task history + stream state: is `CAPTURE_SPOTIFY_PRIORITY_PLAYLIST_EVENTS` running successfully and has it processed recent data from ChartMetric? 3. **Pipeline ingested?** — `FACTS.PROD.FACT_NEW_MUSIC_FRIDAY_AVAILABILITY`: which markets have `DATA_RECEIVED = TRUE` for the target date? (`CHART_DATE` is already timezone-corrected by the Snowflake task, so no UTC adjustment needed here.) 4. **NMF page available?** — `FACTS.PROD.FACT_CHARTS`: are there tracks per market for the target date? 5. **Playlist page available?** — `FACTS.PROD.PLAYLISTS_PRIORITY_PLACEMENTS_BY_ISRC_PLAYLIST_PUBLIC`: do NMF playlist IDs have placements with `last_added_on_date ≥ target Friday − 1 day` (UTC)? 6. **Pipeline timing** *(automatic when CP4 has data)* — Per-market breakdown of latency through each pipeline stage: Spotify → ChartMetric delivery → FACT_CHARTS write → playlist page. Useful for identifying optimisation opportunities. 7. **Tracklist match?** *(optional — run with `tracklist-check` argument)* — Compare the full tracklist Spotify has in each NMF playlist against what Insights shows in `FACT_CHARTS`, matched by ISRC. After displaying results, ask the user whether to copy a Slack-ready summary to the clipboard. --- ## Step-by-Step Instructions ### 1. Determine the target date If a `date` argument was provided, validate it before using it: - Parse it as a date and check it falls within **±7 days of today**. - If it is outside that window, warn: `⚠️ Provided date {date} is more than 7 days from today ({today}) — this may be the wrong year. Proceeding anyway.` - If the date is not a Friday, warn: `⚠️ {date} is a {weekday}, not a Friday. Proceeding — double-check the date is correct.` Otherwise, compute the target Friday automatically: - **Friday**: use *today*. - **Thursday**: use *tomorrow* (the upcoming Friday). Early-timezone markets (AU, Asia) may already have updated. - **Saturday–Wednesday**: use the *most recent* past Friday. Display: `Checking NMF status for: {date}` and proceed — do not wait for confirmation. ### 2. Fetch NMF playlist IDs from Snowflake Run this query to get the live list of active markets and their Spotify playlist IDs: ```sql SELECT COUNTRY, SPOTIFY_ID FROM FACTS.PROD.DIM_NEW_MUSIC_FRIDAY_CHART WHERE ACTIVE = TRUE ORDER BY COUNTRY ``` Store the results as a market → spotify_id map for use across all subsequent steps. ### 3. Checkpoint 1 — Spotify updated? **Resolve Spotify credentials** using this priority order: 1. Check if `~/.claude/skills/nmf-status-check/.env` exists and contains both `SPOTIFY_CLIENT_ID` and `SPOTIFY_CLIENT_SECRET`. If so, read from there. 2. Otherwise, ask the user: > To check Spotify directly I need Spotify API credentials (client ID + secret). > You can either: > - **[A]** Provide a path to an existing `.env` file that contains `SPOTIFY_CLIENT_ID` and `SPOTIFY_CLIENT_SECRET` > - **[B]** Enter the credentials now and I'll save them for future runs If they choose **[A]**: read from the path they provide. If they choose **[B]**: ask for the client ID and secret separately, then write them to `~/.claude/skills/nmf-status-check/.env`: ``` SPOTIFY_CLIENT_ID= SPOTIFY_CLIENT_SECRET= ``` Confirm: `Credentials saved to ~/.claude/skills/nmf-status-check/.env for future runs.` Once credentials are resolved, read them: ```bash SPOTIFY_CLIENT_ID=$(grep SPOTIFY_CLIENT_ID | cut -d= -f2) SPOTIFY_CLIENT_SECRET=$(grep SPOTIFY_CLIENT_SECRET | cut -d= -f2) ``` Get an access token: ```bash TOKEN=$(curl -s -X POST "https://accounts.spotify.com/api/token" \ -H "Content-Type: application/x-www-form-urlencoded" \ -d "grant_type=client_credentials&client_id=$SPOTIFY_CLIENT_ID&client_secret=$SPOTIFY_CLIENT_SECRET" \ | python3 -c "import sys,json; print(json.load(sys.stdin)['access_token'])") ``` For each NMF playlist ID, fetch the first track's `added_at`: ```bash curl -s "https://api.spotify.com/v1/playlists/{SPOTIFY_ID}/tracks?fields=items(added_at,track(name))&limit=1" \ -H "Authorization: Bearer $TOKEN" ``` A market is **updated** if the returned `added_at` is on or after **target Friday − 1 day** (in UTC). This lookback is necessary because early-timezone markets (e.g. AU at UTC+10) update at midnight local Friday, which falls on Thursday in UTC. NMF playlists are fully replaced each week, so checking a single track is sufficient — all tracks share the same `added_at` timestamp. Any result more than 7 days old means the playlist has not yet updated for this week. Store the **full `added_at` datetime** (ISO 8601, UTC) per market — not just the date. This is used in the pipeline timing analysis to determine the first meaningful ChartMetric delivery. **Early-update detection:** For each updated market, compare `added_at` against the expected release window for that market's timezone (midnight local Friday = `target_friday 00:00` local, converted to UTC). If `added_at` is more than **6 hours earlier** than the expected UTC release time, flag it: > ⚠️ {MARKET} updated at {added_at} UTC — approximately {N}h earlier than expected for its timezone. Confirm this is a genuine early release and not a stale track at position 1. To confirm a suspected early update, fetch the first 5–10 tracks and check they all share the same `added_at`. If they do, the full playlist was swapped and the early update is genuine. If `added_at` varies across tracks, the playlist was not fully replaced and the CP1 result may be misleading. Expected UTC release times by region (approximate, for reference): - UTC+10/+11 (AU, Pacific): Thu ~14:00–15:00 UTC - UTC+8/+9 (APAC — SG, HK, TW, JP, KR): Thu ~15:00–16:00 UTC - UTC+7 (ID, VN, TH, MY, PH): Thu ~17:00 UTC - UTC+2/+3 (EU, Middle East): Thu ~22:00 UTC - UTC+1 (GB, IE, PT): Thu ~23:00 UTC - UTC−5/−6 (US/CA Eastern): Fri ~05:00 UTC - UTC−3/−5 (LatAm): Fri ~03:00–05:00 UTC Summarise: how many markets are updated vs. still showing a prior week's date. Note any flagged early updates. ### 4. Checkpoint 2 — Pipeline health? `L_SPOTIFY_PLAYLIST_SONY` is a Snowflake data share from ChartMetric. Direct queries against it return stale cached snapshots — the Snowflake task reads from it via a stream, which sees live changes before they are visible to direct `SELECT` queries. Querying `ADDED_AT` directly is therefore unreliable as a freshness signal. Instead, check the task's health and stream state. Run both queries: ```sql -- Recent task runs (last 2 hours) SELECT name, state, scheduled_time, completed_time, error_message FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY( SCHEDULED_TIME_RANGE_START => DATEADD('hour', -2, CURRENT_TIMESTAMP()), TASK_NAME => 'CAPTURE_SPOTIFY_PRIORITY_PLAYLIST_EVENTS' )) WHERE state IN ('SUCCEEDED', 'FAILED', 'SKIPPED') ORDER BY scheduled_time DESC LIMIT 10 ``` ```sql -- Does the stream have data waiting to be processed? SELECT SYSTEM$STREAM_HAS_DATA('FACTS.PROD.SPOTIFY_PRIORITY_PLAYLIST_EVENTS') AS stream_has_pending_data ``` The task is stream-gated — it only runs when ChartMetric has posted new data. Interpret results as: - **Normal (idle)**: no recent task runs + stream has no pending data → ChartMetric has not posted new data yet; task is correctly idle - **Active**: stream has pending data + recent `SUCCEEDED` runs → pipeline is consuming new ChartMetric data - **Up to date**: recent `SUCCEEDED` runs + stream has no pending data → pipeline has processed all available data from ChartMetric - **Needs attention**: stream has pending data but no `SUCCEEDED` runs in the last 10 minutes → task may not be triggering; worth investigating - **Problem**: any `FAILED` runs → investigate `error_message` ### 5. Checkpoint 3 — Pipeline ingested? ```sql SELECT country, data_received, COUNT(*) AS market_count FROM FACTS.PROD.FACT_NEW_MUSIC_FRIDAY_AVAILABILITY WHERE chart_date = '{target_friday}' GROUP BY 1, 2 ORDER BY data_received DESC, country ``` If no rows exist at all for the target date, the Snowflake task has not yet run for this week. List any markets where `data_received = FALSE` — these are markets ChartMetric has acknowledged exist but has not delivered data for yet. **Timezone pre-population:** Two overlapping patterns can cause `DATA_RECEIVED = TRUE` in CP3 before Spotify has updated: 1. **Null-timezone markets (CHR, GULF, LEV, SUR):** These markets have no timezone set in `DIM_NEW_MUSIC_FRIDAY_CHART` and default to `Australia/Sydney (UTC+10)`. The task assigns `CHART_DATE = {target_friday}` based on the ChartMetric scrape time alone. 2. **Early-assigned APAC markets:** Markets in UTC+7/+8 timezones (ID, VN, SG, MY, PH, TH, HK, TW, JP, KR) have their `CHART_DATE` assigned when the task processes a ChartMetric delivery that arrives while it is already Friday in their local timezone. This can happen while their Spotify playlist still shows the previous week's content (i.e. the Snowflake task fires on a scrape of old content that ChartMetric delivered just as the timezone tipped over). For any market where CP3 is `DATA_RECEIVED = TRUE` but CP1 shows last week's `added_at`, cross-reference CP4 (track count) and consider whether the ingested content is this week's or last week's. Flag in the output: > ⚠️ {MARKETS} show DATA_RECEIVED=TRUE but their Spotify playlists have not yet updated. This may be timezone pre-population — content in FACT_CHARTS for these markets could reflect last week's tracklist until Spotify refreshes. ### 6. Checkpoint 4 — NMF page available? ```sql SELECT dnmfc.country, COUNT(fc.isrc) AS tracks_in_fact_charts FROM FACTS.PROD.DIM_NEW_MUSIC_FRIDAY_CHART dnmfc LEFT JOIN FACTS.PROD.FACT_CHARTS fc ON fc.chartid = dnmfc.chartid AND fc.chart_date = '{target_friday}' WHERE dnmfc.active = TRUE GROUP BY 1 ORDER BY tracks_in_fact_charts ASC ``` A market is **live on the NMF page** if `tracks_in_fact_charts > 0`. Flag any markets with zero tracks that have `data_received = TRUE` in checkpoint 3 — that would indicate a data quality issue. ### 7. Checkpoint 5 — Playlist page available? ```sql SELECT dnmfc.country, dnmfc.spotify_id, COUNT(DISTINCT p.isrc) AS tracked_isrcs, MAX(p.last_added_on_date) AS latest_placement_date, CASE WHEN MAX(p.last_added_on_date) >= DATEADD(day, -1, '{target_friday}'::date) THEN TRUE ELSE FALSE END AS updated_this_week FROM FACTS.PROD.DIM_NEW_MUSIC_FRIDAY_CHART dnmfc LEFT JOIN FACTS.PROD.PLAYLISTS_PRIORITY_PLACEMENTS_BY_ISRC_PLAYLIST_PUBLIC p ON p.store_playlist_id = dnmfc.spotify_id AND p.store_id = 286 WHERE dnmfc.active = TRUE GROUP BY 1, 2 ORDER BY updated_this_week ASC, country ``` A market is **live on the playlist page** if `updated_this_week = TRUE`. The −1 day UTC lookback matches checkpoints 1 and 2, ensuring early-timezone markets are not incorrectly flagged as pending. ### 8. Pipeline Timing Analysis Run automatically after checkpoint 5 when any markets have `tracks_in_fact_charts > 0` (checkpoint 4). Shows how long ChartMetric took to scrape each market after Spotify's playlist refresh. **Query — First post-Spotify-update ChartMetric delivery, per market (pre-aggregated, market-filtered):** Only include markets that have CP4 data (tracks in `FACT_CHARTS`) AND have a known Spotify `added_at` from CP1. Build the WHERE clause dynamically using one `OR` condition per market with its exact CP1 `added_at` as the cutoff. This avoids returning the full delivery log (which can exceed tool output limits) and excludes deliveries of the old week's content. ```sql SELECT dnmfc.country, MIN(log.update_sent[0]::TIMESTAMP_NTZ) AS first_meaningful_delivery FROM FACTS.PROD.SPOTIFY_PRIORITY_PLAYLIST_PROCESSING_LOG log JOIN FACTS.PROD.DIM_NEW_MUSIC_FRIDAY_CHART dnmfc ON log.playlist_id = dnmfc.spotify_id WHERE dnmfc.country IN ({comma-separated CP4 markets with known spotify_added_at}) AND ( (dnmfc.country = 'AU' AND log.update_sent[0]::TIMESTAMP_NTZ > '{AU_spotify_added_at}'::TIMESTAMP_NTZ) OR (dnmfc.country = 'JP' AND log.update_sent[0]::TIMESTAMP_NTZ > '{JP_spotify_added_at}'::TIMESTAMP_NTZ) -- ... one line per market ) GROUP BY 1 ORDER BY 1 ``` For each market, the **first meaningful ChartMetric delivery** is the minimum delivery timestamp after the market's Spotify `added_at`. Deliveries before `spotify_added_at` contain the old week's content and must be excluded — this is why per-market cutoffs are essential rather than a single global floor. If no delivery exists after `spotify_added_at`, the market has not yet been picked up and should be omitted from the timing table. **Lag A — Spotify → ChartMetric** (`first_meaningful_delivery − spotify_added_at`): how long after Spotify's official playlist refresh until ChartMetric first scraped the new content. Always ≥ 0. If the result is negative, the per-market cutoff was too early (a pre-update delivery was captured) — exclude that market from the table. **Note on NMF page timing:** `FACT_CHARTS.modified_at` is overwritten on every batch write, so `MIN(modified_at)` does not reliably reflect when data *first* appeared — it reflects the most recent write. NMF page timing has therefore been removed from this analysis. CP3 (`FACT_NEW_MUSIC_FRIDAY_AVAILABILITY`) has no ingestion timestamp and also cannot be timed. **Display** as a table sorted by Spotify `added_at` ascending, only for markets where Lag A ≥ 0: ``` Pipeline timing — Spotify → ChartMetric lag Market Spotify →CM ------ ----------- ------- AU 14:00 UTC +0h 01m JP 15:00 UTC +0h 07m ``` Where **Spotify** is the absolute `added_at` time in UTC. Markets in CP4 but without a CP1 `added_at` (Spotify not yet updated — timezone pre-population) are excluded. Below the table, include: > ℹ️ "→CM" is time from Spotify's official playlist refresh to ChartMetric's first post-refresh scrape. NMF page and playlist page timing are not shown: `FACT_CHARTS.modified_at` is overwritten on batch writes (not a reliable first-write timestamp), and `last_added_on_date` carries the Spotify added_at rather than a pipeline write time. Most useful when checked on the day markets first ingest, before any subsequent batch rewrites occur. ### 9. Checkpoint 6 — Tracklist match? *(optional)* Only run this checkpoint if the skill was invoked with a `tracklist-check` argument (e.g. `/nmf-status-check tracklist-check`). **Prerequisites:** Checkpoint 1 must have succeeded (Spotify API available and returning valid data). If checkpoint 1 was unavailable or errored, skip this checkpoint and note: `⚠️ Tracklist check skipped — Spotify API unavailable.` **Fetch all ISRCs from Spotify** for each market where checkpoint 4 has tracks (`tracks_in_fact_charts > 0`). Paginate through the full playlist — NMF playlists have 50–130+ tracks, so use `limit=100` and follow the `next` URL until exhausted: ```bash # Page 1 curl -s "https://api.spotify.com/v1/playlists/{SPOTIFY_ID}/tracks?fields=items(track(name,external_ids(isrc))),next&limit=100&offset=0" \ -H "Authorization: Bearer $TOKEN" # Continue with offset=100 if `next` is not null ``` Collect all ISRCs where `track.external_ids.isrc` is non-null, normalised to **uppercase**. Also track: - **Unavailable tracks** — items where `track` is `null` (geo-blocked or removed tracks that Spotify still lists as a slot). Count these per market as `unavailable_count`. Do not include them in `spotify_count`. - **Null-ISRC tracks** — items where `track` is present but `external_ids.isrc` is null (local files or unlicensed content — rare). Skip these too. **Position shift:** ChartMetric excludes unavailable tracks from their feed entirely. This means FACT_CHARTS positions are renumbered without the unavailable slots, producing a 0–N offset between Spotify position numbers and FACT_CHARTS position numbers. This is expected behaviour, not a bug. The mapping rule is: `FACT_CHARTS position = Spotify position − (count of unavailable tracks before that position)`. **Fetch all ISRCs from FACT_CHARTS** for the target date in a single query. Include null ISRC rows — they represent tracks ChartMetric delivered without a resolvable ISRC (a ChartMetric data quality issue, not a pipeline failure): ```sql SELECT dnmfc.country, fc.isrc, fc.position FROM FACTS.PROD.FACT_CHARTS fc JOIN FACTS.PROD.DIM_NEW_MUSIC_FRIDAY_CHART dnmfc ON fc.chartid = dnmfc.chartid WHERE fc.chart_date = '{target_friday}' AND dnmfc.active = TRUE ORDER BY dnmfc.country, fc.position ``` Separate null-ISRC rows from valid rows per market. Use only valid (non-null) ISRCs, uppercased, for the match comparison. **For each market, compute:** - `spotify_count` — number of valid ISRCs on Spotify (excluding unavailable tracks and null-ISRC tracks) - `insights_count` — number of valid (non-null) ISRCs in FACT_CHARTS - `null_isrc_count` — rows in FACT_CHARTS with null ISRC (ChartMetric data quality gaps — track ingested but ISRC unresolved) - `matched` — ISRCs present in both (case-insensitive — uppercase both sides before comparing) - `only_spotify` — ISRCs on Spotify but not in FACT_CHARTS (not yet ingested, or corresponds to a null_isrc_count row) - `only_insights` — ISRCs in FACT_CHARTS but not on Spotify (stale tracks no longer in the playlist) - `match_pct` — `matched / max(spotify_count, insights_count) * 100` **Display** as a table, sorted by match % ascending (worst first): ``` 6. Tracklist match Market Spotify Insights Matched +Spotify +Insights Match% ------ ------- -------- ------- -------- --------- ------ US 100 100 98 2 2 98% AU 86 86 86 0 0 100% ... ``` Where `+Spotify` = only on Spotify (missing from Insights), `+Insights` = only in Insights (removed from Spotify). **Interpret results:** - **100% match** — tracklists are identical. - **+Spotify only + null_isrc_count > 0** — ChartMetric delivered those tracks without an ISRC. The confirmed root cause is geo-restriction: tracks with `is_playable: false` and `restrictions.reason: "market"` in the playlist's home market have their ISRCs dropped by ChartMetric's pipeline, even though the Spotify API returns the ISRC when called with a market parameter. The track IS in Insights but shows as a blank entry at that position. See the ChartMetric Data Quality Diagnostic section below to confirm, identify which failure mode applies, and gather evidence for a bug report. - **+Spotify only (null_isrc_count = 0)** — pipeline hasn't fully ingested yet (normal if checkpoint 3 is still processing); or the track was genuinely missed by ChartMetric's scrape for that market. - **+Insights only (no +Spotify)** — Insights is showing tracks that are no longer on the Spotify playlist. These are stale and could indicate a data quality issue. - **Both +Spotify and +Insights** — mixed state; ISRC variant mismatches are the most common cause. A small number (1–3 per market) is expected due to regional re-releases. **Caveats to include in output:** > ℹ️ A small number of unmatched ISRCs per market is expected due to regional ISRC variants (the same recording released under different ISRCs in different territories). This check is most reliable after all markets have fully ingested (checkpoint 3 = 57/57). --- ## Displaying Results Present a summary table with one row per checkpoint, showing counts (e.g. `57/57 ✅` or `52/57 ⚠️`), followed by a per-market breakdown for any checkpoint that has failures or pending markets. Use this format: ``` NMF Status — {target_friday} ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 1. Spotify updated {N}/57 markets ✅ / ⚠️ / ❌ 2. Pipeline health Active / Up to date / Needs attention ✅ / ⚠️ / ❌ 3. Pipeline ingested {N}/57 DATA_RECEIVED=TRUE ✅ / ⚠️ / ❌ 4. NMF page {N}/57 markets with tracks ✅ / ⚠️ / ❌ 5. Playlist page {N}/57 markets updated ✅ / ⚠️ / ❌ 6. Tracklist match {N}/57 markets 100% matched ✅ / ⚠️ / ❌ (only shown if tracklist-check was run) ``` If any step has failures, list the affected markets below the table and briefly diagnose: - Step 2 (pipeline health) shows failures or pending stream data with no task runs → pipeline issue; check `error_message` from task history. - Step 3 failing + step 2 healthy (task running, no pending stream data) → ChartMetric has not yet posted this week's data; lag is normal earlier in the day. - Step 3 failing + step 2 shows stream has pending data but task is not running → task may not be triggering; investigate. - Step 4 failing but step 3 passing → Data quality issue in `FACT_CHARTS` for that market. - Step 5 failing → Priority playlist tracker has not picked up this week's tracklist yet (normal until a few hours after the Snowflake task runs). - CHR / GULF / LEV / SUR appear in step 3 before step 1 → expected; these markets have null timezone in `DIM_NEW_MUSIC_FRIDAY_CHART` and default to Australia/Sydney, causing early `CHART_DATE` assignment. - Other APAC markets (ID, VN, SG, MY, PH, TH, HK, TW, JP, KR) appear in step 3 before step 1 → also expected during the Thursday UTC window; the task assigns `CHART_DATE = {target_friday}` based on local timezone before Spotify's playlist refresh has propagated. The ingested content may be last week's tracklist — verify once CP1 updates for those markets. - Step 1 flags an anomalously early `added_at` for a market → investigate whether the playlist genuinely updated early (check that multiple tracks share the same timestamp) or whether a stale track at position 1 is giving a false signal. - Step 6 shows +Spotify mismatches + null ISRC rows in FACT_CHARTS → ChartMetric data quality issue; track was delivered without an ISRC. Use the ChartMetric Data Quality Diagnostic below to confirm. - Step 6 shows +Spotify mismatches with no null rows → pipeline still processing, or ChartMetric omitted the track from their feed for that market; review after step 3 is 57/57. - Step 6 shows +Insights mismatches → stale tracks in FACT_CHARTS no longer on Spotify playlist; potential data quality issue. - OTH shows DATA_RECEIVED=TRUE but step 5 (playlist page) is stale → OTH is a global catch-all playlist that sometimes receives a re-delivery of the previous week's tracklist from ChartMetric. Expected if OTH's Spotify playlist also hasn't updated (verify via step 1 for OTH). ### ChartMetric Data Quality Diagnostic When step 6 shows +Spotify mismatches that correspond to null ISRC rows in FACT_CHARTS, the root cause is in ChartMetric's data feed. The confirmed mechanism is geo-restriction: ChartMetric drops ISRCs for tracks that are `is_playable: false` with `restrictions.reason: "market"` in the playlist's home market, even though the Spotify API returns the ISRC when called with that market. Use these steps to confirm and gather evidence for a bug report. **Two failure modes — identify which applies before filing a bug:** - **Mode A (catalogued, ISRC missing):** Track IS in `CHARTMETRIC.RAW_DATA.SPOTIFY` but with `ISRC = null`. ChartMetric has the track in their catalogue but lost the ISRC at scrape time. - **Mode B (not in catalogue):** Track is completely absent from `CHARTMETRIC.RAW_DATA.SPOTIFY` — not indexed at all — yet was still delivered via the stream with null ISRC. More severe; ChartMetric has no record of the track. --- **Step 1 — Confirm the null rows in FACT_CHARTS:** ```sql SELECT fc.position, fc.isrc, fc.chartmetric_track_id FROM FACTS.PROD.FACT_CHARTS fc JOIN FACTS.PROD.DIM_NEW_MUSIC_FRIDAY_CHART dnmfc ON fc.chartid = dnmfc.chartid WHERE fc.chart_date = '{target_friday}' AND dnmfc.country = '{COUNTRY}' AND fc.isrc IS NULL ORDER BY fc.position; ``` Note the `chartmetric_track_id` for each null row — this is needed for the RAW_DATA lookups below. **Step 2 — Prove the null ISRC came from ChartMetric's feed** (not introduced by our pipeline) by querying the processing log, which stores the raw per-track payload as received from ChartMetric's stream: ```sql WITH latest AS ( SELECT * FROM FACTS.PROD.SPOTIFY_PRIORITY_PLAYLIST_PROCESSING_LOG WHERE playlist_id = '{SPOTIFY_PLAYLIST_ID}' ORDER BY tracks_updated DESC LIMIT 1 ) SELECT latest.playlist_id, f.value:position::INT AS position, f.value:track_id::STRING AS chartmetric_track_id, f.value:track_name::STRING AS track_name, f.value:artist_names::STRING AS artist_name, f.value:isrc::STRING AS isrc FROM latest, LATERAL FLATTEN(input => track_objects) f WHERE f.value:isrc IS NULL ORDER BY position; ``` A null `isrc` alongside a non-null `track_id` confirms ChartMetric has identified the track but failed to resolve its ISRC. Use the `chartmetric_track_id` in the bug report as the reference for the affected track. **Step 3 — Determine the failure mode using ChartMetric's RAW_DATA tables:** ```sql -- Look up each affected Spotify track ID in ChartMetric's catalogue -- Mode A: returns row with ISRC = null -- Mode B: returns no rows SELECT spotify_track_id, track_name, artist_name, isrc, created_at, modified_at FROM CHARTMETRIC.RAW_DATA.SPOTIFY WHERE spotify_track_id IN ('{SPOTIFY_TRACK_ID_1}', '{SPOTIFY_TRACK_ID_2}'); ``` For Mode A tracks, confirm playlist membership (note: `position_latest` is 0-indexed; Spotify API positions are 1-indexed): ```sql SELECT sp.playlist_id, sp.name AS playlist_name, sp.code2 AS country, lsp.position_latest, s.spotify_track_id, s.track_name, s.artist_name, s.isrc FROM CHARTMETRIC.RAW_DATA.SPOTIFY s JOIN CHARTMETRIC.RAW_DATA.L_SPOTIFY_PLAYLIST lsp ON lsp.spotify = s.id JOIN CHARTMETRIC.RAW_DATA.SPOTIFY_PLAYLIST sp ON sp.id = lsp.spotify_playlist WHERE s.spotify_track_id = '{SPOTIFY_TRACK_ID}' AND sp.playlist_id IN ('{NMF_PLAYLIST_ID_1}', '{NMF_PLAYLIST_ID_2}'); ``` **Step 4 — Verify Spotify returns the ISRC** even for geo-restricted tracks (confirm the data is available to ChartMetric): ```bash curl -s "https://api.spotify.com/v1/tracks/{TRACK_ID}?market={MARKET_CODE}" \ -H "Authorization: Bearer $TOKEN" | python3 -c " import sys, json t = json.load(sys.stdin) print('ISRC:', t['external_ids'].get('isrc')) print('is_playable:', t.get('is_playable')) print('restrictions:', t.get('restrictions')) " ``` A result with `is_playable: false`, `restrictions.reason: market`, and a non-null `isrc` proves ChartMetric has the ISRC available but is not carrying it through their pipeline. Include this output alongside the RAW_DATA query results in the bug report. **Note on data share staleness:** `CHARTMETRIC.SHARES.L_SPOTIFY_PLAYLIST_SONY` (ChartMetric's raw data share) returns a cached snapshot that is typically 3–5+ weeks stale under direct `SELECT`. It is not useful for investigating current-week issues. `SPOTIFY_PRIORITY_PLAYLIST_PROCESSING_LOG` is the reliable source for current-week evidence. --- ## Clipboard Copy After displaying results, ask: > Would you like to copy a Slack summary to your clipboard? If the user confirms, format a Slack-ready message and pipe it to `pbcopy`: ```bash cat <<'SLACK' | pbcopy :musical_note: *NMF Status — {target_friday}* {For each checkpoint, one line:} 1. Spotify updated: {✅ 57/57} or {⚠️ N/57 — markets: X, Y, Z} 2. ChartMetric scraped: ... 3. Pipeline ingested: ... 4. NMF page: ... 5. Playlist page: ... {One of:} ✅ All systems go for {target_friday} NMF. ⚠️ Partial — {N} markets still pending at step {X}. May resolve as ChartMetric finishes scraping. ❌ Issue detected at step {X}. {Brief diagnosis}. SLACK ``` Then confirm: `Summary copied to clipboard.`