# artist-metadata-cea

Maps Luminate artist metadata (country of origin, artist type, gender, name) to Spotify artist IDs by joining ISRC data across Luminate and Chartmetric sources.

## Data flow

1. **Refresh** — Appends new artists to `sme_analytics.fi.dim_artist_metadata` in Snowflake. Joins Luminate musical recordings with Chartmetric's Spotify ISRC mapping to resolve Spotify artist IDs, then enriches with Luminate artist metadata. Only inserts artists not already present (incremental).

2. **Sync** — Pushes new rows from Snowflake to `prod_shared.dim_artist_metadata` in Redshift using a high-water-mark pattern (`max(added_at)` as the cutoff).

## Source tables

| Alias | Table |
|-------|-------|
| Luminate recordings | `luminate_db_listing_detail.extract_s.vw_musical_recording_ds` |
| Luminate artists | `luminate_db_listing_detail.extract_s.vw_artist_ds` |
| Chartmetric Spotify | `ds_chartmetric.raw_data.spotify` |

## Target tables

| Alias | Table |
|-------|-------|
| Snowflake table | `sme_analytics.fi.dim_artist_metadata` |
| Snowflake view | `sme_analytics.shared.dim_artist_metadata` |
| Redshift table | `prod_shared.dim_artist_metadata` |

## Schema

| Column | Type | Description |
|--------|------|-------------|
| `spotify_artist_id` | `varchar(22)` | Spotify artist identifier |
| `country_of_origin` | `varchar(2)` | ISO country code from Luminate |
| `artist_type` | `varchar(20)` | `Solo Music Artist` or `Musical Group` |
| `gender` | `varchar(10)` | Artist gender (nullable) |
| `artist_name` | `varchar(256)` | Artist name from Luminate |
| `added_at` | `timestamp` | Row insertion timestamp |

## Usage

```bash
# Refresh Snowflake table (append new artists)
uv run -m src refresh

# Sync new rows to Redshift
uv run -m src sync

# Both
uv run -m src all
```

### Options

```
--log-file, -l    Log file name (default: artist-metadata-cea.log, stored in LOGDIR)
```

## Project structure

```
artist-metadata-cea/
  pyproject.toml          # Dependencies (uv)
  src/
    __main__.py           # CLI entry point (refresh | sync | all)
    config/               # Tables, paths, warehouses
    db/                   # Singleton Snowflake/Redshift connections
      queries.py          # Jinja2 SQL template loader
    services/
      refresh.py          # Snowflake incremental append
      sync.py             # Snowflake-to-Redshift high-water-mark sync
  sql/
    snw-append.sql        # Insert new artists (Jinja2: {% if incremental %})
    snw-get-new.sql       # Select rows after cutoff
    rdb-get-cutoff.sql    # Get max(added_at) from Redshift
    _create/              # DDL for table/view initialization
```

## Environment variables

| Variable | Purpose |
|----------|---------|
| `INFAMOUSDIR` | Base path override (defaults to project root) |
| `LOGDIR` | Directory for rotating log files |
| Snowflake/Redshift credentials | Managed by `djagitit` (`SnowflakeDB`, `ReportingDB`) |