---
name: dsp-backfill
description: Use when a user reports missing analytics data for a track on Spotify or Apple Music in Insights, and the root cause is that dim_track was created after the release date (so staging data existed but failed to ingest into fact_analytics). Triggers on phrases like "backfill Spotify/Apple Music", "missing streams for ISRC", "data not in fact_analytics", "streams visible in staging but not Insights". SKIP if the issue is permissions/access (IN-17197 pattern), bad spotifyId, or a dbt rebuild issue.
---

# DSP Fact Analytics Backfill

## When to Use This Skill

**TRIGGER — use this skill when:**
- A user says streams are missing in Insights for a specific track/ISRC
- Data is visible in `staging_raw_spotify_v2` or `staging_raw_apple_music_summary_streams` but NOT in `fact_analytics`
- You confirm the track was created in `dim_track` *after* the release date
- A Jira ticket asks to "backfill Spotify and/or Apple Music" for a date range

**SKIP if:**
- The issue is a permissions/visibility problem (check `fullCatalogAccess` flag instead — see IN-17197 pattern)
- The ISRC is linked to the wrong GlobalParticipant (LP→GP bug — see `insights_lp_gp_spotify_new_bucket.md` memory)
- The data is missing because it was never reported to us by the DSP (not an ingestion failure)

---

## Root Cause

The standard cause: the track is released and DSPs report streams on Day 1, but `dim_track` (the internal track master) is not created until a few days later. The ingestion pipeline joins staging data against `dim_track_clean_mv` — if the track doesn't exist yet, rows go to `fact_analytics_error` instead of `fact_analytics`. Once `dim_track` is created, *future* data processes correctly, but the historical rows sit in `fact_analytics_error` forever until manually backfilled.

---

## Step 1 — Investigate Before Writing SQL

Run these Snowflake queries in order. Fill in `<ISRC>` with the actual value.

### 1a. Confirm staging data exists and find the date gap

```sql
-- Spotify
SELECT download_date, upc, licensor, COUNT(*) AS row_count
FROM facts.prod.staging_raw_spotify_v2
WHERE UPPER(isrc) = '<ISRC>'
  AND download_date >= DATEADD('day', -30, CURRENT_DATE)
GROUP BY 1, 2, 3
ORDER BY 1;

-- Apple Music
SELECT download_date, vendor_name, COUNT(*) AS row_count
FROM facts.prod.staging_raw_apple_music_summary_streams
WHERE UPPER(isrc) = '<ISRC>'
  AND download_date >= DATEADD('day', -30, CURRENT_DATE)
GROUP BY 1, 2
ORDER BY 1;
```

Note the **earliest download_date** — that's where the backfill must start.

### 1b. Get track metadata (needed for SQL parameters)

```sql
SELECT dt.trackid, dt.isrcid, dt.labelid, dt.upc, dt.isrc,
       dr.subaccountid, dr.artistid, dr.genreid, dr.catalogid, dr.imprintid, dr.releaseid
FROM facts.prod.dim_track_clean_mv dt
INNER JOIN facts.prod.dim_release dr ON dr.releaseid = dt.upc
WHERE UPPER(dt.isrc) = '<ISRC>'
  AND dr.product_type = 'digital';
```

If this returns no rows, the track still doesn't exist in dim_track — stop and investigate why.

### 1c. Check what's already in fact_analytics (find the gap end)

```sql
SELECT dd.displaydate, fa.storeid, dl.storelicensorid, COUNT(*) AS row_count
FROM facts.prod.fact_analytics fa
INNER JOIN facts.prod.dim_day dd ON dd.dayid = fa.dayid
INNER JOIN facts.prod.dim_licensor dl ON dl.licensorid = fa.licensorid
WHERE fa.trackid = <trackid_from_1b>
  AND dd.displaydate >= DATEADD('day', -30, CURRENT_DATE)
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;
```

The **first date** where `storeid=286` (Spotify) or `storeid=1` (Apple) appears is where normal processing resumed. The backfill covers everything *before* that date.

### 1d. Confirm rows are in fact_analytics_error

