# CLAUDE.md -- dbt-analytics

This file provides guidance for AI assistants working in the dbt-analytics repository.

## Project Overview

**dbt-analytics** is the data transformation layer for the Orchard Insights platform. It transforms raw FACT_* tables in Snowflake into aggregated tables, views, and rollups that are consumed by OWS services (ows-analytics, ows-charts, ows-playlist). Built with dbt-core and organized into 14 local packages with 606+ SQL models.

## Essential Commands

```bash
# Development
make run                        # Run all models (incremental)
make run_full_refresh           # Full refresh all models
make run ARGS="--select model_name"          # Run a single model
make run ARGS="--select +model_name"         # Run model + all upstream deps
make run ARGS="--select model_name+"         # Run model + all downstream deps
make run ARGS="--select package:streams"     # Run all models in a package

# Testing
make test_unit                  # dbt unit tests
make test_integration           # Integration tests (requires Snowflake)
make test_integration ARGS="--select model_name"  # Test a single model

# Linting
make lint                       # SQL + Python linting

# Documentation
make generate_docs              # Generate dbt docs
make serve_docs                 # Serve docs locally
```

To run dbt commands directly (after `pipenv shell`):

```bash
dbt run --select model_name
dbt test --select model_name
dbt compile --select model_name    # Preview compiled SQL without running
dbt ls --select model_name         # List models matching selector
dbt deps                           # Install dbt packages
```

## Setup

```bash
pipenv install --dev
pipenv shell
# Configure Snowflake credentials via environment variables
```

Snowflake access requires:
- Account: `orchard`
- Private key in `~/.ssh/snowflake/` (local dev)
- AWS Secrets Manager credentials (CI/CD)

## Architecture

### Tech Stack

| Layer | Technology |
|-------|-----------|
| Framework | dbt-core 1.9.1 |
| Snowflake Adapter | dbt-snowflake 1.9.0 |
| Language | SQL (Jinja-templated), Python 3.11 |
| Package Manager | Pipenv |
| Data Quality | dbt_expectations 0.10.8 (metaplane) |
| ML | tadas (trending detection: Spotify, Apple, TikTok, Pandora) |
| CI/CD | Jenkins (Dockerfile: python:3.11.12), Makefile |
| Container | Docker (python:3.11.12 base) |

### Package Structure

```
dbt-analytics/
├── dbt_project.yml              # Root dbt configuration
├── profiles/                    # Snowflake connection profiles
├── Makefile                     # 11 build targets
├── Dockerfile                   # python:3.11.12 base
├── Pipfile / Pipfile.lock       # Python dependencies
├── packages.yml                 # dbt package dependencies
│
├── base/                        # Source definitions, staging models
├── mappings/                    # Dimension mapping tables
├── views/                       # Snowflake views (V_STREAMS_BY_*)
├── metrics/                     # Core metric calculations
├── streams/                     # Streaming data aggregations (23 models)
├── downloads/                   # Download data aggregations (12 models)
├── playlists/                   # Playlist analytics
├── summary_demographics/        # Demographic breakdowns (5 models)
├── summary_geographics_v2/      # Geographic breakdowns
├── summary_streams/             # Stream summary tables
├── point_of_sale/               # Point of sale data
├── data_availability/           # Data freshness tracking
├── ml/                          # ML models (tadas trending)
├── utils/                       # Shared macros (30+)
├── operations/                  # Operational macros
└── monitoring/                  # Data quality monitoring
```

### Materialization Strategy

| Pattern | Materialization | Refresh Strategy | Example |
|---------|----------------|------------------|---------|
| `*_DAILY` tables | Incremental | 3-day rolling refresh (configurable via `DAYS_TO_RECALCULATE`) | `STREAMS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY` |
| `*_ROLLUP` tables | Full refresh | Complete rebuild each run | `METRICS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_ROLLUP` |
| `V_*` views | View | Always current (Snowflake view) | `V_STREAMS_BY_SOUND_RECORDING` |

All tables are Snowflake **transient** (no time travel, reduced storage cost).

### Data Flow

```
Raw FACT_* tables (ingested by external pipelines)
       |
       v
  base/ package (source definitions, staging)
       |
       v
  mappings/ (dimension tables, lookups)
       |
       +------------------+--------------+---------------+
       v                  v              v               v
  streams/           downloads/    playlists/      point_of_sale/
  metrics/           summary_*    ml/ (tadas)     data_availability/
       |                  |              |               |
       v                  v              v               v
  Aggregated tables in Snowflake (read by ows-* services)
```

### Downstream Consumers

| dbt Output | Consumed By | Purpose |
|------------|-------------|---------|
| `STREAMS_BY_*_DAILY` tables | ows-analytics | Streaming analytics endpoints |
| `METRICS_BY_*_ROLLUP` tables | ows-analytics | Metric aggregation endpoints |
| `V_STREAMS_BY_*` views | ows-analytics | Real-time stream views |
| Playlist aggregation tables | ows-playlist | Playlist position/history endpoints |
| Chart ranking tables | ows-charts | Chart ranking endpoints |
| ML trending tables (tadas) | ows-analytics | Trending detection endpoints |

