# dbt-audience Project Patterns Analysis

## Project Overview
- **Name**: dbt-audience
- **Purpose**: dbt Core transformations for the Audience app
- **Industry**: Music industry fan data management (Sony Music/The Orchard)
- **dbt Version**: >= 1.10.0

## 1. Project Structure Patterns

### Directory Organization
```
models/
├── core/                    # Core business logic models
├── global_segmentation/     # Global fan segmentation
├── segmentation/           # Artist-specific segmentation
├── segmentation_qa/        # QA version of segmentation
├── roster/                 # Artist roster management
├── audience/               # Audience filtering
├── account/                # Account management
├── custom_list/            # Custom list management
├── artist/                 # Artist-specific models
├── event_ingestion/        # Event data ingestion
├── backfill/               # Historical data backfilling
└── songwhip/              # Songwhip integration
```

### Layered Architecture Pattern
- **Staging**: `staging/` subdirectories in major model folders
- **Marts**: `marts/` subdirectories for business logic
- **Core**: Foundational models in `core/`
- **Event Ingestion**: Dedicated layer for data ingestion

## 2. Naming Convention Patterns

### Model Naming
- **Suffix Pattern**: All models end with `_DBT` (e.g., `FAN_DSP_DBT`, `EVENT_DBT`)
- **Prefix Patterns**:
  - `INT_` for intermediate models (e.g., `INT_FAN_LOCATION_DBT`)
  - `STG_` for staging models (e.g., `STG_SONGWHIP_PRESAVE_EVENT_DBT`)
  - `GLOBAL_` for global scope models (e.g., `GLOBAL_FAN_ARTIST_ACQUISITION_CHANNEL_DBT`)
- **Case Convention**: UPPER_SNAKE_CASE for all model names
- **Business Domain**: Clear business domain prefixes (`FAN_`, `ARTIST_`, `EVENT_`)

### File Organization
- Schema files use pattern: `_<folder_name>__models.yml`
- Unit test files: `_unittests.yml`, `__unittests2.yml`

## 3. Configuration Patterns

### dbt_project.yml Structure
- **Extensive Variable Usage**: 200+ lines of project variables for business logic
- **Tag-Based Organization**: Models organized by functional tags
- **Materialization Strategy**: Default to `table` materialization
- **Conditional Enablement**: Backfill models disabled by default

### Key Variable Categories
- **Business Rules**: DSP validation, acquisition channels, country filters
- **Segmentation Logic**: Fan segments, feature lists, timeframes
- **Test Data**: PR test configurations with specific IDs

### Tag Strategy
```yaml
tags:
  - "core"              # Foundational models
  - "dashboard"         # Dashboard-feeding models
  - "audience_filter"   # Audience filtering models
  - "segmentation"      # Segmentation models
  - "global_segmentation" # Global segmentation
  - "segmentation_qa"   # QA environment
  - "event_ingestion"   # Data ingestion
  - "backfill"          # Historical backfilling
  - "songwhip"          # Third-party integration
```

## 4. Data Modeling Patterns

### Primary Key Patterns
- **Fan-Centric**: Most models keyed on `FAN_ID` (SHA256 hash of email)
- **Composite Keys**: Complex business keys combining:
  - `FAN_ID + VENDOR_ID + SUBACCOUNT_ID + GLOBAL_PARTICIPANT_ID + CUSTOM_LIST_ID`
  - Conditional uniqueness based on business rules

### Fan ID Strategy
- **Consistent Hashing**: `sha2(lower(email), 256)` pattern
- **Privacy-First**: Email addresses hashed for GDPR compliance
- **Cross-Platform**: Single fan identity across multiple systems

### Business Entity Modeling
- **Multi-Tenant**: Vendor/Subaccount structure for different music labels
- **Global vs Local**: Distinction between artist-specific and global fan data
- **Acquisition Tracking**: Detailed fan acquisition channel modeling

## 5. Testing Patterns