```sql
-- Spotify errors
SELECT reportdate, storeid, COUNT(*) AS row_count
FROM facts.prod.fact_analytics_error
WHERE reportdate BETWEEN '<start_date>' AND '<end_date>'
  AND stagingrawid IN (
    SELECT row_id FROM facts.prod.staging_raw_spotify_v2
    WHERE UPPER(isrc) = '<ISRC>'
      AND download_date BETWEEN '<start_date>' AND '<end_date>'
  )
GROUP BY 1, 2 ORDER BY 1;

-- Apple errors
SELECT reportdate, storeid, COUNT(*) AS row_count
FROM facts.prod.fact_analytics_error
WHERE reportdate BETWEEN '<start_date>' AND '<end_date>'
  AND stagingrawid IN (
    SELECT row_id FROM facts.prod.staging_raw_apple_music_summary_streams
    WHERE UPPER(isrc) = '<ISRC>'
      AND download_date BETWEEN '<start_date>' AND '<end_date>'
  )
GROUP BY 1, 2 ORDER BY 1;
```

Row counts here should match the staging counts from Step 1a. If they don't match, investigate what happened to the missing rows.

### 1e. Get dayids for the backfill range

```sql
SELECT displaydate, dayid
FROM facts.prod.dim_day
WHERE displaydate BETWEEN '<start_date>' AND '<end_date>'
ORDER BY 1;
```

### 1f. Check Apple Music mapping (Apple only)

```sql
SELECT apple_id, upc, isrc
FROM facts.prod.sony_apple_id_mapping
WHERE UPPER(isrc) = '<ISRC>'
UNION ALL
SELECT apple_id, upc, isrc
FROM facts.prod.sony_apple_id_mapping_derived
WHERE UPPER(isrc) = '<ISRC>';
```

If this returns nothing, the Apple backfill will insert zero rows — the ISRC isn't in the SME mapping tables. Investigate before creating the PR.

---

## Step 2 — Fill In Parameters

By the end of Step 1 you should have:

| Parameter | Where to find it | Example (IN-17335) |
|---|---|---|
| `backfill_isrc` | The ISRC from the ticket | `NOM7G2601010` |
| `backfill_start_date` | Earliest date in staging with no fact_analytics row | `2026-05-07` |
| `backfill_end_date` | Day *before* normal processing resumed (inclusive for Spotify BETWEEN, exclusive for Apple `<`) | `2026-05-14` (Spotify) / `2026-05-15` (Apple) |
| `start_dayid` / `end_dayid` | From Step 1e | `9990` / `9997` |
| Spotify licensors | `licensor` values from staging query | `sme`, `smejpintl` |
| Apple `licensor` variable | `sme` for SME/Norway/Japan tracks, `awal` for AWAL, `theorchard` for Orchard | `sme` |

---

## Step 3 — Create the SQL Files

**File location:** `snowflake/FACTS/build/changelog/dml/`
**Naming:** `<TICKET>_backfill_spotify_fact_analytics.sql` and `<TICKET>_backfill_apple_fact_analytics.sql`
**Changeset author:** `rroy`

### Spotify SQL — 3 changesets

Follows the DS-9637 pattern. One changeset per licensor group to build the staging table, then a final changeset to apply the DML.

**Changeset 1** — Create staging table for `sme` rows:

