Is it true that we never enable foreign key constraints in the dimensional model of a 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 the picture, even though ETL is responsible enough through proper dependency.
Late arriving dimension from source
few records could not pass data quality checks and routed to the 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 the target table load session accidentally.