07 May 20 · rajneesh4u · #general ·   Bookmark   ×

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.

Examples:

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?

rajneesh4u

posted on 07 May 20

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




npackMay 08 12:16

Performance!  Data Warehouses ingest millions of rows everyday.  And having constraints like Primary Key / Foreign Key is a real killer. It hurts the load performance and also causes hectic maintenance, to constantly monitor and respond to failures. Its good and bad though)

Remember, all the modern data warehouse platforms (Snowflake / Bigquery), do not even have an option to create constrains / enforce them on the tables

rajneesh4uMay 08 03:24

Thank you !!!


Community Software by Hittly