```sql
--liquibase formatted sql

--changeset rroy:1 runAlways:true runOnChange:true

set storeid = 286;
set feedid = 1;
set licensor = 'sme';
set backfill_start_date = '<YYYY-MM-DD>';
set backfill_end_date = '<YYYY-MM-DD>';
set start_dayid = <dayid>;
set end_dayid = <dayid>;
set backfill_isrc = '<ISRC>';

CREATE OR REPLACE TRANSIENT TABLE FACTS.<SCHEMA_NAME>.<ticket>_spotify_staging_fact_analytics_backfill
AS
WITH
    fa AS (
        SELECT labelid, subaccountid, trackid, isrcid, stagingrawid,
               artistid, genreid, releaseid, catalogid, imprintid
        FROM FACTS.<SCHEMA_NAME>.fact_analytics
        WHERE dayid BETWEEN $start_dayid AND $end_dayid
          AND storeid = $storeid
          AND feedid = $feedid
          AND licensorid IN (
            SELECT licensorid FROM FACTS.<SCHEMA_NAME>.dim_licensor
            WHERE feedid = $feedid
              AND storelicensorid IN (SELECT value FROM TABLE(SPLIT_TO_TABLE($licensor, ',')))
          )
    ),
    track_release_data AS (
        SELECT dr.releaseid, dr.artistid, dr.genreid, dr.catalogid, dr.imprintid, dr.subaccountid,
               dr.display_upc, dt.trackid, dt.labelid, dt.isrcid, dt.isrc
        FROM FACTS.<SCHEMA_NAME>.dim_release dr
            INNER JOIN FACTS.<SCHEMA_NAME>.dim_track_clean_mv dt ON dt.upc = dr.releaseid
        WHERE dr.product_type = 'digital'
    )
SELECT
    COALESCE(trd.artistid, trf.artistid) AS artistid,
    dd.dayid,
    COALESCE(trd.labelid, trf.labelid) AS labelid,
    $storeid AS storeid,
    COALESCE(trd.genreid, trf.genreid) AS genreid,
    COALESCE(trd.releaseid, trf.releaseid) AS releaseid,
    COALESCE(trd.trackid, trf.trackid) AS trackid,
    dc.countryid,
    dtt.transactiontypeid,
    COALESCE(dz1.zipid, dz2.zipid, 0) AS zipid,
    COALESCE(trd.catalogid, trf.catalogid) AS catalogid,
    COALESCE(trd.isrcid, trf.isrcid) AS isrcid,
    COALESCE(trd.imprintid, trf.imprintid) AS imprintid,
    170 AS currencyid,
    NULL AS storeuserid,
    TO_TIMESTAMP_NTZ(CURRENT_TIMESTAMP(3)) AS processeddaytime,
    0 AS royalty,
    0 AS royaltydollar,
    1 AS units,
    1 AS paidunits,
    0 AS freeunits,
    srs.row_id AS uuid,
    COALESCE(trd.subaccountid, trf.subaccountid) AS subaccountid,
    srs.download_date AS download_activity_date,
    1 AS formatid,
    srs.length AS listenduration,
    dp.playlistid,
    dst.sourcetypeid,
    NULL AS containertypeid,
    srs.download_date AS reportdate,
    srs.row_id AS stagingrawid,
    df.feedid,
    NULL AS userid,
    srs.user_id AS storeuserid_varchar,
    dl.licensorid,
    srs.user_region AS regioncode,
    dsubscrpaytier.subscriptionpaytierid,
    dsubscrtype.subscriptiontypeid,
    ddv.deviceid,
    dos.osid,
    CASE WHEN fa.stagingrawid IS NULL THEN 'INSERT' ELSE 'UPDATE' END AS change_mode
FROM FACTS.<SCHEMA_NAME>.staging_raw_spotify_v2 srs
    INNER JOIN FACTS.<SCHEMA_NAME>.dim_feed df ON $feedid = df.feedid
    INNER JOIN FACTS.<SCHEMA_NAME>.dim_day dd ON dd.displaydate = srs.download_date
    INNER JOIN FACTS.<SCHEMA_NAME>.dim_country dc ON dc.country_code = srs.user_country
    LEFT JOIN track_release_data trd ON trd.releaseid = srs.upc AND UPPER(trd.isrc) = UPPER(srs.isrc)
    LEFT JOIN FACTS.<SCHEMA_NAME>.sme_product_mapping_view trf
        ON srs.licensor IN ('sme', 'smejp', 'smejpintl', 'smecharity')
        AND trd.releaseid IS NULL AND UPPER(trf.isrc) = UPPER(srs.isrc)
    LEFT JOIN FACTS.<SCHEMA_NAME>.dim_zip dz1 ON dz1.zipcode = srs.zipcode AND dz1.country_code = srs.user_country
    LEFT JOIN FACTS.<SCHEMA_NAME>.dim_zip dz2 ON dz2.country_code = srs.user_country AND TRIM(dz2.zipcode) = ''
    LEFT JOIN FACTS.<SCHEMA_NAME>.map_transactiontype mt ON mt.inputvalue = srs.user_access AND mt.storeId = $storeid
    INNER JOIN FACTS.<SCHEMA_NAME>.dim_transactiontype dtt ON COALESCE(mt.outputvalue, 'AS') = dtt.transactiontypeabbr
    LEFT JOIN FACTS.<SCHEMA_NAME>.dim_playlist dp
        ON dp.storeplaylistid = CASE ARRAY_SIZE(SPLIT(srs.source_uri, ':')) <= 1
                                    WHEN TRUE THEN srs.source_uri
                                    WHEN FALSE THEN
                                        CASE WHEN ARRAY_SIZE(SPLIT(srs.source_uri, ':')) = 5 THEN SPLIT_PART(srs.source_uri, ':', 5)
                                             ELSE SPLIT_PART(srs.source_uri, ':', 3) END
                                 END
        AND dp.storeid = $storeid AND dp.playlisttypeid = srs.source AND dp.feedid = df.feedid
    LEFT JOIN FACTS.<SCHEMA_NAME>.dim_sourcetype dst ON dst.storeid = $storeid AND dst.feedid = df.feedid AND dst.storesourcetypeid = srs.source
    LEFT JOIN FACTS.<SCHEMA_NAME>.dim_subscriptiontype dsubscrtype ON dsubscrtype.storeid = $storeid AND dsubscrtype.feedid = df.feedid AND dsubscrtype.subscriptiontypename = srs.product
    LEFT JOIN FACTS.<SCHEMA_NAME>.dim_device ddv ON ddv.devicedesc = srs.device_type
    LEFT JOIN FACTS.<SCHEMA_NAME>.dim_os dos ON dos.osdesc = srs.os
    LEFT JOIN FACTS.<SCHEMA_NAME>.dim_subscriptionpaytier dsubscrpaytier
        ON dsubscrpaytier.storeid = $storeid AND dsubscrpaytier.feedid = $feedid AND dsubscrpaytier.subscriptionpaytiername = srs.user_type
    INNER JOIN FACTS.<SCHEMA_NAME>.dim_licensor dl ON dl.storelicensorid = srs.licensor AND dl.feedid = $feedid
    LEFT JOIN fa ON fa.stagingrawid = srs.row_id
WHERE srs.download_date BETWEEN $backfill_start_date AND $backfill_end_date
  AND UPPER(srs.isrc) = $backfill_isrc
  AND srs.licensor IN (SELECT value FROM TABLE(SPLIT_TO_TABLE($licensor, ',')))
  AND COALESCE(trd.trackid, trf.trackid) IS NOT NULL
  AND (
    fa.stagingrawid IS NULL OR (
        fa.stagingrawid IS NOT NULL AND (
            NOT EQUAL_NULL(COALESCE(trd.trackid, trf.trackid), fa.trackid) OR
            NOT EQUAL_NULL(COALESCE(trd.isrcid, trf.isrcid), fa.isrcid) OR
            NOT EQUAL_NULL(COALESCE(trd.labelid, trf.labelid), fa.labelid) OR
            NOT EQUAL_NULL(COALESCE(trd.subaccountid, trf.subaccountid), fa.subaccountid) OR
            NOT EQUAL_NULL(COALESCE(trd.artistid, trf.artistid), fa.artistid) OR
            NOT EQUAL_NULL(COALESCE(trd.genreid, trf.genreid), fa.genreid) OR
            NOT EQUAL_NULL(COALESCE(trd.releaseid, trf.releaseid), fa.releaseid) OR
            NOT EQUAL_NULL(COALESCE(trd.catalogid, trf.catalogid), fa.catalogid) OR
            NOT EQUAL_NULL(COALESCE(trd.imprintid, trf.imprintid), fa.imprintid)
        )
    )
  );
```

