Table tests

Y42 comes with the following built-in table tests:

Expression is true: Asserts that a valid SQL expression is true for all records. This is useful for checking integrity across columns. This implements the dbt_utils.expression_is_true (opens in a new tab) test under the hood.
Recency: Asserts that a timestamp column in the reference model contains data that is at least as recent as the defined date interval. This implements the dbt_utils.recency (opens in a new tab) test under the hood.
Mutually exclusive ranges: Asserts that for a given lower_bound_column and upper_bound_column, the ranges between the lower and upper bounds do not overlap with the ranges of another row. This implements the dbt_utils.mutually_exclusive_ranges (opens in a new tab) test under the hood.

Add a table test

Advanced tests

You can declare additional tests that go beyond the built-in tests provided, in a model's .yml file.

models/orders.yml


_28version: 2
_28
_28models:
_28  - name: orders
_28		tests: 
_28      - dbt_utils.fewer_rows_than: # multi-table test
_28          compare_model: ref('other_table_name')
_28      - dbt_expectations.expect_table_row_count_to_be_between: # table-level test
_28          min_value: 1 
_28          max_value: 4
_28			- dbt_utils.recency:
_28          datepart: day
_28          field: created_at
_28          interval: 1
_28    columns:
_28      - name: order_id
_28        tests: # column-level tests
_28          - unique
_28          - not_null
_28      - name: status
_28        tests:
_28          - accepted_values: # column-level test
_28              values: ['placed', 'shipped', 'completed', 'returned']
_28      - name: customer_id
_28        tests:
_28          - relationships: # multi-table test
_28              to: ref('customers')
_28              field: id

Column tests Anomaly detection