# Snowflake to RDS - Weekly User Engagement Job

This AWS Glue job performs a direct transfer of weekly user-to-user engagement data from Snowflake to RDS without intermediate S3 storage.

## Overview

- **Source**: Snowflake table `agg_user_to_user_engagement_weekly`
- **Destination**: RDS table `recommendations_usertouserengagement`
- **Schedule**: Weekly on Tuesday at 3:00 PM UTC
- **Environment**: Production and Staging support
- **Transfer Type**: Direct Snowflake-to-RDS (no S3 staging)

## Job Details

### Configuration
- **Job Name**: `snowflake_to_rds_agg_user_to_user_engagement_weekly_weekly`
- **Worker Type**: G.8X
- **Number of Workers**: 50
- **Timeout**: 48 hours (2880 minutes)
- **Glue Version**: 5.0

### Data Flow
1. **Extract**: Query Snowflake for last week's data with deduplication
2. **Transform**: Add timestamps and map columns to Django model schema
3. **Load**: Direct transfer to RDS using psycopg2 connection
4. **Cleanup**: Remove old records and handle duplicates

### Column Mapping
| Snowflake Column | RDS Column | Type |
|------------------|------------|------|
| USER_ID | user_id | integer |
| ENGAGE_USER_ID | engage_user_id | integer |
| TOTAL_SCORE | total_score | integer |
| RELATIONSHIP | relationship | string |
| P_DATE | p_date | date |
| - | created_at | timestamp with time zone |
| - | updated_at | timestamp with time zone |
| - | id | UUID (auto-generated) |

### Data Processing
- **Deduplication**: Uses window function to select latest record per `(user_id, engage_user_id, p_date)` combination
- **Type Conversion**: Handles Snowflake NUMBER types to Python integers
- **Batch Processing**: Processes data in batches of 1M records for efficiency
- **UUID Generation**: Generates unique UUIDs for primary key

## Monitoring

The job logs execution status to Snowflake using the `TASK_MONITOR_INSERT_PROC` procedure with:
- Task name: `GLUE_SNOWFLAKE_TO_RDS_AGG_USER_TO_USER_ENGAGEMENT_WEEKLY_DIRECT`
- Status: SUCCESS/FAILED
- Row count: Number of records processed

## Dependencies

- Snowflake connection via `utils.snowflake.snowflake_client`
- RDS connection via `utils.postgres.postgres_client`
- psycopg2 for direct PostgreSQL operations

## Error Handling

- Comprehensive error logging to CloudWatch
- Snowflake task monitoring integration
- Graceful handling of empty datasets
- Batch processing with transaction commits
- Automatic cleanup of old records

## Performance Optimizations

- **Direct Transfer**: Eliminates S3 staging for faster processing
- **Batch Operations**: Uses batch inserts and deletes for efficiency
- **Partitioned Processing**: Processes data in partitions using Spark
- **Memory Management**: Efficient handling of large datasets

## Deployment

1. Upload the script to S3: `s3://{s3bucket}/scripts/snowflake_to_rds_agg_user_to_user_engagement_weekly_weekly.py`
2. Create the Glue job using the configuration in `config.json`
3. The job will automatically start on every Tuesday at 3:00 PM UTC

## Notes

- This job performs a direct Snowflake-to-RDS transfer without intermediate S3 storage
- Data is deduplicated before transfer to prevent conflicts
- Old records are automatically cleaned up to maintain data freshness
- The job uses high-performance G.8X workers for optimal processing speed