Column tests

Column tests

In Y42 you can use built-in column tests, advanced tests or define your own custom tests. Note that advanced and custom tests can only be created in Code Editor mode.

Built-in column tests

Y42 comes with the following built-in column tests:

  • Unique: Checks that all values in a column are unique. It's especially important for columns that serve as primary keys.

  • Not Null: Checks that every value in a specific column is not null. This is crucial for columns where you expect every record to have a value.

  • Accepted Values: Validates that all the values in a column are within a specific set of accepted values. This helps in maintaining data consistency and prevents the occurrence of unexpected values.

  • Relationship: Ensures that there is a foreign-key relationship between a column in one table and a column in another table. This ensures data integrity and prevents orphan records.

Add a standard column-level tests.

Advanced column tests

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

models/orders.yml

_28
version: 2
_28
_28
models:
_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

Custom tests

Custom tests must be located in the tests folder. This path is configurable in the dbt_project.yml file.

Add a custom column-level test.
OverviewTable tests