**Changeset 2** — INSERT `smejpintl` rows into the same staging table. Identical SELECT, just change:
- `set licensor = 'smejpintl';` (or whatever other licensor appeared in Step 1a)
- First line becomes `INSERT INTO FACTS.<SCHEMA_NAME>.<ticket>_spotify_staging_fact_analytics_backfill`

If Step 1a showed only `sme` (no `smejpintl`), skip this changeset.

**Changeset 3** — Apply the DML (`set licensor = 'sme,smejpintl'`):

```sql
--changeset rroy:3 runAlways:true runOnChange:true

set storeid = 286;
set feedid = 1;
set licensor = 'sme,smejpintl';
set backfill_start_date = '<YYYY-MM-DD>';
set backfill_end_date = '<YYYY-MM-DD>';
set start_dayid = <dayid>;
set end_dayid = <dayid>;
set backfill_isrc = '<ISRC>';

-- Update rows that were previously inserted but with wrong dim IDs (rare in this case)
UPDATE FACTS.<SCHEMA_NAME>.fact_analytics fa
SET trackid = ss.trackid, isrcid = ss.isrcid, labelid = ss.labelid,
    subaccountid = ss.subaccountid, artistid = ss.artistid, genreid = ss.genreid,
    releaseid = ss.releaseid, catalogid = ss.catalogid, imprintid = ss.imprintid
FROM FACTS.<SCHEMA_NAME>.<ticket>_spotify_staging_fact_analytics_backfill ss
WHERE fa.dayid BETWEEN $start_dayid AND $end_dayid
  AND fa.storeid = $storeid AND fa.feedid = $feedid
  AND ss.change_mode = 'UPDATE' AND fa.stagingrawid = ss.stagingrawid;

-- Clean up fact_analytics_error
DELETE FROM FACTS.<SCHEMA_NAME>.fact_analytics_error
WHERE reportdate BETWEEN $backfill_start_date AND $backfill_end_date
  AND storeid = $storeid AND feedid = $feedid
  AND stagingrawid IN (
    SELECT stagingrawid FROM FACTS.<SCHEMA_NAME>.<ticket>_spotify_staging_fact_analytics_backfill
    WHERE change_mode = 'INSERT'
  );

-- Insert the missing rows
INSERT INTO FACTS.<SCHEMA_NAME>.fact_analytics
SELECT * EXCLUDE change_mode
FROM FACTS.<SCHEMA_NAME>.<ticket>_spotify_staging_fact_analytics_backfill
WHERE change_mode = 'INSERT'
ORDER BY labelid, dayid, subaccountid;

-- Rebuild aggregated_skips_and_saves for this track (delete first, then re-insert from aggregated streams)
DELETE FROM FACTS.<SCHEMA_NAME>.aggregated_skips_and_saves
WHERE reportdate BETWEEN $backfill_start_date AND $backfill_end_date
  AND feedid = $feedid
  AND trackid = (SELECT trackid FROM FACTS.<SCHEMA_NAME>.dim_track_clean_mv WHERE UPPER(isrc) = $backfill_isrc LIMIT 1);

INSERT INTO FACTS.<SCHEMA_NAME>.aggregated_skips_and_saves (
    storeid, feedid, vendor_name, licensorid, labelid, subaccountid,
    activitydate, countryid, artistid, releaseid, trackid,
    skips, saves, reportdate, streams
)
WITH track_release_data AS (
    SELECT dr.releaseid, dr.artistid, dr.subaccountid, dt.trackid, dt.labelid, dt.isrc
    FROM FACTS.<SCHEMA_NAME>.dim_release dr
        INNER JOIN FACTS.<SCHEMA_NAME>.dim_track_clean_mv dt ON dt.upc = dr.releaseid
    WHERE dr.product_type = 'digital'
)
SELECT $storeid, $feedid, srs.api_licensor, dl.licensorid,
  COALESCE(trd.labelid, trf.labelid), COALESCE(trd.subaccountid, trf.subaccountid),
  srs.download_date, dc.countryid,
  COALESCE(trd.artistid, trf.artistid), COALESCE(trd.releaseid, trf.releaseid),
  COALESCE(trd.trackid, trf.trackid),
  SUM(srs.skips), SUM(srs.saves), srs.download_date, SUM(srs.streams)
FROM FACTS.<SCHEMA_NAME>.staging_raw_spotify_aggregated_streams srs
    INNER JOIN FACTS.<SCHEMA_NAME>.dim_country dc ON dc.country_code = srs.country
    LEFT JOIN orchard_app_reporting_v2.prod_ddex_ingester_ddex_ingester.upc_remap ur
        ON LTRIM(ur.original_upc, '0') = srs.upc::string
    LEFT JOIN track_release_data trd ON trd.releaseid = srs.upc AND UPPER(trd.isrc) = UPPER(srs.track_isrc)
    LEFT JOIN FACTS.<SCHEMA_NAME>.sme_product_mapping_view trf
        ON srs.api_licensor IN ('sme', 'smejp', 'smejpintl', 'smecharity')
        AND trd.releaseid IS NULL AND UPPER(trf.isrc) = UPPER(srs.track_isrc)
    INNER JOIN FACTS.<SCHEMA_NAME>.dim_licensor dl ON dl.storelicensorid = srs.api_licensor AND dl.feedid = $feedid
WHERE srs.download_date BETWEEN $backfill_start_date AND $backfill_end_date
  AND UPPER(srs.track_isrc) = $backfill_isrc
  AND srs.api_licensor IN (SELECT value FROM TABLE(SPLIT_TO_TABLE($licensor, ',')))
  AND COALESCE(trd.trackid, trf.trackid) IS NOT NULL
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 14
HAVING sum_skips != 0 OR sum_saves != 0;
```

