---
topic: Infrastructure (Terraform, Snowflake roles, Snowflake tasks)
last_updated: 2026-03-16
---

# Playlist Pages Infrastructure

## Snowflake Roles (terraform-infra)

Repo: `/Users/cbeesley/code/terraform-infra`
Role definitions: `prod/snowflake/orchard/roles/variables.tf`

### Playlist-specific Roles

| Role | Purpose |
|------|---------|
| `QA_INSIGHTS_PLAYLIST_PAGES_GSHEETS_SYNC_TASK_ROLE` | Runs gsheets sync tasks in QA |
| `PROD_INSIGHTS_PLAYLIST_PAGES_GSHEETS_SYNC_TASK_ROLE` | Runs gsheets sync tasks in prod |

Both roles granted:

- `ORCHARD_APP_REPORTING_V2.DB_{env}_INSIGHTS_PLAYLIST_PAGES_GSHEETS_SYNC_SCHEMA_READ` (access to Fivetran-synced Google Sheet data)
- `{env}_INSIGHTS_PLAYLISTS_WAREHOUSE_WAREHOUSE_ACCESS`

### Service Users

| Directory | Purpose |
|-----------|---------|
| `prod/snowflake/orchard/service_users/qa-graphql-knowledge-playlist-pages-service-user/` | QA graphql-knowledge Snowflake access |
| `prod/snowflake/orchard/service_users/prod-graphql-knowledge-playlist-pages-service-user/` | Prod graphql-knowledge Snowflake access |
| `prod/snowflake/orchard/service_users/qa-get-playlist-batches-from-snowflake-service-user/` | QA batch playlist service user |

## Snowflake Tasks (database repo)

Repo: `/Users/cbeesley/code/database`

---

### Priority Playlist Task Graph (THE primary data pipeline)

DDL: `snowflake/FACTS/build/changelog/ddl/snowflake_tasks/priority_playlist_updates/`
Canonical file: `spotify_priority_playlists_full_graph.sql` (all other files in `archived/`)

This is a 14-task DAG that processes Spotify priority playlist changes in near-real-time. It is the producer of all `PLAYLISTS_PRIORITY_*` tables, which are then referenced as dbt sources in `unioned/sources.yml` and surfaced via the `V_` unioned views consumed by all services.

**Data source**: ChartMetric's `L_SPOTIFY_PLAYLIST_SONY` table via Snowflake Stream (`SPOTIFY_PRIORITY_PLAYLIST_EVENTS`)
**Replication cadence**: `L_SPOTIFY_PLAYLIST_SONY` is replicated by Chartmetric every **5 minutes** — this is a dedicated table negotiated specifically for priority playlists, not the standard ~1-2 hour cadence
**End-to-end latency**: ~6 minutes from Spotify playlist update → Chartmetric replication (5 min) + task graph processing (~1 min)

**Warehouse**: `{env}_ETL_WAREHOUSE`

**Task DAG**:
```
CAPTURE_SPOTIFY_PRIORITY_PLAYLIST_EVENTS  (root, 1 min, stream-triggered)
├── CAPTURE_TRACK_METADATA
├── UPSERT_PLAYLISTS_PLACEMENTS_BY_ISRC_PLAYLIST_PUBLIC_IF_NOT_PROCESSED
│   ├── UPDATE_PLAYLIST_METADATA_METRICS
│   ├── UPSERT_PLAYLISTS_PLACEMENTS_BY_PLAYLIST_PUBLIC
│   │   └── REBUILD_PLAYLISTS_PRIORITY_BY_PLAYLIST_CURRENT_TRACKLIST
│   ├── REBUILD_PLAYLISTS_PRIORITY_PLACEMENTS_BY_TRACK_PLAYLIST_DISTRIBUTOR_PUBLIC
│   │   └── REBUILD_..._ONLY_FRESHEST
│   └── REBUILD_RECENT_PLAYLISTS_PRIORITY_PLACEMENTS_BY_TRACK_PLAYLIST_DISTRIBUTOR
│       └── REBUILD_..._ONLY_FRESHEST
├── UPSERT_PLAYLISTS_PLACEMENTS_BY_PARTICIPANT_ISRC_PLAYLIST_PUBLIC_IF_NOT_PROCESSED
├── UPSERT_FACT_CHARTS_NMF
├── CAPTURE_SPOTIFY_PRIORITY_PLAYLIST_PROCESSING_LOG
└── PURGE_PLACEMENTS_STAGE_AFTER_PROCESSING  (cleanup - waits for all above)
```

**Separate time-based task** (independent, not in DAG):
- `UPDATE_SPOTIFY_PRIORITY_PLAYLIST_METADATA` — runs every 10 min, maintains `PRIORITY_PLAYLIST_METADATA`

**Tables produced** (all in `FACTS.{env}`):

