In a star schema, are foreign key constraints between facts and dimensions neccessary?

Answer

Most data-warehouses (DW) do not have foreign keys implemented as constraints, because:

  • In general, foreign key constraint would trigger on: an insert into a fact table, any key-updates, and a delete from a dimension table.

  • During loading, indexes and constraints are dropped to speed-up the loading process, data integrity is enforced by the ETL application.

  • Once tables are loaded, DW is essentially read-only -- the constraint does not trigger on reads.

  • Any required indexes are re-built after the loading.

  • Deleting in a DW is a controlled process. Before deleting rows from dimensions, fact tables are queried for keys of rows to be deleted -- deleting is allowed only if those keys do not exists in any of fact tables.

All data warehouse Questions

Ask your interview questions on data-warehouse

Write Your comment or Questions if you want the answers on data-warehouse from data-warehouse Experts
Name* :
Email Id* :
Mob no* :
Question
Or
Comment* :
 





Disclimer: PCDS.CO.IN not responsible for any content, information, data or any feature of website. If you are using this website then its your own responsibility to understand the content of the website

--------- Tutorials ---