DBT Test configuration for particular scenario

Question

Hello Could anyone help me how to simulate this scenario. Example I want to validate these 3 fields on my table "symbol_type", "symbol_subtype", "taker_symbol" and return unique combination/result.

I tried to use this command, however Its not working properly on my test. Not sure if this is the correct syntax to simulate my scenario. Your response is highly appreciated.

Expected Result: These 3 fields should return my unique combination using DBT commands.

Answer 1

I'd recommend to either:

1. use the generate_surrogate_key (docs) macro in the model, or
2. use the dbt_utils.unique_combination_of_columns (docs) generic test.

For the first case, you would need to define the following in the model:

select
  {{- dbt_utils.generate_surrogate_key(['symbol_type', 'symbol_subtype', 'taker_symbol']) }} as hashed_key_,
  (...)
from your_model

This would create a hashed value of the three columns. You could then use a unique test in your YAML file.

For the second case, you would only need to add the generic test in your YAML file as follows:

# your model's YAML file
- name: your_model_name
    description: ""
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - symbol_type
            - symbol_subtype
            - taker_symbol

Both these approaches will let you check whether the combination of the three columns is unique over the whole model's output.