# CLAUDE.md

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

## Project Overview

This is a dbt Core project that transforms marketing data ingested by Fivetran for Fansifter. The project uses Snowflake as the data warehouse and processes advertising data from multiple platforms (Facebook, Google, TikTok), as well as email (SendGrid) and text messaging (Twilio) data.

## Development Environment

**Python & Dependencies:**
- Python 3.13 (specified in pyproject.toml)
- Use `uv` for dependency management (NOT pip/poetry)
- Install dependencies: `make env` or `uv sync`
- Install dbt packages: `make packages` or `uv run dbt deps`

**Database:**
- Snowflake (dbt-snowflake adapter >=1.11.0)
- Source databases: FANSIFTER_APP_REPORTING, ORCHARD_APP_REPORTING, FANSIFTER_PG_REPORTING, PREFERENCE_CENTER, DELPHI_EXPLORATION

## Common Development Commands

**Linting & Formatting:**
- Lint all SQL: `make lint` or `uv run sqlfluff lint .`
- Format all SQL: `make fmt` or `uv run sqlfluff fix .`
- Lint specific path: `make lint_models path=models/facebook_ads`
- Format specific path: `make fmt_models path=models/facebook_ads`

**dbt Commands:**
All dbt commands should be run with `uv run dbt [command]`

- Update seeds: `dbt seed` (run when seed table structure changes — may need to drop the table in Snowflake first)
- Run models: `dbt run --select "MODEL_NAME"` or `dbt run --select models/facebook_ads/intermediate`
- Test models: `dbt test --select "MODEL_NAME"`
- Build (run + test): `dbt build --select "MODEL_NAME"`
- Full refresh: `dbt run --full-refresh` (for incremental models)
- Retry failed: `dbt retry`
- List selection: `dbt ls --select models/facebook_ads/intermediate` (for debugging selectors)

**Graph Operators:**
- `dbt run --select "MODEL_NAME+"` - run model and all descendants
- `dbt run --select "+MODEL_NAME"` - run model and all ancestors
- `dbt run --select models/core --exclude "MODEL_NAME"`

**Generate models.yml:**
```bash
make yml_models path=models/facebook_ads/staging
# OR manually:
uv run dbt --quiet run-operation generate_model_yaml --args '{"model_names": ["MODEL_1", "MODEL_2"]}' > models/[PATH]/models.yml
```

**Docker (CI/CD):**
- Run linting + tests: `make ci_lint_and_test`
- Run only dbt tests: `make dbt_test`

## Architecture & Model Structure

**Three-Tier Model Architecture:**
```
staging/          # Raw data transformation (STG_ prefix)
intermediate/     # Business logic layer (INT_ prefix)
marts/            # Final business-facing models
```

Each ad platform (Facebook, Google, TikTok) follows the full three-tier pattern. SendGrid uses intermediate (ephemeral) → marts (table). Twilio has only a marts layer.

**Additional Model Categories:**
- `internal_views/` - Views exposed in QA_MARKETING / PROD_MARKETING schema
- `external_views/` - Views exposed in QA_VIEWS / PROD_VIEWS schema
- Top-level models (`AD_REPORTING_*`) - Cross-platform aggregated reporting models that union all platforms

**Materialization Strategy:**
- Default: `table`
- Facebook/Google/TikTok marts: `view`
- Internal/External views: `view` with custom schema mapping
- SendGrid intermediate: `ephemeral`

**Naming Conventions:**
- Staging: `STG_{PLATFORM}__{TABLE}_DBT.sql`
- Intermediate: `INT_{PLATFORM}__{PURPOSE}_DBT.sql`
- Marts/Final: `{PURPOSE}_DBT.sql`
- All model filenames end with `_DBT` suffix
- Temporary staging models in `tmp/` subdirectories end with `_TMP_DBT.sql` (excluded from SQLFluff)

**Schema Configuration:**
- Internal views: Schema becomes `QA_MARKETING` or `PROD_MARKETING` in QA/PROD targets
- External views: Schema becomes `QA_VIEWS` or `PROD_VIEWS` in QA/PROD targets
- Custom schema name macro: `macros/generate_schema_name.sql`

## Key Non-Obvious Files

- `macros/generate_schema_name.sql` - Controls QA vs PROD schema routing for internal/external views
- `macros/tests/is_null.sql` - Custom null-checking test macro
- `seeds/_seeds.yml` - Seed table documentation with column types and purposes

## Important Variables

Defined in `dbt_project.yml`:
- `ad_campaign_benchmark_window: '365 days'` - Window for benchmark calculations
- `disallowed_link_clicks` - List of URL patterns excluded from link click metrics (QA links, email footers, etc.)

## SQL Style & Formatting

**SQLFluff Configuration:**
- Dialect: Snowflake
- Max line length: 88 characters
- Keywords: UPPER case
- Identifiers: UPPER case (extended_capitalisation_policy)
- Excluded rules: L031, L034
- Ignores: target/, dbt_packages/, macros/, *_TMP_DBT.sql

## Custom Macros

Platform-specific macros in `macros/{facebook,google,tiktok}/`:
- `get_*_history_columns.sql` - Column selection helpers for each entity type
- `calculate_actions_and_rate.sql` - Conversion actions and rate calculations
- `create_object_urls.sql` - Generate platform object URLs
- `get_tables_across_schemas.sql` - Query tables across multiple Snowflake schemas

Cross-platform macros:
- `macros/custom_metrics/calculate_cost.sql` - Cost calculations
- `macros/custom_metrics/calculate_frequency.sql` - Frequency metrics
- `macros/external_views/generate_ad_reporting_view.sql` - External view generation helper

Common utility macros:
- `source_relation()` - Generate and normalize source relation field
- `string_to_uuid()` - Convert strings to UUID using MD5
- `to_utc()` - Timezone conversion
- `list_inclause()` - Generate SQL IN clause from a list

## Seeds

Seeds provide reference data:
- `seeds/{platform}/` - Campaign naming conventions (for fuzzy-matching artists to campaigns) and objective mappings (deprecated→current)
- `seeds/{platform}/MOCK_*` - Mock data for testing
- `GLOBAL_PARTICIPANT_ADVERTISING_ROSTER_DBT.csv` - Artist-to-participant mapping used across all platforms

## Testing

Tests are defined in `models.yml` files within each model directory:
- dbt native tests: `unique`, `not_null`, `relationships`, `accepted_values`
- dbt_utils: `unique_combination_of_columns`
- dbt_expectations: advanced data quality assertions
- Custom: `is_null` test macro in `macros/tests/`