# dbt-analytics Patterns

## Project Organization

14 local dbt packages organized by data domain. Each package is self-contained with its own models, schemas, and tests. Cross-package references use `{{ ref('package_name', 'model_name') }}`.

## Naming Conventions

### Tables

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

| Component | Convention | Examples |
|-----------|-----------|----------|
| Metric | Uppercase, describes the measure | `STREAMS`, `METRICS`, `DOWNLOADS` |
| Dimensions | Uppercase, underscore-separated | `PRODUCT_COUNTRY_FEED_DISTRIBUTOR` |
| Granularity | `DAILY` (incremental) or `ROLLUP` (full refresh) | `DAILY`, `ROLLUP` |
| Views | Prefix with `V_` | `V_STREAMS_BY_SOUND_RECORDING` |

### Models (SQL files)

- Lowercase with underscores: `streams_by_product_country_feed_distributor_daily.sql`
- Staging models prefixed with `stg_`: `stg_fact_streams.sql`
- Intermediate models prefixed with `int_`: `int_stream_classification.sql`

### Macros

- Lowercase with underscores: `get_max_available_date.sql`, `grant_permissions.sql`
- Organized by domain in subdirectories: `macros/dates/`, `macros/operations/`

## Materialization Rules

| Pattern | Materialization | Config | Use Case |
|---------|----------------|--------|----------|
| Date-partitioned aggregations | `incremental` | `delete+insert`, `transient=true` | Daily stream/download counts |
| Pre-aggregated summaries | `table` | `transient=true` | Rollup tables, dimension tables |
| Real-time lookups | `view` | -- | Views referenced by OWS services |
| One-time seeds | `seed` | -- | Static reference data |

**All physical tables MUST use `transient=true`** to disable Snowflake time travel and reduce storage cost.

## Incremental Strategy

Standard incremental pattern with rolling refresh:

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

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

- Default refresh window: 3 days (`DAYS_TO_RECALCULATE`)
- Strategy: `delete+insert` (not `merge`) for performance on large tables
- Unique key must include the date column plus all dimension columns

## Testing Requirements

### Minimum Tests per Model

Every model must have:
1. `not_null` on all primary key columns
2. `relationships` test for foreign keys referencing dimension tables
3. At least one data quality test (e.g., `dbt_expectations.expect_column_values_to_be_between`)

### Test Organization

Schema tests go in YAML files alongside models:
```
packages/<package>/models/
├── my_model.sql
└── schema.yml        # Contains tests for my_model
```

Data tests (custom SQL assertions) go in the `tests/` directory.

### Coverage Target

80%+ of models must have schema tests. All new models must include tests.

## SQL Style

- **Uppercase keywords**: `SELECT`, `FROM`, `WHERE`, `GROUP BY`
- **Lowercase identifiers**: column names, aliases
- **Trailing commas**: allowed
- **CTE-first style**: Use CTEs (`WITH`) instead of nested subqueries
- **Explicit column lists**: Never use `SELECT *` in final models
- **Jinja whitespace control**: Use `{%- ... -%}` to avoid extra blank lines
- **SQL formatting**: sqlfmt (shandy-sqlfmt 0.24.0 with jinjafmt)

```sql
WITH source AS (
    SELECT
        date,
        product_id,
        country,
        feed_id,
        SUM(streams) AS total_streams
    FROM {{ ref('upstream_model') }}
    GROUP BY 1, 2, 3, 4
),

filtered AS (
    SELECT *
    FROM source
    WHERE total_streams > 0
)

SELECT * FROM filtered
```

## Macro Conventions

- Macros accept keyword arguments with defaults
- Document macros with a Jinja comment block at the top
- Place macros in the appropriate subdirectory under `macros/`
- Operational macros (cluster, grant, unload) are typically used as post-hooks

```sql
{# Cluster a table on the specified columns for query performance #}
{% macro cluster_table(columns) %}
    ALTER TABLE {{ this }} CLUSTER BY ({{ columns | join(', ') }})
{% endmacro %}
```

## Source Definitions

All raw tables are defined as sources in `base/`:
- Use `{{ source('source_name', 'table_name') }}` to reference raw tables
- Never reference raw tables directly by name
- Source freshness tests track data pipeline health

## Environment Configuration

- Database and schema are environment-specific: use `{{ env_var() }}` or `{{ target }}`
- Never hardcode the Snowflake account, database, or schema
- The `profiles/` directory maps target names to Snowflake credentials
- Two auth methods: password (`sf_pswd_auth`) and SSH key pair (`sf_ssh_auth`)

## Dependency Graph Discipline

- Models should only reference their direct upstream dependencies
- Avoid circular dependencies between packages
- Use `dbt ls --select +model_name` to inspect the upstream DAG
- Use `dbt ls --select model_name+` to inspect the downstream DAG

## Post-Hooks

All models automatically run:
- `utils.grant_permissions()` -- Ensures proper access control after every model build

On-run-start hooks:
- `utils.create_udfs()` -- Creates source-of-streams UDFs
- `utils.create_playlist_udfs()` -- Creates playlist-specific UDFs

## Documentation

- Every model must have a `description` in its YAML schema file
- Every column with business logic should have a `description`
- Use `dbt docs generate` to build the documentation site
- Review the DAG visualization for unexpected dependencies