# Hooks Recommendation Ranker - Feature Stores

This directory contains feature stores for the Hooks ML Ranker, designed to power personalized hook recommendations using [Feast](https://feast.dev/) for feature serving.

## Overview

These feature stores compute rolling 30-day aggregated features from hook play data, with proper timestamp handling for point-in-time correctness and data leakage prevention. All features are filtered to only include plays from recommendations (`RECOMMENDATION_ITEM_ID IS NOT NULL`).

## Architecture

### Design Patterns

All feature stores follow consistent patterns:

1. **Date Spines**: Creates all entity-date combinations from first interaction onwards (not before)
2. **Rolling Windows**: 30-day lookback windows that **exclude** the current partition date
3. **Feast Timestamps**: Two timestamps for proper feature serving:
   - `event_timestamp`: When features are "valid as of" (point-in-time correctness)
   - `created_timestamp`: When features were actually computed (handles data delays)
4. **Data Leakage Prevention**: Uses `f.p_date < s.p_date` to exclude current day from features
5. **Recommendation Focus**: All queries filter to `RECOMMENDATION_ITEM_ID IS NOT NULL`

### Why Exclude Current Partition Date?

```sql
AND f.p_date < s.p_date  -- Excludes current date
```

**Prevents temporal data leakage:**
- **Training**: On 2024-11-12, features use data from 2024-10-13 to 2024-11-11
- **Inference**: On 2024-11-12 at 10am, features still use data from 2024-10-13 to 2024-11-11
- **Result**: Consistent features between training and inference

### Timestamp Strategy

```sql
s.p_date as event_timestamp,           -- Valid "as of" this date
CURRENT_TIMESTAMP() as created_timestamp  -- When computed
```

**Handles Snowflake Data Delays:**
```
Timeline for 2024-11-11 features:
├─ event_timestamp: 2024-11-11 00:00:00 (features valid as of Nov 11)
├─ Data arrives: 2024-11-12 02:00:00 (delayed)
├─ Job runs: 2024-11-12 04:00:00
└─ created_timestamp: 2024-11-12 04:00:00 (actual creation time)

Feast Query at 2024-11-12 03:00:00:
→ Filters: created_timestamp <= 2024-11-12 03:00:00
→ Result: NO features (not created yet)
→ Falls back to older features ✓

Feast Query at 2024-11-12 05:00:00:
→ Filters: created_timestamp <= 2024-11-12 05:00:00
→ Result: Returns Nov 11 features ✓
```

## Feature Store Tables

### 1. User Features (`recs_hooks_ranker_user_feature_store`)

**Entity**: `user_id`, `p_date`

**Features**:
- `average_watch_time_30d`: Average watch time for all hooks watched by this user in past 30 days

**Use Case**: Captures overall user engagement level

**Example**:
```sql
user_id  | p_date     | average_watch_time_30d
---------|------------|----------------------
user_123 | 2024-11-12 | 182.5
```

Ideas of user features:
- Number of days since joining Suno
- Number of songs created
- Number of songs published
- User segment
- Number of hooks watched
- Number of days active on Hooks

### 2. Hook Features (`recs_hooks_ranker_hook_feature_store`)

**Entity**: `hook_id`, `p_date`

**Features**:
- `average_watch_time_30d`: Average watch time for this hook in past 30 days

**Use Case**: Captures hook quality/popularity

**Optimization**: Only includes dates from first play onwards (not before hook existed)

**Example**:
```sql
hook_id  | p_date     | average_watch_time_30d
---------|------------|----------------------
hook_abc | 2024-11-12 | 215.8
```

Ideas for hooks features:
- Features extracted by Gemini ([see extraction code](https://github.com/suno-ai/glockenspiel/blob/eaa0df88ece0bdb2daf63bae8b4ee3e327a17d6b/suno_utils/suno_utils/worker/modal_runner_video_feature_extraction.py#L316))
- Gemini Rating

### 3. Creator Features (`recs_hooks_ranker_creator_feature_store`)

**Entity**: `creator_id`, `p_date`

**Features**:
- `average_watch_time_30d`: Average watch time for all hooks created by this creator in past 30 days

**Use Case**: Captures creator content quality

**Optimization**: 
- Pre-computes creator-to-hooks mapping once
- Only includes dates from first play of any creator's hooks

**Example**:
```sql
creator_id | p_date     | average_watch_time_30d
-----------|------------|----------------------
creator_A  | 2024-11-12 | 198.3
```

Ideas of creator features:
- Number of moderation violations and disputes
- Number of hooks published
- Followers / following
- Number of likes received / given
- Number of clips created and published
- Spam score: measure spam activity (follows, likes, comments)

### 4. User-Creator Features (`recs_hooks_ranker_user_creator_feature_store`)

**Entity**: `user_id`, `creator_id`, `p_date`

**Features**:
- `average_watch_time_30d`: Average watch time for this user watching this creator's hooks in past 30 days

**Use Case**: Captures user-creator affinity (personalized creator preferences)

**Optimization**:
- Pre-computes creator-to-hooks mapping
- Separates date spine for better query planning
- Early filtering on `RECOMMENDATION_ITEM_ID`
- Only includes dates from first user-creator interaction

**Example**:
```sql
user_id  | creator_id | p_date     | average_watch_time_30d
---------|------------|------------|----------------------
user_123 | creator_A  | 2024-11-12 | 210.5
user_123 | creator_B  | 2024-11-12 | 95.2
```

Ideas of user-creator features:
- Follow (bool)
- Like rate

### 5. User-Hook Events (`recs_hooks_ranker_user_hook_feature_store`)

**Entity**: `user_id`, `hook_id`, `event_timestamp`

**Features**:
- `watch_time`: Individual play duration

**Use Case**: Event-level features for recent interactions (last N plays)

**Note**: Unlike other stores, this contains individual events, not aggregations

**Example**:
```sql
user_id  | hook_id  | p_date     | event_timestamp     | watch_time
---------|----------|------------|---------------------|------------
user_123 | hook_abc | 2024-11-12 | 2024-11-12 10:30:00 | 180
user_123 | hook_xyz | 2024-11-12 | 2024-11-12 11:45:00 | 220
```

Ideas of user-hook features:
- Genre average watch time
- Language match
- All features extracted by Gemini: average watch time per category

### 6. Clip Features (`recs_hooks_ranker_clip_feature_store`)

**Entity**: `clip_id`

**Features**: One-hot encoded genre flags
- `genre_tag`: Original comma-separated genre predictions
- `genre_pop`, `genre_rock`, `genre_jazz`, ... (dynamic based on `GENRE_DATA` table)

**Use Case**: Content-based features for recommendation diversity

**How It Works**:
1. Queries `GENRE_DATA` table at compile time to get all possible genres
2. Splits comma-separated `GENRE_TAG` from `CLIP_GENRE` (up to 3 predictions)
3. One-hot encodes each genre as a boolean column
4. Lowercases all genre labels for consistent matching

**Example**:
```sql
clip_id | genre_tag        | genre_pop | genre_rock | genre_jazz | ...
--------|------------------|-----------|------------|------------|----
clip123 | "Pop,Rock,Jazz"  | 1         | 1          | 1          | ...
clip456 | "Electronic"     | 0         | 0          | 0          | ...
```

**Dynamic Genre List**: Genres are automatically discovered from `GENRE_DATA.LABEL` column, so new genres are automatically included.

Ideas of features:
- Clip popularity (verified likes, listen time outside of hooks)

## Configuration

All feature stores use:

```yaml
database: SUNO_DEV_DELPHINE
schema: PROD
materialized: view
warehouse: DBT_DEV_MEDIUM
```

### Dagster Configuration

In `schema.yml`, each feature store has:

```yaml
meta:
  dagster:
    partitions_def:
      type: daily
      start_date: 2024-01-01
      end_offset: 0
    backfill_policy:
      max_partitions_per_run: 7
```

## Data Flow

```
Source Tables:
├─ fact_hook_play       → Play events
├─ dim_hook             → Hook metadata (includes creator)
├─ genre_data           → Genre reference data
└─ clip_genre           → Clip-genre predictions

↓ (dbt transformation)

Feature Stores:
├─ User features        → user_id, p_date
├─ Hook features        → hook_id, p_date
├─ Creator features     → creator_id, p_date
├─ User-Creator         → user_id, creator_id, p_date
├─ User-Hook events     → user_id, hook_id, event_timestamp
└─ Clip genres          → clip_id (with one-hot encoded genres)

↓ (Feast ingestion)

Feast Feature Store → Online/Offline serving for ML model
```

## Usage with Feast

### Feature Definition Example

```python
from feast import Entity, FeatureView, Field
from feast.types import Float32, Int64

user = Entity(name="user_id", join_keys=["user_id"])
hook = Entity(name="hook_id", join_keys=["hook_id"])

user_features = FeatureView(
    name="user_features",
    entities=[user],
    schema=[
        Field(name="average_watch_time_30d", dtype=Float32),
    ],
    source=SnowflakeSource(
        database="SUNO_DEV_DELPHINE",
        schema="PROD",
        table="recs_hooks_ranker_user_feature_store",
        timestamp_field="event_timestamp",
        created_timestamp_column="created_timestamp",
    ),
)
```

### Point-in-Time Lookup

```python
from feast import FeatureStore

store = FeatureStore(repo_path=".")

# Get features for a specific point in time
features = store.get_historical_features(
    entity_df=pd.DataFrame({
        'user_id': ['user_123', 'user_456'],
        'hook_id': ['hook_abc', 'hook_xyz'],
        'event_timestamp': [
            datetime(2024, 11, 12, 15, 30),
            datetime(2024, 11, 12, 16, 45)
        ]
    }),
    features=[
        'user_features:average_watch_time_30d',
        'hook_features:average_watch_time_30d',
        'user_creator_features:average_watch_time_30d',
    ]
).to_df()
```

## Performance Considerations

### Query Optimization Patterns Used

1. **Pre-compute mappings** (e.g., `creator_hooks` CTE)
2. **Separate date spines** for better query planning
3. **Early filtering** on `RECOMMENDATION_ITEM_ID` and `user_id IS NOT NULL`
4. **INNER JOIN** for spine generation (instead of CROSS JOIN + WHERE)
5. **Transform once, compare many** (lowercase before joins, not during)

### Expected Performance

- **Full backfill** (all dates since 2024-09-18): ~30-60 minutes per table
- **Incremental daily run** (3-day window): ~2-5 minutes per table
- **Storage**: ~10-50M rows per table depending on entities

### Incremental Updates (Production)

For production, change to incremental materialization:

```yaml
config:
  materialized: incremental
  unique_key: ['user_id', 'p_date']
```

Then add incremental logic:

```sql
{% if is_incremental() %}
  AND p_date >= DATEADD(day, -3, CURRENT_DATE())
{% endif %}
```

This will only compute the last 3 days on each run (handles late data).

## Data Quality

### Key Filters Applied

- `p_date >= '2024-09-18'`: Only data since Sept 18, 2024
- `RECOMMENDATION_ITEM_ID IS NOT NULL`: Only recommendation-driven plays
- `user_id IS NOT NULL`: Valid users only
- `f.p_date < s.p_date`: Exclude current date from rolling windows

### NULL Handling

- Users/hooks with no activity in 30-day window: `average_watch_time_30d = NULL`
- Consider using `COALESCE(AVG(...), 0)` if your model requires non-NULL values

## Maintenance

### Adding New Features

To add a new feature (e.g., `like_rate_30d`):

1. Add column to SQL query
2. Update `schema.yml` with column description
3. Update Feast feature view
4. Backfill historical data

### Adding New Genres

Genres are automatically discovered from `GENRE_DATA` table. To add a new genre:

1. Insert into `GENRE_DATA` table
2. Re-run `dbt compile` (fetches new genre list)
3. Re-run `dbt run` (generates new column)

No code changes needed! 🎯

## Troubleshooting

### "Source not found" Error

**Problem**: `Model depends on a source named 'snowflake.genre_data' which was not found`

**Solution**: Ensure tables are defined in `staging/cleaned/cleaned_sources.yaml`:

```yaml
sources:
  - name: snowflake
    tables:
      - name: genre_data
      - name: clip_genre
```

### NULL Features

**Problem**: Features are NULL for recent users/hooks

**Cause**: Not enough history (< 30 days of data)

**Expected**: Users/hooks need time to accumulate 30 days of play data

### Incorrect Timestamps

**Problem**: Features showing up at wrong times in Feast

**Check**:
1. Verify `event_timestamp` is the partition date
2. Verify `created_timestamp` is when feature was computed
3. Check Feast is using both timestamp columns correctly

## References

- [Feast Documentation](https://docs.feast.dev/)
- [dbt Best Practices](https://docs.getdbt.com/guides/best-practices)
- [Snowflake Query Optimization](https://docs.snowflake.com/en/user-guide/queries-performance)