Constraints for PK and FK

[romiof]

Hello guys,

I'm start to use dbt to replace a old SSIS implementation.
But for now, our DW is going to stay on-premises, with SQL Server.

Our data warehouse is modeled in Dimensional form, using star schema.

What is the best approach to do ALTER TABLE foo ADD CONSTRAINTS ... ?
This is still valid ?

They are useful, for instance, when loading data in Power BI.
All relations are made automatically, helping our analysts who aren't from IT field.

[matsonj]

Have you seen these docs?

https://docs.getdbt.com/reference/resource-configs/mssql-configs#indices

[romiof]

Hello! Thanks for the link.

Maybe I'm missing something, but is possible to create constraints for table references (PK / FK) using the indices macro ?

[matsonj]

Post hooks will conflict with the existing macros for adding indexes, so you need to use the config blocks add constraints via indexes. For analytical databases, it is a better pattern to enforce FK / PK relationships with tests than with indexes. So adding relationship tests + uniqueness tests as part of your pipeline is the recommended pattern.