---

### Apple Music SQL — 1 changeset

Follows the DS-8480 pattern. Apple uses summary-level staging (not per-stream), so one changeset is enough.

> **Key difference from Spotify:** `backfill_end_date` is **exclusive** here (use `download_date < $backfill_end_date`), so set it to the day *after* the last missing date. E.g., if last missing day is 2026-05-14, set `backfill_end_date = '2026-05-15'`.

```sql
--liquibase formatted sql

--changeset rroy:1 runAlways:true runOnChange:true

set storeid = 1;
set feedid = 4;
set licensor = 'sme';    -- use 'awal' for AWAL tracks, 'theorchard' for Orchard tracks
set backfill_start_date = '<YYYY-MM-DD>';
set backfill_end_date = '<YYYY-MM-DD+1>';  -- exclusive end date

-- Clean up fact_analytics_error
DELETE FROM FACTS.<SCHEMA_NAME>.fact_analytics_error
WHERE feedid = $feedid AND storeid = $storeid
  AND reportdate >= $backfill_start_date AND reportdate < $backfill_end_date
  AND stagingrawid IN (
    SELECT row_id FROM FACTS.<SCHEMA_NAME>.staging_raw_apple_music_summary_streams
    WHERE download_date >= $backfill_start_date AND download_date < $backfill_end_date
      AND UPPER(isrc) = '<ISRC>'
  );

INSERT INTO FACTS.<SCHEMA_NAME>.fact_analytics (
    artistid, dayid, labelid, storeid, genreid, releaseid, trackid, countryid,
    transactiontypeid, zipid, catalogid, isrcid, imprintid, currencyid,
    storeuserid, processeddaytime, royalty, royaltydollar, units, paidunits, freeunits,
    uuid, subaccountid, download_activity_date, formatid, listenduration, playlistid,
    sourcetypeid, containertypeid, reportdate, stagingrawid, feedid, userid,
    storeuserid_varchar, licensorid, regioncode, subscriptionpaytierid,
    subscriptiontypeid, deviceid, osid
) (
SELECT
    dr.artistid, ss.dayid, dt.labelid, ss.storeid, dr.genreid, dr.releaseid, dt.trackid,
    ss.countryid, ss.transactiontypeid, ss.zipid, dr.catalogid, dt.isrcid, dr.imprintid,
    ss.currencyid, 0 AS storeuserid, ss.processeddaytime, ss.royalty, ss.royaltydollar,
    ss.units, ss.paid_units, ss.free_units,
    TO_VARCHAR(ss.stagingrawid) || '_' || ss.reportdate || '_AppleMusic' AS uuid,
    dr.subaccountid, ss.download_activity_date, ss.formatid, ss.listenduration,
    ss.playlistid, ss.sourcetypeid, ss.containertypeid, ss.reportdate, ss.stagingrawid,
    ss.feedid, NULL AS userid, ss.storeuserid AS storeuserid_varchar,
    ss.licensorid, ss.regioncode, ss.subscriptionpaytierid, ss.subscriptiontypeid,
    ss.deviceid, ss.osid
FROM (
    SELECT DISTINCT
        dd.dayid, $storeid AS storeid, m.upc AS releaseid,
        dc.countryid,
        CASE WHEN src.media_type = 2 THEN 17 ELSE 1 END AS transactiontypeid,
        0 AS zipid, 0 AS currencyid, NULL AS storeuserid,
        src.processed_daytime AS processeddaytime,
        0 AS royalty, 0 AS royaltydollar, src.streams AS units,
        CASE WHEN src.subscription_mode = 'TRIAL' THEN 0 ELSE src.streams END AS paid_units,
        CASE WHEN src.subscription_mode = 'TRIAL' THEN src.streams ELSE 0 END AS free_units,
        m.isrc, src.download_date AS download_activity_date,
        dft.formatid, NULL AS listenduration, dp.playlistid, dst.sourcetypeid,
        dconttype.containertypeid, src.download_date AS reportdate,
        src.row_id AS stagingrawid, df.feedid, dl.licensorid, NULL AS regioncode,
        dsubscrpaytier.subscriptionpaytierid, dsubscrtype.subscriptiontypeid,
        ddv.deviceid, NULL AS osid, src.storefront_id AS storeuserid
    FROM FACTS.<SCHEMA_NAME>.staging_raw_apple_music_summary_streams src
    INNER JOIN (
        SELECT apple_id, upc, isrc FROM FACTS.<SCHEMA_NAME>.sony_apple_id_mapping WHERE 'sme' = $licensor
        UNION
        SELECT d.apple_id, d.upc, d.isrc FROM FACTS.<SCHEMA_NAME>.sony_apple_id_mapping_derived d
        LEFT JOIN FACTS.<SCHEMA_NAME>.sony_apple_id_mapping m ON d.apple_id = m.apple_id
        WHERE m.apple_id IS NULL AND 'sme' = $licensor
        -- For awal: UNION SELECT apple_id, upc, isrc FROM awal_apple_id_mapping WHERE 'awal' = $licensor
        -- For theorchard: UNION SELECT apple_id, orchard_release_id, orchard_track_id FROM apple_id_mapping WHERE 'theorchard' = $licensor
    ) m ON m.apple_id = src.apple_identifier
    INNER JOIN FACTS.<SCHEMA_NAME>.dim_day dd ON dd.displaydate = src.download_date
    INNER JOIN FACTS.<SCHEMA_NAME>.dim_country dc ON dc.country_code = src.storefront_name
    INNER JOIN FACTS.<SCHEMA_NAME>.dim_feed df ON df.feedid = $feedid
    LEFT JOIN FACTS.<SCHEMA_NAME>.dim_format dft
        ON dft.formatname = CASE WHEN src.audio_format IS NULL THEN 'Unspecified' ELSE src.audio_format END
    LEFT JOIN FACTS.<SCHEMA_NAME>.dim_device ddv
        ON ddv.devicedesc = CASE src.device_type
                                WHEN '1' THEN 'Mobile phone' WHEN '2' THEN 'Computer'
                                WHEN '3' THEN 'Voice Activated' ELSE 'Unknown' END
    LEFT JOIN FACTS.<SCHEMA_NAME>.dim_playlist dp
        ON src.container_id = dp.storeplaylistid AND dp.storeid = $storeid AND dp.feedid = df.feedid
    LEFT JOIN FACTS.<SCHEMA_NAME>.dim_sourcetype dst
        ON dst.storeid = $storeid AND dst.feedid = df.feedid AND dst.storesourcetypeid::VARCHAR = src.source_of_stream::VARCHAR
    LEFT JOIN FACTS.<SCHEMA_NAME>.apple_music_container_subtype_mapping cstmap
        ON cstmap.sub_container_type::VARCHAR = src.container_subtype::VARCHAR
    LEFT JOIN FACTS.<SCHEMA_NAME>.dim_containertype dconttype
        ON dst.storeid = $storeid AND dconttype.storecontainertypeid::VARCHAR = cstmap.container_type::VARCHAR
    LEFT JOIN FACTS.<SCHEMA_NAME>.dim_subscriptiontype dsubscrtype
        ON dsubscrtype.storeid = $storeid AND dsubscrtype.feedid = df.feedid AND dsubscrtype.subscriptiontypename = src.subscription_type
    LEFT JOIN FACTS.<SCHEMA_NAME>.dim_subscriptionpaytier dsubscrpaytier
        ON dsubscrpaytier.storeid = $storeid AND dsubscrpaytier.feedid = df.feedid AND dsubscrpaytier.subscriptionpaytiername = src.subscription_mode
    LEFT JOIN FACTS.<SCHEMA_NAME>.dim_licensor dl
        ON dl.storelicensorid = src.vendor_name AND dl.feedid = $feedid
    WHERE src.download_date >= $backfill_start_date AND src.download_date < $backfill_end_date
) ss
INNER JOIN FACTS.<SCHEMA_NAME>.dim_track_clean_mv dt ON dt.isrc = TRIM(ss.isrc) AND dt.upc = TRY_TO_NUMBER(ss.releaseid)
INNER JOIN FACTS.<SCHEMA_NAME>.dim_release dr ON dr.releaseid = dt.upc
)
ORDER BY labelid, dayid, subaccountid;
```

