Am currently creating a datawarehouse in Azure Synapse, however Synapse does not allow for the creation of foreign keys. This is vital for referential integrity between the fact and dimension table. Does anyone have any suggestions as to what the alternatives are in synapse to enforce a PK FK relationship?
2 Answers
I searched about this topic and I found that the focus of Synapse is performance and not integrity reinforcement. We can create primary keys and structure the star schema with fact, dimensions and code join tables between them.
It was confused me too until I make this tutorial and read this carefully.
In a star schema any referential integrity should be enforced within the ETL tool used to load the data and not in the DB itself. Some DBs support logical FKs that can help with query execution plans but they should never be physicalised
Related