Do Data Warehouse standards allow foreign key constraints at a dimensional model?
Is it true that we never enable foreign key constraints in dimensional model of data warehouse? If yes, then what is the rationale behind that?
As per my research:
Some experts told me in a dimensional model, FK will never be enabled, and it is the responsibility of the ETL process to ensure consistency and integrity.
Data integrity issues may come into picture, even though ETL is responsible enough through proper dependency.
Late arriving dimension from source
few records could not pass data quality check and routed to error table.
intermediate tables are not populated due to batch load failure, and proper restart or recover steps are not followed. Someone restarted the last session to load data into the facts table while some of the dimensions are yet to be populated,
primary key constraints will help me to avoid duplicate record population if data in intermediate tables are getting processed one more time due to re triggering target table load session accidentally.
What issues do you see by enabling FK constraints in dimensional model?
Enjoy great content like this and a lot more !
Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds