Skip to main content

Can I test the uniqueness of two columns?

Yes, There's a few different options.

Consider an orders table that contains records from multiple countries, and the combination of ID and country code is unique:

order_idcountry_code
1AU
2AU
......
1US
2US
......

Here are some approaches:

1. Create a unique key in the model and test that#

models/orders.sql

select  country_code || '-' || order_id as surrogate_key,  ...
models/orders.yml
version: 2
models:  - name: orders    columns:      - name: surrogate_key        tests:          - unique

2. Test an expression#

models/orders.yml
version: 2
models:  - name: orders    tests:      - unique:          column_name: "(country_code || '-' || order_id)"

3. Use the dbt_utils.unique_combination_of_columns test#

This is especially useful for large datasets since it is more performant. Check out the docs on packages for more information.

models/orders.yml
version: 2
models:  - name: orders    tests:      - dbt_utils.unique_combination_of_columns:          combination_of_columns:            - country_code            - order_id