| Table | Clustering | Purpose |
|-------|-----------|---------|
| `SPOTIFY_PRIORITY_PLAYLIST_EVENT_STAGE` | — | Staging area for stream data |
| `PLAYLISTS_PRIORITY_PLACEMENTS_BY_ISRC_PLAYLIST_PUBLIC` | ISRC | Track-centric placement queries |
| `PLAYLISTS_PRIORITY_PLACEMENTS_BY_PLAYLIST_PUBLIC` | store_playlist_id | Playlist-centric queries |
| `PLAYLISTS_PRIORITY_PLACEMENTS_BY_PARTICIPANT_ISRC_PLAYLIST_PUBLIC` | global_participant_id | Artist/participant queries |
| `PLAYLISTS_PRIORITY_BY_PLAYLIST_CURRENT_TRACKLIST` | store_playlist_id | Current tracklist with streaming metrics |
| `PLAYLISTS_PRIORITY_PLACEMENTS_BY_TRACK_PLAYLIST_DISTRIBUTOR_PUBLIC` | — | Distributor-focused view |
| `RECENT_PLAYLISTS_PRIORITY_PLACEMENTS_BY_TRACK_PLAYLIST_DISTRIBUTOR` | — | 24-month rolling window |
| `PRIORITY_PLAYLIST_METADATA` | — | Playlist metadata (also fed by metadata task) |
| `SPOTIFY_TRACK_METADATA` | ISRC | Track metadata cache |
| `SPOTIFY_PRIORITY_PLAYLIST_PROCESSING_LOG` | — | Audit trail |

**Key design patterns**:
- **Preflight checks**: `UPSERT_*_IF_NOT_PROCESSED` tasks skip if data already processed
- **Deduplication**: `ROW_NUMBER() OVER (PARTITION BY ... ORDER BY created_at DESC)`
- **Personalized playlist handling**: position data nullified for `playlist_type = 'PERSONALIZED'`
- **Cleanup logic**: tracks marked removed when position exceeds playlist size or ISRC not in `global_sound_recording`
- **Data semantics**: `last_added_on_date` = historical FIRST add (never updates on position change); `previous_position` = last DIFFERENT position (NULL if never moved); `removed_on` = NULL if currently active

**Deployment** (`runOnChange:true` — only executes when SQL changes):
```bash
mvn initialize liquibase:update \
  -DchangeLogFile=changelog/ddl/snowflake_tasks/priority_playlist_updates/spotify_priority_playlists_full_graph.sql
```

**Suspend/resume**:
```sql
-- Suspend (automatically suspends all children)
ALTER TASK IF EXISTS FACTS.{env}.CAPTURE_SPOTIFY_PRIORITY_PLAYLIST_EVENTS SUSPEND;
-- Resume entire graph
SELECT SYSTEM$TASK_DEPENDENTS_ENABLE('FACTS.{env}.CAPTURE_SPOTIFY_PRIORITY_PLAYLIST_EVENTS');
```

**Diagnostics**:
```sql
-- Check stream for pending data
SELECT SYSTEM$STREAM_HAS_DATA('FACTS.PROD.SPOTIFY_PRIORITY_PLAYLIST_EVENTS');
-- Check for processing lag
SELECT MAX(created_at), DATEDIFF('minute', MAX(created_at), CURRENT_TIMESTAMP()) as lag_min
FROM FACTS.PROD.SPOTIFY_PRIORITY_PLAYLIST_EVENT_STAGE;
-- Recent failures
SELECT NAME, STATE, ERROR_MESSAGE FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY())
WHERE STATE = 'FAILED' ORDER BY SCHEDULED_TIME DESC LIMIT 10;
```

**Architecture diagram**: https://whimsical.com/playlist-freshness-options-5LpGCdkSV9nyNQAv1L3VWW@NKBbAEvLSv9qg6dG4yddypgW26GtRkhB9

---

### Hourly Playlists Gsheets Sync (minor — register maintenance only)

DDL: `snowflake/FACTS/build/changelog/ddl/snowflake_tasks/insights_playlist_lists_sync/`

Two tasks that sync the `HOURLY_PLAYLISTS` register table from a Fivetran-synced Google Sheet. These do NOT produce analytics data — they only maintain the list of which playlists to track. The dbt `hourly/` tier then picks up new playlists from this table on its next run.

Both use scoped DELETE + INSERT (not merge) to avoid overwriting rows owned by the other store's task. Full replace used instead of stream-based merge to avoid stream invalidation when Fivetran drops/recreates source table on schema changes. NULL values for `code2`, `playlist_name`, `chartmetric_playlist_id` on insert are intentional — Chartmetric monitors the table and begins scraping newly-inserted playlist IDs.

