# isrc-origin-cea

Determines the country of origin for ISRCs (International Standard Recording Codes) by combining multiple data signals and syncs the results to Redshift.

## Algorithm

The origin for each ISRC is determined using a priority-based decision tree:

1. **ISRC-level override** — manual override from `isrc_origin_override_isrc`
2. **Rep owner country** — from SME product metadata (`dim_products_rdb`)
3. **Spotify artist override** — manual override from `isrc_origin_override_spotify_artist_id`
4. **Very high ISRC local engagement** — engagement score >= 100 for the ISRC, but < 5 for the artist (overrides Luminate artist origin)
5. **Luminate artist profile** — country of origin from `dim_artist_metadata`
6. **High artist engagement** — Spotify artist engagement score >= 5 (streams ranked by market)
7. **High ISRC engagement** — ISRC engagement score >= 10
8. **Matching signals** — when ISRC and artist engagement agree on a country (streams, monthly listeners)
9. **ISRC prefix** — the first two characters of the ISRC code (fallback, excludes non-geographic prefixes like QZ, TC, QM, DG)

Local engagement scores are computed by ranking ISRCs and artists within each market, then comparing the top market's rank against the average of markets 2-10. A high ratio indicates strong local concentration.

## Data sources

- **Luminate**: streaming data (`rtd_mr_summary`, `vw_daily_fact_mr_summary_ds`, `vw_musical_recording_ds`), product metadata (`dim_products_rdb`)
- **Chartmetric**: Spotify track-artist mappings, artist insights, monthly listeners by location
- **Override tables**: `isrc_origin_override_isrc`, `isrc_origin_override_spotify_artist_id`
- **Artist metadata**: `dim_artist_metadata`

## Modes

| Mode | Description | Typical schedule |
|------|-------------|-----------------|
| `full` | Rebuilds the entire ISRC origin table from scratch using all data sources | Daily |
| `overrides` | Checks for new ISRC/artist overrides since last run; if found, rebuilds the table using the overrides staging query | Hourly |
| `sync` | Detects changes between the Snowflake main table and a mirror, then pushes only the diffs to Redshift via S3 | After `full` or `overrides` |

## Usage

```bash
uv run python -m src full        # Full refresh
uv run python -m src overrides   # Check for new overrides
uv run python -m src sync        # Sync changes to Redshift
```

Options:
```
--log-file, -l    Log file name, stored in LOGDIR (default: isrc-origin-cea.log)
```

## Project structure

```
isrc-origin-cea/
  pyproject.toml              # uv project definition
  sql/                        # SQL queries (placeholder tokens replaced at runtime)
    snw-staging-create-refresh.sql    # Full refresh staging query
    snw-staging-create-overrides.sql  # Overrides staging query
    snw-changed-create.sql            # Diff between main and Redshift mirror
    snw-main-insert.sql               # Insert staging -> main
    snw-main-truncate.sql             # Truncate main
    snw-main-rowcount.sql             # Row count check
    snw-staging-rowcount.sql          # Staging row count
    snw-run-check.sql                 # Check if overrides run is needed
    snw-run-update.sql                # Update last run timestamp
    _create/                          # Table DDL scripts (reference only)
  src/
    __main__.py               # CLI entry point (argparse)
    config/__init__.py        # Paths, table names, placeholders, warehouses
    db/__init__.py            # Singleton DB connections (Snowflake, Redshift, S3)
    db/queries.py             # QueryLoader with $PLACEHOLDER$ substitution
    services/
      refresh.py              # Full refresh logic
      overrides.py            # Override check logic
      sync.py                 # Snowflake-to-Redshift sync
```

## Tables

| Table | Location | Purpose |
|-------|----------|---------|
| `sme_analytics.fi.dim_isrc_origin` | Snowflake | Main table |
| `sme_analytics.shared.dim_isrc_origin` | Snowflake | View on main table |
| `sme_analytics.fi.dim_isrc_origin_redshift_current` | Snowflake | Mirror of Redshift state (for diff detection) |
| `sme_analytics.fi.dim_isrc_origin_redshift_changed` | Snowflake | Temp table holding changed ISRCs |
| `sme_analytics.fi.isrc_origin_staging` | Snowflake | Staging table for refresh/overrides |
| `sme_analytics.fi.isrc_origin_latest_run` | Snowflake | Last run timestamp |
| `prod_eu_analytics.isrc_origin` | Redshift | Main table |
| `prod_eu_analytics.isrc_origin_changed` | Redshift | Staging table for incremental sync |

## Sync design

The sync process detects ISRCs where `isrc_origin` differs between the Snowflake main table and a Snowflake-side mirror (`redshift_current`). Only the diffs are pushed to Redshift via S3. After a successful Redshift write, the mirror is rebuilt as a full snapshot of the main table. This ensures:

- If the Redshift write fails, the mirror is unchanged and the diff will be re-detected on the next run (no data loss)
- The mirror always matches main exactly, preventing row count drift from full refreshes that remove ISRCs