How to Use accepted_values Test in dbt for Data Validation

In dbt, use the accepted_values test to check if a column contains only specific allowed values by specifying the values list. Add this test in your schema.yml file under the column you want to validate to catch unexpected or invalid data entries.

Syntax

The accepted_values test in dbt is defined in your schema.yml file under a model's columns. You specify the column name and a list of allowed values using the values key. Optionally, you can provide a quote key to control quoting behavior.

• column: The column to test.
• values: A list of allowed values for that column.
• quote: Optional boolean to quote values in SQL (default is true).

yaml

version: 2
models:
  - name: your_model_name
    columns:
      - name: your_column_name
        tests:
          - accepted_values:
              values: ['allowed_value1', 'allowed_value2', 'allowed_value3']
              quote: true

Example

This example shows how to apply the accepted_values test on a column named status in a model called orders. It ensures that the status column only contains the values 'pending', 'shipped', or 'delivered'.

yaml

version: 2
models:
  - name: orders
    columns:
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'shipped', 'delivered']

Output

Running with dbt=1.4.6
Found 1 model, 1 test, 0 snapshots, 0 analyses, 0 macros, 0 operations, 0 seed files, 0 sources

14:22:10 | Concurrency: 1 threads (target='dev')
14:22:10 | 
14:22:11 | 1 of 1 START test accepted_values_status_orders......................... [RUN]
14:22:12 | 1 of 1 PASS accepted_values_status_orders................................. [PASS=10 WARN=0 ERROR=0 SKIP=0]

Completed successfully

Common Pitfalls

Common mistakes when using accepted_values include:

• Not matching the exact case or format of values in your data, causing false test failures.
• Forgetting to include all valid values in the values list.
• Using incorrect YAML indentation or syntax, which breaks dbt parsing.
• Not running dbt test after adding the test to see results.

Always double-check your allowed values and run tests regularly.

yaml

version: 2
models:
  - name: users
    columns:
      - name: user_type
        tests:
          # Wrong: missing 'guest' value
          - accepted_values:
              values: ['admin', 'member']

# Correct:
version: 2
models:
  - name: users
    columns:
      - name: user_type
        tests:
          - accepted_values:
              values: ['admin', 'member', 'guest']

Quick Reference

Parameter	Description	Required	Example
column	Name of the column to test	Yes	status
values	List of allowed values for the column	Yes	['pending', 'shipped', 'delivered']
quote	Whether to quote values in SQL (true/false)	No (default true)	true

Key Takeaways

Use the accepted_values test in schema.yml to restrict column values to a defined list.

Always list all valid values exactly as they appear in your data to avoid false failures.

Run dbt test after adding accepted_values to validate your data quality.

Watch YAML syntax carefully to prevent parsing errors in dbt.

The accepted_values test helps catch unexpected or invalid data entries early.