**`GSHEETS_TO_HOURLY_PLAYLISTS`** — Spotify (`store_id=286`)
- Schedule: PROD=10 min, QA=hourly (`USING CRON 0 * * * * UTC`)
- Warehouse: `{env}_TASK_WAREHOUSE`
- Source: `ORCHARD_APP_REPORTING_V2.{env}_INSIGHTS_PLAYLIST_PAGES_GSHEETS_SYNC.SPOTIFY_HOURLY_PLAYLISTS`
- Joins `CHARTMETRIC.RAW_DATA.SPOTIFY_PLAYLIST` (LEFT JOIN — new playlists may not exist yet)
- Excludes playlists already in `PRIORITY_PLAYLISTS` (store_id=286)
- Ownership: `FACTS_DB_{env}_SCHEMA_READWRITE`; MONITOR: `FACTS_DB_{env}_SCHEMA_READ`

**`GSHEETS_TO_AM_HOURLY_PLAYLISTS`** (IN-16924) — Apple Music (`store_id=1`)
- Same schedule and pattern; source: `APPLE_MUSIC_HOURLY_PLAYLISTS`
- Joins `CHARTMETRIC.RAW_DATA.ITUNES_PLAYLIST`; `code2` is always NULL (Apple Music has no country code at this layer)
- **Changeset 2 fix**: ownership transferred to `{env}_INSIGHTS_PLAYLIST_PAGES_GSHEETS_SYNC_TASK_ROLE` (changeset 1 incorrectly left it with SCHEMA_READWRITE, which lacked access to the Fivetran source). `COPY CURRENT GRANTS` not used — deploy role lacks `MANAGE GRANTS`, so existing MONITOR grant is lost on transfer.

**Important**: Do NOT use `runOnChange:true` on these tasks. After `GRANT OWNERSHIP` transfers to the task role, Liquibase (running as the deploy role) can no longer `CREATE OR REPLACE` the task. Add new changesets (`:3`, `:4`, etc.) to modify task body.

## graphql-analytics Redis Cache

graphql-analytics uses Redis for placement DataLoader caching. **This is enabled in production** — the `.env.shadow` file has `CACHE_USE_REDIS=false` as a local-dev default (no Redis available locally), but production runs with `CACHE_USE_REDIS=true`.

- **Prod Redis host**: `master.prod-graphql-analytics.oudhyr.use1.cache.amazonaws.com`
- **QA Redis host**: `master.qa-graphql-analytics.oudhyr.use1.cache.amazonaws.com`
- **Placement DataLoader TTL**: 60 minutes (`60 * 60`) for `playlistPlacementAnalyticsDataLoader`, `playlistPlacementsByPlaylistIdDataLoader`, `playlistPlacementStreamsDataLoader`
- **Historical snapshot DataLoader TTL**: 7 days (`playlistPlacementsByDateDataLoader`) — intentional, historical data doesn't change

### Jenkins hourly cache clear

The `dbt-analytics-hourly-pipeline/Jenkinsfile` runs with `CLEAR_CACHE=true` every hour. After each dbt build, it sequentially clears:

1. **ows-playlist Redis** — full flush
2. **graphql-analytics Redis** — key pattern: `PlaylistAnalytics:* PlaylistAnalyticsByDimension:* PlaylistAnalyticsTimeseries:* PlaylistAnalyticsTimeseriesByDimension:* PlaylistAnalyticsTotalStreamsAndListeners:* PlaylistFollowersTimeseries:* PlacementPositionTS:* PlaylistPlacementCompanyBrands:* PlaylistMetadata:* GSRPlaylistPlacementAnalytics:* PlaylistPlacements:* PlaylistPlacementStreams:* playlist:*`
3. **graphql-knowledge Redis** — key pattern: `Playlist:id:*`

**Effective freshness**: the 60-min DataLoader TTL is reset every hour by this cache clear. For priority playlists (data in Snowflake within ~6 min), the graphql-analytics Redis is the remaining latency ceiling — up to 60 min, but cleared hourly by Jenkins.

---

## ows-playlist AWS Infrastructure

Path: `prod/ows-playlist/`

- Standard Flask service deployment on AWS Fargate

## graphql-knowledge Snowflake Connection

The graphql-knowledge service connects directly to Snowflake to query `V_PLAYLIST_METADATA` via the `playlistMetadataById` dataloader.

- Uses Redis for 1-hour TTL caching
- Service user configured via terraform-infra service users above

## Warehouse

- Dedicated task warehouse: `{env}_TASK_WAREHOUSE` for gsheets sync tasks
- Insights playlists warehouse: `{env}_INSIGHTS_PLAYLISTS_WAREHOUSE`

## Related Terraform Infra PRs

- `#31353` — Infrastructure changes for dedicated task role and warehouse
- `#31376`, `#31395` — Additional role provisioning