---

## Step 4 — Verify Before Merging

Run this after creating the staging table (changeset 1+2 of Spotify) to sanity-check row counts:

```sql
-- Should match staging_raw_spotify_v2 counts from Step 1a
SELECT change_mode, COUNT(*) AS row_count
FROM facts.prod.in_17335_spotify_staging_fact_analytics_backfill  -- replace with actual table name
GROUP BY 1;
-- Expected: all rows = 'INSERT' (no 'UPDATE') since data was never in fact_analytics
```

```sql
-- Apple: row count should match staging counts from Step 1a
SELECT download_date, COUNT(*) AS row_count
FROM facts.prod.staging_raw_apple_music_summary_streams src
INNER JOIN (
    SELECT apple_id FROM facts.prod.sony_apple_id_mapping WHERE UPPER(isrc) = '<ISRC>'
    UNION
    SELECT d.apple_id FROM facts.prod.sony_apple_id_mapping_derived d
    LEFT JOIN facts.prod.sony_apple_id_mapping m ON d.apple_id = m.apple_id
    WHERE m.apple_id IS NULL AND UPPER(d.isrc) = '<ISRC>'
) m ON m.apple_id = src.apple_identifier
WHERE src.download_date >= '<start_date>' AND src.download_date < '<end_date>'
GROUP BY 1 ORDER BY 1;
```