## Key Patterns

### Incremental Models (DAILY)

All `*_DAILY` models use a 3-day rolling refresh window:

```sql
{{
    config(
        materialized='incremental',
        transient=true,
        unique_key=['date', 'product_id', 'country', 'feed_id', 'distributor_id'],
        incremental_strategy='delete+insert'
    )
}}

SELECT ...
FROM {{ ref('source_table') }}

{% if is_incremental() %}
WHERE date >= DATEADD(day, -{{ var('DAYS_TO_RECALCULATE', 3) }}, CURRENT_DATE())
{% endif %}
```

### Rollup Models (Full Refresh)

```sql
{{
    config(
        materialized='table',
        transient=true
    )
}}

SELECT
    product_id,
    country,
    feed_id,
    distributor_id,
    SUM(streams) AS total_streams,
    SUM(revenue) AS total_revenue
FROM {{ ref('streams_daily') }}
GROUP BY 1, 2, 3, 4
```

### Naming Convention

Table names follow a strict pattern:

```
<METRIC>_BY_<DIMENSIONS>_<GRANULARITY>

Examples:
  STREAMS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY
  METRICS_BY_PARTICIPANT_COUNTRY_FEED_DISTRIBUTOR_ROLLUP
  V_STREAMS_BY_SOUND_RECORDING
```

- `STREAMS_BY_*` -- Stream count aggregations
- `METRICS_BY_*` -- Multi-metric aggregations (streams, revenue, saves, etc.)
- `V_*` -- Snowflake views (no physical storage)
- `_DAILY` suffix -- Incremental, date-partitioned
- `_ROLLUP` suffix -- Full refresh, pre-aggregated

### Key Macros

| Directory | Purpose | Examples |
|-----------|---------|----------|
| `macros/dates/` | Date calculations, max available dates | `get_max_available_date`, `get_split_date` |
| `macros/source_of_streams/` | Stream source classification UDFs | `create_udfs`, `return_1_if_active` |
| `macros/operations/` | Snowflake admin operations | `cluster_table`, `grant_permissions`, `unload_to_s3` |
| `macros/sanity_checks/` | Data quality validation | `abort_if_label_*_vendor_id_is_null` |

### Testing

Two categories of dbt tests:

**Schema tests** (in YAML):
```yaml
models:
  - name: streams_by_product_daily
    columns:
      - name: product_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_product')
              field: product_id
      - name: streams
        tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
```

**Data tests** (in SQL):
```sql
-- tests/assert_no_negative_streams.sql
SELECT *
FROM {{ ref('streams_by_product_daily') }}
WHERE streams < 0
```

### ML Package (tadas)

The `ml/` package contains trending detection models for:
- Spotify (viral/editorial playlist detection)
- Apple Music (playlist additions)
- TikTok (sound usage trends)
- Pandora (station adds)

These models produce tables consumed by ows-analytics for trending alerts.

## Environment Variables

| Variable | Purpose |
|----------|---------|
| `SNOWFLAKE_USER` | Snowflake username |
| `SNOWFLAKE_ROLE` | Snowflake role (dev: `DEV_ENGINEERING`) |
| `SNOWFLAKE_WAREHOUSE` | Snowflake warehouse (dev: `DEV_OWS_WAREHOUSE`) |
| `SNOWFLAKE_DATABASE` | Snowflake database (dev: `DEV_ENGINEERING`) |
| `SNOWFLAKE_SCHEMA` | Snowflake schema (dev: `<USERNAME>_DBT`) |
| `SNOWFLAKE_PRIVATE_KEY_PATH` | Path to SSH private key |
| `SNOWFLAKE_KEY_PASSPHRASE` | Private key passphrase |
| `DAYS_TO_RECALCULATE` | Incremental refresh window (default: 3) |
| `CREDIT_SAVINGS_MODE` | Optional: `enabled` for smaller dev datasets |

## CI/CD

Jenkins pipeline with these stages:
1. `dbt deps` -- Install dbt packages
2. `dbt run` -- Execute models (incremental in daily runs, full refresh weekly)
3. `dbt test` -- Run all 143+ tests
4. `dbt docs generate` -- Generate documentation

Docker image: `python:3.11.12` base with Pipenv, dbt-core, dbt-snowflake.

## Common Mistakes to Avoid

- Do NOT use `table` materialization for DAILY models -- use `incremental` with `delete+insert` strategy
- Do NOT forget the `transient=true` config -- all tables must be Snowflake transient
- Do NOT hardcode database/schema names -- use `{{ env_var() }}` or `{{ target.database }}`
- Do NOT skip schema tests for new models -- at minimum: `not_null` on key columns, `relationships` for foreign keys
- Do NOT modify a table's column names or types without checking downstream OWS queries
- Do NOT use `{{ this }}` in incremental predicates without understanding the delete+insert strategy
- Always run `dbt compile --select model_name` before `dbt run` to preview compiled SQL
- Always check the `DAYS_TO_RECALCULATE` variable scope when debugging incremental models
- Pre-commit hooks enforce SQL formatting -- run linting before committing