dbt Test Coverage Plan AI Prompt

Design a comprehensive dbt test suite for this model or project.

Model: {{model_name}}
Grain: {{grain}} (one row per order, one row per customer per day, etc.)
Key columns: {{key_columns}}
Business rules: {{business_rules}}

1. Generic tests (schema.yml):

   Every model must have at minimum:
   - unique: on the primary key or unique identifier
   - not_null: on all columns that must never be null (primary keys, critical FKs, metric numerators)

   Additional recommended generics:
   - accepted_values: on status, type, or enum columns
     values: ['pending', 'completed', 'refunded']
   - relationships: foreign key integrity check
     to: ref('dim_customers'), field: customer_id

2. Singular tests (tests/ folder):
   Write SQL assertions that return 0 rows when the test passes.

   Row count validation:
   -- Test: fct_orders should never have more rows than the source
   SELECT COUNT(*) > (SELECT COUNT(*) FROM {{ source('app', 'orders') }}) AS has_more_rows_than_source
   WHERE has_more_rows_than_source

   Metric range check:
   -- Test: order_amount should be positive
   SELECT * FROM {{ ref('fct_orders') }}
   WHERE order_amount <= 0

   Referential integrity:
   SELECT o.customer_id
   FROM {{ ref('fct_orders') }} o
   LEFT JOIN {{ ref('dim_customers') }} c ON o.customer_id = c.customer_id
   WHERE c.customer_id IS NULL

3. dbt-utils tests:
   - expression_is_true: assert a SQL expression is true for all rows
   - recency: warn if the most recent record is older than N hours
   - equal_rowcount: two models have the same row count
   - mutually_exclusive_ranges: non-overlapping date ranges (for SCDs)

4. Severity and alert routing:
   - warn: flag anomalies without blocking CI (non-critical quality issues)
   - error: block CI and deployment (data integrity failures)
   - config error_if: '>0', warn_if: '>100'

5. Test organization by layer:
   - Staging: focus on not_null, unique, accepted_values on raw source columns
   - Marts: focus on business rule tests, metric range checks, referential integrity

Return: schema.yml test block for the model, singular test SQLs for critical business rules, dbt-utils test recommendations, and severity assignments.