Fact & Dim tables
If I want to remove or move something from a table, should I start with the dim table or the fact table, given their connection?
2 answers ( 0 marked as helpful)
Hi Peter!
Great question!
Generally, if you need to remove or move data, you should start with the fact table. This is because fact tables reference dimension tables through foreign keys. Deleting or modifying a dimension record first can break referential integrity if any fact records still rely on it. For example, if a product is deleted from the dimension table before its related sales data is removed from the fact table, those fact records will point to a missing dimension value. To avoid such data integrity issues, clean up or update the fact table first, then make changes to the dimension table.
Hope thish helps.
Best,
Ivan
Great question!
Generally, if you need to remove or move data, you should start with the fact table. This is because fact tables reference dimension tables through foreign keys. Deleting or modifying a dimension record first can break referential integrity if any fact records still rely on it. For example, if a product is deleted from the dimension table before its related sales data is removed from the fact table, those fact records will point to a missing dimension value. To avoid such data integrity issues, clean up or update the fact table first, then make changes to the dimension table.
Hope thish helps.
Best,
Ivan
Thank you for the answer. I follow the rules stating that I should always start from where the foreign key is found.
Additionally, if constraints are applied, I cannot delete data from the primary keys (dim) without first deleting it from the foreign keys (fact).
Additionally, if constraints are applied, I cannot delete data from the primary keys (dim) without first deleting it from the foreign keys (fact).