How validate one column which values depend on another one for DBT

Question

I want to add test in my model to validate the values of subtype which depends on values of type column.

version: 2
models:
    - name: my_table
      description: my_table
      columns:
          - name: TYPE
            description: "-"
            tests:
                - accepted_values:
                      values: ["1", "2", "3", "4"]
                      severity: error

Now, the idea is validate subtype , where ,for example, if the type is 1, the subtype only could be 3 or 4, if the type is 2, the allowed values for subtype are 1, 5, etc. It is possible, thanks in advance.

Answer 1

You should be able to add different tests to subtype, so that when type is equal to a value, the accepted_values of subtype should be another array of values.

So, for instance:

version: 2
models:
    - name: my_table
      description: my_table
      columns:
          - name: TYPE
            description: "-"
            tests:
                - accepted_values:
                      values: ["1", "2", "3", "4"]
                      severity: error
          - name: SUBTYPE
            description: ""
            tests:
                - accepted_values:
                      name: subtype_accepted_values_when_type_equals_1
                      values: ["3", "4"]
                      config:
                          where: "type = 1"
                - accepted_values:
                      name: subtype_accepted_values_when_type_equals_2
                      values: ["1", "5"]
                      config:
                          where: "type = 2"