dbt test: expect_column_values_to_be_in_type_list

This page explains the expect_column_values_to_be_in_type_list test from the dbt-expectations package. This test is used to ensure that the data types of the values in a specific column conform to a list of expected data types. It is an effective method to validate data types and prevent data type mismatches that could lead to errors in data processing or analysis.

How it Works

The expect_column_values_to_be_in_type_list test checks that every value in a given column matches one of the specified data types in the list. This validation is crucial for ensuring that data stored in a database maintains its integrity, especially when data from various sources might have inconsistent types.

Steps and Conditions:

1. Column and Type List Selection: The specific column to be tested is identified, and a list of acceptable data types is defined.
2. Data Type Verification: For each entry in the column, the test checks whether the data type of the value is among those listed in the column_type_list.
3. Outcome:
   • Pass: If all the column entries match one of the specified acceptable data types, the test passes.
   • Fail: If any entry in the column does not match the specified data types, the test fails, indicating a data type discrepancy.

Example Usage: Fintech

In a Fintech application, accurately tracking transaction dates and times is critical for reporting and analytics. Let's consider a transactions table with a transaction_datetime column intended to capture the exact time each transaction was processed.

models:
  - name: transactions
    columns:
        - name: transaction_datetime
          tests:
            - dbt_expectations.expect_column_values_to_be_in_type_list:
                column_type_list: ['datetime']

In this scenario, the expect_column_values_to_be_in_type_list test ensures that every entry in the transaction_datetime column is of the datetime data type. This validation helps in maintaining the accuracy and consistency of transaction data, which is essential for time-sensitive financial reporting and analysis.