# CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

## Repository Overview

Suno ETL is a central repository for all Extract, Transform, and Load (ETL) operations. It contains two main components:

1. **AWS Glue Jobs** (`/glue/`) - Data extraction and loading from operational databases to S3
2. **Snowflake Data Warehouse** (`/snowflake/`) - Data transformation, storage, and analytics

## Common Development Commands

### Glue Development
```bash
# Install dependencies (from /glue directory)
uv sync

# Test Glue job locally
python scripts/rds_to_s3/<job_name>/<job_name>.py

# Deploy Glue job to staging (triggers automatically on push to test branch)
# Deploy to production (triggers automatically on push to main branch)
```

### Snowflake Development
```bash
# Install dependencies (from /snowflake/code directory)
uv sync

# Lint code 
ruff check

# Deploy single file to staging
uv run deploy_to_staging.py --file <path_to_sql_file>
./run_deploy_to_staging.sh <path_to_sql_file>

# Deploy local changes to production
uv run deploy_local_changes.py --file <path_to_sql_file>

# Resume a suspended task
uv run resume_task.py --name <task_name>

# Check AppsFlyer table schema differences
uv run scripts/appsflyer/diff_af_tables.py --apply-changes
```

### Testing Glue Jobs
```bash
# Trigger Glue job via AWS CLI
aws glue start-job-run --job-name "<job_name>" --profile staging

# Check job run status
aws glue get-job-run --job-name "<job_name>" --run-id "<run_id>" --profile staging
```

## Architecture Overview

### Data Flow Pattern
```
RDS/DynamoDB → AWS Glue → S3 (staging) → Snowflake → RDS (for applications)
```

### AWS Glue Component

**Core Base Classes:**
- `RDStoS3Job`: Abstract base for all RDS extraction jobs
- `HourlyRDStoS3Job`: Standard hourly incremental extraction
- `DailyRDStoS3Job`: Daily batch processing  
- `DynamoDBtoS3Job`: DynamoDB export functionality

**Standard Job Structure:**
All Glue ETL jobs follow this pattern:
```python
from utils.rds_to_s3_job import HourlyRDStoS3JobNoCreatedAtColumnPostgres

table_name = "table_name"
procedure_name = "SNOWFLAKE_PROCEDURE_NAME" 
task_monitor_name = "GLUE_TASK_MONITOR_NAME"

job = HourlyRDStoS3JobNoCreatedAtColumnPostgres(
    table_name=table_name,
    procedure_name=procedure_name, 
    task_monitor_name=task_monitor_name
)

job.run_job()
```

**Configuration-Driven Deployment:**
Each job requires a `config.json` file in the same directory with Glue parameters, schedules, and AWS resources.

### Snowflake Component

**Table Organization:**
- `/tables/rds/` - Raw operational data copies
- `/tables/dim/` - Dimension tables (users, clips, subscription plans)
- `/tables/fact/` - Event tables (plays, subscriptions, credit topups)
- `/tables/aggregation/` - Pre-computed metrics (daily/weekly user stats)
- `/tables/feature/` - ML features and analytics tables
- `/tables/appsflyer/` - Marketing attribution data

**Standard Table Structure:**
Each table typically includes:
- `table.sql` - Schema definition with clustering and partitioning
- `procedure.sql` - Data processing logic (upsert patterns)
- `task.sql` - Scheduled execution definition
- `table_updates/` - Schema evolution scripts (YYYY_MM_DD_operation.sql)

**Processing Patterns:**
1. **Incremental Processing**: Partition-based upsert with DELETE + INSERT
2. **Monitoring**: All procedures call `TASK_MONITOR_INSERT_PROC` for tracking
3. **Error Handling**: Connection refresh for long operations, retry logic

## Environment Management

**Environment Detection:**
- Production: AWS Account ID 734185074900
- Staging: AWS Account ID 590183763515
- Automatic bucket and role selection based on account

**Deployment Workflow:**
1. Push to test branch → deploys to staging
2. Test in staging environment
3. Merge to main → deploys to production
4. Resume tasks manually after deployment

## Key Patterns and Conventions

### Commits

- Never add AI or Claude attribution to commits or PRs

### Creating New Glue Jobs

1. Create folder structure: `scripts/<category>/<job_name>/`
2. Add Python job file: `<job_name>.py`
3. Add configuration: `config.json`
4. Follow naming convention: `<source>_to_<destination>_<table>_<frequency>_<operation>`
5. Push to test branch for staging deployment
6. Test and merge to main for production

### Updating Snowflake Tables

1. Modify `table.sql` with new schema
2. Create `table_updates/YYYY_MM_DD_operation_column.sql` with ALTER statement
3. Update corresponding `procedure.sql` for new columns
4. Test in staging before production deployment
5. Suspend affected tasks before deployment, resume after

### Monitoring and Alerts

- All jobs automatically monitored via CloudWatch and SNS
- Failures alert `#data-alerts` Slack channel
- Task monitoring tables track success/failure rates
- AppsFlyer schema drift detection with automatic alerts

## Special Procedures

### AppsFlyer Integration
AppsFlyer provides 14-day data retention. Daily tasks with `AF_` prefix dump to Suno for historical analysis. Schema changes are detected and handled automatically.

### Cross-System Data Flow
Some jobs run Snowflake → Glue → RDS to push computed aggregations back to operational systems (e.g., user engagement scores).

## Dependencies

**Glue:**
- Python 3.10+
- boto3, pyspark, elasticsearch
- AWS Glue runtime environment

**Snowflake:**
- Python 3.10+
- snowflake-connector-python, snowflake-snowpark-python
- ruff for linting
- geoip2, pandas for data processing