### Data Quality Testing (dbt-expectations)
- **Row Count Validation**: `expect_table_row_count_to_be_between`
- **Column Value Testing**: Extensive use of `accepted_values` tests
- **Expression Testing**: `dbt_utils.expression_is_true` for business logic
- **Uniqueness Testing**: `unique_combination_of_columns` for composite keys

### Test Categories
- **Schema Tests**: Column-level constraints and data types
- **Business Logic Tests**: Custom SQL tests in `/tests` directory
- **Referential Integrity**: Cross-model relationship validation
- **Data Freshness**: Implicit through row count expectations

### Global Test Patterns
- Custom tests for segmentation logic validation
- Artist-specific business rule testing
- Feature scoring validation tests

## 6. Documentation Patterns

### Schema Documentation
- **Inline Documentation**: `'{{ doc("MODEL_NAME") }}'` pattern
- **Column Descriptions**: Comprehensive column documentation
- **Business Context**: Models include business purpose descriptions

### README Structure
- **Setup Instructions**: Poetry, Python 3.10, dbt init workflow
- **Command Documentation**: Extensive dbt command examples
- **Operational Procedures**: Orphan table identification and cleanup
- **Backfill Procedures**: Detailed backfill process documentation

## 7. Package Management Patterns

### Dependencies
```yaml
packages:
  - dbt-labs/dbt_utils (v1.3.0)        # Utility macros
  - metaplane/dbt_expectations (v0.10.8) # Data quality testing
  - elementary-data/elementary (v0.16.1) # Data observability
```

### Macro Patterns
- **Utility Macros**: Custom macros for common operations
- **String Manipulation**: UUID generation, list processing
- **Business Logic**: Songwhip event processing, backfill utilities

## 8. DevOps & Deployment Patterns

### Infrastructure
- **Containerization**: Docker setup with docker-compose
- **CI/CD**: Jenkins pipeline configuration
- **Environment Management**: QA/PROD schema separation
- **Makefile**: Standardized operational commands

### Data Warehouse Integration
- **Snowflake**: Primary data warehouse platform
- **Schema Management**: Environment-based schema deployment
- **State Management**: Incremental model support with full-refresh capability

## 9. Business Domain Patterns

### Music Industry Specific
- **DSP Integration**: Spotify, Apple, YouTube, Deezer, Amazon
- **Fan Segmentation**: 4-tier fan classification (Super, Engaged, Casual, Win-Back)
- **Acquisition Channels**: 20+ different fan acquisition methods
- **Geographic Filtering**: Country-specific business rules and compliance

### Privacy & Compliance
- **GDPR Compliance**: Email hashing, data retention policies
- **Marketing Consent**: Granular consent tracking
- **Data Deletion**: Fan data deletion capabilities
- **Regional Restrictions**: Country-based data filtering

### Fan Lifecycle Management
- **Acquisition Tracking**: First touch attribution
- **Engagement Scoring**: Multi-dimensional fan scoring
- **Retention Analysis**: Fan lifecycle stage tracking
- **Cross-Platform Identity**: Unified fan identity across touchpoints

## 10. Operational Patterns

### Data Quality Monitoring
- **Elementary Integration**: Data observability and alerting
- **Expectation Testing**: Comprehensive data validation
- **Orphan Table Management**: Automated cleanup procedures
- **Backfill Procedures**: Historical data reconstruction capabilities

### Performance Optimization
- **Incremental Models**: Selective data processing
- **Tag-Based Execution**: Selective model runs
- **Materialization Strategy**: Table-based for performance
- **Dependency Management**: Clear model lineage and dependencies

## Key Insights

1. **Enterprise-Scale dbt Project**: Sophisticated multi-tenant architecture
2. **Privacy-First Design**: GDPR compliance built into data modeling
3. **Music Industry Specialization**: Domain-specific business rules and metrics
4. **Comprehensive Testing**: Extensive data quality validation framework
5. **Operational Maturity**: Advanced deployment and monitoring capabilities
6. **Fan-Centric Data Model**: Unified fan identity across multiple platforms
7. **Scalable Architecture**: Clear separation of concerns and modular design