SQL and dbt Review Checklist

# SQL and dbt review: [pull request or model]

## Change context

- **Business change:** [what should behave differently?]
- **Models changed:** [list]
- **Downstream consumers:** [models, exposures, dashboards, APIs]
- **Expected grain:** [one row per…]
- **Breaking change:** [yes / no / uncertain]
- **Deployment plan:** [normal / backfill / dual run / migration]

## Correctness and grain

- [ ] The requested business behavior is stated and testable.
- [ ] Every changed model declares what one row represents.
- [ ] Primary or surrogate keys match the declared grain.
- [ ] Aggregations group by every intended dimension and no accidental dimension.
- [ ] Window partitions and ordering match the business rule.
- [ ] Ties have a deterministic rule where one row must be selected.
- [ ] CASE expressions cover null, unknown, and unexpected values deliberately.

## Joins

- [ ] Join keys represent the intended relationship.
- [ ] One-to-many and many-to-many relationships are identified.
- [ ] Row counts are checked before and after joins.
- [ ] Amounts are aggregated before a join when the downstream grain requires it.
- [ ] LEFT vs. INNER JOIN behavior is intentional for unmatched records.
- [ ] DISTINCT is not hiding an unresolved grain or fan-out problem.

## Metrics and dates

- [ ] Population, exclusions, numerator, denominator, units, and rounding are documented.
- [ ] Count vs. count distinct matches the entity being measured.
- [ ] The reporting timestamp and timezone are explicit.
- [ ] Period boundaries match fiscal or calendar requirements.
- [ ] Late-arriving records, cancellations, refunds, and reversals have defined treatment.
- [ ] Ratios handle zero and null denominators safely.

## dbt structure

- [ ] source(), ref(), and model layering follow repository conventions.
- [ ] Staging models rename, cast, and lightly clean without embedding report logic.
- [ ] Intermediate models represent reusable transformations.
- [ ] Facts, dimensions, and marts have explicit responsibilities.
- [ ] Materialization matches data volume, latency, and rebuild cost.
- [ ] Incremental predicates handle updates, late records, and full refreshes safely.

## Tests and contracts

- [ ] Unique and not_null tests protect the model grain.
- [ ] Relationship tests cover important foreign keys.
- [ ] Accepted values or business-rule tests protect meaningful categories.
- [ ] New failure modes have specific tests, not only generic row-count checks.
- [ ] Contract/type changes are compatible with downstream consumers.
- [ ] Test severity and exception handling are intentional and documented.

## Documentation and lineage

- [ ] Model description explains business purpose and grain.
- [ ] Important columns, metrics, units, and date rules are documented.
- [ ] Sources include freshness expectations where promised.
- [ ] Exposures or owners are updated for important downstream use.
- [ ] Comments explain non-obvious decisions rather than restating syntax.

## Performance and cost

- [ ] Filters and column selection reduce unnecessary scans.
- [ ] Repeated expensive logic is reused at the correct layer.
- [ ] Join order, fan-out, and partition pruning were considered.
- [ ] The change was checked with representative data volume or an explain plan where appropriate.
- [ ] Any cost/latency trade-off is recorded.

## Release safety

- [ ] CI selection covers the changed models and relevant downstream nodes.
- [ ] Backfill scope and runtime are known.
- [ ] Existing dashboards or extracts will not silently change grain.
- [ ] Migration, deprecation, or dual-run steps are documented when needed.
- [ ] Observability and rollback are defined for a high-risk change.

## Review findings

### Blocking

- **Evidence:** [file/model/query result]
- **Risk:** [what can become incorrect or unsafe]
- **Recommendation:** [smallest useful change]
- **Verification:** [test or result that proves it]

### Non-blocking

- [maintainability, clarity, or follow-up item]

## Decision

- **Status:** [approve / request changes / discuss]
- **Reviewer:** [name]
- **Date:** [YYYY-MM-DD]
- **Verification completed:** [commands, tests, or reviewed outputs]