---

## Key Reference: Store and Feed IDs

| DSP | storeid | feedid | Staging table |
|---|---|---|---|
| Spotify | 286 | 1 | `staging_raw_spotify_v2` |
| Apple Music | 1 | 4 | `staging_raw_apple_music_summary_streams` |
| Tidal | 708 | — | — |
| YouTube Music | 187 | — | — |

---

## Key Reference: Licensor Values

| Licensor | Used for | Apple mapping table |
|---|---|---|
| `sme` | SME labels (US, EU, Norway, etc.) | `sony_apple_id_mapping` + `sony_apple_id_mapping_derived` |
| `smejpintl` | SME Japan international | same as sme (via `sme_product_mapping_view`) |
| `smejp` | SME Japan domestic | same as sme |
| `theorchard` | The Orchard distribution | `apple_id_mapping` (column: `orchard_release_id`, `orchard_track_id`) |
| `awal` | AWAL distribution | `awal_apple_id_mapping` |

---

## Past PRs

| Ticket | DSPs | Date range | PR | Notes |
|---|---|---|---|---|
| **IN-17335** | Spotify + Apple | 2026-05-07 to 2026-05-14 | Branch `IN17335_backfill_spotify_apple` | ISRC NOM7G2601010 (SMNorway); dim_track created 2026-05-15; ~133k Spotify rows + ~3.9k Apple rows; licensors `sme` + `smejpintl` |
| **DS-9689** | Spotify | 2025-01-01 to 2025-01-31 | PR #25367 | Large-scale SME/SMEJPINTL/Orchard re-ingestion for Jan 2025; SQL at `dml/DS-9637_Insights_backfill_following_product_refresh_spotify_v3.sql` |
| **DS-8480** | Apple Music | 2025-01-08 to 2025-03-14 | PR #22421 | AWAL-specific Apple re-ingest; SQL at `dml/DS-8480_backfill_fact_analytics.sql`; used `vendor_name IN ('80031998')` filter |

