# dbt Service Agent

Expert agent for working in the `dbt-analytics` dbt project (Snowflake data transformations).

## Context

You are working in a dbt project that transforms raw FACT_* tables in Snowflake into aggregated tables consumed by OWS services (ows-analytics, ows-charts, ows-playlist). The project uses dbt-core 1.9.1 with the dbt-snowflake 1.9.0 adapter and is organized into 14 local packages with 606+ SQL models.

## Package Identification

Identify which package to work in based on the data domain:

- `base/` -- Source definitions, staging models from raw FACT_* tables
- `mappings/` -- Dimension mapping tables (stores, feeds, distributors, countries)
- `views/` -- Snowflake views (V_STREAMS_BY_*)
- `metrics/` -- Core metric calculations (streams, revenue, saves)
- `streams/` -- Streaming data aggregations (STREAMS_BY_*_DAILY)
- `downloads/` -- Download data aggregations
- `playlists/` -- Playlist analytics
- `summary_demographics/` -- Demographic breakdowns
- `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 detection for Spotify, Apple, TikTok, Pandora)
- `utils/` -- Shared SQL utilities and macros (30+)
- `operations/` -- Cluster, grant, S3 unload macros
- `monitoring/` -- Data quality monitoring

## Key Patterns to Follow

### Adding a New Model

1. Determine the correct package based on data domain
2. Choose materialization strategy:
   - Date-partitioned data: `incremental` with `delete+insert` and `DAYS_TO_RECALCULATE`
   - Pre-aggregated summaries: `table` (full refresh) with `transient=true`
   - Real-time lookups: `view`
3. Follow the naming convention: `<METRIC>_BY_<DIMENSIONS>_<GRANULARITY>`
4. Add schema tests in the corresponding YAML file
5. Add the model to the package's `dbt_project.yml` if custom config is needed
6. Document upstream dependencies and downstream consumers

### Incremental Model Template

```sql
{{
    config(
        materialized='incremental',
        transient=true,
        unique_key=['date', 'entity_id', 'dimension_1', 'dimension_2'],
        incremental_strategy='delete+insert'
    )
}}

WITH source AS (
    SELECT *
    FROM {{ ref('upstream_model') }}
    {% if is_incremental() %}
    WHERE date >= DATEADD(day, -{{ var('DAYS_TO_RECALCULATE', 3) }}, CURRENT_DATE())
    {% endif %}
),

aggregated AS (
    SELECT
        date,
        entity_id,
        dimension_1,
        dimension_2,
        SUM(metric) AS total_metric
    FROM source
    GROUP BY 1, 2, 3, 4
)

SELECT * FROM aggregated
```

### Writing Schema Tests

```yaml
models:
  - name: my_new_model
    description: "Description of what this model produces"
    columns:
      - name: date
        tests:
          - not_null
      - name: entity_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_entity')
              field: entity_id
      - name: total_metric
        tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
```

### Using Macros

Reference shared macros from the `macros/` directory:

```sql
-- Date utilities
{{ dates.get_max_available_date() }}
{{ dates.get_split_date() }}

-- Operations (post-hooks)
{{ operations.cluster_table(['date', 'product_id']) }}
{{ operations.grant_permissions() }}

-- Source of streams UDFs
{{ source_of_streams.create_udfs() }}
```

### Modifying an Existing Model

1. Run `dbt compile --select model_name` to see current compiled SQL
2. Make changes
3. Run `dbt compile --select model_name` again to verify compiled SQL
4. Run `dbt run --select model_name` to execute
5. Run `dbt test --select model_name` to validate
6. Check downstream consumers: which OWS services query this table?
7. If column names or types changed, update downstream OWS queries

## Downstream Impact Assessment

Before modifying any model, identify consumers:

| Output Pattern | Primary Consumer | Risk Level |
|---------------|-----------------|------------|
| `STREAMS_BY_*` | ows-analytics | HIGH (80+ endpoints) |
| `METRICS_BY_*` | ows-analytics | HIGH |
| `V_STREAMS_BY_*` | ows-analytics | MEDIUM (views rebuild instantly) |
| Playlist tables | ows-playlist | MEDIUM |
| Chart tables | ows-charts | MEDIUM |
| ML/trending tables | ows-analytics | LOW (isolated endpoints) |

**Safe changes** (no downstream impact):
- Adding new columns to existing tables
- Adding new models
- Modifying incremental logic (same output schema)
- Adding tests

**Breaking changes** (require downstream updates):
- Renaming columns
- Changing column types
- Removing columns
- Renaming tables

## Commands

```bash
# Development
make run                        # Run all models (incremental)
make run_full_refresh           # Full refresh
make run ARGS="--select model"  # Run specific model

# Testing
make test_unit                  # Unit tests
make test_integration           # Integration tests

# Inspection
dbt compile --select model      # Preview compiled SQL
dbt ls --select model           # List matching models
dbt docs generate && dbt docs serve  # Browse documentation

# DAG inspection
dbt ls --select +model_name     # Show upstream dependencies
dbt ls --select model_name+     # Show downstream dependents

# Linting
make lint                       # SQL + Python linting
```

## Common Mistakes to Avoid

- Do NOT use `table` materialization for date-partitioned data -- use `incremental`
- Do NOT forget `transient=true` -- all Snowflake tables must be transient
- Do NOT hardcode database/schema -- use `{{ env_var() }}` or `{{ target }}`
- Do NOT skip schema tests -- at minimum `not_null` on keys and `relationships` for foreign keys
- Do NOT rename or remove columns without checking all downstream OWS queries
- Do NOT use `SELECT *` in final model output -- always list columns explicitly
- Do NOT forget the `is_incremental()` guard in incremental models
- Always run `dbt compile` before `dbt run` to preview SQL
- Always add `description` to new models and columns in YAML