How to use dbt_utils.unique_combination_of_columns on DBT

Question

How could anyone help me how to use this DBT command "dbt_utils.unique_combination_of_columns" I plan to test 2 different column on my DBT unfortunately (user_id & ticker_symbol) I'm encountering an error upon running dbt test. Your response is highly appreciated. Thank you so much.

Screen of my yaml file

Screenshot of my .sql

Error encountered upon running DBT test:

Answer 1

This dbt_utils generic test is thought to be ran on top of a model, not on a particular column, so you'd need to place it under the model name and not to a certain column.

So, instead of what you did in the screenshot, you could do the following:

# this is your schema.yml

version: 2

models:
  
  - name: mrt_test
    description: ""
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - user_id
            - ticker_symbol
    columns:
      - name: (...)
      

Answer 2

An alternative to this is to create a single-column surrogate key in your table as detailed in this and this dbt blog posts This creates a primary key for your table using the dbt_utils package and the unique/not_null tests innate to dbt can be used on this field. There are tradeoffs to adding an additional field to every table, but it provides ease and uniformity of testing across all models.

Your model would look something like this:

WITH final AS (
....
)

SELECT 
  md5({{ dbt_utils.generate_surrogate_key([
    'column1',
    'column2']) }}) AS surrogate_key
  ,*
FROM final