How it Works

The expect_column_values_to_be_in_type_list test from the dbt-expectations package validates that a column's data type is one of a specified list of acceptable types. Unlike expect_column_values_to_be_of_type (which requires an exact match), this test allows multiple compatible types — useful for handling database-specific type variants.

Steps and Conditions

1. Column Selection: Identify the column to evaluate.

2. Define Type List: Provide the list of acceptable types using column_type_list.

3. Execution: The actual column type is retrieved from metadata and checked against the list.

4. Outcome: Pass if the type is in the list; fail if it is not.

Example Usage: Cross-platform Analytics

A team running models across BigQuery and Snowflake wants to ensure their amount column is always a numeric type, but the exact type name differs between platforms.

models:
  - name: payments
    columns:
      - name: amount
        tests:
          - dbt_expectations.expect_column_values_to_be_in_type_list:
              column_type_list: ["numeric", "float64", "number", "decimal"

models:
  - name: payments
    columns:
      - name: amount
        tests:
          - dbt_expectations.expect_column_values_to_be_in_type_list:
              column_type_list: ["numeric", "float64", "number", "decimal"

models:
  - name: payments
    columns:
      - name: amount
        tests:
          - dbt_expectations.expect_column_values_to_be_in_type_list:
              column_type_list: ["numeric", "float64", "number", "decimal"

This ensures the column is always numeric regardless of which database platform is running the model.