---

## Gotchas

- **`smejpintl` in staging but zero Apple rows:** `smejpintl` rows in `staging_raw_spotify_v2` are processed via `sme_product_mapping_view`, but they appear under separate licensor IDs. Both must be in the staging table for Spotify.

- **Apple `backfill_end_date` is exclusive:** DS-8480 uses `download_date < $backfill_end_date`, so set it one day past the last missing date (e.g., last missing day = May 14 → `backfill_end_date = '2026-05-15'`). Spotify BETWEEN is inclusive.

- **Staging table name collision:** Use the ticket number in the transient table name (e.g., `in_17335_spotify_staging_...`). `CREATE OR REPLACE` is safe on re-runs.

- **aggregated_skips_and_saves may already have partial data:** If some days are already present (because the pipeline ran late after dim_track was created), the per-track DELETE + re-INSERT in changeset 3 handles this correctly — it replaces only this track's rows.

- **Branch naming:** Use underscores only — no slashes, no hyphens (e.g., `IN17335_backfill_spotify_apple` not `IN-17335/backfill`).

- **If Apple mapping returns zero rows from `sony_apple_id_mapping`:** Check `sony_apple_id_mapping_derived` separately. If neither has the ISRC, the Apple pipeline would never have attempted the insert — check whether the Apple ID was ever registered.