# Checklist: Adding a New dbt Model

## 1. Planning

- [ ] Identify the correct package for the data domain (streams, metrics, playlists, etc.)
- [ ] Determine materialization: incremental (`*_DAILY`), table (`*_ROLLUP`), or view (`V_*`)
- [ ] Name the model following convention: `<METRIC>_BY_<DIMENSIONS>_<GRANULARITY>`
- [ ] Identify upstream dependencies (which source or ref models are needed)
- [ ] Identify downstream consumers (which OWS service will query this table)

## 2. Source / Upstream

- [ ] Verify source tables exist in `base/` source definitions
- [ ] If new source: add to sources.yml with freshness tests
- [ ] If new staging model needed: create in `base/` with `stg_` prefix

## 3. Model Implementation

- [ ] Create SQL file in the correct package directory
- [ ] Add `config()` block with appropriate materialization:
  - Incremental: `materialized='incremental'`, `transient=true`, `unique_key=[...]`, `incremental_strategy='delete+insert'`
  - Table: `materialized='table'`, `transient=true`
  - View: `materialized='view'`
- [ ] Use CTEs for readability (no nested subqueries)
- [ ] List columns explicitly (no `SELECT *` in final output)
- [ ] For incremental: add `{% if is_incremental() %}` filter with `DAYS_TO_RECALCULATE`
- [ ] Use `{{ ref() }}` for all model references and `{{ source() }}` for raw tables
- [ ] Use `{{ env_var() }}` or `{{ target }}` for database/schema (no hardcoding)

## 4. Schema Tests

- [ ] Add model entry to `schema.yml` in the same directory
- [ ] Add `description` for the model
- [ ] Add `not_null` test on all primary key columns
- [ ] Add `relationships` test for foreign keys
- [ ] Add at least one `dbt_expectations` data quality test
- [ ] Add `description` for columns with business logic

## 5. Macros (if needed)

- [ ] Check existing macros before writing new ones (`macros/dates/`, `macros/operations/`)
- [ ] If new macro: add to appropriate subdirectory with Jinja comment documentation
- [ ] If post-hook needed (cluster, grant): add to model config

## 6. Local Testing

- [ ] Compile and preview SQL: `dbt compile --select model_name`
- [ ] Run the model: `dbt run --select model_name`
- [ ] Run tests: `dbt test --select model_name`
- [ ] Verify row counts are reasonable
- [ ] For incremental: test with `--full-refresh` flag, then test incremental run

## 7. Integration Verification

- [ ] Run `make test_integration ARGS="--select model_name"`
- [ ] Check the DAG: `dbt ls --select +model_name` (upstream) and `model_name+` (downstream)
- [ ] Verify no circular dependencies

## 8. Documentation

- [ ] `dbt docs generate` succeeds
- [ ] Model appears correctly in the DAG visualization
- [ ] Description and column docs are present

## 9. Downstream Coordination

- [ ] Notify OWS service team if new table is ready for consumption
- [ ] If modifying an existing table's schema: update OWS queries first (backward-compatible)
- [ ] If this replaces an existing table: plan migration with deprecation period

## Deployment Order

1. dbt-analytics model (this change) -- deployed via Jenkins dbt run
2. OWS service (if new endpoint) -- add query for the new table
3. GraphQL subgraph (if new field) -- add connector and resolver
4. Frontend (if visible to users) -- add UI component

## Model Template

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

{#
  Model: <METRIC>_BY_<DIMENSIONS>_<GRANULARITY>
  Package: <package_name>
  Description: <what this model produces>
  Consumed by: <ows-service>
#}

WITH source AS (
    SELECT
        date,
        entity_id,
        dimension_1,
        metric_value
    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,
        SUM(metric_value) AS total_metric
    FROM source
    GROUP BY 1, 2, 3
)

SELECT * FROM aggregated
```

## Schema Test Template

```yaml
models:
  - name: <model_name>
    description: "<description>"
    columns:
      - name: date
        description: "Aggregation date"
        tests:
          - not_null
      - name: entity_id
        description: "Entity identifier"
        tests:
          - not_null
          - relationships:
              to: ref('dim_entity')
              field: entity_id
      - name: total_metric
        description: "Aggregated metric value"
        tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
```