dbt-expectations Test Suite AI Prompt

Implement advanced data quality tests using the dbt-expectations package.

Model: {{model_name}}
Quality requirements: {{requirements}} (SLA, business rules, statistical thresholds)

1. Install dbt-expectations:
   packages.yml:
   packages:
     - package: calogica/dbt_expectations
       version: [">=0.10.0", "<0.11.0"]

2. Column value tests:

   Numeric range:
   - dbt_expectations.expect_column_values_to_be_between:
       min_value: 0
       max_value: 1000000
       strictly: false

   Non-negative:
   - dbt_expectations.expect_column_values_to_be_positive:
       severity: error

   String pattern (regex):
   - dbt_expectations.expect_column_values_to_match_regex:
       regex: '^[A-Z]{2}-[0-9]{6}$'

   Date range:
   - dbt_expectations.expect_column_values_to_be_of_type:
       column_type: date

3. Table-level tests:

   Row count bounds:
   - dbt_expectations.expect_table_row_count_to_be_between:
       min_value: 1000
       max_value: 10000000

   Column count:
   - dbt_expectations.expect_table_column_count_to_equal:
       value: 15

   Schema completeness:
   - dbt_expectations.expect_table_columns_to_contain_set:
       column_list: ['order_id', 'customer_id', 'order_amount_usd', 'order_date']

4. Distribution tests:

   Proportion of null values:
   - dbt_expectations.expect_column_proportion_of_unique_values_to_be_between:
       min_value: 0.95
       max_value: 1.0

   Mean value range (catches data quality regressions):
   - dbt_expectations.expect_column_mean_to_be_between:
       min_value: 50
       max_value: 500

5. Cross-column tests:
   - dbt_expectations.expect_column_pair_values_A_to_be_greater_than_B:
       column_A: total_amount
       column_B: discount_amount
       or_equal: true

Return: complete schema.yml with dbt-expectations tests, severity assignments, and interpretation of each test's business meaning.