Recommended approach for testing composite foreign keys in dbt

Help
,
1

Hi everyone,

I’m working on a dbt project where I need to validate referential integrity between a fact model and several dimension models.

For some relationships I can use the standard relationships test because the join is based on a single column. However, for other relationships the join is based on multiple columns (composite business keys).

I know one common approach is to create a surrogate key or concatenated key in both models and then use a standard relationships test. Unfortunately, in my case I cannot modify either model to add additional columns.

Given this constraint, what would be considered the recommended approach in dbt?

  • Use singular tests with custom SQL joins?
  • Create a custom generic test that accepts multiple columns?
  • Is there any existing package or community solution for composite relationship tests?

I would appreciate any recommendations or examples from similar implementations.

Thanks!

2

I think the dbt_utils package’s unique_combination_of_columns generic test might be helpful for you. Here’s a link to the dbt package on hub.getdbt.com (dbt package hub).